1--echo # 2--echo # Start of 5.5 tests 3--echo # 4 5--echo # 6--echo # MDEV-15955 Assertion `field_types == 0 || field_types[field_pos] == MYSQL_TYPE_LONGLONG' failed in Protocol_text::store_longlong 7--echo # 8 9CREATE TABLE t1 (a INT); 10INSERT INTO t1 VALUES (1),(2); 11SELECT @a := 1 FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); 12SELECT COALESCE(1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); 13SELECT COALESCE(@a:=1) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); 14SELECT COALESCE(@a) FROM t1 ORDER BY STRCMP(STDDEV_SAMP(a), 'bar'); 15DROP TABLE t1; 16 17--echo # 18--echo # MDEV-21065 UNIQUE constraint causes a query with string comparison to omit a row in the result set 19--echo # 20 21CREATE TABLE t1 (c0 INT UNIQUE); 22INSERT INTO t1 VALUES (NULL), (NULL), (NULL), (NULL), (1), (0); 23SELECT * FROM t1 WHERE c0 < '\n2'; 24DROP TABLE t1; 25 26SELECT CAST('\n2' AS INT); 27 28 29--echo # 30--echo # End of 5.5 tests 31--echo # 32 33 34--echo # 35--echo # Start of 10.1 tests 36--echo # 37 38--echo # 39--echo # MDEV-8741 Equal field propagation leaves some remainders after simplifying WHERE zerofill_column=2010 AND zerofill_column>=2010 40--echo # 41 42CREATE TABLE t1 (a INT ZEROFILL); 43INSERT INTO t1 VALUES (2010),(2020); 44EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND a>=2010; 45DROP TABLE t1; 46 47--echo # 48--echo # MDEV-8369 Unexpected impossible WHERE for a condition on a ZEROFILL field 49--echo # 50CREATE TABLE t1 (a INT ZEROFILL); 51INSERT INTO t1 VALUES (128),(129); 52SELECT * FROM t1 WHERE a=128; 53SELECT * FROM t1 WHERE hex(a)='80'; 54SELECT * FROM t1 WHERE a=128 AND hex(a)='80'; 55EXPLAIN EXTENDED 56SELECT * FROM t1 WHERE a=128 AND hex(a)='80'; 57DROP TABLE t1; 58 59--echo # 60--echo # End of 10.1 tests 61--echo # 62 63--echo # 64--echo # Start of 10.2 tests 65--echo # 66 67--echo # 68--echo # MDEV-9393 Split Copy_field::get_copy_func() into virtual methods in Field 69--echo # 70 71# DECIMAL -> INT 72CREATE TABLE t1 (a INT); 73INSERT INTO t1 VALUES (10.1),(10.9); 74SELECT * FROM t1; 75DROP TABLE t1; 76 77CREATE TABLE t1 (a INT); 78CREATE TABLE t2 (a DECIMAL(10,2)); 79INSERT INTO t2 VALUES (10.1),(10.9); 80INSERT INTO t1 SELECT a FROM t2; 81SELECT * FROM t1; 82DROP TABLE t1,t2; 83 84CREATE TABLE t1 (a DECIMAL(10,2)); 85INSERT INTO t1 VALUES (10.1),(10.9); 86ALTER TABLE t1 MODIFY a INT; 87SELECT * FROM t1; 88DROP TABLE t1; 89 90# TIME -> INT 91CREATE TABLE t1 (a INT); 92INSERT INTO t1 VALUES (TIME'00:00:10.1'),(TIME'00:00:10.9'); 93SELECT * FROM t1; 94DROP TABLE t1; 95 96CREATE TABLE t1 (a INT); 97CREATE TABLE t2 (a TIME(1)); 98INSERT INTO t2 VALUES (10.1),(10.9); 99INSERT INTO t1 SELECT a FROM t2; 100SELECT * FROM t1; 101DROP TABLE t1,t2; 102 103CREATE TABLE t1 (a TIME(1)); 104INSERT INTO t1 VALUES (10.1),(10.9); 105ALTER TABLE t1 MODIFY a INT; 106SELECT * FROM t1; 107DROP TABLE t1; 108 109--echo # 110--echo # MDEV-9334 ALTER from DECIMAL to BIGINT UNSIGNED returns a wrong result 111--echo # 112 113CREATE TABLE t1 (a DECIMAL(30,0)); 114INSERT INTO t1 VALUES (CAST(0xFFFFFFFFFFFFFFFF AS UNSIGNED)); 115SELECT * FROM t1; 116ALTER TABLE t1 MODIFY a BIGINT UNSIGNED; 117SELECT * FROM t1; 118DROP TABLE t1; 119 120 121--echo # 122--echo # End of 10.2 tests 123--echo # 124 125--echo # 126--echo # Start of 10.3 tests 127--echo # 128 129--echo # 130--echo # MDEV-15926 MEDIUMINT returns wrong I_S attributes 131--echo # 132 133CREATE TABLE t1 (a MEDIUMINT, b MEDIUMINT UNSIGNED); 134SELECT COLUMN_NAME, NUMERIC_PRECISION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' ORDER BY COLUMN_NAME; 135DROP TABLE t1; 136 137 138--echo # 139--echo # MDEV-15946 MEDIUMINT(N<8) creates a wrong data type on conversion to string 140--echo # 141 142CREATE TABLE t1 ( 143 uint8 TINYINT(2) UNSIGNED, sint8 TINYINT(2), 144 uint16 SMALLINT(2) UNSIGNED, sint16 SMALLINT(2), 145 uint24 MEDIUMINT(2) UNSIGNED, sint24 MEDIUMINT(2), 146 uint32 INT(2) UNSIGNED, sint32 INT(2), 147 uint64 BIGINT(2) UNSIGNED, sint64 BIGINT(2) 148); 149 150CREATE TABLE t2 AS SELECT 151 CONCAT(uint8),CONCAT(sint8), 152 CONCAT(uint16),CONCAT(sint16), 153 CONCAT(uint24),CONCAT(sint24), 154 CONCAT(uint32),CONCAT(sint32), 155 CONCAT(uint64),CONCAT(sint64) 156FROM t1; 157SHOW CREATE TABLE t2; 158DROP TABLE t2; 159 160CREATE TABLE t2 AS SELECT 161 CONCAT(COALESCE(uint8)),CONCAT(COALESCE(sint8)), 162 CONCAT(COALESCE(uint16)),CONCAT(COALESCE(sint16)), 163 CONCAT(COALESCE(uint24)),CONCAT(COALESCE(sint24)), 164 CONCAT(COALESCE(uint32)),CONCAT(COALESCE(sint32)), 165 CONCAT(COALESCE(uint64)),CONCAT(COALESCE(sint64)) 166FROM t1; 167SHOW CREATE TABLE t2; 168DROP TABLE t2; 169 170DROP TABLE t1; 171 172CREATE FUNCTION uint8() RETURNS TINYINT(2) UNSIGNED RETURN 1; 173CREATE FUNCTION sint8() RETURNS TINYINT(2) RETURN 1; 174CREATE FUNCTION uint16() RETURNS SMALLINT(2) UNSIGNED RETURN 1; 175CREATE FUNCTION sint16() RETURNS SMALLINT(2) RETURN 1; 176CREATE FUNCTION uint24() RETURNS MEDIUMINT(2) UNSIGNED RETURN 1; 177CREATE FUNCTION sint24() RETURNS MEDIUMINT(2) RETURN 1; 178CREATE FUNCTION uint32() RETURNS INT(2) UNSIGNED RETURN 1; 179CREATE FUNCTION sint32() RETURNS INT(2) RETURN 1; 180CREATE FUNCTION uint64() RETURNS BIGINT(2) UNSIGNED RETURN 1; 181CREATE FUNCTION sint64() RETURNS BIGINT(2) RETURN 1; 182 183CREATE TABLE t1 AS SELECT 184 CONCAT(uint8()), CONCAT(sint8()), 185 CONCAT(uint16()),CONCAT(sint16()), 186 CONCAT(uint24()),CONCAT(sint24()), 187 CONCAT(uint32()),CONCAT(sint32()), 188 CONCAT(uint64()),CONCAT(sint64()); 189SHOW CREATE TABLE t1; 190DROP TABLE t1; 191 192CREATE TABLE t1 AS SELECT 193 CONCAT(COALESCE(uint8())),CONCAT(COALESCE(sint8())), 194 CONCAT(COALESCE(uint16())),CONCAT(COALESCE(sint16())), 195 CONCAT(COALESCE(uint24())),CONCAT(COALESCE(sint24())), 196 CONCAT(COALESCE(uint32())),CONCAT(COALESCE(sint32())), 197 CONCAT(COALESCE(uint64())),CONCAT(COALESCE(sint64())); 198SHOW CREATE TABLE t1; 199DROP TABLE t1; 200 201DROP FUNCTION uint8; 202DROP FUNCTION sint8; 203DROP FUNCTION uint16; 204DROP FUNCTION sint16; 205DROP FUNCTION uint24; 206DROP FUNCTION sint24; 207DROP FUNCTION uint32; 208DROP FUNCTION sint32; 209DROP FUNCTION uint64; 210DROP FUNCTION sint64; 211 212--echo # 213--echo # MDEV-20285 Wrong result on INSERT..SELECT when converting from SIGNED to UNSIGNED 214--echo # 215 216CREATE TABLE t1 (a TINYINT UNSIGNED); 217CREATE TABLE t2 (a TINYINT); 218INSERT INTO t1 VALUES (255); 219INSERT IGNORE INTO t2 SELECT a FROM t1; 220SELECT * FROM t2; 221DROP TABLE t1, t2; 222 223--echo # 224--echo # End of 10.3 tests 225--echo # 226 227--echo # 228--echo # Start of 10.4 tests 229--echo # 230 231--echo # 232--echo # MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters 233--echo # 234 235CREATE TABLE t1 (a INT); 236INSERT INTO t1 VALUES (1),(2),(3); 237EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1+a<=>1+a; 238EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1,1; 239EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1+a' USING 1; 240EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1+a<=>?+a' USING 1; 241DROP TABLE t1; 242 243--echo # 244--echo # MDEV-15759 Expect "Impossible WHERE" for indexed_int_column=out_of_range_int_constant 245--echo # 246 247CREATE TABLE t1 (a TINYINT, KEY(a)); 248INSERT INTO t1 VALUES (1),(2),(3),(4),(5); 249EXPLAIN SELECT * FROM t1 WHERE a=200; 250EXPLAIN SELECT * FROM t1 WHERE a<=>200; 251DROP TABLE t1; 252 253--echo # 254--echo # MDEV-8554 Expect "Impossible WHERE" for never true values like a!=a, a<a, a>a 255--echo # 256 257CREATE TABLE t1 (a INT); 258INSERT INTO t1 VALUES (1),(2),(3); 259 260EXPLAIN SELECT * FROM t1 WHERE a!=a; 261SELECT COUNT(*) FROM t1 WHERE a!=a; 262EXPLAIN SELECT * FROM t1 WHERE a>a; 263SELECT COUNT(*) FROM t1 WHERE a>a; 264EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a; 265SELECT COUNT(*) FROM t1 WHERE a<a; 266 267ALTER TABLE t1 MODIFY a TINYINT NOT NULL; 268 269EXPLAIN SELECT COUNT(*) FROM t1 WHERE a!=a; 270SELECT COUNT(*) FROM t1 WHERE a!=a; 271EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>a; 272SELECT COUNT(*) FROM t1 WHERE a>a; 273EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a; 274SELECT COUNT(*) FROM t1 WHERE a<a; 275 276--echo # 277--echo # MDEV-8554 Modifing expression doesn't hit "Impossible WHERE" clause 278--echo # 279 280EXPLAIN SELECT COUNT(*) FROM t1 WHERE not (a!=a); 281SELECT COUNT(*) FROM t1 WHERE not (a!=a); 282EXPLAIN SELECT COUNT(*) FROM t1 WHERE a>a-1; 283SELECT COUNT(*) FROM t1 WHERE a>a-1; 284EXPLAIN SELECT COUNT(*) FROM t1 WHERE a<a+1; 285SELECT COUNT(*) FROM t1 WHERE a<a+1; 286EXPLAIN SELECT COUNT(*) FROM t1 WHERE a-1<a; 287SELECT COUNT(*) FROM t1 WHERE a-1<a; 288 289EXPLAIN SELECT COUNT(*) FROM t1 WHERE a NOT LIKE a; 290SELECT COUNT(*) FROM t1 WHERE a NOT LIKE a; 291EXPLAIN SELECT COUNT(*) FROM t1 WHERE a XOR a; 292SELECT COUNT(*) FROM t1 WHERE a XOR a; 293 294DROP TABLE t1; 295 296--echo # 297--echo # MDEV-23337 Rounding functions create a wrong data type for numeric input 298--echo # MDEV-23350 ROUND(bigint_22_or_longer) returns a wrong data type 299--echo # 300 301DELIMITER $$; 302CREATE PROCEDURE p1(t VARCHAR(64)) 303BEGIN 304 SELECT t AS ``; 305 EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (a TYPE)', 'TYPE', t); 306 INSERT IGNORE INTO t1 VALUES (-100000000000000000000000000000); 307 INSERT IGNORE INTO t1 VALUES (100000000000000000000000000000); 308 CREATE TABLE t2 AS SELECT a, ROUND(a), ROUND(a,1), ROUND(a,10), TRUNCATE(a,0), FLOOR(a), CEILING(a) FROM t1; 309 SHOW CREATE TABLE t2; 310 SELECT * FROM t2; 311 DROP TABLE t1, t2; 312END; 313$$ 314DELIMITER ;$$ 315 316--vertical_results 317CALL p1('tinyint'); 318CALL p1('smallint'); 319CALL p1('mediumint'); 320CALL p1('int'); 321CALL p1('bigint'); 322CALL p1('bigint(20)'); 323CALL p1('bigint(21)'); 324CALL p1('bigint(22)'); 325CALL p1('bigint(23)'); 326CALL p1('bigint(30)'); 327 328CALL p1('tinyint unsigned'); 329CALL p1('smallint unsigned'); 330CALL p1('mediumint unsigned'); 331CALL p1('int unsigned'); 332CALL p1('bigint unsigned'); 333CALL p1('bigint(20) unsigned'); 334CALL p1('bigint(21) unsigned'); 335CALL p1('bigint(22) unsigned'); 336CALL p1('bigint(23) unsigned'); 337CALL p1('bigint(30) unsigned'); 338--horizontal_results 339 340DROP PROCEDURE p1; 341 342--echo # 343--echo # MDEV-23368 ROUND(18446744073709551615,-11) returns a wrong result 344--echo # 345 346SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11); 347CREATE TABLE t1 AS SELECT ROUND(18446744073709551615,-10), ROUND(18446744073709551615,-11); 348SHOW CREATE TABLE t1; 349SELECT * FROM t1; 350DROP TABLE t1; 351 352--echo # 353--echo # MDEV-23367 ROUND(18446744073709551615,-1) returns a wrong result 354--echo # 355 356--vertical_results 357SELECT 358 ROUND(18446744073709551615,-1) AS c01, 359 ROUND(18446744073709551615,-19) AS c19; 360 361CREATE OR REPLACE TABLE t1 AS 362SELECT 363 ROUND(18446744073709551615,-1) AS c01, 364 ROUND(18446744073709551615,-19) AS c19; 365 366SELECT * FROM t1; 367SHOW CREATE TABLE t1; 368DROP TABLE t1; 369--horizontal_results 370 371DELIMITER $$; 372CREATE PROCEDURE p1(t VARCHAR(64)) 373BEGIN 374 SELECT t AS ``; 375 EXECUTE IMMEDIATE REPLACE('CREATE TABLE t1 (a TYPE)', 'TYPE', t); 376 INSERT IGNORE INTO t1 VALUES (-100000000000000000000000000000); 377 INSERT IGNORE INTO t1 VALUES (100000000000000000000000000000); 378 CREATE TABLE t2 AS SELECT 379 a, ROUND(a,-1), ROUND(a,-2), ROUND(a,-19), ROUND(a,-20), ROUND(a,-30) 380 FROM t1 381 ORDER BY a; 382 SHOW CREATE TABLE t2; 383 SELECT * FROM t2; 384 DROP TABLE t1, t2; 385END; 386$$ 387DELIMITER ;$$ 388 389--vertical_results 390CALL p1('tinyint'); 391CALL p1('smallint'); 392CALL p1('mediumint'); 393CALL p1('int'); 394CALL p1('bigint'); 395CALL p1('bigint(20)'); 396CALL p1('bigint(21)'); 397CALL p1('bigint(22)'); 398CALL p1('bigint(23)'); 399CALL p1('bigint(30)'); 400 401CALL p1('tinyint unsigned'); 402CALL p1('smallint unsigned'); 403CALL p1('mediumint unsigned'); 404CALL p1('int unsigned'); 405CALL p1('bigint unsigned'); 406CALL p1('bigint(20) unsigned'); 407CALL p1('bigint(21) unsigned'); 408CALL p1('bigint(22) unsigned'); 409CALL p1('bigint(23) unsigned'); 410CALL p1('bigint(30) unsigned'); 411--horizontal_results 412 413DROP PROCEDURE p1; 414 415 416--echo # 417--echo # MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result 418--echo # 419 420--vertical_results 421SELECT 422 ROUND(18446744073709551615,NULL) AS c1, 423 ROUND(18446744073709551615,rand()*0) AS c2, 424 ROUND(18446744073709551615,rand()*0-19) AS c3; 425 426CREATE OR REPLACE TABLE t1 AS 427SELECT 428 ROUND(18446744073709551615,NULL) AS c1, 429 ROUND(18446744073709551615,rand()*0) AS c2, 430 ROUND(18446744073709551615,rand()*0-19) AS c3; 431 432SELECT * FROM t1; 433SHOW CREATE TABLE t1; 434DROP TABLE t1; 435--horizontal_results 436 437 438--echo # 439--echo # End of 10.4 tests 440--echo # 441 442--echo # 443--echo # Start of 10.5 tests 444--echo # 445 446--echo # 447--echo # MDEV-20363 Assertion `is_unsigned() == attr.unsigned_flag' failed in Type_handler_longlong::make_table_field 448--echo # 449 450CREATE TABLE t1 (a INT); 451INSERT INTO t1 VALUES (1),(2); 452SELECT DISTINCT 1 FROM t1 GROUP BY 0 >> NULL WITH ROLLUP; 453DROP TABLE t1; 454 455 456--echo # 457--echo # End of 10.5 tests 458--echo # 459