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
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
|
###############################################
# #
# Prepared Statements #
# re-testing bug DB entries #
# #
# The bugs are reported as "closed". #
# Command sequences taken from bug report. #
# No other test contains the bug# as comment. #
# #
# Tests drop/create tables 't1', 't2', ... #
# #
###############################################
--disable_warnings
drop table if exists t1, t2;
--enable_warnings
# bug#1180: optimized away part of WHERE clause cause incorect prepared satatement results
CREATE TABLE t1(session_id char(9) NOT NULL);
INSERT INTO t1 VALUES ("abc");
SELECT * FROM t1;
prepare st_1180 from 'SELECT * FROM t1 WHERE ?="1111" and session_id = "abc"';
# Must not find a row
set @arg1= 'abc';
execute st_1180 using @arg1;
# Now, it should find one row
set @arg1= '1111';
execute st_1180 using @arg1;
# Back to non-matching
set @arg1= 'abc';
execute st_1180 using @arg1;
drop table t1;
# end of bug#1180
# bug#1644: Insertion of more than 3 NULL columns with parameter binding fails
# Using prepared statements, insertion of more than three columns with NULL
# values fails to insert additional NULLS. After the third column NULLS will
# be inserted into the database as zeros.
# First insert four columns of a value (i.e. 22) to verify binding is working
# correctly. Then Bind to each columns bind parameter an is_null value of 1.
# Then insert four more columns of integers, just for sanity.
# A subsequent select on the server will result in this:
# mysql> select * from foo_dfr;
# +------+------+------+------+
# | col1 | col2 | col3 | col4 |
# +------+------+------+------+
# | 22 | 22 | 22 | 22 |
# | NULL | NULL | NULL | 0 |
# | 88 | 88 | 88 | 88 |
# +------+------+------+------+
# Test is extended to more columns - code stores bit vector in bytes.
create table t1 (
c_01 char(6), c_02 integer, c_03 real, c_04 int(3), c_05 varchar(20),
c_06 date, c_07 char(1), c_08 real, c_09 int(11), c_10 time,
c_11 char(6), c_12 integer, c_13 real, c_14 int(3), c_15 varchar(20),
c_16 date, c_17 char(1), c_18 real, c_19 int(11), c_20 text);
# Do not use "timestamp" type, because it has a non-NULL default as of 4.1.2
prepare st_1644 from 'insert into t1 values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
set @arg01= 'row_1'; set @arg02= 1; set @arg03= 1.1; set @arg04= 111; set @arg05= 'row_one';
set @arg06= '2004-10-12'; set @arg07= '1'; set @arg08= 1.1; set @arg09= '100100100'; set @arg10= '12:34:56';
set @arg11= 'row_1'; set @arg12= 1; set @arg13= 1.1; set @arg14= 111; set @arg15= 'row_one';
set @arg16= '2004-10-12'; set @arg17= '1'; set @arg18= 1.1; set @arg19= '100100100'; set @arg20= '12:34:56';
execute st_1644 using @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, @arg09, @arg10,
@arg11, @arg12, @arg13, @arg14, @arg15, @arg16, @arg17, @arg18, @arg19, @arg20;
set @arg01= NULL; set @arg02= NULL; set @arg03= NULL; set @arg04= NULL; set @arg05= NULL;
set @arg06= NULL; set @arg07= NULL; set @arg08= NULL; set @arg09= NULL; set @arg10= NULL;
set @arg11= NULL; set @arg12= NULL; set @arg13= NULL; set @arg14= NULL; set @arg15= NULL;
set @arg16= NULL; set @arg17= NULL; set @arg18= NULL; set @arg19= NULL; set @arg20= NULL;
execute st_1644 using @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, @arg09, @arg10,
@arg11, @arg12, @arg13, @arg14, @arg15, @arg16, @arg17, @arg18, @arg19, @arg20;
set @arg01= 'row_3'; set @arg02= 3; set @arg03= 3.3; set @arg04= 333; set @arg05= 'row_three';
set @arg06= '2004-10-12'; set @arg07= '3'; set @arg08= 3.3; set @arg09= '300300300'; set @arg10= '12:34:56';
set @arg11= 'row_3'; set @arg12= 3; set @arg13= 3.3; set @arg14= 333; set @arg15= 'row_three';
set @arg16= '2004-10-12'; set @arg17= '3'; set @arg18= 3.3; set @arg19= '300300300'; set @arg20= '12:34:56';
execute st_1644 using @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, @arg09, @arg10,
@arg11, @arg12, @arg13, @arg14, @arg15, @arg16, @arg17, @arg18, @arg19, @arg20;
select * from t1;
drop table t1;
# end of bug#1644
# bug#1676: Prepared statement two-table join returns no rows when one is expected
create table t1(
cola varchar(50) not null,
colb varchar(8) not null,
colc varchar(12) not null,
cold varchar(2) not null,
primary key (cola, colb, cold));
create table t2(
cola varchar(50) not null,
colb varchar(8) not null,
colc varchar(2) not null,
cold float,
primary key (cold));
insert into t1 values ('aaaa', 'yyyy', 'yyyy-dd-mm', 'R');
insert into t2 values ('aaaa', 'yyyy', 'R', 203), ('bbbb', 'zzzz', 'C', 201);
prepare st_1676 from 'select a.cola, a.colb, a.cold from t1 a, t2 b where a.cola = ? and a.colb = ? and a.cold = ? and b.cola = a.cola and b.colb = a.colb and b.colc = a.cold';
set @arg0= "aaaa";
set @arg1= "yyyy";
set @arg2= "R";
execute st_1676 using @arg0, @arg1, @arg2;
drop table t1, t2;
# end of bug#1676
# End of 4.1 tests
# bug#18492: mysqld reports ER_ILLEGAL_REFERENCE in --ps-protocol
create table t1 (a int primary key);
insert into t1 values (1);
explain select * from t1 where 3 in (select (1+1) union select 1);
select * from t1 where 3 in (select (1+1) union select 1);
prepare st_18492 from 'select * from t1 where 3 in (select (1+1) union select 1)';
execute st_18492;
drop table t1;
#
# Bug#19356: Assertion failure with undefined @uservar in prepared statement execution
#
create table t1 (a int, b varchar(4));
create table t2 (a int, b varchar(4), primary key(a));
prepare stmt1 from 'insert into t1 (a, b) values (?, ?)';
prepare stmt2 from 'insert into t2 (a, b) values (?, ?)';
set @intarg= 11;
set @varchararg= '2222';
execute stmt1 using @intarg, @varchararg;
execute stmt2 using @intarg, @varchararg;
set @intarg= 12;
execute stmt1 using @intarg, @UNDEFINED;
execute stmt2 using @intarg, @UNDEFINED;
set @intarg= 13;
execute stmt1 using @UNDEFINED, @varchararg;
--error 1048
execute stmt2 using @UNDEFINED, @varchararg;
set @intarg= 14;
set @nullarg= Null;
execute stmt1 using @UNDEFINED, @nullarg;
--error 1048
execute stmt2 using @nullarg, @varchararg;
select * from t1;
select * from t2;
drop table t1;
drop table t2;
#
# Bug #32124: crash if prepared statements refer to variables in the where
# clause
#
CREATE TABLE t1 (a INT);
PREPARE stmt FROM 'select 1 from `t1` where `a` = any (select (@@tmpdir))';
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
CREATE TABLE t2 (a INT PRIMARY KEY);
INSERT INTO t2 VALUES (400000), (400001);
SET @@sort_buffer_size=400000;
DELIMITER |;
CREATE FUNCTION p1(i INT) RETURNS INT
BEGIN
SET @@sort_buffer_size= i;
RETURN i + 1;
END|
DELIMITER ;|
SELECT * FROM t2 WHERE a = @@sort_buffer_size AND p1(@@sort_buffer_size + 1) > a - 1;
DROP TABLE t2;
DROP FUNCTION p1;
SELECT CONCAT(@@sort_buffer_size);
SELECT LEFT("12345", @@ft_boolean_syntax);
SET @@sort_buffer_size=DEFAULT;
--echo End of 5.0 tests.
|
