|
|
drop table if exists t1,t2;
|
|
|
select 0=0,1>0,1>=1,1<0,1<=0,1!=0,strcmp("abc","abcd"),strcmp("b","a"),strcmp("a","a") ;
|
|
|
0=0 1>0 1>=1 1<0 1<=0 1!=0 strcmp("abc","abcd") strcmp("b","a") strcmp("a","a")
|
|
|
1 1 1 0 0 1 -1 1 0
|
|
|
select "a"<"b","a"<="b","b">="a","b">"a","a"="A","a"<>"b";
|
|
|
"a"<"b" "a"<="b" "b">="a" "b">"a" "a"="A" "a"<>"b"
|
|
|
1 1 1 1 1 1
|
|
|
select "a "="A", "A "="a", "a " <= "A b";
|
|
|
"a "="A" "A "="a" "a " <= "A b"
|
|
|
0 0 1
|
|
|
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%";
|
|
|
"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%"
|
|
|
1 1 1 1 1 1 1 1
|
|
|
select "a" like "%%b","a" like "%%ab","ab" like "a\%", "ab" like "_", "ab" like "ab_", "abc" like "%_d", "abc" like "abc%d";
|
|
|
"a" like "%%b" "a" like "%%ab" "ab" like "a\%" "ab" like "_" "ab" like "ab_" "abc" like "%_d" "abc" like "abc%d"
|
|
|
0 0 0 0 0 0 0
|
|
|
select '?' like '|%', '?' like '|%' ESCAPE '|', '%' like '|%', '%' like '|%' ESCAPE '|', '%' like '%';
|
|
|
'?' like '|%' '?' like '|%' ESCAPE '|' '%' like '|%' '%' like '|%' ESCAPE '|' '%' like '%'
|
|
|
0 0 0 1 1
|
|
|
select 'abc' like '%c','abcabc' like '%c', "ab" like "", "ab" like "a", "ab" like "ab";
|
|
|
'abc' like '%c' 'abcabc' like '%c' "ab" like "" "ab" like "a" "ab" like "ab"
|
|
|
1 1 0 0 1
|
|
|
select "Det här är svenska" regexp "h[[:alpha:]]+r", "aba" regexp "^(a|b)*$";
|
|
|
"Det här är svenska" regexp "h[[:alpha:]]+r" "aba" regexp "^(a|b)*$"
|
|
|
1 1
|
|
|
select "aba" regexp concat("^","a");
|
|
|
"aba" regexp concat("^","a")
|
|
|
1
|
|
|
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;
|
|
|
!0 NOT 0=1 !(0=0) 1 AND 1 1 && 0 0 OR 1 1 || NULL 1=1 or 1=1 and 1=0
|
|
|
1 1 0 1 0 1 1 1
|
|
|
Warnings:
|
|
|
Warning 1287 '!' is deprecated and will be removed in a future release. Please use NOT instead
|
|
|
Warning 1287 '!' is deprecated and will be removed in a future release. Please use NOT instead
|
|
|
Warning 1287 '&&' is deprecated and will be removed in a future release. Please use AND instead
|
|
|
Warning 1287 '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead
|
|
|
select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between "max" and "my" and 3=3;
|
|
|
2 between 1 and 3 "monty" between "max" and "my" 2=2 and "monty" between "max" and "my" and 3=3
|
|
|
1 1 1
|
|
|
select 'b' between 'a' and 'c', 'B' between 'a' and 'c';
|
|
|
'b' between 'a' and 'c' 'B' between 'a' and 'c'
|
|
|
1 1
|
|
|
select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0);
|
|
|
2 in (3,2,5,9,5,1) "monty" in ("david","monty","allan") 1.2 in (1.4,1.2,1.0)
|
|
|
1 1 1
|
|
|
select -1.49 or -1.49,0.6 or 0.6;
|
|
|
-1.49 or -1.49 0.6 or 0.6
|
|
|
1 1
|
|
|
select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1;
|
|
|
3 ^ 11 1 ^ 1 1 ^ 0 1 ^ NULL NULL ^ 1
|
|
|
8 0 1 NULL NULL
|
|
|
explain select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select (3 ^ 11) AS `3 ^ 11`,(1 ^ 1) AS `1 ^ 1`,(1 ^ 0) AS `1 ^ 0`,(1 ^ NULL) AS `1 ^ NULL`,(NULL ^ 1) AS `NULL ^ 1`
|
|
|
select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL;
|
|
|
1 XOR 1 1 XOR 0 0 XOR 1 0 XOR 0 NULL XOR 1 1 XOR NULL 0 XOR NULL
|
|
|
0 1 1 0 NULL NULL NULL
|
|
|
select 1 like 2 xor 2 like 1;
|
|
|
1 like 2 xor 2 like 1
|
|
|
0
|
|
|
select 10 % 7, 10 mod 7, 10 div 3;
|
|
|
10 % 7 10 mod 7 10 div 3
|
|
|
3 3 3
|
|
|
explain select 10 % 7, 10 mod 7, 10 div 3;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select (10 % 7) AS `10 % 7`,(10 % 7) AS `10 mod 7`,(10 DIV 3) AS `10 div 3`
|
|
|
select 18446744073709551615, 18446744073709551615 DIV 1, 18446744073709551615 DIV 2;
|
|
|
18446744073709551615 18446744073709551615 DIV 1 18446744073709551615 DIV 2
|
|
|
18446744073709551615 18446744073709551615 9223372036854775807
|
|
|
explain select (1 << 64)-1, ((1 << 64)-1) DIV 1, ((1 << 64)-1) DIV 2;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select ((1 << 64) - 1) AS `(1 << 64)-1`,(((1 << 64) - 1) DIV 1) AS `((1 << 64)-1) DIV 1`,(((1 << 64) - 1) DIV 2) AS `((1 << 64)-1) DIV 2`
|
|
|
create table t1 (a int);
|
|
|
insert t1 values (1);
|
|
|
analyze table t1;
|
|
|
Table Op Msg_type Msg_text
|
|
|
test.t1 analyze status OK
|
|
|
select * from t1 where 1 xor 1;
|
|
|
a
|
|
|
explain select * from t1 where 1 xor 1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where false
|
|
|
select - a from t1;
|
|
|
- a
|
|
|
-1
|
|
|
explain select - a from t1;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select -(`test`.`t1`.`a`) AS `- a` from `test`.`t1`
|
|
|
drop table t1;
|
|
|
select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1;
|
|
|
5 between 0 and 10 between 0 and 1 (5 between 0 and 10) between 0 and 1
|
|
|
0 1
|
|
|
select 1 and 2 between 2 and 10, 2 between 2 and 10 and 1;
|
|
|
1 and 2 between 2 and 10 2 between 2 and 10 and 1
|
|
|
1 1
|
|
|
select 1 and 0 or 2, 2 or 1 and 0;
|
|
|
1 and 0 or 2 2 or 1 and 0
|
|
|
1 1
|
|
|
select _koi8r'a' = _koi8r'A';
|
|
|
_koi8r'a' = _koi8r'A'
|
|
|
1
|
|
|
select _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci;
|
|
|
_koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci
|
|
|
1
|
|
|
explain select _koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci;
|
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
|
Warnings:
|
|
|
Note 1003 /* select#1 */ select (_koi8r'a' = (_koi8r'A' collate koi8r_general_ci)) AS `_koi8r'a' = _koi8r'A' COLLATE koi8r_general_ci`
|
|
|
select _koi8r'a' = _koi8r'A' COLLATE koi8r_bin;
|
|
|
_koi8r'a' = _koi8r'A' COLLATE koi8r_bin
|
|
|
0
|
|
|
select _koi8r'a' COLLATE koi8r_general_ci = _koi8r'A';
|
|
|
_koi8r'a' COLLATE koi8r_general_ci = _koi8r'A'
|
|
|
1
|
|
|
select _koi8r'a' COLLATE koi8r_bin = _koi8r'A';
|
|
|
_koi8r'a' COLLATE koi8r_bin = _koi8r'A'
|
|
|
0
|
|
|
select _koi8r'a' COLLATE koi8r_bin = _koi8r'A' COLLATE koi8r_general_ci;
|
|
|
ERROR HY000: Illegal mix of collations (koi8r_bin,EXPLICIT) and (koi8r_general_ci,EXPLICIT) for operation '='
|
|
|
select _koi8r'a' = _latin1'A';
|
|
|
ERROR HY000: Illegal mix of collations (koi8r_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation '='
|
|
|
select strcmp(_koi8r'a', _koi8r'A');
|
|
|
strcmp(_koi8r'a', _koi8r'A')
|
|
|
0
|
|
|
select strcmp(_koi8r'a', _koi8r'A' COLLATE koi8r_general_ci);
|
|
|
strcmp(_koi8r'a', _koi8r'A' COLLATE koi8r_general_ci)
|
|
|
0
|
|
|
select strcmp(_koi8r'a', _koi8r'A' COLLATE koi8r_bin);
|
|
|
strcmp(_koi8r'a', _koi8r'A' COLLATE koi8r_bin)
|
|
|
1
|
|
|
select strcmp(_koi8r'a' COLLATE koi8r_general_ci, _koi8r'A');
|
|
|
strcmp(_koi8r'a' COLLATE koi8r_general_ci, _koi8r'A')
|
|
|
0
|
|
|
select strcmp(_koi8r'a' COLLATE koi8r_bin, _koi8r'A');
|
|
|
strcmp(_koi8r'a' COLLATE koi8r_bin, _koi8r'A')
|
|
|
1
|
|
|
select strcmp(_koi8r'a' COLLATE koi8r_general_ci, _koi8r'A' COLLATE koi8r_bin);
|
|
|
ERROR HY000: Illegal mix of collations (koi8r_general_ci,EXPLICIT) and (koi8r_bin,EXPLICIT) for operation 'strcmp'
|
|
|
select strcmp(_koi8r'a', _latin1'A');
|
|
|
ERROR HY000: Illegal mix of collations (koi8r_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'strcmp'
|
|
|
select _koi8r'a' LIKE _koi8r'A';
|
|
|
_koi8r'a' LIKE _koi8r'A'
|
|
|
1
|
|
|
select _koi8r'a' LIKE _koi8r'A' COLLATE koi8r_general_ci;
|
|
|
_koi8r'a' LIKE _koi8r'A' COLLATE koi8r_general_ci
|
|
|
1
|
|
|
select _koi8r'a' LIKE _koi8r'A' COLLATE koi8r_bin;
|
|
|
_koi8r'a' LIKE _koi8r'A' COLLATE koi8r_bin
|
|
|
0
|
|
|
select _koi8r'a' COLLATE koi8r_general_ci LIKE _koi8r'A';
|
|
|
_koi8r'a' COLLATE koi8r_general_ci LIKE _koi8r'A'
|
|
|
1
|
|
|
select _koi8r'a' COLLATE koi8r_bin LIKE _koi8r'A';
|
|
|
_koi8r'a' COLLATE koi8r_bin LIKE _koi8r'A'
|
|
|
0
|
|
|
select _koi8r'a' COLLATE koi8r_general_ci LIKE _koi8r'A' COLLATE koi8r_bin;
|
|
|
ERROR HY000: Illegal mix of collations (koi8r_general_ci,EXPLICIT) and (koi8r_bin,EXPLICIT) for operation 'like'
|
|
|
select _koi8r'a' LIKE _latin1'A';
|
|
|
ERROR HY000: Illegal mix of collations (koi8r_general_ci,COERCIBLE) and (latin1_swedish_ci,COERCIBLE) for operation 'like'
|
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
|
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=InnoDB;
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
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=InnoDB;
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
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`;
|
|
|
rank rank rank
|
|
|
DROP TABLE t1,t2;
|
|
|
SET sql_mode = default;
|
|
|
CREATE TABLE t1 (d varchar(6), k int);
|
|
|
INSERT INTO t1 VALUES (NULL, 2);
|
|
|
SELECT GREATEST(d,d) FROM t1 WHERE k=2;
|
|
|
GREATEST(d,d)
|
|
|
NULL
|
|
|
DROP TABLE t1;
|
|
|
select 1197.90 mod 50;
|
|
|
1197.90 mod 50
|
|
|
47.90
|
|
|
select 5.1 mod 3, 5.1 mod -3, -5.1 mod 3, -5.1 mod -3;
|
|
|
5.1 mod 3 5.1 mod -3 -5.1 mod 3 -5.1 mod -3
|
|
|
2.1 2.1 -2.1 -2.1
|
|
|
select 5 mod 3, 5 mod -3, -5 mod 3, -5 mod -3;
|
|
|
5 mod 3 5 mod -3 -5 mod 3 -5 mod -3
|
|
|
2 2 -2 -2
|
|
|
select (12%0) <=> null as '1';
|
|
|
1
|
|
|
1
|
|
|
Warnings:
|
|
|
Warning 1365 Division by 0
|
|
|
select (12%0) is null as '1';
|
|
|
1
|
|
|
1
|
|
|
Warnings:
|
|
|
Warning 1365 Division by 0
|
|
|
select 12%0 as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
Warnings:
|
|
|
Warning 1365 Division by 0
|
|
|
select 12%2 as '0';
|
|
|
0
|
|
|
0
|
|
|
select 12%NULL as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select 12 % null as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select null % 12 as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select null % 0 as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select 0 % null as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select null % null as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select (12 mod 0) <=> null as '1';
|
|
|
1
|
|
|
1
|
|
|
Warnings:
|
|
|
Warning 1365 Division by 0
|
|
|
select (12 mod 0) is null as '1';
|
|
|
1
|
|
|
1
|
|
|
Warnings:
|
|
|
Warning 1365 Division by 0
|
|
|
select 12 mod 0 as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
Warnings:
|
|
|
Warning 1365 Division by 0
|
|
|
select 12 mod 2 as '0';
|
|
|
0
|
|
|
0
|
|
|
select 12 mod null as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select null mod 12 as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select null mod 0 as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select 0 mod null as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select null mod null as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select mod(12.0, 0) as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
Warnings:
|
|
|
Warning 1365 Division by 0
|
|
|
select mod(12, 0.0) as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
Warnings:
|
|
|
Warning 1365 Division by 0
|
|
|
select mod(12, NULL) as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select mod(12.0, NULL) as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select mod(NULL, 2) as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
select mod(NULL, 2.0) as 'NULL';
|
|
|
NULL
|
|
|
NULL
|
|
|
create table t1 (a int, b int);
|
|
|
insert into t1 values (1,2), (2,3), (3,4), (4,5);
|
|
|
select * from t1 where a not between 1 and 2;
|
|
|
a b
|
|
|
3 4
|
|
|
4 5
|
|
|
select * from t1 where a not between 1 and 2 and b not between 3 and 4;
|
|
|
a b
|
|
|
4 5
|
|
|
drop table t1;
|
|
|
SELECT GREATEST(1,NULL) FROM DUAL;
|
|
|
GREATEST(1,NULL)
|
|
|
NULL
|
|
|
SELECT LEAST('xxx','aaa',NULL,'yyy') FROM DUAL;
|
|
|
LEAST('xxx','aaa',NULL,'yyy')
|
|
|
NULL
|
|
|
SELECT LEAST(1.1,1.2,NULL,1.0) FROM DUAL;
|
|
|
LEAST(1.1,1.2,NULL,1.0)
|
|
|
NULL
|
|
|
SELECT GREATEST(1.5E+2,1.3E+2,NULL) FROM DUAL;
|
|
|
GREATEST(1.5E+2,1.3E+2,NULL)
|
|
|
NULL
|
|
|
#
|
|
|
# Bug#22523685 FUNCTION GREATEST AND LEAST WORKS INCORRECTLY
|
|
|
# WITH BIGINT UNSIGNED VALUE
|
|
|
#
|
|
|
SELECT greatest( 9223372036854775807 , 9223372036854775808 ) as g;
|
|
|
g
|
|
|
9223372036854775808
|
|
|
SELECT least ( 9223372036854775807 , 9223372036854775808 ) as l;
|
|
|
l
|
|
|
9223372036854775807
|
|
|
SELECT greatest (9223372036854775808, -1, 18446744073709551615 ) as g;
|
|
|
g
|
|
|
18446744073709551615
|
|
|
SELECT least (9223372036854775808, -1, 18446744073709551615 ) as l;
|
|
|
l
|
|
|
-1
|
|
|
SELECT greatest (9223372036854775808, 18446744073709551615) as g;
|
|
|
g
|
|
|
18446744073709551615
|
|
|
SELECT least (9223372036854775808, 18446744073709551615) as l;
|
|
|
l
|
|
|
9223372036854775808
|
|
|
CREATE TABLE t1 AS SELECT greatest(-1, 9223372036854775808);
|
|
|
CREATE TABLE t2 AS SELECT greatest(9223372036854775808, 9223372036854775808);
|
|
|
SHOW CREATE TABLE t1;
|
|
|
Table Create Table
|
|
|
t1 CREATE TABLE `t1` (
|
|
|
`greatest(-1, 9223372036854775808)` decimal(19,0) NOT NULL DEFAULT '0'
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
|
SHOW CREATE TABLE t2;
|
|
|
Table Create Table
|
|
|
t2 CREATE TABLE `t2` (
|
|
|
`greatest(9223372036854775808, 9223372036854775808)` bigint(19) unsigned NOT NULL DEFAULT '0'
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
|
DROP TABLE t1, t2;
|
|
|
#
|
|
|
# Bug#22839888 UNINITIALIZED VALUE WHEN CONVERTING
|
|
|
# MULTIBYTE STRINGS TO NUMBERS
|
|
|
#
|
|
|
select greatest(1,_utf16'.',_utf8'');
|
|
|
ERROR HY000: Illegal mix of collations (latin1_swedish_ci,NUMERIC), (utf16_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation 'greatest'
|
|
|
#
|
|
|
# Bug#25123839: LEAST AND GREATEST MAKES INCONSISTENT DATA TYPES,
|
|
|
# COMPARED TO UNION AND COALESCE
|
|
|
#
|
|
|
SET sql_mode='';
|
|
|
CREATE TABLE t1 AS
|
|
|
SELECT 5 AS c1, 5 AS c2, 5 AS c3, 5 AS c4, 20010101 AS c5, 010101 AS c6,
|
|
|
010101 AS c7, 20010101 AS c8, 5.55 AS c11, 5.5 AS c12, 5.5 AS c13,
|
|
|
010101.5 AS c14, 5.5 AS c15, 010101.5 as c16, 5.5e5 as c21,
|
|
|
2004041e1 as c22, 150505.0e5 AS c23, 2004041e1 AS c24, '010101' AS c31,
|
|
|
'5' AS c32, '010101' AS c33, date '2005-05-05' AS c41,
|
|
|
date '2005-05-05' AS c42, time '05:05:05.555555' AS c51,
|
|
|
x'c3a5' AS c61, x'c3a5' AS c62, _utf8mb4 x'c3a5' AS c63,
|
|
|
_utf8mb4 x'c3a5' AS c64, JSON_OBJECT('a', 1) as c71
|
|
|
UNION
|
|
|
SELECT -5, 5.5, 5.5e5, '5', date '2005-05-05', time '05:05:05.555555',
|
|
|
timestamp '2005-05-05 05:05:05.555555', NOW(0), 88.8,
|
|
|
5.5e5, '5', date '2005-05-05', time '05:05:05.555555',
|
|
|
timestamp '2005-05-05 05:05:05.555555',
|
|
|
'5', date '2005-05-05', time '05:05:05.555555',
|
|
|
timestamp '2005-05-05 05:05:05.555555',
|
|
|
date '2005-05-05', time '05:05:05.555555',
|
|
|
timestamp '2005-05-05 05:05:05.555555',
|
|
|
time '05:05:05.555555', timestamp '2005-05-05 05:05:05.555555',
|
|
|
timestamp '2005-05-05 05:05:05.555555', '1', _binary '1', '1',
|
|
|
_binary '1', NULL;
|
|
|
SET sql_mode=default;
|
|
|
CREATE TABLE t2 AS
|
|
|
SELECT COALESCE(5, -5) AS c1,
|
|
|
COALESCE(5, 5.5) AS c2,
|
|
|
COALESCE(5, 5.5e5) AS c3,
|
|
|
COALESCE(5, '5') AS c4,
|
|
|
COALESCE(20010101, date '2005-05-05') AS c5,
|
|
|
COALESCE(010101, time '05:05:05.555555') AS c6,
|
|
|
COALESCE(010101, timestamp '2005-05-05 05:05:05.555555') AS c7,
|
|
|
COALESCE(20010101, NOW(0)) AS c8,
|
|
|
COALESCE(5.55, 88.8) AS c11,
|
|
|
COALESCE(5.5, 5.5e5) AS c12,
|
|
|
COALESCE(5.5, '5') AS c13,
|
|
|
COALESCE(010101.5, date '2005-05-05') AS c14,
|
|
|
COALESCE(5.5, time '05:05:05.555555') AS c15,
|
|
|
COALESCE(010101.5, timestamp '2005-05-05 05:05:05.555555') AS c16,
|
|
|
COALESCE(5.5e5, '5') AS c21,
|
|
|
COALESCE(2004041e1, date '2005-05-05') AS c22,
|
|
|
COALESCE(150505.0e5, time '05:05:05.555555') AS c23,
|
|
|
COALESCE(2004041e1, timestamp '2005-05-05 05:05:05.555555') AS c24,
|
|
|
COALESCE('010101', date '2005-05-05') AS c31,
|
|
|
COALESCE('5', time '05:05:05.555555') AS c32,
|
|
|
COALESCE('010101', timestamp '2005-05-05 05:05:05.555555') AS c33,
|
|
|
COALESCE(date '2005-05-05', time '05:05:05.555555') AS c41,
|
|
|
COALESCE(date '2005-05-05', timestamp '2005-05-05 05:05:05.555555') AS
|
|
|
c42,
|
|
|
COALESCE(time '05:05:05.555555', timestamp '2005-05-05
|
|
|
05:05:05.555555') AS c51,
|
|
|
COALESCE(x'c3a5', '1') AS c61,
|
|
|
COALESCE(x'c3a5', _binary '1') AS c62,
|
|
|
COALESCE(_utf8mb4 x'c3a5', '1') AS c63,
|
|
|
COALESCE(_utf8mb4 x'c3a5', _binary '1') AS c64,
|
|
|
COALESCE(JSON_OBJECT('a', 1), NULL) AS c71;
|
|
|
CREATE TABLE t3 AS
|
|
|
SELECT GREATEST(5, -5) AS c1,
|
|
|
GREATEST(5, 5.5) AS c2,
|
|
|
GREATEST(5, 5.5e5) AS c3,
|
|
|
GREATEST(5, '5') AS c4,
|
|
|
GREATEST(20010101, date '2005-05-05') AS c5,
|
|
|
GREATEST(010101, time '05:05:05.555555') AS c6,
|
|
|
GREATEST(010101, timestamp '2005-05-05 05:05:05.555555') AS c7,
|
|
|
GREATEST(20010101, NOW(0)) AS c8,
|
|
|
GREATEST(5.55, 88.8) AS c11,
|
|
|
GREATEST(5.5, 5.5e5) AS c12,
|
|
|
GREATEST(5.5, '5') AS c13,
|
|
|
GREATEST(010101.5, date '2005-05-05') AS c14,
|
|
|
GREATEST(5.5, time '05:05:05.555555') AS c15,
|
|
|
GREATEST(010101.5, timestamp '2005-05-05 05:05:05.555555') AS c16,
|
|
|
GREATEST(5.5e5, '5') AS c21,
|
|
|
GREATEST(2004041e1, date '2005-05-05') AS c22,
|
|
|
GREATEST(150505.0e5, time '05:05:05.555555') AS c23,
|
|
|
GREATEST(2004041e1, timestamp '2005-05-05 05:05:05.555555') AS c24,
|
|
|
GREATEST('010101', date '2005-05-05') AS c31,
|
|
|
GREATEST('5', time '05:05:05.555555') AS c32,
|
|
|
GREATEST('010101', timestamp '2005-05-05 05:05:05.555555') AS c33,
|
|
|
GREATEST(date '2005-05-05', time '05:05:05.555555') AS c41,
|
|
|
GREATEST(date '2005-05-05', timestamp '2005-05-05 05:05:05.555555') AS
|
|
|
c42,
|
|
|
GREATEST(time '05:05:05.555555', timestamp '2005-05-05
|
|
|
05:05:05.555555') AS c51,
|
|
|
GREATEST(x'c3a5', '1') AS c61,
|
|
|
GREATEST(x'c3a5', _binary '1') AS c62,
|
|
|
GREATEST(_utf8mb4 x'c3a5', '1') AS c63,
|
|
|
GREATEST(_utf8mb4 x'c3a5', _binary '1') AS c64;
|
|
|
SHOW CREATE TABLE t1;
|
|
|
Table Create Table
|
|
|
t1 CREATE TABLE `t1` (
|
|
|
`c1` bigint(2) NOT NULL DEFAULT '0',
|
|
|
`c2` decimal(2,1) NOT NULL DEFAULT '0.0',
|
|
|
`c3` double NOT NULL DEFAULT '0',
|
|
|
`c4` varchar(1) NOT NULL DEFAULT '',
|
|
|
`c5` varchar(10) NOT NULL DEFAULT '',
|
|
|
`c6` varchar(17) NOT NULL DEFAULT '',
|
|
|
`c7` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c8` varchar(19) NOT NULL DEFAULT '',
|
|
|
`c11` decimal(4,2) NOT NULL DEFAULT '0.00',
|
|
|
`c12` double NOT NULL DEFAULT '0',
|
|
|
`c13` varchar(4) NOT NULL DEFAULT '',
|
|
|
`c14` varchar(10) NOT NULL DEFAULT '',
|
|
|
`c15` varchar(17) NOT NULL DEFAULT '',
|
|
|
`c16` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c21` varchar(22) NOT NULL DEFAULT '',
|
|
|
`c22` varchar(22) NOT NULL DEFAULT '',
|
|
|
`c23` varchar(22) NOT NULL DEFAULT '',
|
|
|
`c24` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c31` varchar(10) NOT NULL DEFAULT '',
|
|
|
`c32` varchar(17) NOT NULL DEFAULT '',
|
|
|
`c33` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c41` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
|
|
|
`c42` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
|
|
|
`c51` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
|
|
|
`c61` varbinary(4) NOT NULL DEFAULT '',
|
|
|
`c62` varbinary(2) NOT NULL DEFAULT '',
|
|
|
`c63` varchar(1) NOT NULL DEFAULT '',
|
|
|
`c64` varbinary(4) NOT NULL DEFAULT '',
|
|
|
`c71` json DEFAULT NULL
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
|
SHOW CREATE TABLE t2;
|
|
|
Table Create Table
|
|
|
t2 CREATE TABLE `t2` (
|
|
|
`c1` int(2) NOT NULL DEFAULT '0',
|
|
|
`c2` decimal(2,1) NOT NULL DEFAULT '0.0',
|
|
|
`c3` double NOT NULL DEFAULT '0',
|
|
|
`c4` varchar(1) NOT NULL DEFAULT '',
|
|
|
`c5` varchar(10) NOT NULL DEFAULT '',
|
|
|
`c6` varchar(17) NOT NULL DEFAULT '',
|
|
|
`c7` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c8` varchar(19) NOT NULL DEFAULT '',
|
|
|
`c11` decimal(4,2) NOT NULL DEFAULT '0.00',
|
|
|
`c12` double NOT NULL DEFAULT '0',
|
|
|
`c13` varchar(4) NOT NULL DEFAULT '',
|
|
|
`c14` varchar(10) NOT NULL DEFAULT '',
|
|
|
`c15` varchar(17) NOT NULL DEFAULT '',
|
|
|
`c16` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c21` varchar(22) NOT NULL DEFAULT '',
|
|
|
`c22` varchar(22) NOT NULL DEFAULT '',
|
|
|
`c23` varchar(22) NOT NULL DEFAULT '',
|
|
|
`c24` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c31` varchar(10) NOT NULL DEFAULT '',
|
|
|
`c32` varchar(17) NOT NULL DEFAULT '',
|
|
|
`c33` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c41` datetime(6) NOT NULL,
|
|
|
`c42` datetime(6) NOT NULL,
|
|
|
`c51` datetime(6) NOT NULL,
|
|
|
`c61` varbinary(4) NOT NULL DEFAULT '',
|
|
|
`c62` varbinary(2) NOT NULL DEFAULT '',
|
|
|
`c63` varchar(1) NOT NULL DEFAULT '',
|
|
|
`c64` varbinary(4) NOT NULL DEFAULT '',
|
|
|
`c71` json DEFAULT NULL
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
|
SHOW CREATE TABLE t3;
|
|
|
Table Create Table
|
|
|
t3 CREATE TABLE `t3` (
|
|
|
`c1` int(2) NOT NULL DEFAULT '0',
|
|
|
`c2` decimal(2,1) NOT NULL DEFAULT '0.0',
|
|
|
`c3` double NOT NULL DEFAULT '0',
|
|
|
`c4` varchar(1) NOT NULL DEFAULT '',
|
|
|
`c5` varchar(10) NOT NULL DEFAULT '',
|
|
|
`c6` varchar(17) NOT NULL DEFAULT '',
|
|
|
`c7` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c8` varchar(19) NOT NULL DEFAULT '',
|
|
|
`c11` decimal(4,2) NOT NULL DEFAULT '0.00',
|
|
|
`c12` double NOT NULL DEFAULT '0',
|
|
|
`c13` varchar(4) NOT NULL DEFAULT '',
|
|
|
`c14` varchar(10) NOT NULL DEFAULT '',
|
|
|
`c15` varchar(17) NOT NULL DEFAULT '',
|
|
|
`c16` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c21` varchar(22) NOT NULL DEFAULT '',
|
|
|
`c22` varchar(22) NOT NULL DEFAULT '',
|
|
|
`c23` varchar(22) NOT NULL DEFAULT '',
|
|
|
`c24` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c31` varchar(10) NOT NULL DEFAULT '',
|
|
|
`c32` varchar(17) NOT NULL DEFAULT '',
|
|
|
`c33` varchar(26) NOT NULL DEFAULT '',
|
|
|
`c41` datetime(6) NOT NULL,
|
|
|
`c42` datetime(6) NOT NULL,
|
|
|
`c51` datetime(6) NOT NULL,
|
|
|
`c61` varbinary(4) NOT NULL DEFAULT '',
|
|
|
`c62` varbinary(2) NOT NULL DEFAULT '',
|
|
|
`c63` varchar(1) NOT NULL DEFAULT '',
|
|
|
`c64` varbinary(4) NOT NULL DEFAULT ''
|
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
SELECT GREATEST('11', '5', '2');
|
|
|
GREATEST('11', '5', '2')
|
|
|
5
|
|
|
SELECT GREATEST('11', 5, 2);
|
|
|
GREATEST('11', 5, 2)
|
|
|
5
|
|
|
SELECT GREATEST(11, 5, 2);
|
|
|
GREATEST(11, 5, 2)
|
|
|
11
|
|
|
SELECT LEAST('11', '5', '2');
|
|
|
LEAST('11', '5', '2')
|
|
|
11
|
|
|
SELECT LEAST('11', 5, 2);
|
|
|
LEAST('11', 5, 2)
|
|
|
11
|
|
|
SELECT LEAST(11, 5, 2);
|
|
|
LEAST(11, 5, 2)
|
|
|
2
|
|
|
SELECT GREATEST(date '2005-05-05', 20010101, 20040404, 20030303);
|
|
|
GREATEST(date '2005-05-05', 20010101, 20040404, 20030303)
|
|
|
2005-05-05
|
|
|
SELECT LEAST(date '2005-05-05', 20030303, 20010101, 20040404);
|
|
|
LEAST(date '2005-05-05', 20030303, 20010101, 20040404)
|
|
|
2001-01-01
|
|
|
SELECT GREATEST(date '2005-05-05', '20010101', '20040404', '20030303');
|
|
|
GREATEST(date '2005-05-05', '20010101', '20040404', '20030303')
|
|
|
2005-05-05
|
|
|
SELECT LEAST(date '2005-05-05', '20030303', '20010101', '20040404');
|
|
|
LEAST(date '2005-05-05', '20030303', '20010101', '20040404')
|
|
|
2001-01-01
|
|
|
SELECT GREATEST(time '00:00:00', 120000);
|
|
|
GREATEST(time '00:00:00', 120000)
|
|
|
120000
|
|
|
SELECT LEAST(time '00:00:00', 120000);
|
|
|
LEAST(time '00:00:00', 120000)
|
|
|
00:00:00
|
|
|
SELECT GREATEST(time '20:00:00', 120000);
|
|
|
GREATEST(time '20:00:00', 120000)
|
|
|
20:00:00
|
|
|
SELECT LEAST(time '20:00:00', 120000);
|
|
|
LEAST(time '20:00:00', 120000)
|
|
|
120000
|
|
|
SELECT GREATEST('95-05-05', date '10-10-10');
|
|
|
GREATEST('95-05-05', date '10-10-10')
|
|
|
2010-10-10
|
|
|
SELECT GREATEST(date '1995-05-05', '10-10-10');
|
|
|
GREATEST(date '1995-05-05', '10-10-10')
|
|
|
2010-10-10
|
|
|
SELECT LEAST(date '1995-05-05', '10-10-10');
|
|
|
LEAST(date '1995-05-05', '10-10-10')
|
|
|
1995-05-05
|
|
|
SELECT LEAST('95-05-05', date '10-10-10');
|
|
|
LEAST('95-05-05', date '10-10-10')
|
|
|
1995-05-05
|
|
|
SELECT GREATEST('11', '5', '2') + 0;
|
|
|
GREATEST('11', '5', '2') + 0
|
|
|
5
|
|
|
SELECT GREATEST('11', 5, 2) + 0;
|
|
|
GREATEST('11', 5, 2) + 0
|
|
|
5
|
|
|
SELECT GREATEST(11, 5, 2) + 0;
|
|
|
GREATEST(11, 5, 2) + 0
|
|
|
11
|
|
|
SELECT GREATEST(date '2005-05-05', 20010101, 20040404, 20030303) + 0;
|
|
|
GREATEST(date '2005-05-05', 20010101, 20040404, 20030303) + 0
|
|
|
20050505
|
|
|
SELECT GREATEST(time '00:00:00', 120000) + 0;
|
|
|
GREATEST(time '00:00:00', 120000) + 0
|
|
|
120000
|
|
|
SELECT GREATEST(time '20:00:00', 120000) + 0;
|
|
|
GREATEST(time '20:00:00', 120000) + 0
|
|
|
200000
|
|
|
SELECT GREATEST('95-05-05', date '10-10-10') + 0;
|
|
|
GREATEST('95-05-05', date '10-10-10') + 0
|
|
|
20101010
|
|
|
SELECT GREATEST(date '1995-05-05', '10-10-10') + 0;
|
|
|
GREATEST(date '1995-05-05', '10-10-10') + 0
|
|
|
20101010
|
|
|
SELECT GREATEST('11', '5', '2') + 0.00;
|
|
|
GREATEST('11', '5', '2') + 0.00
|
|
|
5
|
|
|
SELECT GREATEST('11', 5, 2) + 0.00;
|
|
|
GREATEST('11', 5, 2) + 0.00
|
|
|
5
|
|
|
SELECT GREATEST(11, 5, 2) + 0.00;
|
|
|
GREATEST(11, 5, 2) + 0.00
|
|
|
11.00
|
|
|
SELECT GREATEST(date '1995-05-05', 19910101, 20050505, 19930303) + 0.00;
|
|
|
GREATEST(date '1995-05-05', 19910101, 20050505, 19930303) + 0.00
|
|
|
20050505.00
|
|
|
SELECT GREATEST(time '00:00:00', 120000) + 0.00;
|
|
|
GREATEST(time '00:00:00', 120000) + 0.00
|
|
|
120000.00
|
|
|
SELECT GREATEST(time '20:00:00', 120000) + 0.00;
|
|
|
GREATEST(time '20:00:00', 120000) + 0.00
|
|
|
200000.00
|
|
|
SELECT GREATEST('95-05-05', date '10-10-10') + 0.00;
|
|
|
GREATEST('95-05-05', date '10-10-10') + 0.00
|
|
|
20101010.00
|
|
|
SELECT GREATEST(date '1995-05-05', '10-10-10') + 0.00;
|
|
|
GREATEST(date '1995-05-05', '10-10-10') + 0.00
|
|
|
20101010.00
|
|
|
# End of test for Bug#25123839
|
|
|
#
|
|
|
# Bug#26975864: ASSERTION FAILED: DEC <= 6, MY_DATETIME_TO_STR, LEAST,
|
|
|
# GREATEST, ETC
|
|
|
#
|
|
|
CREATE TABLE t1 (a DATETIME, b BLOB, c TEXT, d CHAR(10), e BINARY(10),
|
|
|
f VARBINARY(10));
|
|
|
SET @a='2017-01-01';
|
|
|
INSERT INTO t1 VALUES ('2017-02-02 12:00:00', @a, @a, @a, @a, @a);
|
|
|
SELECT GREATEST(a, b), GREATEST(a, c), GREATEST(a, d), GREATEST(a, e),
|
|
|
GREATEST(a, f) FROM t1;
|
|
|
GREATEST(a, b) GREATEST(a, c) GREATEST(a, d) GREATEST(a, e) GREATEST(a, f)
|
|
|
2017-02-02 12:00:00.000000 2017-02-02 12:00:00.000000 2017-02-02 12:00:00.000000 2017-02-02 12:00:00.000000 2017-02-02 12:00:00.000000
|
|
|
SET @a=NULL;
|
|
|
DROP TABLE t1;
|
|
|
#
|
|
|
# Bug#13364839: GREATEST() DOES NOT ALWAYS RETURN SAME SIGNNESS OF ARGUMENT TYPES
|
|
|
#
|
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b BIGINT(20) UNSIGNED);
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
INSERT INTO t1 VALUES (1, 13836376518955650385);
|
|
|
INSERT INTO t1 VALUES (1, 13836376518955650385) ON DUPLICATE KEY UPDATE b=GREATEST(b, VALUES(b));
|
|
|
SELECT * FROM t1;
|
|
|
a b
|
|
|
1 13836376518955650385
|
|
|
DROP TABLE t1;
|
|
|
#
|
|
|
# Bug#27312703 ASAN: HEAP-USE-AFTER-FREE: GREATEST/LEAST FUNCTIONS
|
|
|
#
|
|
|
SET @a:='11';
|
|
|
DO GREATEST(RIGHT(@a,1),1);
|
|
|
DO LEAST(RIGHT(@a,1),1);
|
|
|
#
|
|
|
# Bug#29275835 ASSERTION FAILED:
|
|
|
# !UNSIGNED_FLAG || (UNSIGNED_FLAG && ARGS[I]->UNSIGNED_FLAG)
|
|
|
#
|
|
|
SET sql_mode='';
|
|
|
CREATE TABLE t0017 (
|
|
|
c0002 bigint(20) NOT NULL
|
|
|
);
|
|
|
Warnings:
|
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
|
INSERT INTO t0017 (c0002)
|
|
|
VALUES (0);
|
|
|
SELECT greatest(18446744073709551615, NULL)
|
|
|
FROM t0017;
|
|
|
greatest(18446744073709551615, NULL)
|
|
|
NULL
|
|
|
SELECT
|
|
|
count(greatest(18446744073709551615, NULL))
|
|
|
FROM t0017;
|
|
|
count(greatest(18446744073709551615, NULL))
|
|
|
0
|
|
|
DROP TABLE t0017;
|
|
|
SET sql_mode = default;
|
|
|
#
|
|
|
# Bug#29467577 ASSERTION `!UNSIGNED_FLAG ||
|
|
|
# (UNSIGNED_FLAG && ARGS[I]->UNSIGNED_FLAG)' FAILED.
|
|
|
#
|
|
|
CREATE TABLE t1 (c1 INT UNSIGNED, c2 INT UNSIGNED);
|
|
|
INSERT INTO t1 VALUES (202, 1);
|
|
|
SELECT * FROM t1 WHERE (GREATEST(c1, c2) = 42) AND (c2 = 1);
|
|
|
c1 c2
|
|
|
DROP TABLE t1;
|
|
|
|
