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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
|
drop table if exists t1,t2;
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;
default(str) default(strnull) default(intg) default(rel)
def NULL 10 3.14
explain extended select default(str), default(strnull), default(intg), default(rel) from t1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 select default(`test`.`t1`.`str`) AS `default(str)`,default(`test`.`t1`.`strnull`) AS `default(strnull)`,default(`test`.`t1`.`intg`) AS `default(intg)`,default(`test`.`t1`.`rel`) AS `default(rel)` from dual
select * from t1 where str <> default(str);
str strnull intg rel
0 0
explain select * from t1 where str <> default(str);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
create view v1 as select default(str), default(strnull), default(intg), default(rel) from t1;
select * from v1;
default(str) default(strnull) default(intg) default(rel)
def NULL 10 3.14
show create view v1;
View Create View character_set_client collation_connection
v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select default(`t1`.`str`) AS `default(str)`,default(`t1`.`strnull`) AS `default(strnull)`,default(`t1`.`intg`) AS `default(intg)`,default(`t1`.`rel`) AS `default(rel)` from `t1` latin1 latin1_swedish_ci
drop view v1;
drop table t1;
CREATE TABLE t1 (id int(11), s varchar(20));
INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
SELECT s, 32 AS mi FROM t1 GROUP BY s HAVING DEFAULT(mi) IS NULL;
ERROR HY000: Field 'mi' doesn't have a default value
DROP TABLE t1;
#
# Start of 10.2 tests
#
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;
default(a) default(b) default(c) default(d) default(e) default(f)
1 2 foo bar 2001-01-01 10:20:30 2001-01-01 10:20:30.120000
1 11 foo bar 2001-01-01 10:20:30 2001-01-01 10:20:30.120000
drop table t1;
#
# MDEV-21639 DEFAULT(col) evaluates to a bad value in WHERE clause
#
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;
a DEFAULT(a) c ce
10000 10 10 1
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;
a
10000
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;
a DEFAULT(a) c ce
10000 10 10 1
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;
a
10000
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;
a DEFAULT(a) c ce
10000 10 10 1
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END=10;
a
10000
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;
a DEFAULT(a) c ce
10000 10 10 1
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10';
a
10000
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;
a DEFAULT(a) c ce
2000-01-01 2001-01-01 2001-01-01 1
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01';
a
2000-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;
a DEFAULT(a) c ce
10:00:00 10:20:30 10:20:30 1
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='10:20:30';
a
10:00:00
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;
a DEFAULT(a) c ce
2000-01-01 10:00:00 2001-01-01 10:20:30 2001-01-01 10:20:30 1
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END='2001-01-01 10:20:30';
a
2000-01-01 10:00:00
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;
a DEFAULT(a) c ce
10 0 0 1
SELECT a FROM t1 WHERE CASE WHEN a THEN DEFAULT(a) END IS FALSE;
a
10
DROP TABLE t1;
#
# End of 10.2 tests
#
|