1SET DEFAULT_STORAGE_ENGINE='tokudb'; 2drop table if exists t1; 3create table t1 (t datetime); 4insert into t1 values (101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030100000000),(20030000000000); 5select * from t1; 6t 72000-01-01 00:00:00 82069-12-31 00:00:00 91970-01-01 00:00:00 101999-12-31 00:00:00 111000-01-01 00:00:00 129999-12-31 00:00:00 132000-01-01 00:00:00 142069-12-31 00:00:00 151970-01-01 00:00:00 161999-12-31 23:59:59 171000-01-01 00:00:00 189999-12-31 23:59:59 192003-01-00 00:00:00 202003-00-00 00:00:00 21delete from t1 where t > 0; 22optimize table t1; 23Table Op Msg_type Msg_text 24test.t1 optimize note Table does not support optimize, doing recreate + analyze instead 25test.t1 optimize status OK 26check table t1; 27Table Op Msg_type Msg_text 28test.t1 check status OK 29delete from t1; 30insert into t1 values("000101"),("691231"),("700101"),("991231"),("00000101"),("00010101"),("99991231"),("00101000000"),("691231000000"),("700101000000"),("991231235959"),("10000101000000"),("99991231235959"),("20030100000000"),("20030000000000"); 31insert into t1 values ("2003-003-03"); 32insert into t1 values ("20030102T131415"),("2001-01-01T01:01:01"), ("2001-1-1T1:01:01"); 33select * from t1; 34t 352000-01-01 00:00:00 362069-12-31 00:00:00 371970-01-01 00:00:00 381999-12-31 00:00:00 390000-01-01 00:00:00 400001-01-01 00:00:00 419999-12-31 00:00:00 422000-10-10 00:00:00 432069-12-31 00:00:00 441970-01-01 00:00:00 451999-12-31 23:59:59 461000-01-01 00:00:00 479999-12-31 23:59:59 482003-01-00 00:00:00 492003-00-00 00:00:00 502003-03-03 00:00:00 512003-01-02 13:14:15 522001-01-01 01:01:01 532001-01-01 01:01:01 54truncate table t1; 55insert into t1 values("2003-0303 12:13:14"); 56Warnings: 57Warning 1265 Data truncated for column 't' at row 1 58select * from t1; 59t 600000-00-00 00:00:00 61drop table t1; 62CREATE TABLE t1 (a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, b date, c time, d datetime); 63insert into t1 (b,c,d) values(now(),curtime(),now()); 64Warnings: 65Note 1265 Data truncated for column 'b' at row 1 66select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1; 67date_format(a,"%Y-%m-%d")=b right(a+0,6)=c+0 a=d+0 681 1 1 69drop table t1; 70CREATE TABLE t1 (a datetime not null); 71insert into t1 values (0); 72select * from t1 where a is null; 73a 740000-00-00 00:00:00 75drop table t1; 76create table t1 (id int, dt datetime); 77insert into t1 values (1,"2001-08-14 00:00:00"),(2,"2001-08-15 00:00:00"),(3,"2001-08-16 00:00:00"),(4,"2003-09-15 01:20:30"); 78select * from t1 where dt='2001-08-14 00:00:00' and dt = if(id=1,'2001-08-14 00:00:00','1999-08-15'); 79id dt 801 2001-08-14 00:00:00 81create index dt on t1 (dt); 82select * from t1 where dt > 20021020; 83id dt 844 2003-09-15 01:20:30 85select * from t1 ignore index (dt) where dt > 20021020; 86id dt 874 2003-09-15 01:20:30 88drop table t1; 89CREATE TABLE `t1` ( 90`date` datetime NOT NULL default '0000-00-00 00:00:00', 91`numfacture` int(6) unsigned NOT NULL default '0', 92`expedition` datetime NOT NULL default '0000-00-00 00:00:00', 93PRIMARY KEY (`numfacture`), 94KEY `date` (`date`), 95KEY `expedition` (`expedition`) 96) ENGINE=tokudb; 97INSERT INTO t1 (expedition) VALUES ('0001-00-00 00:00:00'); 98SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00'; 99date numfacture expedition 1000000-00-00 00:00:00 0 0001-00-00 00:00:00 101INSERT INTO t1 (numfacture,expedition) VALUES ('1212','0001-00-00 00:00:00'); 102SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00'; 103date numfacture expedition 1040000-00-00 00:00:00 0 0001-00-00 00:00:00 1050000-00-00 00:00:00 1212 0001-00-00 00:00:00 106EXPLAIN SELECT * FROM t1 WHERE expedition='0001-00-00 00:00:00'; 107id select_type table type possible_keys key key_len ref rows Extra 1081 SIMPLE t1 ALL expedition NULL NULL NULL 2 Using where 109drop table t1; 110create table t1 (a datetime not null, b datetime not null); 111insert into t1 values (now(), now()); 112insert into t1 values (now(), now()); 113select * from t1 where a is null or b is null; 114a b 115drop table t1; 116create table t1 (t datetime); 117insert into t1 values (20030102030460),(20030102036301),(20030102240401), 118(20030132030401),(20031302030401),(100001202030401); 119Warnings: 120Warning 1265 Data truncated for column 't' at row 1 121Warning 1265 Data truncated for column 't' at row 2 122Warning 1265 Data truncated for column 't' at row 3 123Warning 1265 Data truncated for column 't' at row 4 124Warning 1265 Data truncated for column 't' at row 5 125Warning 1265 Data truncated for column 't' at row 6 126select * from t1; 127t 1280000-00-00 00:00:00 1290000-00-00 00:00:00 1300000-00-00 00:00:00 1310000-00-00 00:00:00 1320000-00-00 00:00:00 1330000-00-00 00:00:00 134delete from t1; 135insert into t1 values 136("2003-01-02 03:04:60"),("2003-01-02 03:63:01"),("2003-01-02 24:04:01"), 137("2003-01-32 03:04:01"),("2003-13-02 03:04:01"), ("10000-12-02 03:04:00"); 138Warnings: 139Warning 1265 Data truncated for column 't' at row 1 140Warning 1265 Data truncated for column 't' at row 2 141Warning 1265 Data truncated for column 't' at row 3 142Warning 1265 Data truncated for column 't' at row 4 143Warning 1265 Data truncated for column 't' at row 5 144Warning 1265 Data truncated for column 't' at row 6 145select * from t1; 146t 1470000-00-00 00:00:00 1480000-00-00 00:00:00 1490000-00-00 00:00:00 1500000-00-00 00:00:00 1510000-00-00 00:00:00 1520000-00-00 00:00:00 153delete from t1; 154insert into t1 values ("0000-00-00 00:00:00 some trailer"),("2003-01-01 00:00:00 some trailer"); 155Warnings: 156Warning 1265 Data truncated for column 't' at row 1 157Warning 1265 Data truncated for column 't' at row 2 158select * from t1 order by t; 159t 1600000-00-00 00:00:00 1612003-01-01 00:00:00 162drop table t1; 163create table t1 (dt datetime); 164insert into t1 values ("12-00-00"), ("00-00-00 01:00:00"); 165insert into t1 values ("00-00-00"), ("00-00-00 00:00:00"); 166select * from t1; 167dt 1682012-00-00 00:00:00 1692000-00-00 01:00:00 1700000-00-00 00:00:00 1710000-00-00 00:00:00 172drop table t1; 173select cast('2006-12-05 22:10:10' as datetime) + 0; 174cast('2006-12-05 22:10:10' as datetime) + 0 17520061205221010 176CREATE TABLE t1(a DATETIME NOT NULL); 177INSERT INTO t1 VALUES ('20060606155555'); 178SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555"); 179a 1802006-06-06 15:55:55 181PREPARE s FROM 'SELECT a FROM t1 WHERE a=(SELECT MAX(a) FROM t1) AND (a="20060606155555")'; 182EXECUTE s; 183a 1842006-06-06 15:55:55 185DROP PREPARE s; 186DROP TABLE t1; 187SELECT CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)); 188CAST(CAST('2006-08-10' AS DATE) AS DECIMAL(20,6)) 18920060810.000000 190SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)); 191CAST(CAST('2006-08-10 10:11:12' AS DATETIME) AS DECIMAL(20,6)) 19220060810101112.000000 193SELECT CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)); 194CAST(CAST('2006-08-10 10:11:12' AS DATETIME) + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)) 19520060810101112.000014 196SELECT CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)); 197CAST(CAST('10:11:12.098700' AS TIME) AS DECIMAL(20,6)) 198101112.000000 199set @org_mode=@@sql_mode; 200create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03'); 201Warnings: 202Note 1265 Data truncated for column 'da' at row 1 203show create table t1; 204Table Create Table 205t1 CREATE TABLE `t1` ( 206 `da` date DEFAULT '1962-03-03', 207 `dt` datetime DEFAULT '1962-03-03 00:00:00' 208) ENGINE=TokuDB DEFAULT CHARSET=latin1 209insert into t1 values (); 210insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38'); 211Warnings: 212Note 1265 Data truncated for column 'da' at row 1 213set @@sql_mode='ansi,traditional'; 214insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38'); 215Warnings: 216Note 1265 Data truncated for column 'da' at row 1 217insert into t1 set dt='2007-03-23 13:49:38',da=dt; 218Warnings: 219Note 1265 Data truncated for column 'da' at row 1 220insert into t1 values ('2007-03-32','2007-03-23 13:49:38'); 221ERROR 22007: Incorrect date value: '2007-03-32' for column `test`.`t1`.`da` at row 1 222select * from t1; 223da dt 2241962-03-03 1962-03-03 00:00:00 2252007-03-23 2007-03-23 13:49:38 2262007-03-23 2007-03-23 13:49:38 2272007-03-23 2007-03-23 13:49:38 228drop table t1; 229create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03'); 230ERROR 42000: Invalid default value for 'da' 231create table t1 (t time default '916:00:00 a'); 232ERROR 42000: Invalid default value for 't' 233set @@sql_mode= @org_mode; 234create table t1 (f1 date, f2 datetime, f3 timestamp); 235insert into t1(f1) values(curdate()); 236select curdate() < now(), f1 < now(), cast(f1 as date) < now() from t1; 237curdate() < now() f1 < now() cast(f1 as date) < now() 2381 1 1 239delete from t1; 240insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01'); 241insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01'); 242insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01'); 243insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00'); 244insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01'); 245select f1, f3 from t1 where f1 >= '2001-02-05 00:00:00' and f3 <= '2001-04-15'; 246f1 f3 2472001-02-05 2001-02-05 01:01:01 2482001-03-10 2001-03-10 01:01:01 2492001-04-15 2001-04-15 00:00:00 250select f1, f3 from t1 where f1 >= '2001-2-5 0:0:0' and f2 <= '2001-4-15'; 251f1 f3 2522001-02-05 2001-02-05 01:01:01 2532001-03-10 2001-03-10 01:01:01 2542001-04-15 2001-04-15 00:00:00 255select f1, f2 from t1 where if(1, f1, 0) >= f2; 256f1 f2 2572001-02-05 2001-02-05 00:00:00 2582001-03-10 2001-03-09 01:01:01 2592001-04-15 2001-04-15 00:00:00 260select 1 from dual where cast('2001-1-1 2:3:4' as date) = cast('2001-01-01' as datetime); 2611 2621 263select f1, f2, f1 > f2, f1 = f2, f1 < f2 from t1; 264f1 f2 f1 > f2 f1 = f2 f1 < f2 2652001-01-01 2001-01-01 01:01:01 0 0 1 2662001-02-05 2001-02-05 00:00:00 0 1 0 2672001-03-10 2001-03-09 01:01:01 1 0 0 2682001-04-15 2001-04-15 00:00:00 0 1 0 2692001-05-20 2001-05-20 01:01:01 0 0 1 270drop table t1; 271create table t1 (f1 date, f2 datetime, f3 timestamp); 272insert into t1 values('2001-01-01','2001-01-01 01:01:01','2001-01-01 01:01:01'); 273insert into t1 values('2001-02-05','2001-02-05 00:00:00','2001-02-05 01:01:01'); 274insert into t1 values('2001-03-10','2001-03-09 01:01:01','2001-03-10 01:01:01'); 275insert into t1 values('2001-04-15','2001-04-15 00:00:00','2001-04-15 00:00:00'); 276insert into t1 values('2001-05-20','2001-05-20 01:01:01','2001-05-20 01:01:01'); 277select f2 from t1 where f2 between '2001-2-5' and '01-04-14'; 278f2 2792001-02-05 00:00:00 2802001-03-09 01:01:01 281select f1, f2, f3 from t1 where f1 between f2 and f3; 282f1 f2 f3 2832001-02-05 2001-02-05 00:00:00 2001-02-05 01:01:01 2842001-03-10 2001-03-09 01:01:01 2001-03-10 01:01:01 2852001-04-15 2001-04-15 00:00:00 2001-04-15 00:00:00 286select f1, f2, f3 from t1 where cast(f1 as datetime) between f2 and 287cast(f3 as date); 288f1 f2 f3 2892001-02-05 2001-02-05 00:00:00 2001-02-05 01:01:01 2902001-03-10 2001-03-09 01:01:01 2001-03-10 01:01:01 2912001-04-15 2001-04-15 00:00:00 2001-04-15 00:00:00 292select f2 from t1 where '2001-04-10 12:34:56' between f2 and '01-05-01'; 293f2 2942001-01-01 01:01:01 2952001-02-05 00:00:00 2962001-03-09 01:01:01 297select f2, f3 from t1 where '01-03-10' between f2 and f3; 298f2 f3 2992001-03-09 01:01:01 2001-03-10 01:01:01 300select f2 from t1 where DATE(f2) between "2001-4-15" AND "01-4-15"; 301f2 3022001-04-15 00:00:00 303SELECT 1 from dual where NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE(); 3041 305drop table t1; 306create table t1 (f1 date); 307insert into t1 values('01-01-01'),('01-01-02'),('01-01-03'); 308select * from t1 where f1 in ('01-01-01','2001-01-02','2001-01-03 00:00:00'); 309f1 3102001-01-01 3112001-01-02 3122001-01-03 313create table t2(f2 datetime); 314insert into t2 values('01-01-01 00:00:00'),('01-02-03 12:34:56'),('02-04-06 11:22:33'); 315select * from t2 where f2 in ('01-01-01','01-02-03 12:34:56','01-02-03'); 316f2 3172001-01-01 00:00:00 3182001-02-03 12:34:56 319select * from t1,t2 where '01-01-02' in (f1, cast(f2 as date)); 320f1 f2 3212001-01-02 2001-01-01 00:00:00 3222001-01-02 2001-02-03 12:34:56 3232001-01-02 2002-04-06 11:22:33 324select * from t1,t2 where '01-01-01' in (f1, '01-02-03'); 325f1 f2 3262001-01-01 2001-01-01 00:00:00 3272001-01-01 2001-02-03 12:34:56 3282001-01-01 2002-04-06 11:22:33 329select * from t1,t2 where if(1,'01-02-03 12:34:56','') in (f1, f2); 330f1 f2 3312001-01-01 2001-02-03 12:34:56 3322001-01-02 2001-02-03 12:34:56 3332001-01-03 2001-02-03 12:34:56 334create table t3(f3 varchar(20)); 335insert into t3 select * from t2; 336select * from t2,t3 where f2 in (f3,'03-04-05'); 337f2 f3 3382001-01-01 00:00:00 2001-01-01 00:00:00 3392001-02-03 12:34:56 2001-02-03 12:34:56 3402002-04-06 11:22:33 2002-04-06 11:22:33 341select f1,f2,f3 from t1,t2,t3 where (f1,'1') in ((f2,'1'),(f3,'1')); 342f1 f2 f3 3432001-01-01 2001-01-01 00:00:00 2001-01-01 00:00:00 3442001-01-01 2001-02-03 12:34:56 2001-01-01 00:00:00 3452001-01-01 2002-04-06 11:22:33 2001-01-01 00:00:00 3462001-01-01 2001-01-01 00:00:00 2001-02-03 12:34:56 3472001-01-01 2001-01-01 00:00:00 2002-04-06 11:22:33 348select f1 from t1 where ('1',f1) in (('1','01-01-01'),('1','2001-1-1 0:0:0'),('1','02-02-02')); 349f1 3502001-01-01 351drop table t1,t2,t3; 352select least(cast('01-01-01' as date), '01-01-02'); 353least(cast('01-01-01' as date), '01-01-02') 3542001-01-01 355select greatest(cast('01-01-01' as date), '01-01-02'); 356greatest(cast('01-01-01' as date), '01-01-02') 3572001-01-02 358select least(cast('01-01-01' as date), '01-01-02') + 0; 359least(cast('01-01-01' as date), '01-01-02') + 0 36020010101 361select greatest(cast('01-01-01' as date), '01-01-02') + 0; 362greatest(cast('01-01-01' as date), '01-01-02') + 0 36320010102 364select least(cast('01-01-01' as datetime), '01-01-02') + 0; 365least(cast('01-01-01' as datetime), '01-01-02') + 0 36620010101000000.000000 367select cast(least(cast('01-01-01' as datetime), '01-01-02') as signed); 368cast(least(cast('01-01-01' as datetime), '01-01-02') as signed) 36920010101000000 370select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2)); 371cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2)) 37220010101000000.00 373DROP PROCEDURE IF EXISTS test27759 ; 374CREATE PROCEDURE test27759() 375BEGIN 376declare v_a date default '2007-4-10'; 377declare v_b date default '2007-4-11'; 378declare v_c datetime default '2004-4-9 0:0:0'; 379select v_a as a,v_b as b, 380least( v_a, v_b ) as a_then_b, 381least( v_b, v_a ) as b_then_a, 382least( v_c, v_a ) as c_then_a; 383END;| 384call test27759(); 385a b a_then_b b_then_a c_then_a 3862007-04-10 2007-04-11 2007-04-10 2007-04-10 2004-04-09 00:00:00 387drop procedure test27759; 388create table t1 (f1 date); 389insert into t1 values (curdate()); 390select left(f1,10) = curdate() from t1; 391left(f1,10) = curdate() 3921 393drop table t1; 394create table t1(f1 date); 395insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02'); 396set @bug28261=''; 397select if(@bug28261 = f1, '', @bug28261:= f1) from t1; 398if(@bug28261 = f1, '', @bug28261:= f1) 3992001-01-01 4002002-02-02 4012001-01-01 4022002-02-02 403Warnings: 404Warning 1292 Incorrect datetime value: '' 405select if(@bug28261 = f1, '', @bug28261:= f1) from t1; 406if(@bug28261 = f1, '', @bug28261:= f1) 4072001-01-01 4082002-02-02 4092001-01-01 4102002-02-02 411select if(@bug28261 = f1, '', @bug28261:= f1) from t1; 412if(@bug28261 = f1, '', @bug28261:= f1) 4132001-01-01 4142002-02-02 4152001-01-01 4162002-02-02 417drop table t1; 418create table t1(f1 datetime); 419insert into t1 values('2001-01-01'),('2002-02-02'); 420select * from t1 where f1 between 20020101 and 20070101000000; 421f1 4222002-02-02 00:00:00 423select * from t1 where f1 between 2002010 and 20070101000000; 424f1 4252001-01-01 00:00:00 4262002-02-02 00:00:00 427Warnings: 428Warning 1292 Incorrect datetime value: '2002010' 429select * from t1 where f1 between 20020101 and 2007010100000; 430f1 431Warnings: 432Warning 1292 Incorrect datetime value: '2007010100000' 433drop table t1; 434# 435# Bug#27216: functions with parameters of different date types may 436# return wrong type of the result. 437# 438create table t1 (f1 date, f2 datetime, f3 varchar(20)); 439create table t2 as select coalesce(f1,f1) as f4 from t1; 440desc t2; 441Field Type Null Key Default Extra 442f4 date YES NULL 443create table t3 as select coalesce(f1,f2) as f4 from t1; 444desc t3; 445Field Type Null Key Default Extra 446f4 datetime YES NULL 447create table t4 as select coalesce(f2,f2) as f4 from t1; 448desc t4; 449Field Type Null Key Default Extra 450f4 datetime YES NULL 451create table t5 as select coalesce(f1,f3) as f4 from t1; 452desc t5; 453Field Type Null Key Default Extra 454f4 varchar(20) YES NULL 455create table t6 as select coalesce(f2,f3) as f4 from t1; 456desc t6; 457Field Type Null Key Default Extra 458f4 varchar(20) YES NULL 459create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1; 460desc t7; 461Field Type Null Key Default Extra 462f4 datetime YES NULL 463create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4 464from t1; 465desc t8; 466Field Type Null Key Default Extra 467f4 datetime YES NULL 468create table t9 as select case when 1 then cast('01-01-01' as date) 469when 0 then cast('01-01-01' as date) end as f4 from t1; 470desc t9; 471Field Type Null Key Default Extra 472f4 date YES NULL 473create table t10 as select case when 1 then cast('01-01-01' as datetime) 474when 0 then cast('01-01-01' as datetime) end as f4 from t1; 475desc t10; 476Field Type Null Key Default Extra 477f4 datetime YES NULL 478create table t11 as select if(1, cast('01-01-01' as datetime), 479cast('01-01-01' as date)) as f4 from t1; 480desc t11; 481Field Type Null Key Default Extra 482f4 datetime YES NULL 483create table t12 as select least(cast('01-01-01' as datetime), 484cast('01-01-01' as date)) as f4 from t1; 485desc t12; 486Field Type Null Key Default Extra 487f4 datetime YES NULL 488create table t13 as select ifnull(cast('01-01-01' as datetime), 489cast('01-01-01' as date)) as f4 from t1; 490desc t13; 491Field Type Null Key Default Extra 492f4 datetime YES NULL 493drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13; 494################################################################### 495create table t1 (f1 time); 496insert into t1 set f1 = '45:44:44'; 497insert into t1 set f1 = '15:44:44'; 498select * from t1 where (convert(f1,datetime)) != 1; 499f1 50045:44:44 50115:44:44 502Warnings: 503Warning 1292 Incorrect datetime value: '1' 504drop table t1; 505create table t1 (a tinyint); 506insert into t1 values (), (), (); 507select sum(a) from t1 group by convert(a, datetime); 508sum(a) 509NULL 510drop table t1; 511create table t1 (id int(10) not null, cur_date datetime not null); 512create table t2 (id int(10) not null, cur_date date not null); 513insert into t1 (id, cur_date) values (1, '2007-04-25 18:30:22'); 514insert into t2 (id, cur_date) values (1, '2007-04-25'); 515explain extended 516select * from t1 517where id in (select id from t1 as x1 where (t1.cur_date is null)); 518id select_type table type possible_keys key key_len ref rows filtered Extra 5191 PRIMARY t1 ALL NULL NULL NULL NULL 1 100.00 Using where 5201 PRIMARY x1 ALL NULL NULL NULL NULL 1 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 521Warnings: 522Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 523Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where `test`.`x1`.`id` = `test`.`t1`.`id` and `test`.`t1`.`cur_date` = 0 524select * from t1 525where id in (select id from t1 as x1 where (t1.cur_date is null)); 526id cur_date 527explain extended 528select * from t2 529where id in (select id from t2 as x1 where (t2.cur_date is null)); 530id select_type table type possible_keys key key_len ref rows filtered Extra 5311 PRIMARY t2 ALL NULL NULL NULL NULL 1 100.00 Using where 5321 PRIMARY x1 ALL NULL NULL NULL NULL 1 100.00 Using where; FirstMatch(t2); Using join buffer (flat, BNL join) 533Warnings: 534Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 535Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where `test`.`x1`.`id` = `test`.`t2`.`id` and `test`.`t2`.`cur_date` = 0 536select * from t2 537where id in (select id from t2 as x1 where (t2.cur_date is null)); 538id cur_date 539insert into t1 (id, cur_date) values (2, '2007-04-26 18:30:22'); 540insert into t2 (id, cur_date) values (2, '2007-04-26'); 541explain extended 542select * from t1 543where id in (select id from t1 as x1 where (t1.cur_date is null)); 544id select_type table type possible_keys key key_len ref rows filtered Extra 5451 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 5461 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 547Warnings: 548Note 1276 Field or reference 'test.t1.cur_date' of SELECT #2 was resolved in SELECT #1 549Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`cur_date` AS `cur_date` from `test`.`t1` semi join (`test`.`t1` `x1`) where `test`.`x1`.`id` = `test`.`t1`.`id` and `test`.`t1`.`cur_date` = 0 550select * from t1 551where id in (select id from t1 as x1 where (t1.cur_date is null)); 552id cur_date 553explain extended 554select * from t2 555where id in (select id from t2 as x1 where (t2.cur_date is null)); 556id select_type table type possible_keys key key_len ref rows filtered Extra 5571 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 5581 PRIMARY x1 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t2); Using join buffer (flat, BNL join) 559Warnings: 560Note 1276 Field or reference 'test.t2.cur_date' of SELECT #2 was resolved in SELECT #1 561Note 1003 select `test`.`t2`.`id` AS `id`,`test`.`t2`.`cur_date` AS `cur_date` from `test`.`t2` semi join (`test`.`t2` `x1`) where `test`.`x1`.`id` = `test`.`t2`.`id` and `test`.`t2`.`cur_date` = 0 562select * from t2 563where id in (select id from t2 as x1 where (t2.cur_date is null)); 564id cur_date 565drop table t1,t2; 566End of 5.0 tests 567set @org_mode=@@sql_mode; 568create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03'); 569Warnings: 570Note 1265 Data truncated for column 'da' at row 1 571show create table t1; 572Table Create Table 573t1 CREATE TABLE `t1` ( 574 `da` date DEFAULT '1962-03-03', 575 `dt` datetime DEFAULT '1962-03-03 00:00:00' 576) ENGINE=TokuDB DEFAULT CHARSET=latin1 577insert into t1 values (); 578insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38'); 579Warnings: 580Note 1265 Data truncated for column 'da' at row 1 581set @@sql_mode='ansi,traditional'; 582insert into t1 values ('2007-03-23 13:49:38','2007-03-23 13:49:38'); 583Warnings: 584Note 1265 Data truncated for column 'da' at row 1 585insert into t1 set dt='2007-03-23 13:49:38',da=dt; 586Warnings: 587Note 1265 Data truncated for column 'da' at row 1 588insert into t1 values ('2007-03-32','2007-03-23 13:49:38'); 589ERROR 22007: Incorrect date value: '2007-03-32' for column `test`.`t1`.`da` at row 1 590select * from t1; 591da dt 5921962-03-03 1962-03-03 00:00:00 5932007-03-23 2007-03-23 13:49:38 5942007-03-23 2007-03-23 13:49:38 5952007-03-23 2007-03-23 13:49:38 596drop table t1; 597create table t1 (da date default '1962-03-32 23:33:34', dt datetime default '1962-03-03'); 598ERROR 42000: Invalid default value for 'da' 599create table t1 (t time default '916:00:00 a'); 600ERROR 42000: Invalid default value for 't' 601set @@sql_mode= @org_mode; 602