1 ###############################################
3 # Prepared Statements #
4 # re-testing bug DB entries #
6 # The bugs are reported as "closed". #
7 # Command sequences taken from bug report. #
8 # No other test contains the bug# as comment. #
10 # Tests drop/create tables 't1', 't2', ... #
12 ###############################################
15 drop table if exists t1, t2;
18 # bug#1180: optimized away part of WHERE clause cause incorect prepared satatement results
20 CREATE TABLE t1(session_id char(9) NOT NULL);
21 INSERT INTO t1 VALUES ("abc");
24 prepare st_1180 from 'SELECT * FROM t1 WHERE ?="1111" and session_id = "abc"';
28 execute st_1180 using @arg1;
30 # Now, it should find one row
32 execute st_1180 using @arg1;
34 # Back to non-matching
36 execute st_1180 using @arg1;
43 # bug#1644: Insertion of more than 3 NULL columns with parameter binding fails
45 # Using prepared statements, insertion of more than three columns with NULL
46 # values fails to insert additional NULLS. After the third column NULLS will
47 # be inserted into the database as zeros.
48 # First insert four columns of a value (i.e. 22) to verify binding is working
49 # correctly. Then Bind to each columns bind parameter an is_null value of 1.
50 # Then insert four more columns of integers, just for sanity.
51 # A subsequent select on the server will result in this:
52 # mysql> select * from foo_dfr;
53 # +------+------+------+------+
54 # | col1 | col2 | col3 | col4 |
55 # +------+------+------+------+
56 # | 22 | 22 | 22 | 22 |
57 # | NULL | NULL | NULL | 0 |
58 # | 88 | 88 | 88 | 88 |
59 # +------+------+------+------+
61 # Test is extended to more columns - code stores bit vector in bytes.
64 c_01 char(6), c_02 integer, c_03 real, c_04 int(3), c_05 varchar(20),
65 c_06 date, c_07 char(1), c_08 real, c_09 int(11), c_10 time,
66 c_11 char(6), c_12 integer, c_13 real, c_14 int(3), c_15 varchar(20),
67 c_16 date, c_17 char(1), c_18 real, c_19 int(11), c_20 text);
68 # Do not use "timestamp" type, because it has a non-NULL default as of 4.1.2
70 prepare st_1644 from 'insert into t1 values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
72 set @arg01= 'row_1'; set @arg02= 1; set @arg03= 1.1; set @arg04= 111; set @arg05= 'row_one';
73 set @arg06= '2004-10-12'; set @arg07= '1'; set @arg08= 1.1; set @arg09= '100100100'; set @arg10= '12:34:56';
74 set @arg11= 'row_1'; set @arg12= 1; set @arg13= 1.1; set @arg14= 111; set @arg15= 'row_one';
75 set @arg16= '2004-10-12'; set @arg17= '1'; set @arg18= 1.1; set @arg19= '100100100'; set @arg20= '12:34:56';
76 execute st_1644 using @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, @arg09, @arg10,
77 @arg11, @arg12, @arg13, @arg14, @arg15, @arg16, @arg17, @arg18, @arg19, @arg20;
79 set @arg01= NULL; set @arg02= NULL; set @arg03= NULL; set @arg04= NULL; set @arg05= NULL;
80 set @arg06= NULL; set @arg07= NULL; set @arg08= NULL; set @arg09= NULL; set @arg10= NULL;
81 set @arg11= NULL; set @arg12= NULL; set @arg13= NULL; set @arg14= NULL; set @arg15= NULL;
82 set @arg16= NULL; set @arg17= NULL; set @arg18= NULL; set @arg19= NULL; set @arg20= NULL;
83 execute st_1644 using @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, @arg09, @arg10,
84 @arg11, @arg12, @arg13, @arg14, @arg15, @arg16, @arg17, @arg18, @arg19, @arg20;
86 set @arg01= 'row_3'; set @arg02= 3; set @arg03= 3.3; set @arg04= 333; set @arg05= 'row_three';
87 set @arg06= '2004-10-12'; set @arg07= '3'; set @arg08= 3.3; set @arg09= '300300300'; set @arg10= '12:34:56';
88 set @arg11= 'row_3'; set @arg12= 3; set @arg13= 3.3; set @arg14= 333; set @arg15= 'row_three';
89 set @arg16= '2004-10-12'; set @arg17= '3'; set @arg18= 3.3; set @arg19= '300300300'; set @arg20= '12:34:56';
90 execute st_1644 using @arg01, @arg02, @arg03, @arg04, @arg05, @arg06, @arg07, @arg08, @arg09, @arg10,
91 @arg11, @arg12, @arg13, @arg14, @arg15, @arg16, @arg17, @arg18, @arg19, @arg20;
100 # bug#1676: Prepared statement two-table join returns no rows when one is expected
103 cola varchar(50) not null,
104 colb varchar(8) not null,
105 colc varchar(12) not null,
106 cold varchar(2) not null,
107 primary key (cola, colb, cold));
110 cola varchar(50) not null,
111 colb varchar(8) not null,
112 colc varchar(2) not null,
116 insert into t1 values ('aaaa', 'yyyy', 'yyyy-dd-mm', 'R');
118 insert into t2 values ('aaaa', 'yyyy', 'R', 203), ('bbbb', 'zzzz', 'C', 201);
120 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';
126 execute st_1676 using @arg0, @arg1, @arg2;
134 # bug#18492: mysqld reports ER_ILLEGAL_REFERENCE in --ps-protocol
136 create table t1 (a int primary key);
137 insert into t1 values (1);
139 explain select * from t1 where 3 in (select (1+1) union select 1);
141 select * from t1 where 3 in (select (1+1) union select 1);
143 prepare st_18492 from 'select * from t1 where 3 in (select (1+1) union select 1)';
149 # Bug#19356: Assertion failure with undefined @uservar in prepared statement execution
151 create table t1 (a int, b varchar(4));
152 create table t2 (a int, b varchar(4), primary key(a));
154 prepare stmt1 from 'insert into t1 (a, b) values (?, ?)';
155 prepare stmt2 from 'insert into t2 (a, b) values (?, ?)';
158 set @varchararg= '2222';
159 execute stmt1 using @intarg, @varchararg;
160 execute stmt2 using @intarg, @varchararg;
162 execute stmt1 using @intarg, @UNDEFINED;
163 execute stmt2 using @intarg, @UNDEFINED;
165 execute stmt1 using @UNDEFINED, @varchararg;
167 execute stmt2 using @UNDEFINED, @varchararg;
170 execute stmt1 using @UNDEFINED, @nullarg;
172 execute stmt2 using @nullarg, @varchararg;
181 # Bug #32124: crash if prepared statements refer to variables in the where
185 CREATE TABLE t1 (a INT);
186 PREPARE stmt FROM 'select 1 from `t1` where `a` = any (select (@@tmpdir))';
188 DEALLOCATE PREPARE stmt;
191 CREATE TABLE t2 (a INT PRIMARY KEY);
192 INSERT INTO t2 VALUES (400000), (400001);
194 SET @@sort_buffer_size=400000;
198 CREATE FUNCTION p1(i INT) RETURNS INT
200 SET @@sort_buffer_size= i;
206 SELECT * FROM t2 WHERE a = @@sort_buffer_size AND p1(@@sort_buffer_size + 1) > a - 1;
212 SELECT CONCAT(@@sort_buffer_size);
213 SELECT LEFT("12345", @@ft_boolean_syntax);
215 SET @@sort_buffer_size=DEFAULT;
217 --echo End of 5.0 tests.