diff options
Diffstat (limited to 'mysql-test/r/ps.result')
| -rw-r--r-- | mysql-test/r/ps.result | 211 |
1 files changed, 195 insertions, 16 deletions
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index e94c2952893..9afbca9dcb1 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1,4 +1,5 @@ drop table if exists t1,t2; +drop database if exists client_test_db; create table t1 ( a int primary key, @@ -137,7 +138,7 @@ create table t1 c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 integer, c6 bigint, c7 float, c8 double, c9 double precision, c10 real, c11 decimal(7, 4), c12 numeric(8, 4), -c13 date, c14 datetime, c15 timestamp(14), c16 time, +c13 date, c14 datetime, c15 timestamp, c16 time, c17 year, c18 bit, c19 bool, c20 char, c21 char(10), c22 varchar(30), c23 tinyblob, c24 tinytext, c25 blob, c26 text, c27 mediumblob, c28 mediumtext, @@ -153,24 +154,24 @@ id select_type table type possible_keys key key_len ref rows Extra 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found 4 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found -3 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found 4 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found -3 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found 4 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found -3 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found -2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables deallocate prepare stmt1; drop tables t1,t2; set @arg00=1; @@ -207,10 +208,10 @@ create table t1 ( a int primary key, b varchar(30)) engine = MYISAM ; prepare stmt1 from ' show table status from test like ''t1%'' '; execute stmt1; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 MyISAM 9 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL +t1 MyISAM 10 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL show table status from test like 't1%' ; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment -t1 MyISAM 9 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL +t1 MyISAM 10 Dynamic 0 0 0 4294967295 1024 0 NULL # # # latin1_swedish_ci NULL deallocate prepare stmt1 ; drop table t1; create table t1(a varchar(2), b varchar(3)); @@ -336,7 +337,7 @@ set @precision=10000000000; select rand(), cast(rand(10)*@precision as unsigned integer) from t1; rand() cast(rand(10)*@precision as unsigned integer) -- 6570515219 +- 6570515220 - 1282061302 - 6698761160 - 9647622201 @@ -347,23 +348,23 @@ prepare stmt from set @var=1; execute stmt using @var; rand() cast(rand(10)*@precision as unsigned integer) cast(rand(?)*@precision as unsigned integer) -- 6570515219 - +- 6570515220 - - 1282061302 - - 6698761160 - - 9647622201 - set @var=2; execute stmt using @var; rand() cast(rand(10)*@precision as unsigned integer) cast(rand(?)*@precision as unsigned integer) -- 6570515219 6555866465 -- 1282061302 1223466192 -- 6698761160 6449731873 +- 6570515220 6555866465 +- 1282061302 1223466193 +- 6698761160 6449731874 - 9647622201 8578261098 set @var=3; execute stmt using @var; rand() cast(rand(10)*@precision as unsigned integer) cast(rand(?)*@precision as unsigned integer) -- 6570515219 9057697559 +- 6570515220 9057697560 - 1282061302 3730790581 -- 6698761160 1480860534 +- 6698761160 1480860535 - 9647622201 6211931236 drop table t1; deallocate prepare stmt; @@ -480,6 +481,11 @@ execute stmt; pnum deallocate prepare stmt; drop table t1, t2; +create table t1 (a varchar(20)); +insert into t1 values ('foo'); +prepare stmt FROM 'SELECT char_length (a) FROM t1'; +ERROR 42000: FUNCTION test.char_length does not exist +drop table t1; prepare stmt from "SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0"; execute stmt; foo @@ -492,6 +498,77 @@ SELECT FOUND_ROWS(); FOUND_ROWS() 2 deallocate prepare stmt; +create table t1 (a char(3) not null, b char(3) not null, +c char(3) not null, primary key (a, b, c)); +create table t2 like t1; +prepare stmt from +"select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b) + where t1.a=1"; +execute stmt; +a +execute stmt; +a +execute stmt; +a +prepare stmt from +"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from +(t1 left outer join t2 on t2.a=? and t1.b=t2.b) +left outer join t2 t3 on t3.a=? where t1.a=?"; +set @a:=1, @b:=1, @c:=1; +execute stmt using @a, @b, @c; +a b c a b c +execute stmt using @a, @b, @c; +a b c a b c +execute stmt using @a, @b, @c; +a b c a b c +deallocate prepare stmt; +drop table t1,t2; +SET @aux= "SELECT COUNT(*) + FROM INFORMATION_SCHEMA.COLUMNS A, + INFORMATION_SCHEMA.COLUMNS B + WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA + AND A.TABLE_NAME = B.TABLE_NAME + AND A.COLUMN_NAME = B.COLUMN_NAME AND + A.TABLE_NAME = 'user'"; +prepare my_stmt from @aux; +execute my_stmt; +COUNT(*) +37 +execute my_stmt; +COUNT(*) +37 +execute my_stmt; +COUNT(*) +37 +deallocate prepare my_stmt; +drop procedure if exists p1| +drop table if exists t1| +create table t1 (id int)| +insert into t1 values(1)| +create procedure p1(a int, b int) +begin +declare c int; +select max(id)+1 into c from t1; +insert into t1 select a+b; +insert into t1 select a-b; +insert into t1 select a-c; +end| +set @a= 3, @b= 4| +prepare stmt from "call p1(?, ?)"| +execute stmt using @a, @b| +execute stmt using @a, @b| +select * from t1| +id +1 +7 +-1 +1 +7 +-1 +-5 +deallocate prepare stmt| +drop procedure p1| +drop table t1| drop table if exists t1; Warnings: Note 1051 Unknown table 't1' @@ -555,6 +632,47 @@ id 3 deallocate prepare stmt; drop table t1, t2; +create table t1 (a int); +insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +prepare stmt from "select * from t1 limit ?, ?"; +set @offset=0, @limit=1; +execute stmt using @offset, @limit; +a +1 +select * from t1 limit 0, 1; +a +1 +set @offset=3, @limit=2; +execute stmt using @offset, @limit; +a +4 +5 +select * from t1 limit 3, 2; +a +4 +5 +prepare stmt from "select * from t1 limit ?"; +execute stmt using @limit; +a +1 +2 +prepare stmt from "select * from t1 where a in (select a from t1 limit ?)"; +ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' +prepare stmt from "select * from t1 union all select * from t1 limit ?, ?"; +set @offset=9; +set @limit=2; +execute stmt using @offset, @limit; +a +10 +1 +prepare stmt from "(select * from t1 limit ?, ?) union all + (select * from t1 limit ?, ?) order by a limit ?"; +execute stmt using @offset, @limit, @offset, @limit, @limit; +a +10 +10 +drop table t1; +deallocate prepare stmt; create table t1 (id int); prepare stmt from "insert into t1 (id) select id from t1 union select id from t1"; execute stmt; @@ -655,6 +773,14 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp select ? from t1; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? from t1' at line 1 drop table t1; +CREATE TABLE b12651_T1(a int) ENGINE=MYISAM; +CREATE TABLE b12651_T2(b int) ENGINE=MYISAM; +CREATE VIEW b12651_V1 as SELECT b FROM b12651_T2; +PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)'; +EXECUTE b12651; +1 +DROP VIEW b12651_V1; +DROP TABLE b12651_T1, b12651_T2; prepare stmt from "select @@time_zone"; execute stmt; @@time_zone @@ -747,3 +873,56 @@ length(a) 10 drop table t1; deallocate prepare stmt; +create table t1 (id int); +prepare ins_call from "insert into t1 (id) values (1)"; +execute ins_call; +select row_count(); +row_count() +1 +drop table t1; +create table t1 (a int, b int); +insert into t1 (a,b) values (2,8),(1,9),(3,7); +prepare stmt from "select * from t1 order by ?"; +execute stmt using @a; +a b +2 8 +1 9 +3 7 +set @a=1; +execute stmt using @a; +a b +1 9 +2 8 +3 7 +set @a=2; +execute stmt using @a; +a b +3 7 +2 8 +1 9 +deallocate prepare stmt; +select * from t1 order by 1; +a b +1 9 +2 8 +3 7 +prepare stmt from "select * from t1 order by ?+1"; +set @a=0; +execute stmt using @a; +a b +2 8 +1 9 +3 7 +set @a=1; +execute stmt using @a; +a b +2 8 +1 9 +3 7 +deallocate prepare stmt; +select * from t1 order by 1+1; +a b +2 8 +1 9 +3 7 +drop table t1; |