1118
1119
DEALLOCATE PREPARE stmt;
1123
# Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work
1124
# from stored procedure.
1126
# The cause of a bug was that cached LEX::create_list was modified,
1127
# and then together with LEX::key_list was reset.
1130
DROP TABLE IF EXISTS t1, t2;
1133
CREATE TABLE t1 (i INT);
1136
FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1";
1143
# Check that on second execution we don't loose 'j' column and the keys
1144
# on 'i' and 'j' columns.
1148
DEALLOCATE PREPARE st_19182;
1152
# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server"
1154
# Code which implemented CREATE/ALTER TABLE and CREATE DATABASE
1155
# statement modified HA_CREATE_INFO structure in LEX, making these
1156
# statements PS/SP-unsafe (their re-execution might have resulted
1157
# in incorrect results).
1160
drop database if exists mysqltest;
1161
drop table if exists t1, t2;
1163
# CREATE TABLE and CREATE TABLE ... SELECT
1164
create database mysqltest character set utf8;
1165
prepare stmt1 from "create table mysqltest.t1 (c char(10))";
1166
prepare stmt2 from "create table mysqltest.t2 select 'test'";
1169
show create table mysqltest.t1;
1170
show create table mysqltest.t2;
1171
drop table mysqltest.t1;
1172
drop table mysqltest.t2;
1173
alter database mysqltest character set latin1;
1176
show create table mysqltest.t1;
1177
show create table mysqltest.t2;
1178
drop database mysqltest;
1179
deallocate prepare stmt1;
1180
deallocate prepare stmt2;
1182
# CREATE TABLE with DATA DIRECTORY option
1184
# Protect ourselves from data left in tmp/ by a previos possibly failed
1186
--system rm -f $MYSQLTEST_VARDIR/tmp/t1.*
1189
eval prepare stmt from "create table t1 (c char(10)) data directory='$MYSQLTEST_VARDIR/tmp'";
1193
# DATA DIRECTORY option does not always work: if the operating
1194
# system does not support symlinks, have_symlinks option is automatically
1196
# In this case DATA DIRECTORY is silently ignored when
1197
# creating a table, and is not output by SHOW CREATE TABLE.
1199
--disable_result_log
1200
show create table t1;
1204
--disable_result_log
1205
show create table t1;
1209
deallocate prepare stmt;
1213
# Bug #27937: crash on the second execution for prepared statement
1214
# from UNION with ORDER BY an expression containing RAND()
1217
CREATE TABLE t1(a int);
1218
INSERT INTO t1 VALUES (2), (3), (1);
1221
'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
1226
DEALLOCATE PREPARE st1;
1121
1229
--echo End of 4.1 tests.
1124
1231
############################# 5.0 tests start ################################
1596
1704
DEALLOCATE PREPARE stmt;
1598
1706
DROP TABLE t1,t2;
1708
# Bug#4968 "Stored procedure crash if cursor opened on altered table"
1709
# The bug is not repeatable any more after the fix for
1710
# Bug#15217 "Bug #15217 Using a SP cursor on a table created with PREPARE
1711
# fails with weird error", however ALTER TABLE is not re-execution friendly
1712
# and that caused a valgrind warning. Check that the warning is gone.
1715
drop table if exists t1;
1717
create table t1 (s1 char(20));
1718
prepare stmt from "alter table t1 modify s1 int";
1722
deallocate prepare stmt;
1725
# Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing"
1728
drop table if exists t1;
1730
create table t1 (a int, b int);
1731
prepare s_6895 from "alter table t1 drop column b";
1733
show columns from t1;
1735
create table t1 (a int, b int);
1737
show columns from t1;
1739
create table t1 (a int, b int);
1741
show columns from t1;
1742
deallocate prepare s_6895;
1746
# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server"
1748
# 5.0 part of the test.
1752
create table t1 (i int primary key auto_increment) comment='comment for table t1';
1753
create table t2 (i int, j int, k int);
1754
prepare stmt from "alter table t1 auto_increment=100";
1756
show create table t1;
1757
# Let us trash table-cache's memory
1761
show create table t1;
1762
deallocate prepare stmt;
1764
# 5.1 part of the test.
1766
set @old_character_set_server= @@character_set_server;
1767
set @@character_set_server= latin1;
1768
prepare stmt from "create database mysqltest_1";
1770
show create database mysqltest_1;
1771
drop database mysqltest_1;
1772
set @@character_set_server= utf8;
1774
show create database mysqltest_1;
1775
drop database mysqltest_1;
1776
deallocate prepare stmt;
1777
set @@character_set_server= @old_character_set_server;
1781
# BUG#24491 "using alias from source table in insert ... on duplicate key"
1784
drop tables if exists t1;
1786
create table t1 (id int primary key auto_increment, value varchar(10));
1787
insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
1788
# Let us prepare INSERT ... SELECT ... ON DUPLICATE KEY UPDATE statement
1789
# which in its ON DUPLICATE KEY clause erroneously tries to assign value
1790
# to a column which is mentioned only in SELECT part.
1791
prepare stmt from "insert into t1 (id, value) select * from (select 4 as i, 'FOURTH' as v) as y on duplicate key update v = 'DUP'";
1792
# Both first and second attempts to execute it should fail
1793
--error ER_BAD_FIELD_ERROR
1795
--error ER_BAD_FIELD_ERROR
1797
deallocate prepare stmt;
1798
# And now the same test for more complex case which is more close
1799
# to the one that was reported originally.
1800
prepare stmt from "insert into t1 (id, value) select * from (select 4 as id, 'FOURTH' as value) as y on duplicate key update y.value = 'DUP'";
1801
--error ER_BAD_FIELD_ERROR
1803
--error ER_BAD_FIELD_ERROR
1805
deallocate prepare stmt;
1809
# Bug #28509: strange behaviour: passing a decimal value to PS
1811
prepare stmt from "create table t1 select ?";
1813
execute stmt using @a;
1814
show create table t1;
1600
1817
--echo End of 5.0 tests.
1801
2018
select func_1(), func_1(), func_1() from dual;
1802
2019
drop function func_1;
1803
2020
drop procedure proc_1;
2022
# make the output deterministic:
2023
# the order used in SHOW OPEN TABLES
2024
# is too much implementation dependent
2025
--disable_ps_protocol
1805
2027
select Host, User from mysql.user limit 0;
1806
2028
select Host, Db from mysql.host limit 0;
1807
2029
show open tables from mysql;
2030
--enable_ps_protocol
1808
2032
prepare abc from "flush tables";
1810
2034
show open tables from mysql;
2145
2369
create procedure proc_1() install plugin my_plug soname 'some_plugin.so';
2146
2370
--replace_regex /(Can\'t open shared library).*$/\1/
2147
--error ER_CANT_OPEN_LIBRARY
2149
--replace_regex /(Can\'t open shared library).*$/\1/
2150
--error ER_CANT_OPEN_LIBRARY
2152
--replace_regex /(Can\'t open shared library).*$/\1/
2153
--error ER_CANT_OPEN_LIBRARY
2371
--error ER_CANT_OPEN_LIBRARY,ER_FEATURE_DISABLED
2373
--replace_regex /(Can\'t open shared library).*$/\1/
2374
--error ER_CANT_OPEN_LIBRARY,ER_FEATURE_DISABLED
2376
--replace_regex /(Can\'t open shared library).*$/\1/
2377
--error ER_CANT_OPEN_LIBRARY,ER_FEATURE_DISABLED
2155
2379
drop procedure proc_1;
2485
2709
execute stmt2 using @to_format, @dec;
2486
2710
deallocate prepare stmt2;
2714
# BUG#18326: Do not lock table for writing during prepare of statement
2717
DROP TABLE IF EXISTS t1, t2;
2720
CREATE TABLE t1 (i INT);
2721
INSERT INTO t1 VALUES (1);
2722
CREATE TABLE t2 (i INT);
2723
INSERT INTO t2 VALUES (2);
2725
LOCK TABLE t1 READ, t2 WRITE;
2727
connect (conn1, localhost, root, , );
2729
# Prepare never acquires the lock, and thus should not block.
2730
PREPARE stmt1 FROM "SELECT i FROM t1";
2731
PREPARE stmt2 FROM "INSERT INTO t2 (i) VALUES (3)";
2733
# This should not block because READ lock on t1 is shared.
2736
# This should block because WRITE lock on t2 is exclusive.
2743
let $wait_condition= SELECT COUNT(*) = 2 FROM t2;
2744
--source include/wait_condition.inc
2747
# DDL and DML works even if some client have a prepared statement
2748
# referencing the table.
2749
ALTER TABLE t1 ADD COLUMN j INT;
2750
ALTER TABLE t2 ADD COLUMN j INT;
2751
INSERT INTO t1 VALUES (4, 5);
2752
INSERT INTO t2 VALUES (4, 5);
2768
# Bug #24879 Prepared Statements: CREATE TABLE (UTF8 KEY) produces a growing
2771
# Test that parse information is not altered by subsequent executions of a
2772
# prepared statement
2774
drop table if exists t1;
2776
from "create table t1 (c char(100) character set utf8, key (c(10)))";
2778
show create table t1;
2781
show create table t1;
2784
--echo End of 5.1 tests.