create table t1 (a char(16), b date, c datetime); insert into t1 SET a='test 2000-01-01', b='2000-01-01', c='2000-01-01'; select * from t1 where c = '2000-01-01'; a b c test 2000-01-01 2000-01-01 2000-01-01 00:00:00 select * from t1 where b = '2000-01-01'; a b c test 2000-01-01 2000-01-01 2000-01-01 00:00:00 drop table t1; CREATE TABLE t1 (name char(6),cdate date); INSERT INTO t1 VALUES ('name1','1998-01-01'); INSERT INTO t1 VALUES ('name2','1998-01-01'); INSERT INTO t1 VALUES ('name1','1998-01-02'); INSERT INTO t1 VALUES ('name2','1998-01-02'); CREATE TABLE t2 (cdate date, note char(6)); INSERT INTO t2 VALUES ('1998-01-01','note01'); INSERT INTO t2 VALUES ('1998-01-02','note02'); select name,t1.cdate,note from t1,t2 where t1.cdate=t2.cdate and t1.cdate='1998-01-01'; name cdate note name1 1998-01-01 note01 name2 1998-01-01 note01 drop table t1,t2; CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(1); SELECT * FROM t1 WHERE LAST_DAY('0000-00-00 00:00:00') IS NULL; a 1 DROP TABLE t1; CREATE TABLE t1 ( datum DATE ); INSERT INTO t1 VALUES ( "2000-1-1" ); INSERT INTO t1 VALUES ( "2000-1-2" ); INSERT INTO t1 VALUES ( "2000-1-3" ); INSERT INTO t1 VALUES ( "2000-1-4" ); INSERT INTO t1 VALUES ( "2000-1-5" ); SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND cast("2000-1-4" as date); datum 2000-01-02 2000-01-03 2000-01-04 SELECT * FROM t1 WHERE datum BETWEEN cast("2000-1-2" as date) AND datum - INTERVAL 100 DAY; datum DROP TABLE t1; CREATE TABLE t1 ( user_id char(10), summa int(11), rdate date ); INSERT INTO t1 VALUES ('aaa',100,'1998-01-01'); INSERT INTO t1 VALUES ('aaa',200,'1998-01-03'); INSERT INTO t1 VALUES ('bbb',50,'1998-01-02'); INSERT INTO t1 VALUES ('bbb',200,'1998-01-04'); select max(rdate) as s from t1 where rdate < '1998-01-03' having s> "1998-01-01"; s 1998-01-02 select max(rdate) as s from t1 having s="1998-01-04"; s 1998-01-04 select max(rdate+0) as s from t1 having s="19980104"; s 19980104 drop table t1; create table t1 (date date); insert into t1 values ("2000-08-10"),("2000-08-11"); select date_add(date,INTERVAL 1 DAY),date_add(date,INTERVAL 1 SECOND) from t1; date_add(date,INTERVAL 1 DAY) date_add(date,INTERVAL 1 SECOND) 2000-08-11 2000-08-10 00:00:01 2000-08-12 2000-08-11 00:00:01 drop table t1; CREATE TABLE t1(AFIELD INT); INSERT INTO t1 VALUES(1); CREATE TABLE t2(GMT VARCHAR(32)); INSERT INTO t2 VALUES('GMT-0800'); SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) FROM t1, t2 GROUP BY t1.AFIELD; DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) Wed, 06 March 2002 10:11:12 GMT-0800 INSERT INTO t1 VALUES(1); SELECT DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)), DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) FROM t1,t2 GROUP BY t1.AFIELD; DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) DATE_FORMAT("2002-03-06 10:11:12", CONCAT('%a, %d %M %Y %H:%i:%s ', t2.GMT)) Wed, 06 March 2002 10:11:12 GMT-0800 Wed, 06 March 2002 10:11:12 GMT-0800 drop table t1,t2; CREATE TABLE t1 (f1 time default NULL, f2 time default NULL); INSERT INTO t1 (f1, f2) VALUES ('09:00', '12:00'); SELECT DATE_FORMAT(f1, "%l.%i %p") , DATE_FORMAT(f2, "%l.%i %p") FROM t1; DATE_FORMAT(f1, "%l.%i %p") DATE_FORMAT(f2, "%l.%i %p") 9.00 AM 12.00 PM DROP TABLE t1; CREATE TABLE t1 (f1 DATE); CREATE TABLE t2 (f2 VARCHAR(8)); CREATE TABLE t3 (f2 CHAR(8)); INSERT INTO t1 VALUES ('1978-11-26'); INSERT INTO t2 SELECT f1+0 FROM t1; INSERT INTO t2 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1; INSERT INTO t3 SELECT f1+0 FROM t1; INSERT INTO t3 SELECT f1+0 FROM t1 UNION SELECT f1+0 FROM t1; SELECT * FROM t2; f2 19781126 19781126 SELECT * FROM t3; f2 19781126 19781126 DROP TABLE t1, t2, t3; CREATE TABLE t1 (y YEAR); INSERT IGNORE INTO t1 VALUES ('abc'); Warnings: Warning 1366 Incorrect integer value: 'abc' for column `test`.`t1`.`y` at row 1 SELECT * FROM t1; y 0000 DROP TABLE t1; create table t1(start_date date, end_date date); insert into t1 values ('2000-01-01','2000-01-02'); select 1 from t1 where cast('2000-01-01 12:01:01' as datetime) between start_date and end_date; 1 1 drop table t1; select @d:=1111; @d:=1111 1111 select year(@d), month(@d), day(@d), cast(@d as date); year(@d) month(@d) day(@d) cast(@d as date) 2000 11 11 2000-11-11 select @d:=011111; @d:=011111 11111 select year(@d), month(@d), day(@d), cast(@d as date); year(@d) month(@d) day(@d) cast(@d as date) 2001 11 11 2001-11-11 select @d:=1311; @d:=1311 1311 select year(@d), month(@d), day(@d), cast(@d as date); year(@d) month(@d) day(@d) cast(@d as date) NULL NULL NULL NULL Warnings: Warning 1292 Incorrect datetime value: '1311' Warning 1292 Incorrect datetime value: '1311' Warning 1292 Incorrect datetime value: '1311' Warning 1292 Incorrect datetime value: '1311' create table t1 (d date , dt datetime , ts timestamp); insert ignore into t1 values (9912101,9912101,9912101); Warnings: Warning 1265 Data truncated for column 'd' at row 1 Warning 1265 Data truncated for column 'dt' at row 1 Warning 1265 Data truncated for column 'ts' at row 1 insert into t1 values (11111,11111,11111); select * from t1; d dt ts 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 2001-11-11 2001-11-11 00:00:00 2001-11-11 00:00:00 drop table t1; CREATE TABLE t1 ( a INT ); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (NULL); SELECT str_to_date( '', a ) FROM t1; str_to_date( '', a ) 0000-00-00 00:00:00.000000 NULL DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT, PRIMARY KEY (a,b)); SET timestamp=UNIX_TIMESTAMP('2016-07-21 14:48:18'); INSERT INTO t1 VALUES (DATE(NOW()), 1); SELECT COUNT(*) FROM t1 WHERE a = NOW(); COUNT(*) 0 EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); 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 INSERT INTO t1 VALUES (DATE(NOW()), 2); SELECT COUNT(*) FROM t1 WHERE a = NOW(); COUNT(*) 0 EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); 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 SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; COUNT(*) 0 EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; 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 ALTER TABLE t1 DROP PRIMARY KEY; SELECT COUNT(*) FROM t1 WHERE a = NOW(); COUNT(*) 0 EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where SET timestamp=DEFAULT; DROP TABLE t1; CREATE TABLE t1 (a DATE); CREATE TABLE t2 (a DATE); CREATE INDEX i ON t1 (a); INSERT INTO t1 VALUES ('0000-00-00'),('0000-00-00'); INSERT INTO t2 VALUES ('0000-00-00'),('0000-00-00'); SELECT * FROM t1 WHERE a = '0000-00-00'; a 0000-00-00 0000-00-00 SELECT * FROM t2 WHERE a = '0000-00-00'; a 0000-00-00 0000-00-00 SET SQL_MODE=TRADITIONAL; EXPLAIN SELECT * FROM t1 WHERE a = '0000-00-00'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i i 4 const 2 Using index SELECT * FROM t1 WHERE a = '0000-00-00'; a 0000-00-00 0000-00-00 SELECT * FROM t2 WHERE a = '0000-00-00'; a 0000-00-00 0000-00-00 INSERT INTO t1 VALUES ('0000-00-00'); ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`a` at row 1 SET SQL_MODE=DEFAULT; DROP TABLE t1,t2; CREATE TABLE t1 (a DATE); CREATE TABLE t2 (a DATE); CREATE INDEX i ON t1 (a); INSERT INTO t1 VALUES ('1000-00-00'),('1000-00-00'); INSERT INTO t2 VALUES ('1000-00-00'),('1000-00-00'); SELECT * FROM t1 WHERE a = '1000-00-00'; a 1000-00-00 1000-00-00 SELECT * FROM t2 WHERE a = '1000-00-00'; a 1000-00-00 1000-00-00 SET SQL_MODE=TRADITIONAL; EXPLAIN SELECT * FROM t1 WHERE a = '1000-00-00'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref i i 4 const 2 Using index SELECT * FROM t1 WHERE a = '1000-00-00'; a 1000-00-00 1000-00-00 SELECT * FROM t2 WHERE a = '1000-00-00'; a 1000-00-00 1000-00-00 INSERT INTO t1 VALUES ('1000-00-00'); ERROR 22007: Incorrect date value: '1000-00-00' for column `test`.`t1`.`a` at row 1 SET SQL_MODE=DEFAULT; DROP TABLE t1,t2; CREATE TABLE t1 SELECT curdate() AS f1; SELECT hour(f1), minute(f1), second(f1) FROM t1; hour(f1) minute(f1) second(f1) 0 0 0 DROP TABLE t1; End of 5.0 tests create table t1 (a date, primary key (a))engine=memory; insert into t1 values ('0000-01-01'), ('0000-00-01'), ('0001-01-01'); select * from t1 where a between '0000-00-01' and '0000-00-02'; a 0000-00-01 drop table t1; # # Bug#50918: Date columns treated differently in Views than in Base # Tables # CREATE TABLE t1 ( the_date DATE, the_time TIME ); INSERT INTO t1 VALUES ( '2010-01-01', '01:01:01' ); SELECT * FROM t1 t11 JOIN t1 t12 ON addtime( t11.the_date, t11.the_time ) = addtime( t12.the_date, t12.the_time ); the_date the_time the_date the_time 2010-01-01 01:01:01 2010-01-01 01:01:01 CREATE VIEW v1 AS SELECT * FROM t1; SELECT * FROM t1 JOIN v1 ON addtime( t1.the_date, t1.the_time ) = addtime( v1.the_date, v1.the_time ); the_date the_time the_date the_time 2010-01-01 01:01:01 2010-01-01 01:01:01 SELECT * FROM t1 JOIN v1 ON addtime( t1.the_date, t1.the_time ) = addtime( cast(v1.the_date AS DATETIME), v1.the_time ); the_date the_time the_date the_time 2010-01-01 01:01:01 2010-01-01 01:01:01 DROP TABLE t1; DROP VIEW v1; # # Bug#59685 crash in String::length with date types # CREATE TABLE t1(a DATE, b YEAR, KEY(a)); INSERT INTO t1 VALUES ('2011-01-01',2011); SELECT b = (SELECT CONVERT(a, DATE) FROM t1 GROUP BY a) FROM t1; b = (SELECT CONVERT(a, DATE) FROM t1 GROUP BY a) 0 SELECT b = CONVERT((SELECT CONVERT(a, DATE) FROM t1 GROUP BY a), DATE) FROM t1; b = CONVERT((SELECT CONVERT(a, DATE) FROM t1 GROUP BY a), DATE) 0 DROP TABLE t1; End of 5.1 tests create table t1 (f1 date, key (f1)); insert ignore t1 values ('2010-10-10 15:foobar'); Warnings: Warning 1265 Data truncated for column 'f1' at row 1 drop table t1; # # MDEV-4634 Crash in CONVERT_TZ # SELECT CONVERT_TZ(GREATEST(DATE('2021-00-00'),DATE('2022-00-00')),'+00:00','+7:5'); CONVERT_TZ(GREATEST(DATE('2021-00-00'),DATE('2022-00-00')),'+00:00','+7:5') NULL Warnings: Warning 1292 Incorrect datetime value: '2022-00-00' # # MDEV-4804 Date comparing false result # SET @h0="20111107"; SET @h1="0"; SET @@timestamp=UNIX_TIMESTAMP('2013-08-19 20:30:00'); SELECT COALESCE(DATE(@h0),DATE("1901-01-01")) AS h0d, COALESCE(DATE(@h1),DATE(NOW())) AS h1d, COALESCE(DATE(@h0),DATE("1901-01-01"))>COALESCE(DATE(@h1),DATE(NOW())) AS compare_h0_gt_h1; h0d h1d compare_h0_gt_h1 2011-11-07 2013-08-19 0 Warnings: Warning 1292 Incorrect datetime value: '0' Warning 1292 Incorrect datetime value: '0' SELECT DATE('20011107'), DATE('0'), COALESCE(DATE('0'),CURRENT_DATE) AS d1, DATE('20011107')>COALESCE(DATE('0'),CURRENT_DATE) AS cmp; DATE('20011107') DATE('0') d1 cmp 2001-11-07 NULL 2013-08-19 0 Warnings: Warning 1292 Incorrect datetime value: '0' Warning 1292 Incorrect datetime value: '0' Warning 1292 Incorrect datetime value: '0' SELECT DATE('20011107'), DATE('0'), IFNULL(DATE('0'),CURRENT_DATE) AS d1, DATE('20011107')>IFNULL(DATE('0'),CURRENT_DATE) AS cmp; DATE('20011107') DATE('0') d1 cmp 2001-11-07 NULL 2013-08-19 0 Warnings: Warning 1292 Incorrect datetime value: '0' Warning 1292 Incorrect datetime value: '0' Warning 1292 Incorrect datetime value: '0' # # MDEV-5041 Inserting a TIME with hour>24 into a DATETIME column produces a wrong value # SELECT CAST(TIME('-800:20:30') AS DATE); CAST(TIME('-800:20:30') AS DATE) 2013-07-16 SELECT CAST(TIME('800:20:30') AS DATE); CAST(TIME('800:20:30') AS DATE) 2013-09-21 SELECT CAST(TIME('33 08:20:30') AS DATE); CAST(TIME('33 08:20:30') AS DATE) 2013-09-21 CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES (TIME('800:20:30')); Warnings: Note 1265 Data truncated for column 'a' at row 1 INSERT INTO t1 VALUES (TIME('33 08:20:30')); Warnings: Note 1265 Data truncated for column 'a' at row 1 SET SQL_MODE=NO_ZERO_IN_DATE; INSERT INTO t1 VALUES (TIME('48:20:30')); Warnings: Note 1265 Data truncated for column 'a' at row 1 SET SQL_MODE=DEFAULT; SELECT * FROM t1; a 2013-09-21 2013-09-21 2013-08-21 DROP TABLE t1; CREATE PROCEDURE test5041() BEGIN DECLARE t TIME; DECLARE d DATE; SET t= TIME('800:00:00'); SET d= t; SELECT d; END;| call test5041(); d 2013-09-21 Warnings: Note 1265 Data truncated for column 'd' at row 0 drop procedure test5041; SET @@timestamp=DEFAULT; # # End of 5.3 tests # # # Bug #33629: last_day function can return null, but has 'not null' # flag set for result # SELECT 1 FROM (SELECT LAST_DAY('0')) a; 1 1 Warnings: Warning 1292 Incorrect datetime value: '0' SELECT 1 FROM (SELECT MAKEDATE(2011,0)) a; 1 1 CREATE TABLE t1 AS SELECT LAST_DAY('1970-01-01') AS f1, MAKEDATE(1970, 1) AS f2; DESCRIBE t1; Field Type Null Key Default Extra f1 date YES NULL f2 date YES NULL DROP TABLE t1; # # # Bug#57278: Crash on min/max + with date out of range. # set @a=(select min(makedate('111','1'))) ; select @a; @a 0111-01-01 # # # BUG LP:1008487 virtual bool Item_cache::is_expensive(): Assertion `example' failed # SET TIMESTAMP=UNIX_TIMESTAMP('2017-01-03 00:00:00'); create table t1(a date,key(a)); insert into t1 values ('2012-01-01'),('2012-02-02'); explain select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1_0 ref a a 4 const 1 Using where; Using index 1 SIMPLE t2 index NULL a 4 NULL 2 Using index; Using join buffer (flat, BNL join) 1 SIMPLE t1 index NULL a 4 NULL 2 Using index; Using join buffer (incremental, BNL join) select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; 1 SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-03 00:00:01'); explain select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; 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 select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; 1 drop table t1; SET TIMESTAMP=DEFAULT; # # MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null # MDEV-9972 Least function retuns date in date time format # CREATE TABLE t1 ( id BIGINT NOT NULL, date_debut DATE NOT NULL, date_fin DATE DEFAULT NULL); CREATE TABLE t2( id BIGINT NOT NULL, date_debut DATE NOT NULL, date_fin DATE DEFAULT NULL); INSERT INTO t1 VALUES (1,'2016-01-01','2016-01-31'); INSERT INTO t1 VALUES (2,'2016-02-01',null); INSERT INTO t1 VALUES (3,'2016-03-01','2016-03-31'); INSERT INTO t1 VALUES (4,'2016-04-01',null); INSERT INTO t2 VALUES (1,'2016-01-01','2016-01-31'); INSERT INTO t2 VALUES (2,'2016-02-01','2016-01-28'); INSERT INTO t2 VALUES (3,'2016-03-01',null); INSERT INTO t2 VALUES (4,'2016-04-01',null); SELECT t1.id, GREATEST(t2.date_debut, t1.date_debut) AS date_debut, LEAST(IFNULL(t2.date_fin, IFNULL(t1.date_fin, NULL)), IFNULL(t1.date_fin, IFNULL(t2.date_fin, NULL))) AS date_fin FROM t1 LEFT JOIN t2 ON (t1.id=t2.id); id date_debut date_fin 1 2016-01-01 2016-01-31 2 2016-02-01 2016-01-28 3 2016-03-01 2016-03-31 4 2016-04-01 NULL DROP TABLE t1,t2; SELECT LEAST(COALESCE(DATE(NULL), DATE(NULL)), COALESCE(DATE(NULL), DATE(NULL))) AS d0, LEAST(IFNULL(DATE(NULL), DATE(NULL)), IFNULL(DATE(NULL), DATE(NULL))) AS d1; d0 d1 NULL NULL # # MDEV-9511 Valgrind warnings 'Invalid read' in Field_newdate::cmp and Field_newdate::val_str # CREATE TABLE t1 (f1 DATE, f2 VARCHAR(1)); INSERT INTO t1 VALUES ('2003-04-27','a'),('1900-01-01','a'); SELECT GROUP_CONCAT(f2, IF(f1, f2, f1), f1 ORDER BY 2,1,3) FROM t1; GROUP_CONCAT(f2, IF(f1, f2, f1), f1 ORDER BY 2,1,3) aa1900-01-01,aa2003-04-27 DROP TABLE t1; # # Start of 10.1 tests # # # MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value # SET sql_mode=DEFAULT; CREATE TABLE t1 (a DATE DEFAULT '0000-00-00'); SET sql_mode=TRADITIONAL; INSERT INTO t1 VALUES ('0000-00-00'); ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`a` at row 1 INSERT INTO t1 VALUES (); ERROR 22007: Incorrect default value '0000-00-00' for column 'a' INSERT INTO t1 VALUES (DEFAULT); ERROR 22007: Incorrect default value '0000-00-00' for column 'a' DROP TABLE t1; SET sql_mode=DEFAULT; CREATE TABLE t1 (a DATE NOT NULL DEFAULT '0000-00-00', b DATE NOT NULL DEFAULT '0000-00-00'); CREATE TABLE t2 (a DATE NOT NULL DEFAULT '0000-00-00'); INSERT INTO t2 VALUES ('0000-00-00'); SET sql_mode=TRADITIONAL; INSERT INTO t1 (a) SELECT a FROM t2; ERROR 22007: Incorrect default value '0000-00-00' for column 'b' DROP TABLE t1, t2; SET sql_mode=DEFAULT; CREATE TABLE t1 (a DATE DEFAULT '0000-00-00', b DATE DEFAULT '0000-00-00'); INSERT INTO t1 VALUES (DEFAULT,DEFAULT);; SELECT a INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' FROM t1; DELETE FROM t1; SET sql_mode=TRADITIONAL; LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/mdev-7824.txt' INTO TABLE t1 (a); ERROR 22007: Incorrect default value '0000-00-00' for column 'b' DROP TABLE t1; SET sql_mode=DEFAULT; CREATE TABLE t1 (a DATE DEFAULT '0000-00-00');; SET sql_mode='NO_ZERO_DATE'; ALTER TABLE t1 ADD b INT NOT NULL; ERROR 42000: Invalid default value for 'a' DROP TABLE t1; SET sql_mode=DEFAULT; # # End of MDEV-7824 [Bug #68041] Zero date can be inserted in strict no-zero mode through a default value # # # MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field # SET sql_mode=DEFAULT; CREATE TABLE t1 (a DATE);; INSERT INTO t1 VALUES (0); SET sql_mode='TRADITIONAL'; CREATE TABLE t2 AS SELECT * FROM t1; ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t2`.`a` at row 1 DROP TABLE t1; # # End of MDEV-8373 Zero date can be inserted in strict no-zero mode through CREATE TABLE AS SELECT timestamp_field # # # MDEV-8722 The patch for MDEV-8688 disabled equal field propagation for temporal column and BETWEEN and IN # CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'); EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a BETWEEN '2001-01-01' AND '2001-01-02'; 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` = DATE'2001-01-01' EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a IN ('2001-01-01','2001-01-02'); 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` = DATE'2001-01-01' DROP TABLE t1; # # MDEV-8699 Wrong result for SELECT..WHERE HEX(date_column)!='323030312D30312D3031' AND date_column='2001-01-01x' # CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'); SELECT * FROM t1 WHERE a='2001-01-01x'; a 2001-01-01 Warnings: Warning 1292 Truncated incorrect date value: '2001-01-01x' SELECT * FROM t1 WHERE HEX(a)!='323030312D30312D3031'; a 2001-01-02 SELECT * FROM t1 WHERE HEX(a)!='323030312D30312D3031' AND a='2001-01-01x'; a Warnings: Warning 1292 Truncated incorrect date value: '2001-01-01x' EXPLAIN EXTENDED SELECT * FROM t1 WHERE HEX(a)!='323030312D30312D3031' AND a='2001-01-01x'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Warning 1292 Truncated incorrect date value: '2001-01-01x' Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 EXPLAIN EXTENDED SELECT * FROM t1 WHERE HEX(a)!=CONCAT('xx',RAND()) AND a='2001-01-01x'; 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: Warning 1292 Truncated incorrect date value: '2001-01-01x' Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'2001-01-01' and (hex(DATE'2001-01-01')) <> concat('xx',rand()) DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'); SELECT * FROM t1 WHERE LENGTH(a)=11; a SELECT * FROM t1 WHERE LENGTH(a)=11 AND a=' 2001-01-01'; a EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=11 AND a=' 2001-01-01'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=11+RAND() AND a=' 2001-01-01'; 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` = DATE'2001-01-01' and (octet_length(DATE'2001-01-01')) = 11 + rand() EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=11+RAND() AND a=' garbage '; 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: Warning 1292 Truncated incorrect datetime value: ' garbage ' Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = DATE'0000-00-00' and (octet_length(DATE'0000-00-00')) = 11 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-01'); SELECT * FROM t1 WHERE LENGTH(a)=8; a SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='20010101'; a EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=8 AND a='20010101'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 0 EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=8+RAND() AND a='20010101'; 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` = DATE'2001-01-01' and (octet_length(DATE'2001-01-01')) = 8 + rand() DROP TABLE t1; # # MDEV-8706 Wrong result for SELECT..WHERE time_column=TIMESTAMP'2015-08-30 00:00:00' AND time_column='00:00:00' # SET timestamp=UNIX_TIMESTAMP('2015-08-30 10:20:30'); CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31'); SELECT * FROM t1 WHERE a=TIME'00:00:00'; a 2015-08-30 SELECT * FROM t1 WHERE LENGTH(a)=10; a 2015-08-30 2015-08-31 SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIME'00:00:00'; a 2015-08-30 EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIME'00:00:00'; 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` = DATE'2015-08-30' EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'00:00:00'; 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` = DATE'2015-08-30' and (octet_length(DATE'2015-08-30')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31'); SELECT * FROM t1 WHERE a=TIME'24:00:00'; a 2015-08-31 SELECT * FROM t1 WHERE LENGTH(a)=10; a 2015-08-30 2015-08-31 SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIME'24:00:00'; a 2015-08-31 EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIME'24:00:00'; 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` = DATE'2015-08-31' EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIME'24:00:00'; 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` = DATE'2015-08-31' and (octet_length(DATE'2015-08-31')) = 30 + rand() DROP TABLE t1; # In this example '00:00:00' is not recognized as TIME'00:00:00' # and is treated as DATE'0000-00-00'. # This may change after MDEV-8322 Distinguish between time and date strings more carefully CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31'); SELECT * FROM t1 WHERE a='00:00:00'; a SELECT * FROM t1 WHERE LENGTH(a)=10; a 2015-08-30 2015-08-31 SELECT * FROM t1 WHERE LENGTH(a)=10 AND a='00:00:00'; a EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=10 AND a='00:00:00'; 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` = DATE'0000-00-00' EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='00:00:00'; 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` = DATE'0000-00-00' and (octet_length(DATE'0000-00-00')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31'); SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00'; a 2015-08-30 SELECT * FROM t1 WHERE LENGTH(a)=10; a 2015-08-30 2015-08-31 SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIMESTAMP'2015-08-30 00:00:00'; a 2015-08-30 EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIMESTAMP'2015-08-30 00:00:00'; 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` = TIMESTAMP'2015-08-30 00:00:00' EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2015-08-30 00:00:00'; 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` = TIMESTAMP'2015-08-30 00:00:00' and (octet_length(DATE'2015-08-30')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31'); SELECT * FROM t1 WHERE a=TIMESTAMP'2015-08-30 00:00:00.1'; a SELECT * FROM t1 WHERE LENGTH(a)=10; a 2015-08-30 2015-08-31 SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIMESTAMP'2015-08-30 00:00:00.1'; a EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=10 AND a=TIMESTAMP'2015-08-30 00:00:00.1'; 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` = TIMESTAMP'2015-08-30 00:00:00.1' EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2015-08-30 00:00:00.1'; 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` = TIMESTAMP'2015-08-30 00:00:00.1' and (octet_length(DATE'2015-08-30')) = 30 + rand() DROP TABLE t1; CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2015-08-30'),('2015-08-31'); SELECT * FROM t1 WHERE a='2015-08-30 00:00:00'; a 2015-08-30 SELECT * FROM t1 WHERE LENGTH(a)=10; a 2015-08-30 2015-08-31 SELECT * FROM t1 WHERE LENGTH(a)=10 AND a='2015-08-30 00:00:00'; a 2015-08-30 EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=10 AND a='2015-08-30 00:00:00'; 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` = DATE'2015-08-30' EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a='2015-08-30 00:00:00'; 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` = DATE'2015-08-30' and (octet_length(DATE'2015-08-30')) = 30 + rand() DROP TABLE t1; SET timestamp=DEFAULT; # # MDEV-8795 Equal expression propagation does not work for temporal literals # CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'); EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-01' AND COALESCE(a)>=DATE'2001-01-01'; 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 coalesce(`test`.`t1`.`a`) = DATE'2001-01-01' DROP TABLE t1; # # MDEV-8658 DATE(zerofill_column) and DATE(COALESCE(zerofill_column)) return different results # CREATE TABLE t1 (a INT(6) ZEROFILL, b DECIMAL(6) ZEROFILL, c DOUBLE(6,0) ZEROFILL); INSERT INTO t1 VALUES (1,1,1); INSERT INTO t1 VALUES (10101,10101,10101); SELECT DATE(a), DATE(b), DATE(c) FROM t1; DATE(a) DATE(b) DATE(c) NULL NULL NULL 2001-01-01 2001-01-01 2001-01-01 Warnings: Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`a` at row 1 Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`b` at row 1 Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`c` at row 1 SELECT DATE(COALESCE(a)), DATE(COALESCE(b)), DATE(COALESCE(c)) FROM t1; DATE(COALESCE(a)) DATE(COALESCE(b)) DATE(COALESCE(c)) NULL NULL NULL 2001-01-01 2001-01-01 2001-01-01 Warnings: Warning 1292 Incorrect datetime value: '1' Warning 1292 Incorrect datetime value: '1' Warning 1292 Incorrect datetime value: '1' DROP TABLE t1; CREATE TABLE t1 (a INT(6), b DECIMAL(6), c DOUBLE(6,0)); INSERT INTO t1 VALUES (1,1,1); INSERT INTO t1 VALUES (10101,10101,10101); SELECT DATE(a), DATE(b), DATE(c) FROM t1; DATE(a) DATE(b) DATE(c) NULL NULL NULL 2001-01-01 2001-01-01 2001-01-01 Warnings: Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`a` at row 1 Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`b` at row 1 Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`c` at row 1 SELECT DATE(COALESCE(a)), DATE(COALESCE(b)), DATE(COALESCE(c)) FROM t1; DATE(COALESCE(a)) DATE(COALESCE(b)) DATE(COALESCE(c)) NULL NULL NULL 2001-01-01 2001-01-01 2001-01-01 Warnings: Warning 1292 Incorrect datetime value: '1' Warning 1292 Incorrect datetime value: '1' Warning 1292 Incorrect datetime value: '1' DROP TABLE t1; # # MDEV-14221 Assertion `0' failed in Item::field_type_for_temporal_comparison # CREATE TABLE t1 (d DATE); INSERT INTO t1 VALUES ('1985-05-13'),('1989-12-24'); SELECT d, COUNT(*) FROM t1 GROUP BY d WITH ROLLUP HAVING CASE d WHEN '2017-05-25' THEN 0 ELSE 1 END; d COUNT(*) 1985-05-13 1 1989-12-24 1 NULL 2 DROP TABLE t1; # # MDEV-17299 Assertion `maybe_null' failed in make_sortkey # CREATE TABLE t1 (pk int NOT NULL, d1 date, d2 date NOT NULL); INSERT INTO t1 values (1,'2018-06-22','2018-06-22'),(2,'2018-07-11','2018-07-11'); CREATE VIEW v1 AS SELECT * FROM t1; SELECT group_concat(d1/(CASE 'b' WHEN 'j' THEN 'c' END)) FROM v1 GROUP BY greatest(pk, 0, d2); group_concat(d1/(CASE 'b' WHEN 'j' THEN 'c' END)) NULL NULL Warnings: Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 1 Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 CREATE TABLE t2 AS SELECT greatest(pk, 0, d2) AS c1 FROM t1 LIMIT 0; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t2; DROP VIEW v1; DROP TABLE t1; # # MDEV-19699 Server crashes in Item_null_result::field_type upon SELECT with ROLLUP on constant table # CREATE TABLE t1 (d DATE) ENGINE=MyISAM; INSERT INTO t1 VALUES ('1999-11-04'); SELECT d FROM t1 GROUP BY d WITH ROLLUP HAVING d > '1990-01-01'; d 1999-11-04 DROP TABLE t1; # # MDEV-20431 GREATEST(int_col,date_col) returns wrong results in a view # CREATE TABLE t1 (pk INT NOT NULL, d DATE NOT NULL); CREATE VIEW v1 AS SELECT * FROM t1; INSERT INTO t1 VALUES (1,'2018-06-22'),(2,'2018-07-11'); SELECT GREATEST(pk, d) FROM t1; GREATEST(pk, d) 2018-06-22 2018-07-11 Warnings: Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 SELECT GREATEST(pk, d) FROM v1; GREATEST(pk, d) 2018-06-22 2018-07-11 Warnings: Warning 1292 Incorrect datetime value: '1' for column `test`.`t1`.`pk` at row 1 Warning 1292 Incorrect datetime value: '2' for column `test`.`t1`.`pk` at row 2 DROP VIEW v1; DROP TABLE t1; # # End of 10.1 tests # # # Start of 10.2 tests # # # MDEV-21619 Server crash or assertion failures in my_datetime_to_str # CREATE TABLE t1 (f DATE, KEY(f)); INSERT INTO t1 VALUES ('2020-01-01'),('2020-01-02'); EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1995.0000000 BETWEEN f AND '2012-12-12'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range f f 4 NULL 1 100.00 Using where; Using index Warnings: Warning 1292 Truncated incorrect datetime value: '1995.0000000' Note 1003 select `test`.`t1`.`f` AS `f` from `test`.`t1` where '0000-00-00' between `test`.`t1`.`f` and ('2012-12-12') DROP TABLE t1; # # MDEV-27072 Subquery using the ALL keyword on date columns produces a wrong result # CREATE TABLE t1 (d DATE); INSERT INTO t1 VALUES ('2021-11-17'), ('2021-10-17'),('2022-11-17'), ('2020-10-17'); SELECT * FROM t1 WHERE d >= ALL (SELECT * FROM t1); d 2022-11-17 SELECT * FROM t1 WHERE d <= ALL (SELECT * FROM t1); d 2020-10-17 DROP TABLE t1; # # End of 10.2 tests # # # Start of 10.3 tests # # # MDEV-12721 Wrong execution plan for WHERE (date_field <=> timestamp_expr AND TRUE) # CREATE TABLE t1(a DATE,KEY(a)); INSERT INTO t1 VALUES ('2012-01-01'),('2012-02-02'); EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP'2001-01-01 00:00:01' AND TRUE; 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 1 FROM t1 WHERE t1.a <=> TIMESTAMP('2001-01-01', '00:00:01') AND TRUE; 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 1 FROM t1 WHERE t1.a <=> TIMESTAMP'2001-01-01 00:00:00' AND TRUE; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 4 const 1 Using where; Using index EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP('2001-01-01', '00:00:00') AND TRUE; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a a 4 const 1 Using where; Using index DROP TABLE t1; # # 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 DATE); INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'),('2001-01-03'); # Equal values EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001:01:01',a)<=>COALESCE(DATE'2001-01-01',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 COALESCE(?,a)<=>COALESCE(?,a)' USING DATE'2001-01-01',DATE'2001-01-01'; 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 COALESCE(DATE''2001-01-01'',a)<=>COALESCE(?,a)' USING DATE'2001-01-01'; 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 COALESCE(?,a)<=>COALESCE(DATE''2001-01-01'',a)' USING DATE'2001-01-01'; 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 # Equal values but of different data types (should not propagate) EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001:01:01',a)<=>COALESCE(TIMESTAMP'2001-01-01 00:00:00',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 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce(TIMESTAMP'2001-01-01 00:00:00',`test`.`t1`.`a`) EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING DATE'2001-01-01',TIMESTAMP'2001-01-01 00:00:00'; 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 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce((DATE'2001-01-01'),`test`.`t1`.`a`) <=> coalesce((TIMESTAMP'2001-01-01 00:00:00'),`test`.`t1`.`a`) EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE''2001-01-01'',a)<=>COALESCE(?,a)' USING TIMESTAMP'2001-01-01 00:00:00'; 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 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce((TIMESTAMP'2001-01-01 00:00:00'),`test`.`t1`.`a`) EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(DATE''2001-01-01'',a)' USING TIMESTAMP'2001-01-01 00:00:00'; 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 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce((TIMESTAMP'2001-01-01 00:00:00'),`test`.`t1`.`a`) <=> coalesce(DATE'2001-01-01',`test`.`t1`.`a`) # Not equal values EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE'2001-01-01',a)<=>COALESCE(DATE'2001-01-02',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 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce(DATE'2001-01-02',`test`.`t1`.`a`) EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING DATE'2001-01-01',DATE'2001-01-02'; 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 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce((DATE'2001-01-01'),`test`.`t1`.`a`) <=> coalesce((DATE'2001-01-02'),`test`.`t1`.`a`) EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(DATE''2001-01-01'',a)<=>COALESCE(?,a)' USING DATE'2001-01-02'; 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 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(DATE'2001-01-01',`test`.`t1`.`a`) <=> coalesce((DATE'2001-01-02'),`test`.`t1`.`a`) EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(DATE''2001-01-01'',a)' USING DATE'2001-01-02'; 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 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce((DATE'2001-01-02'),`test`.`t1`.`a`) <=> coalesce(DATE'2001-01-01',`test`.`t1`.`a`) DROP TABLE t1; # # MDEV-15406 NO_ZERO_IN_DATE erroneously affects how CAST(AS DATE) warns about fractional digit truncation # SET sql_mode=''; CREATE TABLE t1 (a DATE); SELECT CAST(20061108.01 AS DATE); CAST(20061108.01 AS DATE) 2006-11-08 Warnings: Note 1292 Truncated incorrect date value: '20061108.01' INSERT INTO t1 VALUES (20061108.01); Warnings: Note 1265 Data truncated for column 'a' at row 1 DROP TABLE t1; SET sql_mode=NO_ZERO_IN_DATE; SELECT CAST(20061108.01 AS DATE); CAST(20061108.01 AS DATE) 2006-11-08 Warnings: Note 1292 Truncated incorrect date value: '20061108.01' CREATE TABLE t1 (a DATE); INSERT INTO t1 VALUES (20061108.01); Warnings: Note 1265 Data truncated for column 'a' at row 1 DROP TABLE t1; SET sql_mode=DEFAULT; # # MDEV-17216 Assertion `!dt->fraction_remainder(decimals())' failed in Field_temporal_with_date::store_TIME_with_warning # SET sql_mode=''; CREATE TABLE t1 (i1 date ); CREATE TABLE t2 (i2 int unsigned ); INSERT INTO t2 VALUES (0); INSERT INTO t1 SELECT * FROM t2; DROP TABLE t1,t2; SET sql_mode=DEFAULT; # # MDEV-19301 Assertion `!is_valid_datetime() || fraction_remainder(((item->decimals) < (6) ? (item->decimals) : (6))) == 0' failed in Datetime_truncation_not_needed::Datetime_truncation_not_needed # SELECT NULLIF(CAST(1012.5 AS DATE), 1); NULLIF(CAST(1012.5 AS DATE), 1) 2000-10-12 Warnings: Note 1292 Truncated incorrect date value: '1012.5' Warning 1292 Truncated incorrect datetime value: '1' Note 1292 Truncated incorrect date value: '1012.5' SELECT CAST(1012.5 AS DATE) * 1.0; CAST(1012.5 AS DATE) * 1.0 20001012.0 Warnings: Note 1292 Truncated incorrect date value: '1012.5' # # MDEV-23351 Rounding functions return wrong data types for DATE input # CREATE TABLE t1 (a date); CREATE TABLE t2 AS SELECT FLOOR(a), CEIL(a),ROUND(a),TRUNCATE(a,0) FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `FLOOR(a)` int(8) unsigned DEFAULT NULL, `CEIL(a)` int(8) unsigned DEFAULT NULL, `ROUND(a)` int(8) unsigned DEFAULT NULL, `TRUNCATE(a,0)` int(8) unsigned DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t2,t1; # # MDEV-23388 Assertion `args[0]->decimals == 0' failed in Item_func_round::fix_arg_int # SELECT ROUND(GREATEST('1', CAST('2020-12-12' AS DATE))); ROUND(GREATEST('1', CAST('2020-12-12' AS DATE))) 20201212 Warnings: Warning 1292 Truncated incorrect datetime value: '1' SELECT GREATEST('1', CAST('2020-12-12' AS DATE)); Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def GREATEST('1', CAST('2020-12-12' AS DATE)) 10 10 10 Y 128 0 63 GREATEST('1', CAST('2020-12-12' AS DATE)) 2020-12-12 Warnings: Warning 1292 Truncated incorrect datetime value: '1' CREATE TABLE t1 (c_date DATE NOT NULL, c_int INT NOT NULL); CREATE TABLE t2 AS SELECT GREATEST(c_date,c_date), GREATEST(c_date,c_int), GREATEST(c_int,c_date) FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `GREATEST(c_date,c_date)` date NOT NULL, `GREATEST(c_date,c_int)` date DEFAULT NULL, `GREATEST(c_int,c_date)` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t2; DROP TABLE t1; # # MDEV-29890 Update with inner join false row count result # set sql_mode='NO_ZERO_DATE'; create table t1 (a1 bigint primary key, a2 date not null, a3 bigint not null); create table t2 (b1 bigint primary key); insert into t2 (b1) values (1); insert into t1 (a1, a2, a3) values (1, current_date, 1),( 2, current_date, 1); update t1 inner join t2 on t1.a3 = t2.b1 set t1.a2 = t1.a2 + interval 1 day; select row_count(); row_count() 2 set sql_mode=''; alter table t1 modify a2 date not null default '0000-00-00'; set sql_mode='NO_ZERO_DATE'; update t1 inner join t2 on t1.a3 = t2.b1 set t1.a2 = t1.a2 + interval 1 day; drop table t1, t2; set sql_mode=default; # # End of 10.4 tests #