#
# Start of 5.5 tests
#
#
# MDEV-15955 Assertion `field_types == 0 || field_types[field_pos] == MYSQL_TYPE_LONGLONG' failed in Protocol_text::store_longlong
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
SELECT @a := 1 FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
@a := 1
1
SELECT COALESCE(1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
COALESCE(1)
1
SELECT COALESCE(@a:=1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
COALESCE(@a:=1)
1
SELECT COALESCE(@a) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar');
COALESCE(@a)
1
DROP TABLE t1;
#
# MDEV-21065 UNIQUE constraint causes a query with string comparison to omit a row in the result set
#
CREATE TABLE t1 (c0 INT UNIQUE);
INSERT INTO t1 VALUES (NULL), (NULL), (NULL), (NULL), (1), (0);
SELECT * FROM t1 WHERE c0 < '\n2';
c0
0
1
DROP TABLE t1;
SELECT CAST('\n2' AS INT);
CAST('\n2' AS INT)
2
#
# End of 5.5 tests
#
#
# Start of 10.1 tests
#
#
# MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010
#
CREATE TABLE t1 (a INT ZEROFILL);
INSERT INTO t1 VALUES (2010),(2020);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=2010;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2010
DROP TABLE t1;
#
# MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field
#
CREATE TABLE t1 (a INT ZEROFILL);
INSERT INTO t1 VALUES (128),(129);
SELECT * FROM t1 WHERE a=128;
a
0000000128
SELECT * FROM t1 WHERE hex(a)='80';
a
0000000128
SELECT * FROM t1 WHERE a=128 AND hex(a)='80';
a
0000000128
EXPLAIN EXTENDED
SELECT * FROM t1 WHERE a=128 AND hex(a)='80';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 128 and hex(`test`.`t1`.`a`) = '80'
DROP TABLE t1;
#
# End of 10.1 tests
#
#
# Start of 10.2 tests
#
#
# MDEV-9393 Split Copy_field::get_copy_func() into virtual methods in Field
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10.1),(10.9);
SELECT * FROM t1;
a
10
11
DROP TABLE t1;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a DECIMAL(10,2));
INSERT INTO t2 VALUES (10.1),(10.9);
INSERT INTO t1 SELECT a FROM t2;
SELECT * FROM t1;
a
10
11
DROP TABLE t1,t2;
CREATE TABLE t1 (a DECIMAL(10,2));
INSERT INTO t1 VALUES (10.1),(10.9);
ALTER TABLE t1 MODIFY a INT;
SELECT * FROM t1;
a
10
11
DROP TABLE t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (TIME'00:00:10.1'),(TIME'00:00:10.9');
SELECT * FROM t1;
a
10
10
DROP TABLE t1;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a TIME(1));
INSERT INTO t2 VALUES (10.1),(10.9);
INSERT INTO t1 SELECT a FROM t2;
SELECT * FROM t1;
a
10
10
DROP TABLE t1,t2;
CREATE TABLE t1 (a TIME(1));
INSERT INTO t1 VALUES (10.1),(10.9);
ALTER TABLE t1 MODIFY a INT;
SELECT * FROM t1;
a
10
10
DROP TABLE t1;
#
# MDEV-9334 ALTER from DECIMAL to BIGINT UNSIGNED returns a wrong result
#
CREATE TABLE t1 (a DECIMAL(30,0));
INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED));
SELECT * FROM t1;
a
18446744073709551615
ALTER TABLE t1 MODIFY a BIGINT UNSIGNED;
SELECT * FROM t1;
a
18446744073709551615
DROP TABLE t1;
#
# End of 10.2 tests
#
#
# Start of 10.3 tests
#
#
# MDEV-15926 MEDIUMINT returns wrong I_S attributes
#
CREATE TABLE t1 (a MEDIUMINT, b MEDIUMINT UNSIGNED);
SELECT COLUMN_NAME, NUMERIC_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' ORDER BY COLUMN_NAME;
COLUMN_NAME	NUMERIC_PRECISION
a	7
b	8
DROP TABLE t1;
#
# MDEV-15946 MEDIUMINT(N<8) creates a wrong data type on conversion to string
#
CREATE TABLE t1 (
uint8  TINYINT(2) UNSIGNED,   sint8  TINYINT(2),
uint16 SMALLINT(2) UNSIGNED,  sint16 SMALLINT(2),
uint24 MEDIUMINT(2) UNSIGNED, sint24 MEDIUMINT(2),
uint32 INT(2) UNSIGNED,       sint32 INT(2),
uint64 BIGINT(2) UNSIGNED,    sint64 BIGINT(2)
);
CREATE TABLE t2 AS SELECT
CONCAT(uint8),CONCAT(sint8),
CONCAT(uint16),CONCAT(sint16),
CONCAT(uint24),CONCAT(sint24),
CONCAT(uint32),CONCAT(sint32),
CONCAT(uint64),CONCAT(sint64)
FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `CONCAT(uint8)` varchar(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(sint8)` varchar(4) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(uint16)` varchar(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(sint16)` varchar(6) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(uint24)` varchar(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(sint24)` varchar(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(uint32)` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(sint32)` varchar(11) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(uint64)` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(sint64)` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t2;
CREATE TABLE t2 AS SELECT
CONCAT(COALESCE(uint8)),CONCAT(COALESCE(sint8)),
CONCAT(COALESCE(uint16)),CONCAT(COALESCE(sint16)),
CONCAT(COALESCE(uint24)),CONCAT(COALESCE(sint24)),
CONCAT(COALESCE(uint32)),CONCAT(COALESCE(sint32)),
CONCAT(COALESCE(uint64)),CONCAT(COALESCE(sint64))
FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `CONCAT(COALESCE(uint8))` varchar(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(sint8))` varchar(4) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(uint16))` varchar(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(sint16))` varchar(6) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(uint24))` varchar(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(sint24))` varchar(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(uint32))` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(sint32))` varchar(11) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(uint64))` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(sint64))` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t2;
DROP TABLE t1;
CREATE FUNCTION uint8() RETURNS TINYINT(2) UNSIGNED RETURN 1;
CREATE FUNCTION sint8() RETURNS TINYINT(2) RETURN 1;
CREATE FUNCTION uint16() RETURNS SMALLINT(2) UNSIGNED RETURN 1;
CREATE FUNCTION sint16() RETURNS SMALLINT(2) RETURN 1;
CREATE FUNCTION uint24() RETURNS MEDIUMINT(2) UNSIGNED RETURN 1;
CREATE FUNCTION sint24() RETURNS MEDIUMINT(2) RETURN 1;
CREATE FUNCTION uint32() RETURNS INT(2) UNSIGNED RETURN 1;
CREATE FUNCTION sint32() RETURNS INT(2) RETURN 1;
CREATE FUNCTION uint64() RETURNS BIGINT(2) UNSIGNED RETURN 1;
CREATE FUNCTION sint64() RETURNS BIGINT(2) RETURN 1;
CREATE TABLE t1 AS SELECT
CONCAT(uint8()), CONCAT(sint8()),
CONCAT(uint16()),CONCAT(sint16()),
CONCAT(uint24()),CONCAT(sint24()),
CONCAT(uint32()),CONCAT(sint32()),
CONCAT(uint64()),CONCAT(sint64());
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `CONCAT(uint8())` varchar(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(sint8())` varchar(4) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(uint16())` varchar(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(sint16())` varchar(6) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(uint24())` varchar(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(sint24())` varchar(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(uint32())` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(sint32())` varchar(11) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(uint64())` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(sint64())` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE TABLE t1 AS SELECT
CONCAT(COALESCE(uint8())),CONCAT(COALESCE(sint8())),
CONCAT(COALESCE(uint16())),CONCAT(COALESCE(sint16())),
CONCAT(COALESCE(uint24())),CONCAT(COALESCE(sint24())),
CONCAT(COALESCE(uint32())),CONCAT(COALESCE(sint32())),
CONCAT(COALESCE(uint64())),CONCAT(COALESCE(sint64()));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `CONCAT(COALESCE(uint8()))` varchar(3) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(sint8()))` varchar(4) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(uint16()))` varchar(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(sint16()))` varchar(6) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(uint24()))` varchar(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(sint24()))` varchar(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(uint32()))` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(sint32()))` varchar(11) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(uint64()))` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CONCAT(COALESCE(sint64()))` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
DROP FUNCTION uint8;
DROP FUNCTION sint8;
DROP FUNCTION uint16;
DROP FUNCTION sint16;
DROP FUNCTION uint24;
DROP FUNCTION sint24;
DROP FUNCTION uint32;
DROP FUNCTION sint32;
DROP FUNCTION uint64;
DROP FUNCTION sint64;
#
# MDEV-20285 Wrong result on INSERT..SELECT when converting from SIGNED to UNSIGNED
#
CREATE TABLE t1 (a TINYINT UNSIGNED);
CREATE TABLE t2 (a TINYINT);
INSERT INTO t1 VALUES (255);
INSERT IGNORE INTO t2 SELECT a FROM t1;
Warnings:
Warning	1264	Out of range value for column 'a' at row 1
SELECT * FROM t2;
a
127
DROP TABLE t1, t2;
#
# MDEV-21445 Strange/inconsistent behavior of IN condition when mixing numbers and strings
#
CREATE TABLE t1 (
id bigint(20) unsigned NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO t1 VALUES(1234),(97716021308405775),(97716021308405770),(97716021308405780);
SELECT * FROM t1 WHERE id IN(97716021308405775);
id
97716021308405775
SELECT * FROM t1 WHERE id IN('97716021308405775');
id
97716021308405775
SELECT * FROM t1 WHERE id IN('1234',97716021308405775);
id
1234
97716021308405775
SELECT * FROM t1 WHERE id IN('1234','97716021308405775');
id
1234
97716021308405775
SELECT * FROM t1 WHERE id = 1234 OR id = '97716021308405775';
id
1234
97716021308405775
SELECT * FROM t1 WHERE id IN(1234,'97716021308405775');
id
1234
97716021308405775
SELECT * FROM t1 WHERE id IN('97716021308405775',1234);
id
1234
97716021308405775
DROP TABLE t1;
CREATE TABLE t1 (id bigint(20) unsigned NOT NULL PRIMARY KEY);
INSERT INTO t1 VALUES(1234),(97716021308405775),(97716021308405770),(97716021308405780);
SELECT
id,
CASE id
WHEN '97716021308405770' THEN '70'
    WHEN '97716021308405775' THEN '75'
    WHEN '97716021308405780' THEN '80'
  END AS c1
FROM t1;
id	c1
1234	NULL
97716021308405770	70
97716021308405775	75
97716021308405780	80
SELECT
id,
CASE
WHEN id='97716021308405770' THEN '70'
    WHEN id='97716021308405775' THEN '75'
    WHEN id='97716021308405780' THEN '80'
  END AS c1
FROM t1;
id	c1
1234	NULL
97716021308405770	70
97716021308405775	75
97716021308405780	80
SELECT id, id BETWEEN '97716021308405775' AND '97716021308405775' AS c1 FROM t1;
id	c1
1234	0
97716021308405770	0
97716021308405775	1
97716021308405780	0
SELECT id, id BETWEEN '97716021308405775' AND 97716021308405775 AS c1 FROM t1;
id	c1
1234	0
97716021308405770	0
97716021308405775	1
97716021308405780	0
SELECT id, id BETWEEN 97716021308405775 AND '97716021308405775' AS c1 FROM t1;
id	c1
1234	0
97716021308405770	0
97716021308405775	1
97716021308405780	0
DROP TABLE t1;
SELECT '97716021308405770'=97716021308405770;
'97716021308405770'=97716021308405770
1
SELECT 97716021308405770='97716021308405770';
97716021308405770='97716021308405770'
1
SELECT '9e+16'=90000000000000000;
'9e+16'=90000000000000000
1
SELECT 90000000000000000='9e+16';
90000000000000000='9e+16'
1
SELECT '9e+30'>90000000000000000;
'9e+30'>90000000000000000
1
SELECT 90000000000000000<'9e+30';
90000000000000000<'9e+30'
1
SELECT '9e+80'>90000000000000000;
'9e+80'>90000000000000000
1
SELECT 90000000000000000<'9e+80';
90000000000000000<'9e+80'
1
SELECT '9.9999999999999999e+80'>90000000000000000;
'9.9999999999999999e+80'>90000000000000000
1
SELECT 90000000000000000<'9.9999999999999999e+80';
90000000000000000<'9.9999999999999999e+80'
1
SELECT '1e+81'>90000000000000000;
'1e+81'>90000000000000000
1
Warnings:
Warning	1916	Got overflow when converting '' to DECIMAL. Value truncated
Warning	1292	Truncated incorrect DECIMAL value: '1e+81'
SELECT 90000000000000000<'1e+81';
90000000000000000<'1e+81'
1
Warnings:
Warning	1916	Got overflow when converting '' to DECIMAL. Value truncated
Warning	1292	Truncated incorrect DECIMAL value: '1e+81'
SELECT '9e+100'>90000000000000000;
'9e+100'>90000000000000000
1
Warnings:
Warning	1916	Got overflow when converting '' to DECIMAL. Value truncated
Warning	1292	Truncated incorrect DECIMAL value: '9e+100'
SELECT 90000000000000000<'9e+100';
90000000000000000<'9e+100'
1
Warnings:
Warning	1916	Got overflow when converting '' to DECIMAL. Value truncated
Warning	1292	Truncated incorrect DECIMAL value: '9e+100'
SELECT CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)=18446744073709551615;
CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)=18446744073709551615
1
SELECT 18446744073709551615=CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED);
18446744073709551615=CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)
1
SELECT CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED)=9223372036854775807;
CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED)=9223372036854775807
1
SELECT 9223372036854775807=CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED);
9223372036854775807=CAST(0x7FFFFFFFFFFFFFFF AS UNSIGNED)
1
#
# MDEV-25492 BETWEEN clause returns incorrect results on quoted 64-bit ints
#
CREATE TABLE betweentest
(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
range1 bigint(20) DEFAULT NULL,
range2 bigint(20) DEFAULT NULL,
PRIMARY KEY (id)
);
INSERT INTO betweentest VALUES
(1,2739499741191602369,2739499741191602369),
(2,2739499741191602192,2739499741191602192),
(3,2739138623713574912,2739138623730352127);
SELECT * FROM betweentest WHERE '2739499741191602192' BETWEEN range1 AND range2;
id	range1	range2
2	2739499741191602192	2739499741191602192
SELECT * FROM betweentest WHERE 2739499741191602192 BETWEEN range1 AND range2;
id	range1	range2
2	2739499741191602192	2739499741191602192
DROP TABLE betweentest;
#
# End of 10.3 tests
#
#
# Start of 10.4 tests
#
#
# MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1+a<=>1+a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1,1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1+a' USING 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1+a<=>?+a' USING 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
DROP TABLE t1;
#
# MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant
#
CREATE TABLE t1 (a TINYINT, KEY(a));
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
EXPLAIN SELECT * FROM t1 WHERE a=200;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT * FROM t1 WHERE a<=>200;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
DROP TABLE t1;
#
# MDEV-8554 Expect "Impossible WHERE" for never true values like a!=a, a<a, a>a
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
EXPLAIN SELECT * FROM t1 WHERE a!=a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
SELECT COUNT(*) FROM t1 WHERE a!=a;
COUNT(*)
0
EXPLAIN SELECT * FROM t1 WHERE a>a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
SELECT COUNT(*) FROM t1 WHERE a>a;
COUNT(*)
0
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
SELECT COUNT(*) FROM t1 WHERE a<a;
COUNT(*)
0
ALTER TABLE t1 MODIFY a TINYINT NOT NULL;
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a!=a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
SELECT COUNT(*) FROM t1 WHERE a!=a;
COUNT(*)
0
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
SELECT COUNT(*) FROM t1 WHERE a>a;
COUNT(*)
0
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
SELECT COUNT(*) FROM t1 WHERE a<a;
COUNT(*)
0
#
# MDEV-8554 Modifing expression doesn't hit "Impossible WHERE" clause
#
EXPLAIN SELECT COUNT(*) FROM t1 WHERE not (a!=a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
SELECT COUNT(*) FROM t1 WHERE not (a!=a);
COUNT(*)
3
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>a-1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
SELECT COUNT(*) FROM t1 WHERE a>a-1;
COUNT(*)
3
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a+1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
SELECT COUNT(*) FROM t1 WHERE a<a+1;
COUNT(*)
3
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a-1<a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
SELECT COUNT(*) FROM t1 WHERE a-1<a;
COUNT(*)
3
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a NOT LIKE a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
SELECT COUNT(*) FROM t1 WHERE a NOT LIKE a;
COUNT(*)
0
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a XOR a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
SELECT COUNT(*) FROM t1 WHERE a XOR a;
COUNT(*)
0
DROP TABLE t1;
#
# MDEV-23337 Rounding functions create a wrong data type for numeric input
# MDEV-23350 ROUND(bigint_22_or_longer) returns a wrong data type
#
CREATE PROCEDURE p1(t VARCHAR(64))
BEGIN
SELECT t AS ``;
EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (a TYPE)', 'TYPE', t);
INSERT IGNORE INTO t1 VALUES (-100000000000000000000000000000);
INSERT IGNORE INTO t1 VALUES (100000000000000000000000000000);
CREATE  TABLE t2 AS SELECT a, ROUND(a), ROUND(a,1), ROUND(a,10), TRUNCATE(a,0), FLOOR(a), CEILING(a) FROM t1;
SHOW CREATE TABLE t2;
SELECT * FROM t2;
DROP TABLE t1, t2;
END;
$$
CALL p1('tinyint');
	tinyint
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` tinyint(4) DEFAULT NULL,
  `ROUND(a)` tinyint(4) DEFAULT NULL,
  `ROUND(a,1)` tinyint(4) DEFAULT NULL,
  `ROUND(a,10)` tinyint(4) DEFAULT NULL,
  `TRUNCATE(a,0)` tinyint(4) DEFAULT NULL,
  `FLOOR(a)` tinyint(4) DEFAULT NULL,
  `CEILING(a)` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-128
ROUND(a)	-128
ROUND(a,1)	-128
ROUND(a,10)	-128
TRUNCATE(a,0)	-128
FLOOR(a)	-128
CEILING(a)	-128
a	127
ROUND(a)	127
ROUND(a,1)	127
ROUND(a,10)	127
TRUNCATE(a,0)	127
FLOOR(a)	127
CEILING(a)	127
CALL p1('smallint');
	smallint
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` smallint(6) DEFAULT NULL,
  `ROUND(a)` smallint(6) DEFAULT NULL,
  `ROUND(a,1)` smallint(6) DEFAULT NULL,
  `ROUND(a,10)` smallint(6) DEFAULT NULL,
  `TRUNCATE(a,0)` smallint(6) DEFAULT NULL,
  `FLOOR(a)` smallint(6) DEFAULT NULL,
  `CEILING(a)` smallint(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-32768
ROUND(a)	-32768
ROUND(a,1)	-32768
ROUND(a,10)	-32768
TRUNCATE(a,0)	-32768
FLOOR(a)	-32768
CEILING(a)	-32768
a	32767
ROUND(a)	32767
ROUND(a,1)	32767
ROUND(a,10)	32767
TRUNCATE(a,0)	32767
FLOOR(a)	32767
CEILING(a)	32767
CALL p1('mediumint');
	mediumint
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` mediumint(9) DEFAULT NULL,
  `ROUND(a)` mediumint(9) DEFAULT NULL,
  `ROUND(a,1)` mediumint(9) DEFAULT NULL,
  `ROUND(a,10)` mediumint(9) DEFAULT NULL,
  `TRUNCATE(a,0)` mediumint(9) DEFAULT NULL,
  `FLOOR(a)` mediumint(9) DEFAULT NULL,
  `CEILING(a)` mediumint(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-8388608
ROUND(a)	-8388608
ROUND(a,1)	-8388608
ROUND(a,10)	-8388608
TRUNCATE(a,0)	-8388608
FLOOR(a)	-8388608
CEILING(a)	-8388608
a	8388607
ROUND(a)	8388607
ROUND(a,1)	8388607
ROUND(a,10)	8388607
TRUNCATE(a,0)	8388607
FLOOR(a)	8388607
CEILING(a)	8388607
CALL p1('int');
	int
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `ROUND(a)` int(11) DEFAULT NULL,
  `ROUND(a,1)` int(11) DEFAULT NULL,
  `ROUND(a,10)` int(11) DEFAULT NULL,
  `TRUNCATE(a,0)` int(11) DEFAULT NULL,
  `FLOOR(a)` int(11) DEFAULT NULL,
  `CEILING(a)` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-2147483648
ROUND(a)	-2147483648
ROUND(a,1)	-2147483648
ROUND(a,10)	-2147483648
TRUNCATE(a,0)	-2147483648
FLOOR(a)	-2147483648
CEILING(a)	-2147483648
a	2147483647
ROUND(a)	2147483647
ROUND(a,1)	2147483647
ROUND(a,10)	2147483647
TRUNCATE(a,0)	2147483647
FLOOR(a)	2147483647
CEILING(a)	2147483647
CALL p1('bigint');
	bigint
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(20) DEFAULT NULL,
  `ROUND(a)` bigint(20) DEFAULT NULL,
  `ROUND(a,1)` bigint(20) DEFAULT NULL,
  `ROUND(a,10)` bigint(20) DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(20) DEFAULT NULL,
  `FLOOR(a)` bigint(20) DEFAULT NULL,
  `CEILING(a)` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a)	-9223372036854775808
ROUND(a,1)	-9223372036854775808
ROUND(a,10)	-9223372036854775808
TRUNCATE(a,0)	-9223372036854775808
FLOOR(a)	-9223372036854775808
CEILING(a)	-9223372036854775808
a	9223372036854775807
ROUND(a)	9223372036854775807
ROUND(a,1)	9223372036854775807
ROUND(a,10)	9223372036854775807
TRUNCATE(a,0)	9223372036854775807
FLOOR(a)	9223372036854775807
CEILING(a)	9223372036854775807
CALL p1('bigint(20)');
	bigint(20)
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(20) DEFAULT NULL,
  `ROUND(a)` bigint(20) DEFAULT NULL,
  `ROUND(a,1)` bigint(20) DEFAULT NULL,
  `ROUND(a,10)` bigint(20) DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(20) DEFAULT NULL,
  `FLOOR(a)` bigint(20) DEFAULT NULL,
  `CEILING(a)` bigint(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a)	-9223372036854775808
ROUND(a,1)	-9223372036854775808
ROUND(a,10)	-9223372036854775808
TRUNCATE(a,0)	-9223372036854775808
FLOOR(a)	-9223372036854775808
CEILING(a)	-9223372036854775808
a	9223372036854775807
ROUND(a)	9223372036854775807
ROUND(a,1)	9223372036854775807
ROUND(a,10)	9223372036854775807
TRUNCATE(a,0)	9223372036854775807
FLOOR(a)	9223372036854775807
CEILING(a)	9223372036854775807
CALL p1('bigint(21)');
	bigint(21)
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(21) DEFAULT NULL,
  `ROUND(a)` bigint(21) DEFAULT NULL,
  `ROUND(a,1)` bigint(21) DEFAULT NULL,
  `ROUND(a,10)` bigint(21) DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(21) DEFAULT NULL,
  `FLOOR(a)` bigint(21) DEFAULT NULL,
  `CEILING(a)` bigint(21) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a)	-9223372036854775808
ROUND(a,1)	-9223372036854775808
ROUND(a,10)	-9223372036854775808
TRUNCATE(a,0)	-9223372036854775808
FLOOR(a)	-9223372036854775808
CEILING(a)	-9223372036854775808
a	9223372036854775807
ROUND(a)	9223372036854775807
ROUND(a,1)	9223372036854775807
ROUND(a,10)	9223372036854775807
TRUNCATE(a,0)	9223372036854775807
FLOOR(a)	9223372036854775807
CEILING(a)	9223372036854775807
CALL p1('bigint(22)');
	bigint(22)
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(22) DEFAULT NULL,
  `ROUND(a)` bigint(22) DEFAULT NULL,
  `ROUND(a,1)` bigint(22) DEFAULT NULL,
  `ROUND(a,10)` bigint(22) DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(22) DEFAULT NULL,
  `FLOOR(a)` bigint(22) DEFAULT NULL,
  `CEILING(a)` bigint(22) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a)	-9223372036854775808
ROUND(a,1)	-9223372036854775808
ROUND(a,10)	-9223372036854775808
TRUNCATE(a,0)	-9223372036854775808
FLOOR(a)	-9223372036854775808
CEILING(a)	-9223372036854775808
a	9223372036854775807
ROUND(a)	9223372036854775807
ROUND(a,1)	9223372036854775807
ROUND(a,10)	9223372036854775807
TRUNCATE(a,0)	9223372036854775807
FLOOR(a)	9223372036854775807
CEILING(a)	9223372036854775807
CALL p1('bigint(23)');
	bigint(23)
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(23) DEFAULT NULL,
  `ROUND(a)` bigint(23) DEFAULT NULL,
  `ROUND(a,1)` bigint(23) DEFAULT NULL,
  `ROUND(a,10)` bigint(23) DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(23) DEFAULT NULL,
  `FLOOR(a)` bigint(23) DEFAULT NULL,
  `CEILING(a)` bigint(23) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a)	-9223372036854775808
ROUND(a,1)	-9223372036854775808
ROUND(a,10)	-9223372036854775808
TRUNCATE(a,0)	-9223372036854775808
FLOOR(a)	-9223372036854775808
CEILING(a)	-9223372036854775808
a	9223372036854775807
ROUND(a)	9223372036854775807
ROUND(a,1)	9223372036854775807
ROUND(a,10)	9223372036854775807
TRUNCATE(a,0)	9223372036854775807
FLOOR(a)	9223372036854775807
CEILING(a)	9223372036854775807
CALL p1('bigint(30)');
	bigint(30)
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(30) DEFAULT NULL,
  `ROUND(a)` bigint(30) DEFAULT NULL,
  `ROUND(a,1)` bigint(30) DEFAULT NULL,
  `ROUND(a,10)` bigint(30) DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(30) DEFAULT NULL,
  `FLOOR(a)` bigint(30) DEFAULT NULL,
  `CEILING(a)` bigint(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a)	-9223372036854775808
ROUND(a,1)	-9223372036854775808
ROUND(a,10)	-9223372036854775808
TRUNCATE(a,0)	-9223372036854775808
FLOOR(a)	-9223372036854775808
CEILING(a)	-9223372036854775808
a	9223372036854775807
ROUND(a)	9223372036854775807
ROUND(a,1)	9223372036854775807
ROUND(a,10)	9223372036854775807
TRUNCATE(a,0)	9223372036854775807
FLOOR(a)	9223372036854775807
CEILING(a)	9223372036854775807
CALL p1('tinyint unsigned');
	tinyint unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` tinyint(3) unsigned DEFAULT NULL,
  `ROUND(a)` tinyint(3) unsigned DEFAULT NULL,
  `ROUND(a,1)` tinyint(3) unsigned DEFAULT NULL,
  `ROUND(a,10)` tinyint(3) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` tinyint(3) unsigned DEFAULT NULL,
  `FLOOR(a)` tinyint(3) unsigned DEFAULT NULL,
  `CEILING(a)` tinyint(3) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a)	0
ROUND(a,1)	0
ROUND(a,10)	0
TRUNCATE(a,0)	0
FLOOR(a)	0
CEILING(a)	0
a	255
ROUND(a)	255
ROUND(a,1)	255
ROUND(a,10)	255
TRUNCATE(a,0)	255
FLOOR(a)	255
CEILING(a)	255
CALL p1('smallint unsigned');
	smallint unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` smallint(5) unsigned DEFAULT NULL,
  `ROUND(a)` smallint(5) unsigned DEFAULT NULL,
  `ROUND(a,1)` smallint(5) unsigned DEFAULT NULL,
  `ROUND(a,10)` smallint(5) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` smallint(5) unsigned DEFAULT NULL,
  `FLOOR(a)` smallint(5) unsigned DEFAULT NULL,
  `CEILING(a)` smallint(5) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a)	0
ROUND(a,1)	0
ROUND(a,10)	0
TRUNCATE(a,0)	0
FLOOR(a)	0
CEILING(a)	0
a	65535
ROUND(a)	65535
ROUND(a,1)	65535
ROUND(a,10)	65535
TRUNCATE(a,0)	65535
FLOOR(a)	65535
CEILING(a)	65535
CALL p1('mediumint unsigned');
	mediumint unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` mediumint(8) unsigned DEFAULT NULL,
  `ROUND(a)` mediumint(8) unsigned DEFAULT NULL,
  `ROUND(a,1)` mediumint(8) unsigned DEFAULT NULL,
  `ROUND(a,10)` mediumint(8) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` mediumint(8) unsigned DEFAULT NULL,
  `FLOOR(a)` mediumint(8) unsigned DEFAULT NULL,
  `CEILING(a)` mediumint(8) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a)	0
ROUND(a,1)	0
ROUND(a,10)	0
TRUNCATE(a,0)	0
FLOOR(a)	0
CEILING(a)	0
a	16777215
ROUND(a)	16777215
ROUND(a,1)	16777215
ROUND(a,10)	16777215
TRUNCATE(a,0)	16777215
FLOOR(a)	16777215
CEILING(a)	16777215
CALL p1('int unsigned');
	int unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` int(10) unsigned DEFAULT NULL,
  `ROUND(a)` int(10) unsigned DEFAULT NULL,
  `ROUND(a,1)` int(10) unsigned DEFAULT NULL,
  `ROUND(a,10)` int(10) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` int(10) unsigned DEFAULT NULL,
  `FLOOR(a)` int(10) unsigned DEFAULT NULL,
  `CEILING(a)` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a)	0
ROUND(a,1)	0
ROUND(a,10)	0
TRUNCATE(a,0)	0
FLOOR(a)	0
CEILING(a)	0
a	4294967295
ROUND(a)	4294967295
ROUND(a,1)	4294967295
ROUND(a,10)	4294967295
TRUNCATE(a,0)	4294967295
FLOOR(a)	4294967295
CEILING(a)	4294967295
CALL p1('bigint unsigned');
	bigint unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(20) unsigned DEFAULT NULL,
  `ROUND(a)` bigint(20) unsigned DEFAULT NULL,
  `ROUND(a,1)` bigint(20) unsigned DEFAULT NULL,
  `ROUND(a,10)` bigint(20) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(20) unsigned DEFAULT NULL,
  `FLOOR(a)` bigint(20) unsigned DEFAULT NULL,
  `CEILING(a)` bigint(20) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a)	0
ROUND(a,1)	0
ROUND(a,10)	0
TRUNCATE(a,0)	0
FLOOR(a)	0
CEILING(a)	0
a	18446744073709551615
ROUND(a)	18446744073709551615
ROUND(a,1)	18446744073709551615
ROUND(a,10)	18446744073709551615
TRUNCATE(a,0)	18446744073709551615
FLOOR(a)	18446744073709551615
CEILING(a)	18446744073709551615
CALL p1('bigint(20) unsigned');
	bigint(20) unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(20) unsigned DEFAULT NULL,
  `ROUND(a)` bigint(20) unsigned DEFAULT NULL,
  `ROUND(a,1)` bigint(20) unsigned DEFAULT NULL,
  `ROUND(a,10)` bigint(20) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(20) unsigned DEFAULT NULL,
  `FLOOR(a)` bigint(20) unsigned DEFAULT NULL,
  `CEILING(a)` bigint(20) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a)	0
ROUND(a,1)	0
ROUND(a,10)	0
TRUNCATE(a,0)	0
FLOOR(a)	0
CEILING(a)	0
a	18446744073709551615
ROUND(a)	18446744073709551615
ROUND(a,1)	18446744073709551615
ROUND(a,10)	18446744073709551615
TRUNCATE(a,0)	18446744073709551615
FLOOR(a)	18446744073709551615
CEILING(a)	18446744073709551615
CALL p1('bigint(21) unsigned');
	bigint(21) unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(21) unsigned DEFAULT NULL,
  `ROUND(a)` bigint(21) unsigned DEFAULT NULL,
  `ROUND(a,1)` bigint(21) unsigned DEFAULT NULL,
  `ROUND(a,10)` bigint(21) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(21) unsigned DEFAULT NULL,
  `FLOOR(a)` bigint(21) unsigned DEFAULT NULL,
  `CEILING(a)` bigint(21) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a)	0
ROUND(a,1)	0
ROUND(a,10)	0
TRUNCATE(a,0)	0
FLOOR(a)	0
CEILING(a)	0
a	18446744073709551615
ROUND(a)	18446744073709551615
ROUND(a,1)	18446744073709551615
ROUND(a,10)	18446744073709551615
TRUNCATE(a,0)	18446744073709551615
FLOOR(a)	18446744073709551615
CEILING(a)	18446744073709551615
CALL p1('bigint(22) unsigned');
	bigint(22) unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(22) unsigned DEFAULT NULL,
  `ROUND(a)` bigint(22) unsigned DEFAULT NULL,
  `ROUND(a,1)` bigint(22) unsigned DEFAULT NULL,
  `ROUND(a,10)` bigint(22) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(22) unsigned DEFAULT NULL,
  `FLOOR(a)` bigint(22) unsigned DEFAULT NULL,
  `CEILING(a)` bigint(22) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a)	0
ROUND(a,1)	0
ROUND(a,10)	0
TRUNCATE(a,0)	0
FLOOR(a)	0
CEILING(a)	0
a	18446744073709551615
ROUND(a)	18446744073709551615
ROUND(a,1)	18446744073709551615
ROUND(a,10)	18446744073709551615
TRUNCATE(a,0)	18446744073709551615
FLOOR(a)	18446744073709551615
CEILING(a)	18446744073709551615
CALL p1('bigint(23) unsigned');
	bigint(23) unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(23) unsigned DEFAULT NULL,
  `ROUND(a)` bigint(23) unsigned DEFAULT NULL,
  `ROUND(a,1)` bigint(23) unsigned DEFAULT NULL,
  `ROUND(a,10)` bigint(23) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(23) unsigned DEFAULT NULL,
  `FLOOR(a)` bigint(23) unsigned DEFAULT NULL,
  `CEILING(a)` bigint(23) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a)	0
ROUND(a,1)	0
ROUND(a,10)	0
TRUNCATE(a,0)	0
FLOOR(a)	0
CEILING(a)	0
a	18446744073709551615
ROUND(a)	18446744073709551615
ROUND(a,1)	18446744073709551615
ROUND(a,10)	18446744073709551615
TRUNCATE(a,0)	18446744073709551615
FLOOR(a)	18446744073709551615
CEILING(a)	18446744073709551615
CALL p1('bigint(30) unsigned');
	bigint(30) unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(30) unsigned DEFAULT NULL,
  `ROUND(a)` bigint(30) unsigned DEFAULT NULL,
  `ROUND(a,1)` bigint(30) unsigned DEFAULT NULL,
  `ROUND(a,10)` bigint(30) unsigned DEFAULT NULL,
  `TRUNCATE(a,0)` bigint(30) unsigned DEFAULT NULL,
  `FLOOR(a)` bigint(30) unsigned DEFAULT NULL,
  `CEILING(a)` bigint(30) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a)	0
ROUND(a,1)	0
ROUND(a,10)	0
TRUNCATE(a,0)	0
FLOOR(a)	0
CEILING(a)	0
a	18446744073709551615
ROUND(a)	18446744073709551615
ROUND(a,1)	18446744073709551615
ROUND(a,10)	18446744073709551615
TRUNCATE(a,0)	18446744073709551615
FLOOR(a)	18446744073709551615
CEILING(a)	18446744073709551615
DROP PROCEDURE p1;
#
# MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result
#
SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11);
ROUND(18446744073709551615,-10)	ROUND(18446744073709551615,-11)
18446744070000000000	18446744100000000000
CREATE TABLE t1 AS SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `ROUND(18446744073709551615,-10)` decimal(21,0) unsigned NOT NULL,
  `ROUND(18446744073709551615,-11)` decimal(21,0) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT * FROM t1;
ROUND(18446744073709551615,-10)	ROUND(18446744073709551615,-11)
18446744070000000000	18446744100000000000
DROP TABLE t1;
#
# MDEV-23367 ROUND(18446744073709551615,-1) returns a wrong result
#
SELECT
ROUND(18446744073709551615,-1) AS c01,
ROUND(18446744073709551615,-19) AS c19;
c01	18446744073709551620
c19	20000000000000000000
CREATE OR REPLACE TABLE t1 AS
SELECT
ROUND(18446744073709551615,-1) AS c01,
ROUND(18446744073709551615,-19) AS c19;
SELECT * FROM t1;
c01	18446744073709551620
c19	20000000000000000000
SHOW CREATE TABLE t1;
Table	t1
Create Table	CREATE TABLE `t1` (
  `c01` decimal(21,0) unsigned NOT NULL,
  `c19` decimal(21,0) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE PROCEDURE p1(t VARCHAR(64))
BEGIN
SELECT t AS ``;
EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (a TYPE)', 'TYPE', t);
INSERT IGNORE INTO t1 VALUES (-100000000000000000000000000000);
INSERT IGNORE INTO t1 VALUES (100000000000000000000000000000);
CREATE TABLE t2 AS SELECT
a, ROUND(a,-1), ROUND(a,-2), ROUND(a,-19), ROUND(a,-20), ROUND(a,-30)
FROM t1
ORDER BY a;
SHOW CREATE TABLE t2;
SELECT * FROM t2;
DROP TABLE t1, t2;
END;
$$
CALL p1('tinyint');
	tinyint
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` tinyint(4) DEFAULT NULL,
  `ROUND(a,-1)` int(5) DEFAULT NULL,
  `ROUND(a,-2)` int(5) DEFAULT NULL,
  `ROUND(a,-19)` int(5) DEFAULT NULL,
  `ROUND(a,-20)` int(5) DEFAULT NULL,
  `ROUND(a,-30)` int(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-128
ROUND(a,-1)	-130
ROUND(a,-2)	-100
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	127
ROUND(a,-1)	130
ROUND(a,-2)	100
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('smallint');
	smallint
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` smallint(6) DEFAULT NULL,
  `ROUND(a,-1)` int(7) DEFAULT NULL,
  `ROUND(a,-2)` int(7) DEFAULT NULL,
  `ROUND(a,-19)` int(7) DEFAULT NULL,
  `ROUND(a,-20)` int(7) DEFAULT NULL,
  `ROUND(a,-30)` int(7) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-32768
ROUND(a,-1)	-32770
ROUND(a,-2)	-32800
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	32767
ROUND(a,-1)	32770
ROUND(a,-2)	32800
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('mediumint');
	mediumint
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` mediumint(9) DEFAULT NULL,
  `ROUND(a,-1)` bigint(10) DEFAULT NULL,
  `ROUND(a,-2)` bigint(10) DEFAULT NULL,
  `ROUND(a,-19)` bigint(10) DEFAULT NULL,
  `ROUND(a,-20)` bigint(10) DEFAULT NULL,
  `ROUND(a,-30)` bigint(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-8388608
ROUND(a,-1)	-8388610
ROUND(a,-2)	-8388600
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	8388607
ROUND(a,-1)	8388610
ROUND(a,-2)	8388600
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('int');
	int
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `ROUND(a,-1)` bigint(12) DEFAULT NULL,
  `ROUND(a,-2)` bigint(12) DEFAULT NULL,
  `ROUND(a,-19)` bigint(12) DEFAULT NULL,
  `ROUND(a,-20)` bigint(12) DEFAULT NULL,
  `ROUND(a,-30)` bigint(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-2147483648
ROUND(a,-1)	-2147483650
ROUND(a,-2)	-2147483600
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	2147483647
ROUND(a,-1)	2147483650
ROUND(a,-2)	2147483600
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint');
	bigint
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(20) DEFAULT NULL,
  `ROUND(a,-1)` decimal(20,0) DEFAULT NULL,
  `ROUND(a,-2)` decimal(20,0) DEFAULT NULL,
  `ROUND(a,-19)` decimal(20,0) DEFAULT NULL,
  `ROUND(a,-20)` decimal(20,0) DEFAULT NULL,
  `ROUND(a,-30)` decimal(20,0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a,-1)	-9223372036854775810
ROUND(a,-2)	-9223372036854775800
ROUND(a,-19)	-10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
a	9223372036854775807
ROUND(a,-1)	9223372036854775810
ROUND(a,-2)	9223372036854775800
ROUND(a,-19)	10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint(20)');
	bigint(20)
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(20) DEFAULT NULL,
  `ROUND(a,-1)` decimal(20,0) DEFAULT NULL,
  `ROUND(a,-2)` decimal(20,0) DEFAULT NULL,
  `ROUND(a,-19)` decimal(20,0) DEFAULT NULL,
  `ROUND(a,-20)` decimal(20,0) DEFAULT NULL,
  `ROUND(a,-30)` decimal(20,0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a,-1)	-9223372036854775810
ROUND(a,-2)	-9223372036854775800
ROUND(a,-19)	-10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
a	9223372036854775807
ROUND(a,-1)	9223372036854775810
ROUND(a,-2)	9223372036854775800
ROUND(a,-19)	10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint(21)');
	bigint(21)
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(21) DEFAULT NULL,
  `ROUND(a,-1)` decimal(21,0) DEFAULT NULL,
  `ROUND(a,-2)` decimal(21,0) DEFAULT NULL,
  `ROUND(a,-19)` decimal(21,0) DEFAULT NULL,
  `ROUND(a,-20)` decimal(21,0) DEFAULT NULL,
  `ROUND(a,-30)` decimal(21,0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a,-1)	-9223372036854775810
ROUND(a,-2)	-9223372036854775800
ROUND(a,-19)	-10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
a	9223372036854775807
ROUND(a,-1)	9223372036854775810
ROUND(a,-2)	9223372036854775800
ROUND(a,-19)	10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint(22)');
	bigint(22)
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(22) DEFAULT NULL,
  `ROUND(a,-1)` decimal(22,0) DEFAULT NULL,
  `ROUND(a,-2)` decimal(22,0) DEFAULT NULL,
  `ROUND(a,-19)` decimal(22,0) DEFAULT NULL,
  `ROUND(a,-20)` decimal(22,0) DEFAULT NULL,
  `ROUND(a,-30)` decimal(22,0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a,-1)	-9223372036854775810
ROUND(a,-2)	-9223372036854775800
ROUND(a,-19)	-10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
a	9223372036854775807
ROUND(a,-1)	9223372036854775810
ROUND(a,-2)	9223372036854775800
ROUND(a,-19)	10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint(23)');
	bigint(23)
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(23) DEFAULT NULL,
  `ROUND(a,-1)` decimal(23,0) DEFAULT NULL,
  `ROUND(a,-2)` decimal(23,0) DEFAULT NULL,
  `ROUND(a,-19)` decimal(23,0) DEFAULT NULL,
  `ROUND(a,-20)` decimal(23,0) DEFAULT NULL,
  `ROUND(a,-30)` decimal(23,0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a,-1)	-9223372036854775810
ROUND(a,-2)	-9223372036854775800
ROUND(a,-19)	-10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
a	9223372036854775807
ROUND(a,-1)	9223372036854775810
ROUND(a,-2)	9223372036854775800
ROUND(a,-19)	10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint(30)');
	bigint(30)
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(30) DEFAULT NULL,
  `ROUND(a,-1)` decimal(30,0) DEFAULT NULL,
  `ROUND(a,-2)` decimal(30,0) DEFAULT NULL,
  `ROUND(a,-19)` decimal(30,0) DEFAULT NULL,
  `ROUND(a,-20)` decimal(30,0) DEFAULT NULL,
  `ROUND(a,-30)` decimal(30,0) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	-9223372036854775808
ROUND(a,-1)	-9223372036854775810
ROUND(a,-2)	-9223372036854775800
ROUND(a,-19)	-10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
a	9223372036854775807
ROUND(a,-1)	9223372036854775810
ROUND(a,-2)	9223372036854775800
ROUND(a,-19)	10000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('tinyint unsigned');
	tinyint unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` tinyint(3) unsigned DEFAULT NULL,
  `ROUND(a,-1)` int(4) unsigned DEFAULT NULL,
  `ROUND(a,-2)` int(4) unsigned DEFAULT NULL,
  `ROUND(a,-19)` int(4) unsigned DEFAULT NULL,
  `ROUND(a,-20)` int(4) unsigned DEFAULT NULL,
  `ROUND(a,-30)` int(4) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a,-1)	0
ROUND(a,-2)	0
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	255
ROUND(a,-1)	260
ROUND(a,-2)	300
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('smallint unsigned');
	smallint unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` smallint(5) unsigned DEFAULT NULL,
  `ROUND(a,-1)` int(6) unsigned DEFAULT NULL,
  `ROUND(a,-2)` int(6) unsigned DEFAULT NULL,
  `ROUND(a,-19)` int(6) unsigned DEFAULT NULL,
  `ROUND(a,-20)` int(6) unsigned DEFAULT NULL,
  `ROUND(a,-30)` int(6) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a,-1)	0
ROUND(a,-2)	0
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	65535
ROUND(a,-1)	65540
ROUND(a,-2)	65500
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('mediumint unsigned');
	mediumint unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` mediumint(8) unsigned DEFAULT NULL,
  `ROUND(a,-1)` int(9) unsigned DEFAULT NULL,
  `ROUND(a,-2)` int(9) unsigned DEFAULT NULL,
  `ROUND(a,-19)` int(9) unsigned DEFAULT NULL,
  `ROUND(a,-20)` int(9) unsigned DEFAULT NULL,
  `ROUND(a,-30)` int(9) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a,-1)	0
ROUND(a,-2)	0
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	16777215
ROUND(a,-1)	16777220
ROUND(a,-2)	16777200
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('int unsigned');
	int unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` int(10) unsigned DEFAULT NULL,
  `ROUND(a,-1)` bigint(11) unsigned DEFAULT NULL,
  `ROUND(a,-2)` bigint(11) unsigned DEFAULT NULL,
  `ROUND(a,-19)` bigint(11) unsigned DEFAULT NULL,
  `ROUND(a,-20)` bigint(11) unsigned DEFAULT NULL,
  `ROUND(a,-30)` bigint(11) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a,-1)	0
ROUND(a,-2)	0
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	4294967295
ROUND(a,-1)	4294967300
ROUND(a,-2)	4294967300
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint unsigned');
	bigint unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(20) unsigned DEFAULT NULL,
  `ROUND(a,-1)` decimal(21,0) unsigned DEFAULT NULL,
  `ROUND(a,-2)` decimal(21,0) unsigned DEFAULT NULL,
  `ROUND(a,-19)` decimal(21,0) unsigned DEFAULT NULL,
  `ROUND(a,-20)` decimal(21,0) unsigned DEFAULT NULL,
  `ROUND(a,-30)` decimal(21,0) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a,-1)	0
ROUND(a,-2)	0
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	18446744073709551615
ROUND(a,-1)	18446744073709551620
ROUND(a,-2)	18446744073709551600
ROUND(a,-19)	20000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint(20) unsigned');
	bigint(20) unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(20) unsigned DEFAULT NULL,
  `ROUND(a,-1)` decimal(21,0) unsigned DEFAULT NULL,
  `ROUND(a,-2)` decimal(21,0) unsigned DEFAULT NULL,
  `ROUND(a,-19)` decimal(21,0) unsigned DEFAULT NULL,
  `ROUND(a,-20)` decimal(21,0) unsigned DEFAULT NULL,
  `ROUND(a,-30)` decimal(21,0) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a,-1)	0
ROUND(a,-2)	0
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	18446744073709551615
ROUND(a,-1)	18446744073709551620
ROUND(a,-2)	18446744073709551600
ROUND(a,-19)	20000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint(21) unsigned');
	bigint(21) unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(21) unsigned DEFAULT NULL,
  `ROUND(a,-1)` decimal(22,0) unsigned DEFAULT NULL,
  `ROUND(a,-2)` decimal(22,0) unsigned DEFAULT NULL,
  `ROUND(a,-19)` decimal(22,0) unsigned DEFAULT NULL,
  `ROUND(a,-20)` decimal(22,0) unsigned DEFAULT NULL,
  `ROUND(a,-30)` decimal(22,0) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a,-1)	0
ROUND(a,-2)	0
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	18446744073709551615
ROUND(a,-1)	18446744073709551620
ROUND(a,-2)	18446744073709551600
ROUND(a,-19)	20000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint(22) unsigned');
	bigint(22) unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(22) unsigned DEFAULT NULL,
  `ROUND(a,-1)` decimal(23,0) unsigned DEFAULT NULL,
  `ROUND(a,-2)` decimal(23,0) unsigned DEFAULT NULL,
  `ROUND(a,-19)` decimal(23,0) unsigned DEFAULT NULL,
  `ROUND(a,-20)` decimal(23,0) unsigned DEFAULT NULL,
  `ROUND(a,-30)` decimal(23,0) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a,-1)	0
ROUND(a,-2)	0
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	18446744073709551615
ROUND(a,-1)	18446744073709551620
ROUND(a,-2)	18446744073709551600
ROUND(a,-19)	20000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint(23) unsigned');
	bigint(23) unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(23) unsigned DEFAULT NULL,
  `ROUND(a,-1)` decimal(24,0) unsigned DEFAULT NULL,
  `ROUND(a,-2)` decimal(24,0) unsigned DEFAULT NULL,
  `ROUND(a,-19)` decimal(24,0) unsigned DEFAULT NULL,
  `ROUND(a,-20)` decimal(24,0) unsigned DEFAULT NULL,
  `ROUND(a,-30)` decimal(24,0) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a,-1)	0
ROUND(a,-2)	0
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	18446744073709551615
ROUND(a,-1)	18446744073709551620
ROUND(a,-2)	18446744073709551600
ROUND(a,-19)	20000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
CALL p1('bigint(30) unsigned');
	bigint(30) unsigned
Table	t2
Create Table	CREATE TABLE `t2` (
  `a` bigint(30) unsigned DEFAULT NULL,
  `ROUND(a,-1)` decimal(31,0) unsigned DEFAULT NULL,
  `ROUND(a,-2)` decimal(31,0) unsigned DEFAULT NULL,
  `ROUND(a,-19)` decimal(31,0) unsigned DEFAULT NULL,
  `ROUND(a,-20)` decimal(31,0) unsigned DEFAULT NULL,
  `ROUND(a,-30)` decimal(31,0) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
a	0
ROUND(a,-1)	0
ROUND(a,-2)	0
ROUND(a,-19)	0
ROUND(a,-20)	0
ROUND(a,-30)	0
a	18446744073709551615
ROUND(a,-1)	18446744073709551620
ROUND(a,-2)	18446744073709551600
ROUND(a,-19)	20000000000000000000
ROUND(a,-20)	0
ROUND(a,-30)	0
DROP PROCEDURE p1;
#
# MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result
#
SELECT
ROUND(18446744073709551615,NULL) AS c1,
ROUND(18446744073709551615,rand()*0) AS c2,
ROUND(18446744073709551615,rand()*0-19) AS c3;
c1	NULL
c2	18446744073709551615
c3	20000000000000000000
CREATE OR REPLACE TABLE t1 AS
SELECT
ROUND(18446744073709551615,NULL) AS c1,
ROUND(18446744073709551615,rand()*0) AS c2,
ROUND(18446744073709551615,rand()*0-19) AS c3;
SELECT * FROM t1;
c1	NULL
c2	18446744073709551615
c3	20000000000000000000
SHOW CREATE TABLE t1;
Table	t1
Create Table	CREATE TABLE `t1` (
  `c1` bigint(20) unsigned DEFAULT NULL,
  `c2` decimal(21,0) unsigned NOT NULL,
  `c3` decimal(21,0) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
#
# End of 10.4 tests
#
#
# Start of 10.5 tests
#
#
# MDEV-20363 Assertion `is_unsigned() == attr.unsigned_flag' failed in Type_handler_longlong::make_table_field
#
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
SELECT DISTINCT 1 FROM t1 GROUP BY 0 >> NULL WITH ROLLUP;
1
1
DROP TABLE t1;
#
# MDEV-30710 Incorrect operator when comparing large unsigned integers.
#
create table t1(c0 tinyint unique);
insert into t1 values (127);
insert into t1 values (-128);
select * from t1 where 18446744073700599371 > c0;
c0
-128
127
drop table t1;
#
# MDEV-20548 Unexpected error on CREATE..SELECT HEX(num)
#
SET sql_mode=STRICT_ALL_TABLES;
CREATE TABLE t1 AS SELECT HEX(-2) AS h;
SELECT * FROM t1;
h
FFFFFFFFFFFFFFFE
SHOW COLUMNS IN t1;
Field	Type	Null	Key	Default	Extra
h	varchar(16)	YES		NULL	
DROP TABLE t1;
CREATE TABLE t1 AS SELECT HEX(-1) AS h;
SELECT * FROM t1;
h
FFFFFFFFFFFFFFFF
SHOW COLUMNS IN t1;
Field	Type	Null	Key	Default	Extra
h	varchar(16)	YES		NULL	
DROP TABLE t1;
CREATE TABLE t1 AS SELECT HEX(1) AS h;
SELECT * FROM t1;
h
1
SHOW COLUMNS IN t1;
Field	Type	Null	Key	Default	Extra
h	varchar(16)	YES		NULL	
DROP TABLE t1;
CREATE TABLE t1 AS SELECT HEX(2) AS h;
SELECT * FROM t1;
h
2
SHOW COLUMNS IN t1;
Field	Type	Null	Key	Default	Extra
h	varchar(16)	YES		NULL	
DROP TABLE t1;
CREATE TABLE t1 (a TINYINT);
INSERT INTO t1 VALUES (-0x80),(-2),(-1),(1),(2),(0x7f);
CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1;
SELECT * FROM t2;
a	h
-128	FFFFFFFFFFFFFF80
-2	FFFFFFFFFFFFFFFE
-1	FFFFFFFFFFFFFFFF
1	1
2	2
127	7F
SHOW COLUMNS IN t2;
Field	Type	Null	Key	Default	Extra
a	tinyint(4)	YES		NULL	
h	varchar(16)	YES		NULL	
DROP TABLE t1, t2;
CREATE TABLE t1 (a SMALLINT);
INSERT INTO t1 VALUES (-0x8000),(-0x80),(-2),(-1),(1),(2),(0x7f),(0x7fff);
CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1;
SELECT * FROM t2;
a	h
-32768	FFFFFFFFFFFF8000
-128	FFFFFFFFFFFFFF80
-2	FFFFFFFFFFFFFFFE
-1	FFFFFFFFFFFFFFFF
1	1
2	2
127	7F
32767	7FFF
SHOW COLUMNS IN t2;
Field	Type	Null	Key	Default	Extra
a	smallint(6)	YES		NULL	
h	varchar(16)	YES		NULL	
DROP TABLE t1, t2;
CREATE TABLE t1 (a MEDIUMINT);
INSERT INTO t1 VALUES (-0x800000);
INSERT INTO t1 VALUES (-0x8000),(-0x80),(-2),(-1),(1),(2),(0x7f),(0x7fff);
INSERT INTO t1 VALUES (0x7fffff);
CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1;
SELECT * FROM t2;
a	h
-8388608	FFFFFFFFFF800000
-32768	FFFFFFFFFFFF8000
-128	FFFFFFFFFFFFFF80
-2	FFFFFFFFFFFFFFFE
-1	FFFFFFFFFFFFFFFF
1	1
2	2
127	7F
32767	7FFF
8388607	7FFFFF
SHOW COLUMNS IN t2;
Field	Type	Null	Key	Default	Extra
a	mediumint(9)	YES		NULL	
h	varchar(16)	YES		NULL	
DROP TABLE t1, t2;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (-0x80000000);
INSERT INTO t1 VALUES (-0x800000);
INSERT INTO t1 VALUES (-0x8000),(-0x80),(-2),(-1),(1),(2),(0x7f),(0x7fff);
INSERT INTO t1 VALUES (0x7fffff);
INSERT INTO t1 VALUES (0x7fffffff);
CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1;
SELECT * FROM t2;
a	h
-2147483648	FFFFFFFF80000000
-8388608	FFFFFFFFFF800000
-32768	FFFFFFFFFFFF8000
-128	FFFFFFFFFFFFFF80
-2	FFFFFFFFFFFFFFFE
-1	FFFFFFFFFFFFFFFF
1	1
2	2
127	7F
32767	7FFF
8388607	7FFFFF
2147483647	7FFFFFFF
SHOW COLUMNS IN t2;
Field	Type	Null	Key	Default	Extra
a	int(11)	YES		NULL	
h	varchar(16)	YES		NULL	
DROP TABLE t1, t2;
CREATE TABLE t1 (a BIGINT);
INSERT INTO t1 VALUES (-0x8000000000000000);
INSERT INTO t1 VALUES (-0x80000000000000);
INSERT INTO t1 VALUES (-0x800000000000);
INSERT INTO t1 VALUES (-0x8000000000);
INSERT INTO t1 VALUES (-0x80000000);
INSERT INTO t1 VALUES (-0x800000);
INSERT INTO t1 VALUES (-0x8000),(-0x80),(-2),(-1),(1),(2),(0x7f),(0x7fff);
INSERT INTO t1 VALUES (0x7fffff);
INSERT INTO t1 VALUES (0x7fffffff);
INSERT INTO t1 VALUES (0x7fffffffff);
INSERT INTO t1 VALUES (0x7fffffffffff);
INSERT INTO t1 VALUES (0x7fffffffffffff);
INSERT INTO t1 VALUES (0x7fffffffffffffff);
CREATE TABLE t2 AS SELECT a, HEX(a) AS h FROM t1;
SELECT * FROM t2;
a	h
-9223372036854775808	8000000000000000
-36028797018963968	FF80000000000000
-140737488355328	FFFF800000000000
-549755813888	FFFFFF8000000000
-2147483648	FFFFFFFF80000000
-8388608	FFFFFFFFFF800000
-32768	FFFFFFFFFFFF8000
-128	FFFFFFFFFFFFFF80
-2	FFFFFFFFFFFFFFFE
-1	FFFFFFFFFFFFFFFF
1	1
2	2
127	7F
32767	7FFF
8388607	7FFFFF
2147483647	7FFFFFFF
549755813887	7FFFFFFFFF
140737488355327	7FFFFFFFFFFF
36028797018963967	7FFFFFFFFFFFFF
9223372036854775807	7FFFFFFFFFFFFFFF
SHOW COLUMNS IN t2;
Field	Type	Null	Key	Default	Extra
a	bigint(20)	YES		NULL	
h	varchar(16)	YES		NULL	
DROP TABLE t1, t2;
SET sql_mode=DEFAULT;
#
# End of 10.5 tests
#
#
# MDEV-32203 Raise notes when an index cannot be used on data type mismatch
#
SET note_verbosity=unusable_keys;
CREATE TABLE t1 (indexed_col INT, KEY(indexed_col));
FOR i IN 1..31
DO
INSERT INTO t1 VALUES (MAKEDATE(2023, i));
END FOR;
$$
SELECT * FROM t1 WHERE indexed_col=20230101;
indexed_col
20230101
SELECT * FROM t1 WHERE indexed_col=20230101102030;
indexed_col
SELECT * FROM t1 WHERE indexed_col=20230101102030.1;
indexed_col
SELECT * FROM t1 WHERE indexed_col=20230101102030.1e0;
indexed_col
SELECT * FROM t1 WHERE indexed_col='10:20:30';
indexed_col
SELECT * FROM t1 WHERE indexed_col='2001-01-01';
indexed_col
SELECT * FROM t1 WHERE indexed_col='2001-01-01 10:20:30';
indexed_col
SELECT * FROM t1 WHERE indexed_col=DATE'2001-01-01';
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `int` = "DATE'2001-01-01'" of type `date`
SELECT * FROM t1 WHERE indexed_col=TIME'10:20:30';
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `int` = "TIME'10:20:30'" of type `time`
SELECT * FROM t1 WHERE indexed_col=TIMESTAMP'2001-01-01 10:20:30';
indexed_col
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `int` = "TIMESTAMP'2001-01-01 10:20:30'" of type `datetime`
SELECT * FROM t1 WHERE indexed_col=0x00;
indexed_col
SELECT * FROM t1 WHERE indexed_col=_utf8mb3'0' COLLATE utf8mb3_bin;
indexed_col
CREATE TABLE t2 (not_indexed_col INT);
INSERT INTO t2 VALUES (20230101),(20230102);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	20230101
20230102	20230102
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col INT UNSIGNED);
INSERT INTO t2 VALUES (20230101),(20230102);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	20230101
20230102	20230102
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col BIGINT);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col BIGINT UNSIGNED);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DECIMAL(30,6));
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col FLOAT);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DOUBLE);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DATE);
INSERT INTO t2 VALUES ('2023-01-01'),('2023-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	2023-01-01
20230102	2023-01-02
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `int` = "`t2`.`not_indexed_col`" of type `date`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DATETIME);
INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	2023-01-01 00:00:00
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `int` = "`t2`.`not_indexed_col`" of type `datetime`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col TIMESTAMP);
INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	2023-01-01 00:00:00
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `int` = "`t2`.`not_indexed_col`" of type `timestamp`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARBINARY(32));
INSERT INTO t2 VALUES (0x30),(0x31);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARCHAR(32)) CHARACTER SET latin1;
INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '2001-01-01'
Warning	1292	Truncated incorrect INTEGER value: '2001-01-02'
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARCHAR(32) CHARACTER SET utf8mb3);
INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '2001-01-01'
Warning	1292	Truncated incorrect INTEGER value: '2001-01-02'
DROP TABLE t2;
DROP TABLE t1;
SET note_verbosity=DEFAULT;
SET note_verbosity=unusable_keys;
CREATE TABLE t1 (indexed_col BIGINT, KEY(indexed_col));
FOR i IN 1..31
DO
INSERT INTO t1 VALUES (MAKEDATE(2023, i));
END FOR;
$$
SELECT * FROM t1 WHERE indexed_col=20230101;
indexed_col
20230101
SELECT * FROM t1 WHERE indexed_col=20230101102030;
indexed_col
SELECT * FROM t1 WHERE indexed_col=20230101102030.1;
indexed_col
SELECT * FROM t1 WHERE indexed_col=20230101102030.1e0;
indexed_col
SELECT * FROM t1 WHERE indexed_col='10:20:30';
indexed_col
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: '10:20:30'
SELECT * FROM t1 WHERE indexed_col='2001-01-01';
indexed_col
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: '2001-01-01'
SELECT * FROM t1 WHERE indexed_col='2001-01-01 10:20:30';
indexed_col
Warnings:
Warning	1292	Truncated incorrect DECIMAL value: '2001-01-01 10:20:30'
SELECT * FROM t1 WHERE indexed_col=DATE'2001-01-01';
indexed_col
SELECT * FROM t1 WHERE indexed_col=TIME'10:20:30';
indexed_col
SELECT * FROM t1 WHERE indexed_col=TIMESTAMP'2001-01-01 10:20:30';
indexed_col
SELECT * FROM t1 WHERE indexed_col=0x00;
indexed_col
SELECT * FROM t1 WHERE indexed_col=_utf8mb3'0' COLLATE utf8mb3_bin;
indexed_col
CREATE TABLE t2 (not_indexed_col INT);
INSERT INTO t2 VALUES (20230101),(20230102);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	20230101
20230102	20230102
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col INT UNSIGNED);
INSERT INTO t2 VALUES (20230101),(20230102);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	20230101
20230102	20230102
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col BIGINT);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col BIGINT UNSIGNED);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DECIMAL(30,6));
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col FLOAT);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DOUBLE);
INSERT INTO t2 VALUES (20230101102030),(20230101102031);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DATE);
INSERT INTO t2 VALUES ('2023-01-01'),('2023-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	2023-01-01
20230102	2023-01-02
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `bigint` = "`t2`.`not_indexed_col`" of type `date`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col DATETIME);
INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	2023-01-01 00:00:00
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `bigint` = "`t2`.`not_indexed_col`" of type `datetime`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col TIMESTAMP);
INSERT INTO t2 VALUES ('2023-01-01 00:00:00'),('2023-01-01 00:00:01');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
20230101	2023-01-01 00:00:00
Warnings:
Note	1105	Cannot use key `indexed_col` part[0] for lookup: `test`.`t1`.`indexed_col` of type `bigint` = "`t2`.`not_indexed_col`" of type `timestamp`
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARBINARY(32));
INSERT INTO t2 VALUES (0x30),(0x31);
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARCHAR(32)) CHARACTER SET latin1;
INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '2001-01-01'
Warning	1292	Truncated incorrect INTEGER value: '2001-01-02'
DROP TABLE t2;
CREATE TABLE t2 (not_indexed_col VARCHAR(32) CHARACTER SET utf8mb3);
INSERT INTO t2 VALUES ('2001-01-01'),('2001-01-02');
SELECT * FROM t1, t2 WHERE indexed_col=not_indexed_col;
indexed_col	not_indexed_col
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '2001-01-01'
Warning	1292	Truncated incorrect INTEGER value: '2001-01-02'
DROP TABLE t2;
DROP TABLE t1;
SET note_verbosity=DEFAULT;
#
# MDEV-34123 CONCAT Function Returns Unexpected Empty Set in Query
#
CREATE TABLE t0 (c INT);
INSERT INTO t0 VALUES (0),(100),(200);
#
# WHERE <search condition>
#
CREATE TABLE t1 AS SELECT * FROM t0;
SELECT * FROM t1 WHERE c;
c
100
200
SELECT * FROM t1 WHERE c IS FALSE;
c
0
SELECT * FROM t1 WHERE c IS TRUE;
c
100
200
SELECT * FROM t1 WHERE COALESCE(c);
c
100
200
DROP TABLE t1;
#
# HAVING <search condition>
#
CREATE TABLE t1 AS SELECT * FROM t0;
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING c2;
c2
100
200
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING c2 IS FALSE;
c2
0
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING c2 IS TRUE;
c2
100
200
SELECT COALESCE(c,c) AS c2 FROM t1 GROUP BY c2 HAVING COALESCE(c2);
c2
100
200
DROP TABLE t1;
#
# <join condition> :=  ON <search condition>
#
CREATE TABLE t1 AS SELECT * FROM t0;
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (t1.c);
c
100
200
100
200
100
200
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (t1.c IS FALSE);
c
0
0
0
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (t1.c IS TRUE);
c
100
200
100
200
100
200
SELECT t1.c FROM t1 JOIN t1 AS t2 ON (COALESCE(t1.c));
c
100
200
100
200
100
200
DROP TABLE t1;
#
# <delete statement: searched>
#   DELETE FROM <target table> [ WHERE <search condition> ]
#
CREATE TABLE t1 AS SELECT * FROM t0;
DELETE FROM t1 WHERE c;
SELECT * FROM t1;
c
0
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
DELETE FROM t1 WHERE c IS FALSE;
SELECT * FROM t1;
c
100
200
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
DELETE FROM t1 WHERE c IS TRUE;
SELECT * FROM t1;
c
0
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
DELETE FROM t1 WHERE COALESCE(c);
SELECT * FROM t1;
c
0
DROP TABLE t1;
#
# <update statement: searched>
#   UPDATE <target table> SET <set clause list> [ WHERE <search condition> ]
CREATE TABLE t1 AS SELECT * FROM t0;
UPDATE t1 SET c=c+11 WHERE c;
SELECT * FROM t1;
c
0
111
211
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
UPDATE t1 SET c=c+11 WHERE c IS FALSE;
SELECT * FROM t1;
c
11
100
200
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
UPDATE t1 SET c=c+11 WHERE c IS TRUE;
SELECT * FROM t1;
c
0
111
211
DROP TABLE t1;
CREATE TABLE t1 AS SELECT * FROM t0;
UPDATE t1 SET c=c+11 WHERE COALESCE(c);
SELECT * FROM t1;
c
0
111
211
DROP TABLE t1;
#
# <check constraint definition>
#   CHECK <left paren> <search condition> <right paren>
CREATE TABLE t1 LIKE t0;
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(c);
INSERT INTO t1 SELECT * FROM t0 WHERE NOT c;
ERROR 23000: CONSTRAINT `check0` failed for `test`.`t1`
INSERT INTO t1 SELECT * FROM t0 WHERE c;
SELECT * FROM t1;
c
100
200
DROP TABLE t1;
CREATE TABLE t1 LIKE t0;
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(c IS FALSE);
INSERT INTO t1 SELECT * FROM t0 WHERE c IS FALSE;
INSERT INTO t1 SELECT * FROM t0 WHERE c IS TRUE;
ERROR 23000: CONSTRAINT `check0` failed for `test`.`t1`
SELECT * FROM t1;
c
0
DROP TABLE t1;
CREATE TABLE t1 LIKE t0;
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(c IS TRUE);
INSERT INTO t1 SELECT * FROM t0 WHERE c IS FALSE;
ERROR 23000: CONSTRAINT `check0` failed for `test`.`t1`
INSERT INTO t1 SELECT * FROM t0 WHERE c IS TRUE;
SELECT * FROM t1;
c
100
200
DROP TABLE t1;
CREATE TABLE t1 LIKE t0;
ALTER TABLE t1 ADD CONSTRAINT check0 CHECK(COALESCE(c));
INSERT INTO t1 SELECT * FROM t0 WHERE c IS FALSE;
ERROR 23000: CONSTRAINT `check0` failed for `test`.`t1`
INSERT INTO t1 SELECT * FROM t0 WHERE c IS TRUE;
SELECT * FROM t1;
c
100
200
DROP TABLE t1;
#
# <case expression>
#   WHEN <search condition> THEN <result>
CREATE TABLE t1 AS SELECT * FROM t0;
SELECT c, CASE WHEN c THEN 'true' ELSE 'false' END AS c2 FROM t1;
c	c2
0	false
100	true
200	true
SELECT c, CASE WHEN COALESCE(c) THEN 'true' ELSE 'false' END AS c2 FROM t1;
c	c2
0	false
100	true
200	true
DROP TABLE t1;
DROP TABLE t0;
#
# End of 10.6 tests
#