ps.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

#
# Bug #14956: ROW_COUNT() returns incorrect result after EXECUTE of prepared
# statement
#
create table t1 (id int);
prepare ins_call from "insert into t1 (id) values (1)";
execute ins_call;
select row_count();
drop table t1;

#
# BUG#16474: SP crashed MySQL
# (when using "order by localvar", where 'localvar' is just that.
# The actual bug test is in sp.test, this is just testing that we get the
# expected result for prepared statements too, i.e. place holders work as
# textual substitution. If it's a single integer, it works as the (deprecated)
# "order by column#", otherwise it's an expression.
#
create table t1 (a int, b int);
insert into t1 (a,b) values (2,8),(1,9),(3,7);

# Will order by index
prepare stmt from "select * from t1 order by ?";
set @a=NULL;
execute stmt using @a;
set @a=1;
execute stmt using @a;
set @a=2;
execute stmt using @a;
deallocate prepare stmt;
# For reference:
select * from t1 order by 1;

# Will not order by index.
prepare stmt from "select * from t1 order by ?+1";
set @a=0;
execute stmt using @a;
set @a=1;
execute stmt using @a;
deallocate prepare stmt;
# For reference:
select * from t1 order by 1+1;

drop table t1;

#
# Bug#19308 "REPAIR/OPTIMIZE/ANALYZE supported in SP but not in PS".
# Add test coverage for the added commands.
#
create table t1 (a int);
create table t2 like t1;
create table t3 like t2;
prepare stmt from "repair table t1";
execute stmt;
execute stmt;
prepare stmt from "optimize table t1";
execute stmt;
execute stmt;
prepare stmt from "analyze table t1";
execute stmt;
execute stmt;
prepare stmt from "repair table t1, t2, t3";
execute stmt;
execute stmt;
prepare stmt from "optimize table t1, t2, t3";
execute stmt;
execute stmt;
prepare stmt from "analyze table t1, t2, t3";
execute stmt;
execute stmt;
prepare stmt from "repair table t1, t4, t3";
execute stmt;
execute stmt;
prepare stmt from "optimize table t1, t3, t4";
execute stmt;
execute stmt;
prepare stmt from "analyze table t4, t1";
execute stmt;
execute stmt;
deallocate prepare stmt;
drop table t1, t2, t3;

#
# Bug#17199 "Table not found" error occurs if the query contains a call
#            to a function from another database.
#            Test prepared statements- related behaviour.
#
#
# ALTER TABLE RENAME and Prepared Statements: wrong DB name buffer was used
# in ALTER ... RENAME which caused memory corruption in prepared statements.
# No need to fix this problem in 4.1 as ALTER TABLE is not allowed in
# Prepared Statements in 4.1.
#
create database mysqltest_long_database_name_to_thrash_heap;
use test;
create table t1 (i int);
prepare stmt from "alter table test.t1 rename t1";
use mysqltest_long_database_name_to_thrash_heap;
execute stmt;
show tables like 't1';
prepare stmt from "alter table test.t1 rename t1";
use test;
execute stmt;
show tables like 't1';
use mysqltest_long_database_name_to_thrash_heap;
show tables like 't1';
deallocate prepare stmt;
#
# Check that a prepared statement initializes its current database at
# PREPARE, and then works correctly even if the current database has been
# changed.
# 
use mysqltest_long_database_name_to_thrash_heap; 
# Necessary for preparation of INSERT/UPDATE/DELETE to succeed
prepare stmt_create from "create table t1 (i int)";
prepare stmt_insert from "insert into t1 (i) values (1)";
prepare stmt_update from "update t1 set i=2";
prepare stmt_delete from "delete from t1 where i=2";
prepare stmt_select from "select * from t1";
prepare stmt_alter from "alter table t1 add column (b int)";
prepare stmt_alter1 from "alter table t1 drop column b";
prepare stmt_analyze from "analyze table t1";
prepare stmt_optimize from "optimize table t1";
prepare stmt_show from "show tables like 't1'";
prepare stmt_truncate from "truncate table t1";
prepare stmt_drop from "drop table t1";
# Drop the table that was used to prepare INSERT/UPDATE/DELETE: we will
# create a new one by executing stmt_create
drop table t1;
# Switch the current database
use test;
# Check that all prepared statements operate on the database that was
# active at PREPARE
execute stmt_create;
# should return empty set
show tables like 't1';
use mysqltest_long_database_name_to_thrash_heap;
show tables like 't1';
use test;
execute stmt_insert;
select * from mysqltest_long_database_name_to_thrash_heap.t1;
execute stmt_update;
select * from mysqltest_long_database_name_to_thrash_heap.t1;
execute stmt_delete;
execute stmt_select;
execute stmt_alter;
show columns from mysqltest_long_database_name_to_thrash_heap.t1;
execute stmt_alter1;
show columns from mysqltest_long_database_name_to_thrash_heap.t1;
execute stmt_analyze;
execute stmt_optimize;
execute stmt_show;
execute stmt_truncate;
execute stmt_drop;
show tables like 't1';
use mysqltest_long_database_name_to_thrash_heap;
show tables like 't1';
#
# Attempt a statement PREPARE when there is no current database:
# is expected to return an error.
#
drop database mysqltest_long_database_name_to_thrash_heap;
--error ER_NO_DB_ERROR
prepare stmt_create from "create table t1 (i int)";
--error ER_NO_DB_ERROR
prepare stmt_insert from "insert into t1 (i) values (1)";
--error ER_NO_DB_ERROR
prepare stmt_update from "update t1 set i=2";
--error ER_NO_DB_ERROR
prepare stmt_delete from "delete from t1 where i=2";
--error ER_NO_DB_ERROR
prepare stmt_select from "select * from t1";
--error ER_NO_DB_ERROR
prepare stmt_alter from "alter table t1 add column (b int)";
--error ER_NO_DB_ERROR
prepare stmt_alter1 from "alter table t1 drop column b";
--error ER_NO_DB_ERROR
prepare stmt_analyze from "analyze table t1";
--error ER_NO_DB_ERROR
prepare stmt_optimize from "optimize table t1";
--error ER_NO_DB_ERROR
prepare stmt_show from "show tables like 't1'";
--error ER_NO_DB_ERROR
prepare stmt_truncate from "truncate table t1";
--error ER_NO_DB_ERROR
prepare stmt_drop from "drop table t1";
#
# The above has automatically deallocated all our statements.
#
# Attempt to CREATE a temporary table when no DB used: it should fail
# This proves that no table can be used without explicit specification of
# its database if there is no current database. 
#
--error ER_NO_DB_ERROR
create temporary table t1 (i int);
#
# Restore the old environemnt
#
use test;


#
# BUG#21166: Prepared statement causes signal 11 on second execution
#
# Changes in an item tree done by optimizer weren't properly
# registered and went unnoticed, which resulted in preliminary freeing
# of used memory.
#
--disable_warnings
DROP TABLE IF EXISTS t1, t2, t3;
--enable_warnings

CREATE TABLE t1 (i BIGINT, j BIGINT);
CREATE TABLE t2 (i BIGINT);
CREATE TABLE t3 (i BIGINT, j BIGINT);

PREPARE stmt FROM "SELECT * FROM t1 JOIN t2 ON (t2.i = t1.i)
                   LEFT JOIN t3 ON ((t3.i, t3.j) = (t1.i, t1.j))
                   WHERE t1.i = ?";

SET @a= 1;
EXECUTE stmt USING @a;
EXECUTE stmt USING @a;

DEALLOCATE PREPARE stmt;
DROP TABLE IF EXISTS t1, t2, t3;


#
# BUG#21081: SELECT inside stored procedure returns wrong results
#
--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings

CREATE TABLE t1 (i INT KEY);
CREATE TABLE t2 (i INT);

INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1);

PREPARE stmt FROM "SELECT t2.i FROM t1 LEFT JOIN t2 ON t2.i = t1.i
                   WHERE t1.i = ?";

SET @arg= 1;
EXECUTE stmt USING @arg;
SET @arg= 2;
EXECUTE stmt USING @arg;
SET @arg= 1;
EXECUTE stmt USING @arg;

DEALLOCATE PREPARE stmt;
DROP TABLE t1, t2;


#
# BUG#20327: Marking of a wrong field leads to a wrong result on select with
#            view, prepared statement and subquery.
#
CREATE TABLE t1 (i INT);
CREATE VIEW v1 AS SELECT * FROM t1;

INSERT INTO t1 VALUES (1), (2);

let $query = SELECT t1.i FROM t1 JOIN v1 ON t1.i = v1.i
             WHERE EXISTS (SELECT * FROM t1 WHERE v1.i = 1);
eval $query;
eval PREPARE stmt FROM "$query";
# Statement execution should return '1'.
EXECUTE stmt;
# Check re-execution.
EXECUTE stmt;

DEALLOCATE PREPARE stmt;
DROP VIEW v1;
DROP TABLE t1;


#
# BUG#21856: Prepared Statments: crash if bad create
#
--disable_warnings
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

let $iterations= 100;
--disable_query_log
--disable_result_log
while ($iterations > 0)
{
  --error ER_PARSE_ERROR
  PREPARE stmt FROM "CREATE PROCEDURE p1()";
  dec $iterations;
}
--enable_query_log
--enable_result_log

#
# Bug 19764:  SHOW commands end up in the slow log as table scans
#

flush status;
prepare sq from 'show status like "slow_queries"';
execute sq;
prepare no_index from 'select 1 from information_schema.tables limit 1';
execute sq;
execute no_index;
execute sq;
deallocate prepare no_index;
deallocate prepare sq;


#
# Bug 25027: query with a single-row non-correlated subquery
#            and IS NULL predicate
#

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1), (2);
CREATE TABLE t2 (b int);
INSERT INTO t2 VALUES (NULL);

SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL;
PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL';

EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2 limit ?) IS NULL';
SET @arg=1;
EXECUTE stmt USING @arg;
DEALLOCATE PREPARE stmt;

DROP TABLE t1,t2;
#
# Bug#4968 "Stored procedure crash if cursor opened on altered table"
# The bug is not repeatable any more after the fix for
# Bug#15217 "Bug #15217   Using a SP cursor on a table created with PREPARE
# fails with weird error", however ALTER TABLE is not re-execution friendly
# and that caused a valgrind warning. Check that the warning is gone.
#
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (s1 char(20));
prepare stmt from "alter table t1 modify s1 int";
execute stmt;
execute stmt;
drop table t1;
deallocate prepare stmt;

#
# Bug#6895 "Prepared Statements: ALTER TABLE DROP COLUMN does nothing"
#
--disable_warnings
drop table if exists t1;
--enable_warnings
create table t1 (a int, b int);
prepare s_6895 from "alter table t1 drop column b";
execute s_6895;
show columns from t1;
drop table t1;
create table t1 (a int, b int);
execute s_6895;
show columns from t1;
drop table t1;
create table t1 (a int, b int);
execute s_6895;
show columns from t1;
deallocate prepare s_6895;
drop table t1;

#
# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server"
#
# 5.0 part of the test.
#

# ALTER TABLE
create table t1 (i int primary key auto_increment) comment='comment for table t1';
create table t2 (i int, j int, k int);
prepare stmt from "alter table t1 auto_increment=100";
execute stmt;
show create table t1;
# Let us trash table-cache's memory
flush tables;
select * from t2;
execute stmt;
show create table t1;
deallocate prepare stmt;
drop table t1, t2;
# 5.1 part of the test.
# CREATE DATABASE
#set @old_character_set_server= @@character_set_server;
#set @@character_set_server= latin1; 
#prepare stmt from "create database mysqltest";
#execute stmt;
#show create database mysqltest;
#drop database mysqltest;
#set @@character_set_server= utf8; 
#execute stmt;
#show create database mysqltest;
#drop database mysqltest;
#deallocate prepare stmt;
#set @@character_set_server= @old_character_set_server;


#
# BUG#24491 "using alias from source table in insert ... on duplicate key"
#
--disable_warnings
drop tables if exists t1;
--enable_warnings
create table t1 (id int primary key auto_increment, value varchar(10));
insert into t1 (id, value) values (1, 'FIRST'), (2, 'SECOND'), (3, 'THIRD');
# Let us prepare INSERT ... SELECT ... ON DUPLICATE KEY UPDATE statement
# which in its ON DUPLICATE KEY clause erroneously tries to assign value
# to a column which is mentioned only in SELECT part.
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'";
# Both first and second attempts to execute it should fail
--error ER_BAD_FIELD_ERROR 
execute stmt;
--error ER_BAD_FIELD_ERROR
execute stmt;
deallocate prepare stmt;
# And now the same test for more complex case which is more close
# to the one that was reported originally.
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'";
--error ER_BAD_FIELD_ERROR 
execute stmt;
--error ER_BAD_FIELD_ERROR
execute stmt;
deallocate prepare stmt;
drop tables t1;

#
# Bug #28509: strange behaviour: passing a decimal value to PS
#
prepare stmt from "create table t1 select ?";
set @a=1.0;
execute stmt using @a;
show create table t1;
drop table t1;

--echo End of 5.0 tests.