summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/func_test.test
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/func_test.test
parentInitial commit. (diff)
downloadmariadb-upstream.tar.xz
mariadb-upstream.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/func_test.test')
-rw-r--r--mysql-test/main/func_test.test237
1 files changed, 237 insertions, 0 deletions
diff --git a/mysql-test/main/func_test.test b/mysql-test/main/func_test.test
new file mode 100644
index 00000000..c8206d34
--- /dev/null
+++ b/mysql-test/main/func_test.test
@@ -0,0 +1,237 @@
+--disable_warnings
+drop table if exists t1,t2;
+--enable_warnings
+#
+# Testing of comparison functions
+#
+select 0=0,1>0,1>=1,1<0,1<=0,1!=0,strcmp("abc","abcd"),strcmp("b","a"),strcmp("a","a") ;
+select "a"<"b","a"<="b","b">="a","b">"a","a"="A","a"<>"b";
+#enable after fix MDEV-28535
+--disable_view_protocol
+select "a "="A", "A "="a", "a " <= "A b";
+--enable_view_protocol
+select "abc" like "a%", "abc" not like "%d%", "a%" like "a\%","abc%" like "a%\%","abcd" like "a%b_%d", "a" like "%%a","abcde" like "a%_e","abc" like "abc%";
+select "a" like "%%b","a" like "%%ab","ab" like "a\%", "ab" like "_", "ab" like "ab_", "abc" like "%_d", "abc" like "abc%d";
+select '?' like '|%', '?' like '|%' ESCAPE '|', '%' like '|%', '%' like '|%' ESCAPE '|', '%' like '%';
+select 'abc' like '%c','abcabc' like '%c', "ab" like "", "ab" like "a", "ab" like "ab";
+#enable after fix MDEV-29601
+--disable_service_connection
+select "Det här är svenska" regexp "h[[:alpha:]]+r", "aba" regexp "^(a|b)*$";
+--enable_service_connection
+select "aba" regexp concat("^","a");
+select !0,NOT 0=1,!(0=0),1 AND 1,1 && 0,0 OR 1,1 || NULL, 1=1 or 1=1 and 1=0;
+select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between "max" and "my" and 3=3;
+#enable after fix MDEV-28535
+--disable_view_protocol
+select 'b' between 'a' and 'c', 'B' between 'a' and 'c';
+--enable_view_protocol
+select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0);
+select -1.49 or -1.49,0.6 or 0.6;
+select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1;
+explain extended select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1;
+select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL;
+select 1 like 2 xor 2 like 1;
+select 10 % 7, 10 mod 7, 10 div 3;
+explain extended select 10 % 7, 10 mod 7, 10 div 3;
+select 18446744073709551615, 18446744073709551615 DIV 1, 18446744073709551615 DIV 2;
+explain extended select (1 << 64)-1, ((1 << 64)-1) DIV 1, ((1 << 64)-1) DIV 2;
+
+create table t1 (a int);
+insert t1 values (1);
+select * from t1 where 1 xor 1;
+explain extended select * from t1 where 1 xor 1;
+select - a from t1;
+explain extended select - a from t1;
+drop table t1;
+
+#
+# Coercibility
+#
+select _koi8r'a' = _koi8r'A';
+select _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci;
+explain extended select _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci;
+select _koi8r'a' = _koi8r'A' COLLATE koi8r_bin;
+select _koi8r'a' COLLATE koi8r_general_ci = _koi8r'A';
+select _koi8r'a' COLLATE koi8r_bin = _koi8r'A';
+--error 1267
+select _koi8r'a' COLLATE koi8r_bin = _koi8r'A' COLLATE koi8r_general_ci;
+--error 1267
+select _koi8r'a' = _latin1'A';
+
+select strcmp(_koi8r'a', _koi8r'A');
+select strcmp(_koi8r'a', _koi8r'A' COLLATE koi8r_general_ci);
+select strcmp(_koi8r'a', _koi8r'A' COLLATE koi8r_bin);
+select strcmp(_koi8r'a' COLLATE koi8r_general_ci, _koi8r'A');
+select strcmp(_koi8r'a' COLLATE koi8r_bin, _koi8r'A');
+--error 1267
+select strcmp(_koi8r'a' COLLATE koi8r_general_ci, _koi8r'A' COLLATE koi8r_bin);
+--error 1267
+select strcmp(_koi8r'a', _latin1'A');
+
+select _koi8r'a' LIKE _koi8r'A';
+select _koi8r'a' LIKE _koi8r'A' COLLATE koi8r_general_ci;
+select _koi8r'a' LIKE _koi8r'A' COLLATE koi8r_bin;
+select _koi8r'a' COLLATE koi8r_general_ci LIKE _koi8r'A';
+select _koi8r'a' COLLATE koi8r_bin LIKE _koi8r'A';
+--error 1267
+select _koi8r'a' COLLATE koi8r_general_ci LIKE _koi8r'A' COLLATE koi8r_bin;
+--error 1267
+select _koi8r'a' LIKE _latin1'A';
+
+#
+# Test for LEAST() BUG in LEFT JOIN
+#
+CREATE TABLE t1 ( faq_group_id int(11) NOT NULL default '0', faq_id int(11) NOT NULL default '0', title varchar(240) default NULL, keywords text, description longblob, solution longblob, status tinyint(4) NOT NULL default '0', access_id smallint(6) default NULL, lang_id smallint(6) NOT NULL default '0', created datetime NOT NULL default '0000-00-00 00:00:00', updated datetime default NULL, last_access datetime default NULL, last_notify datetime default NULL, solved_count int(11) NOT NULL default '0', static_solved int(11) default NULL, solved_1 int(11) default NULL, solved_2 int(11) default NULL, solved_3 int(11) default NULL, solved_4 int(11) default NULL, solved_5 int(11) default NULL, expires datetime default NULL, notes text, assigned_to smallint(6) default NULL, assigned_group smallint(6) default NULL, last_edited_by smallint(6) default NULL, orig_ref_no varchar(15) binary default NULL, c$fundstate smallint(6) default NULL, c$contributor smallint(6) default NULL, UNIQUE KEY t1$faq_id (faq_id), KEY t1$group_id$faq_id (faq_group_id,faq_id), KEY t1$c$fundstate (c$fundstate) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (82,82,'How to use the DynaVox Usage Counts Feature','usages count, number, corner, white, box, button','<as-html>\r\n<table width=\"100%\" border=\"0\">\r\n <tr>\r\n <td width=\"3%\"> </td>\r\n <td width=\"97%\">\r\n <h3><font face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#000000\">How \r\n To</font><!-- #BeginEditable \"CS_troubleshoot_question\" --><font face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#000099\"><font color=\"#000000\">: \r\n Display or Hide the Usage Counts to find out how many times each button is being selected. </font></font><!-- #EndEditable --></h3>\r\n </td>\r\n </tr>\r\n</table>','<as-html>\r\n <table width=\"100%\" border=\"0\">\r\n <tr>\r\n <td width=\"3%\"> </td>\r\n \r\n<td width=\"97%\"><!-- #BeginEditable \"CS_troubleshoot_answer\" --> \r\n \r\n<p><font color=\"#000000\" face=\"Verdana, Arial, Helvetica, sans-serif\">1. Select \r\n the <i>On/Setup</i> button to access the DynaVox Setup Menu.<br>\r\n 2. Select <b>Button Features.</b><br>\r\n 3. Below the <b>OK</b> button is the <b>Usage Counts</b> button.<br>\r\n a. If it says \"Hidden\" then the Usage Counts will not be displayed.<br>\r\n b. If it says \"Displayed\" then the Usage Counts will be shown.<br>\r\n c. Select the <b>Usage Counts</b> Option Ring once and it will toggle \r\n to the alternative option.<br>\r\n 4. Once the correct setting has been chosen, select <b>OK</b> to leave the <i>Button \r\n Features</i> menu.<br>\r\n 5. Select <b>OK</b> out of the <i>Setup</i> menu and return to the communication \r\n page.</font></p>\r\n <p><font color=\"#000000\" face=\"Verdana, Arial, Helvetica, sans-serif\">For \r\n further information on <i>Usage Counts,</i> see the <i>Button Features \r\n Menu Entry</i> in the DynaVox/DynaMyte Reference Manual.</font></p>\r\n<!-- #EndEditable --></td>\r\n </tr>\r\n</table>',4,1,1,'2001-11-16 16:43:34','2002-11-25 12:09:43','2003-07-24 01:04:48',NULL,11,NULL,0,0,0,0,0,NULL,NULL,NULL,NULL,11,NULL,NULL,NULL);
+CREATE TABLE t2 ( access_id smallint(6) NOT NULL default '0', name varchar(20) binary default NULL, rank smallint(6) NOT NULL default '0', KEY t2$access_id (access_id) ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1,'Everyone',2),(2,'Help',3),(3,'Customer Support',1);
+SELECT f_acc.rank, a1.rank, a2.rank FROM t1 LEFT JOIN t1 f1 ON (f1.access_id=1 AND f1.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a1 ON (a1.access_id = f1.access_id) LEFT JOIN t1 f2 ON (f2.access_id=3 AND f2.faq_group_id = t1.faq_group_id) LEFT JOIN t2 a2 ON (a2.access_id = f2.access_id), t2 f_acc WHERE LEAST(a1.rank,a2.rank) = f_acc.rank;
+DROP TABLE t1,t2;
+
+#
+# Test for GREATEST() and LEAST() bug, which segfaulted 4.1.1 server
+#
+CREATE TABLE t1 (d varchar(6), k int);
+INSERT INTO t1 VALUES (NULL, 2);
+SELECT GREATEST(d,d) FROM t1 WHERE k=2;
+DROP TABLE t1;
+
+#
+# Bug #6138: mod and doubles
+#
+
+select 1197.90 mod 50;
+select 5.1 mod 3, 5.1 mod -3, -5.1 mod 3, -5.1 mod -3;
+
+#
+# Test for mod and signed integers
+#
+
+select 5 mod 3, 5 mod -3, -5 mod 3, -5 mod -3;
+
+#
+# Bug#23411: The "%" (MOD) operator is not documented; MOD-ing zero returns strange result
+# Manual: "Division by zero produces a NULL result"
+#
+select (12%0) <=> null as '1';
+select (12%0) is null as '1';
+select 12%0 as 'NULL';
+select 12%2 as '0';
+select 12%NULL as 'NULL';
+select 12 % null as 'NULL';
+select null % 12 as 'NULL';
+select null % 0 as 'NULL';
+select 0 % null as 'NULL';
+select null % null as 'NULL';
+
+select (12 mod 0) <=> null as '1';
+select (12 mod 0) is null as '1';
+select 12 mod 0 as 'NULL';
+select 12 mod 2 as '0';
+select 12 mod null as 'NULL';
+select null mod 12 as 'NULL';
+select null mod 0 as 'NULL';
+select 0 mod null as 'NULL';
+select null mod null as 'NULL';
+
+select mod(12.0, 0) as 'NULL';
+select mod(12, 0.0) as 'NULL';
+select mod(12, NULL) as 'NULL';
+select mod(12.0, NULL) as 'NULL';
+select mod(NULL, 2) as 'NULL';
+select mod(NULL, 2.0) as 'NULL';
+
+
+#
+# Test for bug #12791: one of the arguments of LEAST/GREATEST is NULL
+#
+
+SELECT GREATEST(1,NULL) FROM DUAL;
+SELECT LEAST('xxx','aaa',NULL,'yyy') FROM DUAL;
+SELECT LEAST(1.1,1.2,NULL,1.0) FROM DUAL;
+SELECT GREATEST(1.5E+2,1.3E+2,NULL) FROM DUAL;
+
+# End of 4.1 tests
+
+#
+# test of replacing NOT <field>
+#
+create table t1 (a int);
+insert into t1 values (1), (100), (0), (NULL);
+
+select not a from t1;
+explain extended select not a from t1;
+
+select * from t1 where not a;
+explain extended select * from t1 where not a;
+
+select not (a+0) from t1;
+explain extended select not (a+0) from t1;
+
+select * from t1 where not (a+0);
+explain extended select * from t1 where not (a+0);
+
+select not 1, not null, not not null, 1 is not null;
+
+drop table t1;
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-7001 Bad result for NOT NOT STRCMP('a','b') and NOT NOT NULLIF(2,3)
+--echo #
+SELECT NOT NOT strcmp('a','b');
+EXPLAIN EXTENDED SELECT NOT NOT strcmp('a','b');
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+--echo #
+--echo # MDEV-8167 XOR returns bad results for an indexed column
+--echo #
+CREATE TABLE t1 (
+ id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+ k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
+ c CHAR(120) DEFAULT '' NOT NULL,
+ pad CHAR(60) DEFAULT '' NOT NULL,
+ PRIMARY KEY (id)
+) ENGINE=MyISAM;
+INSERT INTO t1 (k, c, pad) VALUES (10, 'a', 'xxx');
+INSERT INTO t1 (k, c, pad) VALUES (11, 'a', 'xxx');
+INSERT INTO t1 (k, c, pad) VALUES (12, 'a', 'xxx');
+INSERT INTO t1 (k, c, pad) VALUES (13, 'a', 'xxx');
+INSERT INTO t1 (k, c, pad) VALUES (14, 'a', 'xxx');
+INSERT INTO t1 (k, c, pad) VALUES (15, 'a', 'xxx');
+INSERT INTO t1 (k, c, pad) VALUES (16, 'a', 'xxx');
+SELECT * FROM t1 WHERE id XOR 0;
+SELECT * FROM t1 IGNORE KEY(PRIMARY) WHERE id XOR 0;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-8871 Wrong result for CREATE TABLE .. SELECT LEAST(unsigned_column,unsigned_column)
+--echo #
+CREATE TABLE t1 (a INT,b INT UNSIGNED);
+INSERT INTO t1 VALUES (-2147483648,4294967295);
+--vertical_results
+SELECT a, b, LEAST(a,a), LEAST(b,b), LEAST(a,b), LEAST(b,a), GREATEST(a,b), GREATEST(b,a) FROM t1;
+--horizontal_results
+CREATE TABLE t2 AS
+SELECT a, b, LEAST(a,a), LEAST(b,b), LEAST(a,b), LEAST(b,a), GREATEST(a,b), GREATEST(b,a) FROM t1;
+SHOW CREATE TABLE t2;
+--vertical_results
+SELECT * FROM t2;
+--horizontal_results
+DROP TABLE t2;
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.1 tests
+--echo #