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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
--disable_warnings
drop table if exists t1,t2;
--enable_warnings
create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14');
insert into t1 values ('','',0,0.0);
select default(str), default(strnull), default(intg), default(rel) from t1;
explain extended select default(str), default(strnull), default(intg), default(rel) from t1;
select * from t1 where str <> default(str);
explain select * from t1 where str <> default(str);
create view v1 as select default(str), default(strnull), default(intg), default(rel) from t1;
select * from v1;
show create view v1;
drop view v1;
#TODO: uncomment when bug will be fixed
#create table t2 select default(str), default(strnull), default(intg), default(rel) from t1;
#show create table from t1;
#insert into t2 select select default(str), default(strnull), default(intg), default(rel) from t1;
drop table t1;
# End of 4.1 tests
#
# Bug #11314 (HAVING DEFAULT() hangs)
#
CREATE TABLE t1 (id int(11), s varchar(20));
INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
--error 1364
SELECT s, 32 AS mi FROM t1 GROUP BY s HAVING DEFAULT(mi) IS NULL;
DROP TABLE t1;
--echo #
--echo # Start of 10.2 tests
--echo #
set timestamp=unix_timestamp('2001-01-01 10:20:30.123456');
create table t1 (a int default 1, b int default (a+1),
c varchar(100) default 'foo', d text default 'bar',
e timestamp default now(), f timestamp(6) default now(2));
insert t1 () values ();
insert t1 (a) values (10);
select default(a),default(b),default(c),default(d),default(e),default(f) from t1;
drop table t1;
--echo #
--echo # MDEV-21639 DEFAULT(col) evaluates to a bad value in WHERE clause
--echo #
CREATE TABLE t1 (a BIGINT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));
INSERT INTO t1 VALUES (10000);
SELECT
a,
DEFAULT(a),
CASE WHEN a THEN DEFAULT(a) END AS c,
CASE WHEN a THEN DEFAULT(a) END = 10 AS ce
FROM t1;
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;
DROP TABLE t1;
CREATE TABLE t1 (a DOUBLE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));
INSERT INTO t1 VALUES (10000);
SELECT
a,
DEFAULT(a),
CASE WHEN a THEN DEFAULT(a) END AS c,
CASE WHEN a THEN DEFAULT(a) END = 10 AS ce
FROM t1;
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL(10,0) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));
INSERT INTO t1 VALUES (10000);
SELECT
a,
DEFAULT(a),
CASE WHEN a THEN DEFAULT(a) END AS c,
CASE WHEN a THEN DEFAULT(a) END = 10 AS ce
FROM t1;
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(32) NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),10)));
INSERT INTO t1 VALUES (10000);
SELECT
a,
DEFAULT(a),
CASE WHEN a THEN DEFAULT(a) END AS c,
CASE WHEN a THEN DEFAULT(a) END = '10' AS ce
FROM t1;
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10';
DROP TABLE t1;
CREATE TABLE t1 (a DATE NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP()%10,DATE'2001-01-01')));
INSERT INTO t1 VALUES ('2000-01-01');
SELECT
a,
DEFAULT(a),
CASE WHEN a THEN DEFAULT(a) END AS c,
CASE WHEN a THEN DEFAULT(a) END = '2001-01-01' AS ce
FROM t1;
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01';
DROP TABLE t1;
CREATE TABLE t1 (a TIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP()%10,TIME'10:20:30')));
INSERT INTO t1 VALUES ('10:00:00');
SELECT
a,
DEFAULT(a),
CASE WHEN a THEN DEFAULT(a) END AS c,
CASE WHEN a THEN DEFAULT(a) END = '10:20:30' AS ce
FROM t1;
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10:20:30';
DROP TABLE t1;
CREATE TABLE t1 (a DATETIME NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),TIMESTAMP'2001-01-01 10:20:30')));
INSERT INTO t1 VALUES ('2000-01-01 10:00:00');
SELECT
a,
DEFAULT(a),
CASE WHEN a THEN DEFAULT(a) END AS c,
CASE WHEN a THEN DEFAULT(a) END = '2001-01-01 10:20:30' AS ce
FROM t1;
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01 10:20:30';
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL DEFAULT (IF(false,UNIX_TIMESTAMP(),FALSE)));
INSERT INTO t1 VALUES (10);
SELECT
a,
DEFAULT(a),
CASE WHEN a THEN DEFAULT(a) END AS c,
CASE WHEN a THEN DEFAULT(a) END IS FALSE AS ce
FROM t1;
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END IS FALSE;
DROP TABLE t1;
--echo #
--echo # End of 10.2 tests
--echo #
|
