Edit File: type_date.result
drop table if exists t1,t2; 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 ( 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 INTO t1 VALUES ('abc'); Warnings: Warning 1366 Incorrect integer value: 'abc' for column '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 into t1 values (9912101,9912101,9912101); Warnings: Warning 1264 Out of range value for column 'd' at row 1 Warning 1264 Out of range value 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 NULL DROP TABLE t1; CREATE TABLE t1 (a DATE, b INT, PRIMARY KEY (a,b)); 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 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 1 Using where; Using index Warnings: Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 SELECT * FROM t1 WHERE a = '0000-00-00'; a 0000-00-00 0000-00-00 Warnings: Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 SELECT * FROM t2 WHERE a = '0000-00-00'; a 0000-00-00 0000-00-00 Warnings: Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 INSERT INTO t1 VALUES ('0000-00-00'); ERROR 22007: Incorrect date value: '0000-00-00' for column '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 1 Using where; Using index Warnings: Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 SELECT * FROM t1 WHERE a = '1000-00-00'; a 1000-00-00 1000-00-00 Warnings: Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 SELECT * FROM t2 WHERE a = '1000-00-00'; a 1000-00-00 1000-00-00 Warnings: Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 Warning 1292 Incorrect date value: '1000-00-00' for column 'a' at row 1 INSERT INTO t1 VALUES ('1000-00-00'); ERROR 22007: Incorrect date value: '1000-00-00' for column '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) 1 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) 1 DROP TABLE t1; End of 5.1 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 # End of 6.0 tests
Back to File Manager