1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
SET sql_mode=IF(@@version LIKE '%MariaDB%', 'TIME_ROUND_FRACTIONAL', '');
SET @default_sql_mode=@@sql_mode;
--echo #
--echo # DATE: SET
--echo #
CREATE TABLE t1 (a DATE, b DATETIME(4));
INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999');
UPDATE t1 SET a=b;
SELECT a FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a DATE, b VARCHAR(64));
INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999');
INSERT INTO t1 VALUES(NULL,'2000-12-31 23:59:59.9999999');
UPDATE t1 SET a=b;
SELECT a FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a DATE, b DECIMAL(38,10));
INSERT INTO t1 VALUES(NULL,20001231235959.9999);
INSERT INTO t1 VALUES(NULL,20001231235959.9999999);
UPDATE t1 SET a=b;
SELECT a FROM t1;
DROP TABLE t1;
--echo #
--echo # DATE: ALTER
--echo #
CREATE TABLE t1 (a DATETIME(4));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
ALTER TABLE t1 MODIFY a DATE;
SELECT a FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999999');
ALTER TABLE t1 MODIFY a DATE;
SELECT a FROM t1;
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(38,10));
INSERT INTO t1 VALUES(20001231235959.9999);
INSERT INTO t1 VALUES(20001231235959.9999999);
ALTER TABLE t1 MODIFY a DATE;
SELECT a FROM t1;
DROP TABLE t1;
--echo #
--echo # DATE: CAST
--echo #
CREATE TABLE t1 (a DATETIME(4));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
SELECT a, CAST(a AS DATE) FROM t1;
DROP TABLE t1;
# This truncates microseconds but rounds nanoseconds (MySQL Bug #92475)
CREATE TABLE t1 (a VARCHAR(64));
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999');
INSERT INTO t1 VALUES('2000-12-31 23:59:59.9999999');
SELECT a, CAST(a AS DATE) FROM t1;
DROP TABLE t1;
# This truncates microseconds but rounds nanoseconds (MySQL Bug #92475)
CREATE TABLE t1 (a DECIMAL(38,10));
INSERT INTO t1 VALUES(20001231235959.9999);
INSERT INTO t1 VALUES(20001231235959.9999999);
SELECT a, CAST(a AS DATE) FROM t1;
DROP TABLE t1;
--echo #
--echo # Equal field propagation
--echo #
CREATE TABLE t1 (a DATE);
INSERT INTO t1 VALUES (20010101);
INSERT INTO t1 VALUES (20010102);
# DATE is compared to non-temporal as DATETIME
# In the below queries nanoseconds should round to microseconds
SELECT * FROM t1 WHERE a= 20010101235959.9999999;
SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999';
SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND a>='2001-01-01 23:59:59.9999999';
SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND CONCAT(a)='2001-01-02';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND a>='2001-01-01 23:59:59.9999999';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 23:59:59.9999999' AND CONCAT(a)='2001-01-02';
DROP TABLE t1;
--echo #
--echo # Comparing non-temporal to DATE
--echo #
--echo # Although conversion from non-temporal to DATE (e.g. on SET) does not round,
--echo # comparison between non-temporal to DATE is performed as DATETIME.
--echo # So rounding does happen here.
CREATE TABLE t1 (a VARCHAR(64));
INSERT t1 VALUES ('2001-01-01 23:59:59.9999999');
SELECT * FROM t1 WHERE a=DATE'2001-01-02';
SELECT * FROM t1 WHERE CONCAT(a)=DATE'2001-01-02';
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-02';
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(32,7));
INSERT t1 VALUES (20010101235959.9999999);
SELECT * FROM t1 WHERE a=DATE'2001-01-02';
SELECT * FROM t1 WHERE COALESCE(a)=DATE'2001-01-02';
DROP TABLE t1;
|
