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 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), (utf8mb3_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;
Warnings:
Warning	4095	Delimiter '\n' in position 10 in datetime value '2005-05-05
05:05:05.555555' at row 1 is deprecated. Prefer the standard ' '.
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;
Warnings:
Warning	4095	Delimiter '\n' in position 10 in datetime value '2005-05-05
05:05:05.555555' at row 1 is deprecated. Prefer the standard ' '.
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c1` bigint NOT NULL DEFAULT '0',
  `c2` decimal(2,1) NOT NULL DEFAULT '0.0',
  `c3` double NOT NULL DEFAULT '0',
  `c4` varchar(2) 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 NOT NULL DEFAULT '0',
  `c2` decimal(2,1) NOT NULL DEFAULT '0.0',
  `c3` double NOT NULL DEFAULT '0',
  `c4` varchar(2) 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 NOT NULL DEFAULT '0',
  `c2` decimal(2,1) NOT NULL DEFAULT '0.0',
  `c3` double NOT NULL DEFAULT '0',
  `c4` varchar(2) 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));
Warnings:
Warning	1287	'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
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;
#
# Bug#30326848 ASSERTION FAILED: (SLEN % 4) == 0 IN MY_STRNNCOLLSP_UTF32
#
DO NULLIF((GREATEST(FROM_UNIXTIME(1537024679) , _utf32 "*b!" )),
(FROM_UNIXTIME(1537013301) )
);
Warnings:
Warning	1300	Invalid utf32 character string: '002A62'
Warning	1292	Incorrect datetime value: '?' for column 'FROM_UNIXTIME(1537024679)' at row 1
Warning	1292	Incorrect datetime value: '?' for column 'FROM_UNIXTIME(1537024679)' at row 1
#
# Bug #33624777 sum(least(a, NULL)) result error
#
CREATE TABLE t1 (a DOUBLE, b DOUBLE);
CREATE TABLE t2 (a INTEGER, b INTEGER);
INSERT INTO t1 values(1, NULL), (1, 1);
INSERT INTO t2 values(1, NULL), (1, 1);
SELECT SUM(LEAST(a, b)) FROM t1;
SUM(LEAST(a, b))
1
SELECT SUM(LEAST(a, b)) FROM t2;
SUM(LEAST(a, b))
1
DROP TABLE t1, t2;
#
#Bug 33758255 - An edge case for base BUG 32038406 -
#               FILESORT USED FOR ORDER BY ...DESC EVEN WHEN
#               DESCENDING INDEX AVAILABLE AND USED
#
CREATE TABLE t1 (
c1 VARCHAR(10),
c2 VARCHAR(10),
c3 DATE NOT NULL,
c4 INT,
UNIQUE KEY ix (c1, c3 DESC, c4 DESC));
EXPLAIN
SELECT c1 FROM t1
WHERE c1= 'abc' AND
c2 IN ('def') AND
c3 BETWEEN '2022-03-16' AND '2022-03-16'
ORDER BY c3 DESC;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	ix	ix	46	const,const	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where ((`test`.`t1`.`c3` = DATE'2022-03-16') and (`test`.`t1`.`c2` = 'def') and (`test`.`t1`.`c1` = 'abc')) order by `test`.`t1`.`c3` desc
DROP TABLE t1;
#
# Bug #33996054 Result mismatch with input column re-order for GREATEST()
#
CREATE TABLE T4 (
F1 DATE,
F2 DATETIME,
F3 TIMESTAMP,
F4 INTEGER,
F5 TIME
);
INSERT INTO T4 VALUES
('2018-10-10', '2018-11-11 01:01:01', '2018-10-10 02:02:02', 19590425, '838:01:01'),
('2018-12-31', '2018-12-31 23:59:59', '2019-01-01 00:00:01', 19590425, '000:01:01'),
('1901-12-31', '1901-12-31 23:59:59', '1971-01-01 00:00:01', 20220801, '800:01:01'),
('2060-12-31', '2060-12-31 23:59:59', '2037-01-01 00:00:01', 12350101, '01:01:01'),
('2060-12-31', '2018-12-31 23:59:59', '2018-12-31 23:59:59', 12350101, '01:01:01'),
('2060-12-31', '2018-12-31 23:59:59', '2018-12-31 23:59:59', 20601231, '01:01:01'),
('2060-12-31', '2018-12-31 23:59:59', '2018-12-31 23:59:59', 20601231, '01:01:01'),
('2060-12-31', '2018-12-31 23:59:59', '2018-12-31 23:59:59', 20601231, '01:01:01');
SELECT GREATEST(F4, F1) FROM T4;
GREATEST(F4, F1)
2018-10-10
2018-12-31
2022-08-01
2060-12-31
2060-12-31
2060-12-31
2060-12-31
2060-12-31
SELECT GREATEST(F1, F4) FROM T4;
GREATEST(F1, F4)
2018-10-10
2018-12-31
2022-08-01
2060-12-31
2060-12-31
2060-12-31
2060-12-31
2060-12-31
SELECT GREATEST(F1, F4, F3) FROM T4;
GREATEST(F1, F4, F3)
2018-10-10 02:02:02
2019-01-01 00:00:01
2022-08-01 00:00:00
2060-12-31 00:00:00
2060-12-31 00:00:00
2060-12-31 00:00:00
2060-12-31 00:00:00
2060-12-31 00:00:00
SELECT GREATEST(F3, F4, F1) FROM T4;
GREATEST(F3, F4, F1)
2018-10-10 02:02:02
2019-01-01 00:00:01
2022-08-01 00:00:00
2060-12-31 00:00:00
2060-12-31 00:00:00
2060-12-31 00:00:00
2060-12-31 00:00:00
2060-12-31 00:00:00
SELECT GREATEST(F1, F4, F2) FROM T4;
GREATEST(F1, F4, F2)
2018-11-11 01:01:01
2018-12-31 23:59:59
2022-08-01 00:00:00
2060-12-31 23:59:59
2060-12-31 00:00:00
2060-12-31 00:00:00
2060-12-31 00:00:00
2060-12-31 00:00:00
DROP TABLE T4;