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 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));
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 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 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 = CONVERT(a, DATE) FROM t1;
b = CONVERT(a, DATE)
0
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
#
# 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
#
#
# WL#946: testing rounding from DATETIME/TIME to DATE
#
SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2001-01-01 23:59:59.4');
Warnings:
Note 1265 Data truncated for column 'a' at row 1
INSERT INTO t1 VALUES (TIMESTAMP'2001-01-01 23:59:59.4');
Warnings:
Note 1265 Data truncated for column 'a' at row 1
INSERT INTO t1 VALUES (20010101235959.4);
Warnings:
Note 1265 Data truncated for column 'a' at row 1
INSERT INTO t1 VALUES (TIME'23:59:59.4');
Warnings:
Note 1265 Data truncated for column 'a' at row 1
INSERT INTO t1 VALUES
(TIMESTAMP'9999-12-30 23:59:59.4'),
(TIMESTAMP'9999-12-31 23:59:59.4');
Warnings:
Note 1265 Data truncated for column 'a' at row 1
Note 1265 Data truncated for column 'a' at row 2
SELECT * FROM t1;
a
2001-01-01
2001-01-01
2001-01-01
2001-01-01
9999-12-30
9999-12-31
DROP TABLE t1;
SET timestamp=DEFAULT;
SET timestamp=UNIX_TIMESTAMP('2001-01-01 00:00:00');
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES ('2001-01-01 23:59:59.9');
INSERT INTO t1 VALUES (TIMESTAMP'2001-01-01 23:59:59.9');
INSERT INTO t1 VALUES (20010101235959.9);
INSERT INTO t1 VALUES (TIME'23:59:59.9');
INSERT INTO t1 VALUES
(TIMESTAMP'9999-12-30 23:59:59.9'),
(TIMESTAMP'9999-12-31 23:59:59.9');
Warnings:
Warning 1441 Datetime function: datetime field overflow
Warning 1264 Out of range value for column 'a' at row 2
SELECT * FROM t1;
a
2001-01-02
2001-01-02
2001-01-02
2001-01-02
9999-12-31
0000-00-00
DROP TABLE t1;
SET timestamp=DEFAULT;
#
# Testing cast from DECIMAL to DATE
#
CREATE TABLE t1 (a DECIMAL(23,4));
INSERT INTO t1 VALUES (NULL),(101010.9999),(-101010.9999);
SELECT a, CAST(a AS DATE) FROM t1;
a CAST(a AS DATE)
NULL NULL
101010.9999 2010-10-10
-101010.9999 NULL
Warnings:
Warning 1292 Incorrect datetime value: '-101010.9999'
DROP TABLE t1;
#
# Testing CASE with DATE type without a found item
#
SELECT CAST(CASE WHEN 0 THEN '2001-01-01' END AS DATE);
CAST(CASE WHEN 0 THEN '2001-01-01' END AS DATE)
NULL
SELECT CAST(CASE WHEN 0 THEN DATE'2001-01-01' END AS DATE);
CAST(CASE WHEN 0 THEN DATE'2001-01-01' END AS DATE)
NULL
#
# Testing DATE field with NULL value with NOT IN
#
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 WHERE a NOT IN (DATE'2001-01-01',DATE'2002-02-02');
a
DROP TABLE t1;
#
# Testing COALESCE with DATE type without a found item
#
SELECT COALESCE(DATE(NULL));
COALESCE(DATE(NULL))
NULL
#
# Testing Item_func_min_max::val_str when DATE type and NULL
#
SELECT CAST(LEAST(DATE(NULL), DATE(NULL)) AS CHAR);
CAST(LEAST(DATE(NULL), DATE(NULL)) AS CHAR)
NULL
#
# Testing Item_func_min_max::get_date with non-temporal arguments
#
SELECT CAST(LEAST(20010101,20020202) AS DATE);
CAST(LEAST(20010101,20020202) AS DATE)
2001-01-01
#
# Item::get_date_from_numeric
#
SELECT CAST(SUM(0) AS DATETIME);
CAST(SUM(0) AS DATETIME)
0000-00-00 00:00:00
SELECT CAST(SUM(0 + 0e0) AS DATETIME);
CAST(SUM(0 + 0e0) AS DATETIME)
0000-00-00 00:00:00
SET timestamp=1322115004;
SELECT CAST(UNIX_TIMESTAMP() AS DATE);
CAST(UNIX_TIMESTAMP() AS DATE)
NULL
Warnings:
Warning 1292 Incorrect datetime value: '1322115004'
SET timestamp=DEFAULT;
#
# Item::get_date_from_non_temporal
#
SELECT DATE(20110512154559.6 + 0e0);
DATE(20110512154559.6 + 0e0)
2011-05-12
#
# Item_name_const::get_date
#
SELECT DATE(NAME_CONST('a', 0));
DATE(NAME_CONST('a', 0))
0000-00-00
#
# Item_cache_datetime::get_date
#
SET timestamp=UNIX_TIMESTAMP('2011-11-24');
CREATE TABLE t1 (a TIME);
INSERT INTO t1 VALUES (0);
SELECT DATE(MIN(a)) FROM t1;
DATE(MIN(a))
2011-11-24
DROP TABLE t1;
SET timestamp=DEFAULT;
#
# Bug #13626019 DIFFERENCE IN OUTPUT BETWEEN 5.1 & 5.5 FOR A LAST_DAY IS
# NULL SELECT QUERY
#
CREATE TABLE t1(a INT) ENGINE=InnoDB;
CREATE TABLE t2(b CHAR(19));
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES('0000-00-00 00:00:00');
SELECT * FROM t1 WHERE LAST_DAY('0000-00-00 00:00:00') IS NULL;
a
1
Warnings:
Warning 1292 Incorrect datetime value: '0000-00-00'
Warning 1292 Incorrect datetime value: '0000-00-00'
Warning 1292 Incorrect datetime value: '0000-00-00'
SELECT * FROM t1 WHERE
LAST_DAY((SELECT b FROM t2 WHERE b = '0000-00-00 00:00:00')) IS NULL;
a
1
Warnings:
Warning 1292 Incorrect datetime value: '0000-00-00'
DROP TABLE t1,t2;
#
# Test for bug#11747847 - 34280: create table fails if NO_ZERO_DATE
# or NO_ZERO_IN_DATE SQL mode is set.
DROP TABLE IF EXISTS t1, t2, t3;
SET @org_mode=@@sql_mode;
#Table creation in strict mode with NO_ZERO_IN_DATE/NO_ZERO_DATE
SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
Warnings:
Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release.
CREATE TABLE t1 (c1 DATE DEFAULT 0);
ERROR 42000: Invalid default value for 'c1'
CREATE TABLE t1 (c1 DATE DEFAULT '0000-00-00');
ERROR 42000: Invalid default value for 'c1'
SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
Warnings:
Warning 1681 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release.
CREATE TABLE t1 (c1 DATE DEFAULT '2012-02-00');
ERROR 42000: Invalid default value for 'c1'
#Table creation in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
SET @@sql_mode='NO_ZERO_DATE';
Warnings:
Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release.
CREATE TABLE t1 (c1 DATE DEFAULT 0);
Warnings:
Warning 1264 Out of range value for column 'c1' at row 1
CREATE TABLE t2 (c1 DATE DEFAULT '0000-00-00');
Warnings:
Warning 1264 Out of range value for column 'c1' at row 1
SET @@sql_mode='NO_ZERO_IN_DATE';
Warnings:
Warning 1681 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release.
CREATE TABLE t3 (c1 DATE DEFAULT '2012-02-00');
Warnings:
Warning 1264 Out of range value for column 'c1' at row 1
DROP TABLE t1, t2, t3;
#Table creation with out any SQL modes
SET @@sql_mode='';
CREATE TABLE t1 (c1 DATE DEFAULT 0);
CREATE TABLE t2 (c1 DATE DEFAULT '0000-00-00');
CREATE TABLE t3 (c1 DATE DEFAULT '2012-02-00');
DROP TABLE t1, t2, t3;
CREATE TABLE t1 (c1 INT);
#Alter table in strict mode with NO_ZERO_DATE/NO_ZERO_IN_DATE
SET @@sql_mode='NO_ZERO_DATE,STRICT_ALL_TABLES';
Warnings:
Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release.
ALTER TABLE t1 ADD c2 DATE DEFAULT 0;
ERROR 42000: Invalid default value for 'c2'
ALTER TABLE t1 ADD c2 DATE DEFAULT '0000-00-00';
ERROR 42000: Invalid default value for 'c2'
SET @@sql_mode='NO_ZERO_IN_DATE,STRICT_ALL_TABLES';
Warnings:
Warning 1681 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release.
ALTER TABLE t1 ADD c2 DATE DEFAULT '2012-02-00';
ERROR 42000: Invalid default value for 'c2'
#Alter table in non-strict mode but with NO_ZERO_DATE/NO_ZERO_IN_DATE
SET @@sql_mode='NO_ZERO_DATE';
Warnings:
Warning 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release.
ALTER TABLE t1 ADD c2 DATE DEFAULT 0;
Warnings:
Warning 1264 Out of range value for column 'c2' at row 1
ALTER TABLE t1 ADD c3 DATE DEFAULT '0000-00-00';
Warnings:
Warning 1264 Out of range value for column 'c2' at row 1
Warning 1264 Out of range value for column 'c3' at row 1
SET @@sql_mode='NO_ZERO_IN_DATE';
Warnings:
Warning 1681 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release.
ALTER TABLE t1 ADD c4 DATE DEFAULT '2012-02-00';
Warnings:
Warning 1264 Out of range value for column 'c4' at row 1
DROP TABLE t1;
CREATE TABLE t1 (c1 int);
#Alter table with out any SQL modes
SET @@sql_mode='';
ALTER TABLE t1 ADD c2 DATE DEFAULT 0;
ALTER TABLE t1 ADD c3 DATE DEFAULT '0000-00-00';
ALTER TABLE t1 ADD c4 DATE DEFAULT '2012-02-00';
DROP TABLE t1;
SET @@sql_mode= @org_mode;
# END of Test for bug#11747847 - 34280
End of 5.6 tests