Debian Codegraph @

mysql-test/suite/sys_vars/t/mysql56_temporal_format_func.test

raw
1 #
2 # This test checks that DATETIME, DATETIME(5), TIME(5) columns
3 # occupy different data length depending on @@mysql56_temporal_format:
4 #
5 # Type MariaDB MySQL
6 # ---- ------- -----
7 # DATETIME 8 5
8 # DATETIME(5) 7 8
9 # TIME(5) 5 6
10 #
11 # The DATETIME(1-3,4,6) and TIME(0-4,6) data types use the same size
12 # in both MariaDB and MySQL formats.
13 # TIMESTAMP(0-6) also uses the same size in both formats.
14 #
15 # We use two columns in the tests below, to make sure the record length
16 # is longer than 7. Records whose fields occupy less then 7 bytes use
17 # 7 bytes anyway (assuming the default MAX_ROWS values), to store deleted
18 # record list pointers.
19 #
20
21 --echo #
22 --echo # MariaDB-5.3 format
23 --echo #
24 SET @@global.mysql56_temporal_format=false;
25 CREATE TABLE t1 (a DATETIME NOT NULL, b DATETIME NOT NULL);
26 INSERT INTO t1 VALUES ('2001-01-01 10:10:10','2001-01-01 10:10:10');
27 SELECT * FROM t1;
28 SELECT (AVG_ROW_LENGTH-1) DIV 2 AS FIELD_LENGTH,DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
29 DROP TABLE t1;
30
31 CREATE TABLE t1 (a DATETIME(5) NOT NULL, b DATETIME(5) NOT NULL);
32 INSERT INTO t1 VALUES ('2001-01-01 10:10:10.12345','2001-01-01 10:10:10.12345');
33 SELECT (AVG_ROW_LENGTH-1) DIV 2 AS FIELD_LENGTH,DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
34 SELECT * FROM t1;
35 DROP TABLE t1;
36
37 CREATE TABLE t1 (a TIME(5) NOT NULL,b TIME(5) NOT NULL);
38 INSERT INTO t1 VALUES ('10:10:10.12345','10:10:10.12345');
39 SELECT * FROM t1;
40 SELECT (AVG_ROW_LENGTH-1) DIV 2 AS FIELD_LENGTH,DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
41 DROP TABLE t1;
42
43 --echo #
44 --echo # MySQL-5.6 format
45 --echo #
46
47 SET @@global.mysql56_temporal_format=true;
48 CREATE TABLE t1 (a DATETIME NOT NULL, b DATETIME NOT NULL);
49 INSERT INTO t1 VALUES ('2001-01-01 10:10:10','2001-01-01 10:10:10');
50 SELECT * FROM t1;
51 SELECT (AVG_ROW_LENGTH-1) DIV 2 AS FIELD_LENGTH,DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
52 DROP TABLE t1;
53
54 CREATE TABLE t1 (a DATETIME(5) NOT NULL, b DATETIME(5) NOT NULL);
55 INSERT INTO t1 VALUES ('2001-01-01 10:10:10.12345','2001-01-01 10:10:10.12345');
56 SELECT * FROM t1;
57 SELECT (AVG_ROW_LENGTH-1) DIV 2 AS FIELD_LENGTH,DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
58 DROP TABLE t1;
59
60 CREATE TABLE t1 (a TIME(5) NOT NULL,b TIME(5) NOT NULL);
61 INSERT INTO t1 VALUES ('10:10:10.12345','10:10:10.12345');
62 SELECT * FROM t1;
63 SELECT (AVG_ROW_LENGTH-1) DIV 2 AS FIELD_LENGTH,DATA_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
64 DROP TABLE t1;
65
66 SET @@global.mysql56_temporal_format=DEFAULT;