#
# 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) DEFAULT NULL,
`CONCAT(sint8)` varchar(4) DEFAULT NULL,
`CONCAT(uint16)` varchar(5) DEFAULT NULL,
`CONCAT(sint16)` varchar(6) DEFAULT NULL,
`CONCAT(uint24)` varchar(8) DEFAULT NULL,
`CONCAT(sint24)` varchar(8) DEFAULT NULL,
`CONCAT(uint32)` varchar(10) DEFAULT NULL,
`CONCAT(sint32)` varchar(11) DEFAULT NULL,
`CONCAT(uint64)` varchar(20) DEFAULT NULL,
`CONCAT(sint64)` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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) DEFAULT NULL,
`CONCAT(COALESCE(sint8))` varchar(4) DEFAULT NULL,
`CONCAT(COALESCE(uint16))` varchar(5) DEFAULT NULL,
`CONCAT(COALESCE(sint16))` varchar(6) DEFAULT NULL,
`CONCAT(COALESCE(uint24))` varchar(8) DEFAULT NULL,
`CONCAT(COALESCE(sint24))` varchar(8) DEFAULT NULL,
`CONCAT(COALESCE(uint32))` varchar(10) DEFAULT NULL,
`CONCAT(COALESCE(sint32))` varchar(11) DEFAULT NULL,
`CONCAT(COALESCE(uint64))` varchar(20) DEFAULT NULL,
`CONCAT(COALESCE(sint64))` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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) DEFAULT NULL,
`CONCAT(sint8())` varchar(4) DEFAULT NULL,
`CONCAT(uint16())` varchar(5) DEFAULT NULL,
`CONCAT(sint16())` varchar(6) DEFAULT NULL,
`CONCAT(uint24())` varchar(8) DEFAULT NULL,
`CONCAT(sint24())` varchar(8) DEFAULT NULL,
`CONCAT(uint32())` varchar(10) DEFAULT NULL,
`CONCAT(sint32())` varchar(11) DEFAULT NULL,
`CONCAT(uint64())` varchar(20) DEFAULT NULL,
`CONCAT(sint64())` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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) DEFAULT NULL,
`CONCAT(COALESCE(sint8()))` varchar(4) DEFAULT NULL,
`CONCAT(COALESCE(uint16()))` varchar(5) DEFAULT NULL,
`CONCAT(COALESCE(sint16()))` varchar(6) DEFAULT NULL,
`CONCAT(COALESCE(uint24()))` varchar(8) DEFAULT NULL,
`CONCAT(COALESCE(sint24()))` varchar(8) DEFAULT NULL,
`CONCAT(COALESCE(uint32()))` varchar(10) DEFAULT NULL,
`CONCAT(COALESCE(sint32()))` varchar(11) DEFAULT NULL,
`CONCAT(COALESCE(uint64()))` varchar(20) DEFAULT NULL,
`CONCAT(COALESCE(sint64()))` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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, aa
#
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 aa;
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 aa-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> NULL WITH ROLLUP;
1
1
DROP TABLE t1;
#
# End of 10.5 tests
#