1drop table if exists t1; 2SET SQL_WARNINGS=1; 3create table t1 (this int unsigned); 4insert into t1 values (1); 5insert ignore into t1 values (-1); 6Warnings: 7Warning 1264 Out of range value for column 'this' at row 1 8insert ignore into t1 values ('5000000000'); 9Warnings: 10Warning 1264 Out of range value for column 'this' at row 1 11select * from t1; 12this 131 140 154294967295 16drop table t1; 17create table t1 (a bigint unsigned, b mediumint unsigned); 18insert t1 values (1,2),(0xffffffffffffffff,0xffffff); 19select coalesce(a,b), coalesce(b,a) from t1; 20coalesce(a,b) coalesce(b,a) 211 2 2218446744073709551615 16777215 23create table t2 as select a from t1 union select b from t1; 24show create table t2; 25Table Create Table 26t2 CREATE TABLE `t2` ( 27 `a` bigint(20) unsigned DEFAULT NULL 28) ENGINE=MyISAM DEFAULT CHARSET=latin1 29select * from t2; 30a 311 3218446744073709551615 332 3416777215 35drop table t1, t2; 36# 37# Start of 10.0 tests 38# 39# 40# MDEV-6950 Bad results with joins comparing DATE and INT/ENUM/VARCHAR columns 41# 42CREATE TABLE t1 (a DATE PRIMARY KEY); 43INSERT INTO t1 VALUES ('1999-01-01'); 44CREATE TABLE t2 (a INT UNSIGNED); 45INSERT INTO t2 VALUES (19990101); 46INSERT INTO t2 VALUES (990101); 47SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; 48a 491999-01-01 501999-01-01 51SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; 52a 531999-01-01 541999-01-01 55ALTER TABLE t2 ADD PRIMARY KEY(a); 56SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a; 57a 581999-01-01 591999-01-01 60SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; 61a 621999-01-01 631999-01-01 64# t2 should NOT be eliminated 65EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a=t2.a; 66id select_type table type possible_keys key key_len ref rows Extra 671 SIMPLE t1 system NULL NULL NULL NULL 1 681 SIMPLE t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index 69DROP TABLE t1,t2; 70# 71# End of 10.0 tests 72# 73