719 lines
32 KiB
Plaintext
719 lines
32 KiB
Plaintext
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=MyISAM;
|
||
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=MyISAM;
|
||
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=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
||
SHOW CREATE TABLE t2;
|
||
Table Create Table
|
||
t2 CREATE TABLE `t2` (
|
||
`greatest(9223372036854775808, 9223372036854775808)` bigint(19) unsigned NOT NULL DEFAULT '0'
|
||
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_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) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c5` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c6` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c7` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c8` varchar(19) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c11` decimal(4,2) NOT NULL DEFAULT '0.00',
|
||
`c12` double NOT NULL DEFAULT '0',
|
||
`c13` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c14` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c15` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c16` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c21` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c22` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c23` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c24` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c31` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c32` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c33` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci 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) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c64` varbinary(4) NOT NULL DEFAULT '',
|
||
`c71` json DEFAULT NULL
|
||
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_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) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c5` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c6` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c7` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c8` varchar(19) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c11` decimal(4,2) NOT NULL DEFAULT '0.00',
|
||
`c12` double NOT NULL DEFAULT '0',
|
||
`c13` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c14` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c15` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c16` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c21` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c22` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c23` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c24` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c31` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c32` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c33` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci 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) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c64` varbinary(4) NOT NULL DEFAULT '',
|
||
`c71` json DEFAULT NULL
|
||
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_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) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c5` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c6` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c7` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c8` varchar(19) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c11` decimal(4,2) NOT NULL DEFAULT '0.00',
|
||
`c12` double NOT NULL DEFAULT '0',
|
||
`c13` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c14` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c15` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c16` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c21` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c22` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c23` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c24` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c31` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c32` varchar(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c33` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci 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) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
|
||
`c64` varbinary(4) NOT NULL DEFAULT ''
|
||
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_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;
|