diff options
Diffstat (limited to 'mysql-test')
32 files changed, 789 insertions, 42 deletions
diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result index fef813371c8..2508d751b46 100644 --- a/mysql-test/r/connect.result +++ b/mysql-test/r/connect.result @@ -1,3 +1,4 @@ +drop table if exists t1,t2; show tables; Tables_in_mysql columns_priv @@ -71,3 +72,8 @@ show tables; Tables_in_test delete from mysql.user where user=_binary"test"; flush privileges; +create table t1 (id integer not null auto_increment primary key); +create temporary table t2(id integer not null auto_increment primary key); +set @id := 1; +delete from t1 where id like @id; +drop table t1; diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 64c693a292a..48ff82aeeb2 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -955,6 +955,10 @@ char_length(a) length(a) a 2 4 ан drop table t1; set names utf8; +select 'andre%' like 'andreñ%' escape 'ñ'; +'andre%' like 'andreñ%' escape 'ñ' +1 +set names utf8; select 'a\\' like 'a\\'; 'a\\' like 'a\\' 1 diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index c4e8e08929d..6461c393d51 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -469,6 +469,15 @@ select collation(group_concat(a,b)) from t1; ERROR HY000: Illegal mix of collations (cp1250_general_ci,IMPLICIT) and (koi8r_general_ci,IMPLICIT) for operation 'group_concat' drop table t1; drop table t2; +CREATE TABLE t1 (a CHAR(10) CHARACTER SET cp850); +INSERT INTO t1 VALUES ('À'); +SELECT a FROM t1; +a +À +SELECT GROUP_CONCAT(a) FROM t1; +GROUP_CONCAT(a) +À +DROP TABLE t1; CREATE TABLE t1 (id int); SELECT GROUP_CONCAT(id) AS gc FROM t1 HAVING gc IS NULL; gc @@ -567,3 +576,23 @@ group_concat('x') NULL 1 drop table t1; +CREATE TABLE t1 (id int, a varchar(9)); +INSERT INTO t1 VALUES +(2, ''), (1, ''), (2, 'x'), (1, 'y'), (3, 'z'), (3, ''); +SELECT GROUP_CONCAT(a) FROM t1; +GROUP_CONCAT(a) +,,x,y,z, +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1; +GROUP_CONCAT(a ORDER BY a) +,,,x,y,z +SELECT GROUP_CONCAT(a) FROM t1 GROUP BY id; +GROUP_CONCAT(a) +,y +,x +z, +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY id; +GROUP_CONCAT(a ORDER BY a) +,y +,x +,z +DROP TABLE t1; diff --git a/mysql-test/r/func_like.result b/mysql-test/r/func_like.result index ac8e5eda8e8..7e6fedb9403 100644 --- a/mysql-test/r/func_like.result +++ b/mysql-test/r/func_like.result @@ -158,3 +158,10 @@ DROP TABLE t1; select _cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin; _cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin 1 +set names koi8r; +select 'andre%' like 'andreÊ%' escape 'Ê'; +'andre%' like 'andreÊ%' escape 'Ê' +1 +select _cp1251'andre%' like convert('andreÊ%' using cp1251) escape 'Ê'; +_cp1251'andre%' like convert('andreÊ%' using cp1251) escape 'Ê' +1 diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index f4cf5217fa7..7bc886022cc 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -766,6 +766,15 @@ n Warnings: Warning 1052 Column 'n' in group statement is ambiguous DROP TABLE t1; +create table t1(f1 varchar(5) key); +insert into t1 values (1),(2); +select sql_buffer_result max(f1) is null from t1; +max(f1) is null +0 +select sql_buffer_result max(f1)+1 from t1; +max(f1)+1 +3 +drop table t1; create table t1 (c1 char(3), c2 char(3)); create table t2 (c3 char(3), c4 char(3)); insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2'); diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 20b2f12f0a8..9a7a0b48f47 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -979,3 +979,14 @@ WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE'); 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 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL +DROP TABLE t1,t2; +create table t1(f1 int); +create view v1 (c) as select f1 from t1; +select database(); +database() +NULL +show fields from test.v1; +Field Type Null Key Default Extra +c int(11) YES NULL +drop view v1; +drop table t1; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index d4a20209162..92b352aa608 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -200,7 +200,7 @@ INSERT INTO t1 VALUES (10363,'Tecniques de Comunicacio Oral i Escrita','Tecnicas INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL); INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL); INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL); -INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL); +INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL); CREATE TABLE t2 ( idAssignatura int(11) DEFAULT '0' NOT NULL, Grup int(11) DEFAULT '0' NOT NULL, @@ -1001,3 +1001,136 @@ SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40; c11 c21 40 NULL DROP TABLE t1, t2; +CREATE TABLE t1 (a int PRIMARY KEY, b int); +CREATE TABLE t2 (a int PRIMARY KEY, b int); +INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10); +INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b; +a b a b +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b; +a b a b +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b); +a b a b +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b; +a b a b +2 1 NULL NULL +3 2 3 0 +4 3 4 1 +6 5 6 4 +8 7 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b; +a b a b +2 1 NULL NULL +3 2 3 0 +4 3 4 1 +6 5 6 4 +8 7 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b); +a b a b +2 1 NULL NULL +3 2 3 0 +4 3 4 1 +6 5 6 4 +8 7 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b; +a b a b +2 1 NULL NULL +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +8 7 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b); +a b a b +2 1 NULL NULL +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +8 7 NULL NULL +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b; +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b)); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b; +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b)); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b)); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b; +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b)); +a b a b +3 2 3 0 +4 3 4 1 +6 5 6 4 +7 8 7 5 +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL PRIMARY NULL NULL NULL 4 Using where +1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.a 1 +DROP TABLE t1,t2; diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 69871b2110b..df0ee09ea8e 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -555,6 +555,31 @@ IFNULL(a, 'TEST') COALESCE(b, 'TEST') 4 TEST TEST TEST DROP TABLE t1,t2; +CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 VALUES (1, 2); +SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP; +a b c count +1 1 1 1 +1 1 NULL 1 +1 2 1 1 +1 2 NULL 1 +1 NULL NULL 2 +NULL NULL NULL 2 +DROP TABLE t1; +CREATE TABLE t1 (a int(11) NOT NULL); +INSERT INTO t1 VALUES (1),(2); +SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t; +a a + 1 COUNT(*) +1 2 1 +2 3 1 +NULL NULL 2 +SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t; +a LENGTH(a) COUNT(*) +1 1 1 +2 1 1 +NULL NULL 2 +DROP TABLE t1; CREATE TABLE t1(id int, type char(1)); INSERT INTO t1 VALUES (1,"A"),(2,"C"),(3,"A"),(4,"A"),(5,"B"), @@ -577,15 +602,19 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using filesort DROP VIEW v1; DROP TABLE t1; -CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL); -INSERT INTO t1 VALUES (1, 1); -INSERT INTO t1 VALUES (1, 2); -SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP; -a b c count -1 1 1 1 -1 1 NULL 1 -1 2 1 1 -1 2 NULL 1 -1 NULL NULL 2 -NULL NULL NULL 2 +CREATE TABLE t1 (a int(11) NOT NULL); +INSERT INTO t1 VALUES (1),(2); +CREATE VIEW v1 AS +SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP; +DESC v1; +Field Type Null Key Default Extra +a bigint(11) YES NULL +LENGTH(a) bigint(10) YES NULL +COUNT(*) bigint(21) NO 0 +SELECT * FROM v1; +a LENGTH(a) COUNT(*) +1 1 1 +2 1 1 +NULL NULL 2 +DROP VIEW v1; DROP TABLE t1; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index dc78f8d04ea..f1c3672083d 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -773,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 diff --git a/mysql-test/r/rpl_sp_effects.result b/mysql-test/r/rpl_sp_effects.result index 8bcbf1a60d0..bf8128d9385 100644 --- a/mysql-test/r/rpl_sp_effects.result +++ b/mysql-test/r/rpl_sp_effects.result @@ -156,3 +156,60 @@ slave: 6 drop procedure p1; drop function f1; drop table t1,t2; +create table t1 (a int); +create procedure p1() +begin +insert into t1 values(@x); +set @x=@x+1; +insert into t1 values(@x); +if (f2()) then +insert into t1 values(1243); +end if; +end// +create function f2() returns int +begin +insert into t1 values(@z); +set @z=@z+1; +insert into t1 values(@z); +return 0; +end// +create function f1() returns int +begin +insert into t1 values(@y); +call p1(); +return 0; +end// +set @x=10; +set @y=20; +set @z=100; +select f1(); +f1() +0 +set @x=30; +call p1(); +select 'master', a from t1; +master a +master 20 +master 10 +master 11 +master 100 +master 101 +master 30 +master 31 +master 101 +master 102 +select 'slave', a from t1; +slave a +slave 20 +slave 10 +slave 11 +slave 100 +slave 101 +slave 30 +slave 31 +slave 101 +slave 102 +drop table t1; +drop function f1; +drop function f2; +drop procedure p1; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 0d5c1aed485..24c89039566 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2897,3 +2897,18 @@ select * from t1 natural join t2 where a = 'b'; a b drop table t1, t2; +CREATE TABLE t1 (`id` TINYINT); +CREATE TABLE t2 (`id` TINYINT); +CREATE TABLE t3 (`id` TINYINT); +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (2); +INSERT INTO t3 VALUES (3); +SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); +ERROR 23000: Column 'id' in from clause is ambiguous +SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id); +ERROR 23000: Column 'id' in from clause is ambiguous +SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); +ERROR 23000: Column 'id' in from clause is ambiguous +SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id); +ERROR 23000: Column 'id' in from clause is ambiguous +drop table t1, t2, t3; diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 1a061529fb0..09d829e9d12 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -765,3 +765,24 @@ OPTIMIZE TABLE t1; RETURN 1; END| ERROR 0A000: OPTIMIZE TABLE is not allowed in stored procedures +DROP FUNCTION IF EXISTS bug12995| +CREATE FUNCTION bug12995() RETURNS INT +BEGIN +HANDLER t1 OPEN; +RETURN 1; +END| +ERROR 0A000: HANDLER is not allowed in stored procedures +CREATE FUNCTION bug12995() RETURNS INT +BEGIN +HANDLER t1 READ FIRST; +RETURN 1; +END| +ERROR 0A000: HANDLER is not allowed in stored procedures +CREATE FUNCTION bug12995() RETURNS INT +BEGIN +HANDLER t1 CLOSE; +RETURN 1; +END| +ERROR 0A000: HANDLER is not allowed in stored procedures +SELECT bug12995()| +ERROR 42000: FUNCTION test.bug12995 does not exist diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 4424f4e6ad4..cb696f93f79 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -3085,6 +3085,19 @@ column_name bug10055(t.column_name) id id data data drop function bug10055| +drop procedure if exists bug12297| +create procedure bug12297(lim int) +begin +set @x = 0; +repeat +insert into t1(id,data) +values('aa', @x); +set @x = @x + 1; +until @x >= lim +end repeat; +end| +call bug12297(10)| +drop procedure bug12297| drop function if exists f_bug11247| drop procedure if exists p_bug11247| create function f_bug11247(param int) @@ -3193,4 +3206,23 @@ set f1= concat( 'hello', f1 ); return f1; end| drop function bug9048| +drop procedure if exists bug12849_1| +create procedure bug12849_1(inout x char) select x into x| +set @var='a'| +call bug12849_1(@var)| +select @var| +@var +a +drop procedure bug12849_1| +drop procedure if exists bug12849_2| +create procedure bug12849_2(inout foo varchar(15)) +begin +select concat(foo, foo) INTO foo; +end| +set @var='abcd'| +call bug12849_2(@var)| +select @var| +@var +abcdabcd +drop procedure bug12849_2| drop table t1,t2; diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 4980e2a73d2..f06e290a49b 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -984,3 +984,35 @@ t1 CREATE TABLE `t1` ( `f1` decimal(10,0) unsigned zerofill NOT NULL default '0000000000' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; +drop procedure if exists wg2; +Warnings: +Note 1305 PROCEDURE wg2 does not exist +create procedure wg2() +begin +declare v int default 1; +declare tdec decimal(5) default 0; +while v <= 9 do set tdec =tdec * 10; +select v, tdec; +set v = v + 1; +end while; +end// +call wg2()// +v tdec +1 0 +v tdec +2 0 +v tdec +3 0 +v tdec +4 0 +v tdec +5 0 +v tdec +6 0 +v tdec +7 0 +v tdec +8 0 +v tdec +9 0 +drop procedure wg2; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index a5b76c03b29..265f353ae3c 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -545,3 +545,10 @@ select @@max_heap_table_size > 0; select @@have_innodb; @@have_innodb # +select @@character_set_system; +@@character_set_system +utf8 +set global character_set_system = latin1; +ERROR HY000: Variable 'character_set_system' is a read only variable +set @@global.version_compile_os='234'; +ERROR HY000: Variable 'version_compile_os' is a read only variable diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index e141393176c..558977a6d2d 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -2151,6 +2151,15 @@ select * from v1; strcmp(f1,'a') drop view v1; drop table t1; +create table t1 (f1 int, f2 int,f3 int); +insert into t1 values (1,10,20),(2,0,0); +create view v1 as select * from t1; +select if(sum(f1)>1,f2,f3) from v1 group by f1; +if(sum(f1)>1,f2,f3) +20 +0 +drop view v1; +drop table t1; create table t1 ( r_object_id char(16) NOT NULL, group_name varchar(32) NOT NULL diff --git a/mysql-test/t/connect.test b/mysql-test/t/connect.test index 64b170970ca..60ac7b88bbe 100644 --- a/mysql-test/t/connect.test +++ b/mysql-test/t/connect.test @@ -6,6 +6,10 @@ # This test makes no sense with the embedded server --source include/not_embedded.inc +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + #connect (con1,localhost,root,,""); #show tables; connect (con1,localhost,root,,mysql); @@ -77,4 +81,18 @@ show tables; delete from mysql.user where user=_binary"test"; flush privileges; +# +# Bug#12517: Clear user variables and replication events before +# closing temp tables in thread cleanup. +connect (con2,localhost,root,,test); +connection con2; +create table t1 (id integer not null auto_increment primary key); +create temporary table t2(id integer not null auto_increment primary key); +set @id := 1; +delete from t1 where id like @id; +disconnect con2; +--sleep 5 +connection default; +drop table t1; + # End of 4.1 tests diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index ce259f465d9..041451272d4 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -810,6 +810,12 @@ alter table t1 modify a char(2) character set utf8; select char_length(a), length(a), a from t1 order by a; drop table t1; +# +# Bugs#12611 +# ESCAPE + LIKE do not work when the escape char is a multibyte one +# +set names utf8; +select 'andre%' like 'andreñ%' escape 'ñ'; # # Bugs#11754: SET NAMES utf8 followed by SELECT "A\\" LIKE "A\\" returns 0 diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 0b61a445270..a519d51e0b5 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -282,6 +282,16 @@ drop table t1; drop table t2; # +# Bug #12829 +# Cannot convert the charset of a GROUP_CONCAT result +# +CREATE TABLE t1 (a CHAR(10) CHARACTER SET cp850); +INSERT INTO t1 VALUES ('À'); +SELECT a FROM t1; +SELECT GROUP_CONCAT(a) FROM t1; +DROP TABLE t1; + +# # bug #7769: group_concat returning null is checked in having # CREATE TABLE t1 (id int); @@ -363,4 +373,20 @@ select * from (select group_concat(a) from t1) t2; select group_concat('x') UNION ALL select 1; drop table t1; +# +# Bug #12863 : missing separators after first empty cancatanated elements +# + +CREATE TABLE t1 (id int, a varchar(9)); +INSERT INTO t1 VALUES + (2, ''), (1, ''), (2, 'x'), (1, 'y'), (3, 'z'), (3, ''); + +SELECT GROUP_CONCAT(a) FROM t1; +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1; + +SELECT GROUP_CONCAT(a) FROM t1 GROUP BY id; +SELECT GROUP_CONCAT(a ORDER BY a) FROM t1 GROUP BY id; + +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/func_like.test b/mysql-test/t/func_like.test index 684d7032038..4e1183afeff 100644 --- a/mysql-test/t/func_like.test +++ b/mysql-test/t/func_like.test @@ -96,4 +96,21 @@ DROP TABLE t1; # select _cp866'aaaaaaaaa' like _cp866'%aaaa%' collate cp866_bin; +# +# Check 8bit escape character +# +set names koi8r; +select 'andre%' like 'andreÊ%' escape 'Ê'; + +# Check 8bit escape character with charset conversion: +# For "a LIKE b ESCAPE c" expressions, +# escape character is converted into the operation character set, +# which is result of aggregation of character sets of "a" and "b". +# "c" itself doesn't take part in aggregation, because its collation +# doesn't matter, escape character is always compared binary. +# In the example below, escape character is converted from koi8r into cp1251: +# +select _cp1251'andre%' like convert('andreÊ%' using cp1251) escape 'Ê'; + +# # End of 4.1 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 8300b502518..bf557029a55 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -580,7 +580,6 @@ SELECT COUNT(DISTINCT(t1.id)), LEFT(err_comment, 256) AS comment DROP TABLE t1, t2; - # # Bug #12266 GROUP BY expression on DATE column produces result with # reduced length @@ -602,6 +601,16 @@ SELECT n+1 AS n FROM t1 GROUP BY n; --enable_ps_protocol DROP TABLE t1; +# +# BUG#12695: Item_func_isnull::update_used_tables +# did not update const_item_cache +# +create table t1(f1 varchar(5) key); +insert into t1 values (1),(2); +select sql_buffer_result max(f1) is null from t1; +select sql_buffer_result max(f1)+1 from t1; +drop table t1; + # End of 4.1 tests # diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 6be193e0e0c..aa1b632f919 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -665,4 +665,16 @@ SHOW TABLE STATUS FROM test WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE'); -DROP TABLE t1,t2 +DROP TABLE t1,t2; + +# +# Bug #12905 show fields from view behaving erratically with current database +# +create table t1(f1 int); +create view v1 (c) as select f1 from t1; +connect (con5,localhost,root,,*NO-ONE*); +select database(); +show fields from test.v1; +connection default; +drop view v1; +drop table t1; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index aabc32c009a..367b98f2485 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -135,7 +135,7 @@ INSERT INTO t1 VALUES (10363,'Tecniques de Comunicacio Oral i Escrita','Tecnicas INSERT INTO t1 VALUES (11403,'Projecte Fi de Carrera','Proyecto Fin de Carrera','Projecte Fi de Carrera','PFC',9.0,NULL,NULL,NULL); INSERT INTO t1 VALUES (11404,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',15.0,NULL,NULL,NULL); INSERT INTO t1 VALUES (11405,'+lgebra lineal','Algebra lineal','+lgebra lineal','+lgebra lineal',18.0,NULL,NULL,NULL); -INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL); +INSERT INTO t1 VALUES (11406,'Calcul Infinitesimal','Cßlculo Infinitesimal','Calcul Infinitesimal','Calcul Infinitesimal',15.0,NULL,NULL,NULL); CREATE TABLE t2 ( idAssignatura int(11) DEFAULT '0' NOT NULL, @@ -590,7 +590,6 @@ INSERT INTO t2 VALUES("0", "EN", "0-EN"); INSERT INTO t2 VALUES("0", "SV", "0-SV"); INSERT INTO t2 VALUES("10", "EN", "10-EN"); INSERT INTO t2 VALUES("10", "SV", "10-SV"); - SELECT t1.id, t1.text_id, t2.text_data FROM t1 LEFT JOIN t2 ON t1.text_id = t2.text_id @@ -713,3 +712,49 @@ INSERT INTO t1 VALUES (30), (40), (50); INSERT INTO t2 VALUES (300), (400), (500); SELECT * FROM t1 LEFT JOIN t2 ON (c11=c21 AND c21=30) WHERE c11=40; DROP TABLE t1, t2; +# +# Test for bugs +# #12101: erroneously applied outer join elimination in case of WHERE NOT BETWEEN +# #12102: erroneously missing outer join elimination in case of WHERE IN/IF +# + +CREATE TABLE t1 (a int PRIMARY KEY, b int); +CREATE TABLE t2 (a int PRIMARY KEY, b int); + +INSERT INTO t1 VALUES (1,2), (2,1), (3,2), (4,3), (5,6), (6,5), (7,8), (8,7), (9,10); +INSERT INTO t2 VALUES (3,0), (4,1), (6,4), (7,5); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b <= t1.a AND t1.a <= t1.b; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a BETWEEN t2.b AND t1.b; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT BETWEEN t2.b AND t1.b); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.b > t1.a OR t1.a > t1.b; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT BETWEEN t2.b AND t1.b; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a BETWEEN t2.b AND t1.b); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t2.b > t1.a OR t1.a > t1.b; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a AND t1.a BETWEEN t2.b AND t1.b); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a AND (t2.b > t1.a OR t1.a > t1.b); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a != t2.a OR t1.a BETWEEN t2.b AND t1.b); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a NOT IN(t2.a, t2.b)); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a != t1.b AND t1.a != t2.b; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a NOT IN(t1.b, t2.b); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t1.a IN(t1.b, t2.b)); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b OR (t1.a != t2.a AND t1.a != t2.b); +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b AND t1.a IN(t2.a, t2.b)); + +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t2.a != t2.b AND t1.a != t1.b AND t1.a != t2.b; +SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE NOT(t2.a = t2.b OR t1.a IN(t1.b, t2.b)); + +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a = t2.a OR t1.a = t2.b; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a IN(t2.a, t2.b); +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a WHERE t1.a > IF(t1.a = t2.b-2, t2.b, t2.b-1); + +DROP TABLE t1,t2; + diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index 2e09bc5b3a3..adaf6883d43 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -251,6 +251,32 @@ SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2 DROP TABLE t1,t2; # +# Test for bug #11543: ROLLUP query with a repeated column in GROUP BY +# + +CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 VALUES (1, 2); + +SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP; + +DROP TABLE t1; + +# Bug #12885(1): derived table specified by a subquery with +# ROLLUP over expressions on not nullable group by attributes +# + +CREATE TABLE t1 (a int(11) NOT NULL); +INSERT INTO t1 VALUES (1),(2); + +SELECT * FROM (SELECT a, a + 1, COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t; +SELECT * FROM (SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP) t; + +DROP TABLE t1; + +# End of 4.1 tests + +# # Tests for bug #11639: ROLLUP over view executed through filesort # @@ -266,15 +292,20 @@ EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP; DROP VIEW v1; DROP TABLE t1; -# Test for bug #11543: ROLLUP query with a repeated column in GROUP BY + +# +# Bug #12885(2): view specified by a subquery with +# ROLLUP over expressions on not nullable group by attributes # -CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL); -INSERT INTO t1 VALUES (1, 1); -INSERT INTO t1 VALUES (1, 2); +CREATE TABLE t1 (a int(11) NOT NULL); +INSERT INTO t1 VALUES (1),(2); -SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP; +CREATE VIEW v1 AS + SELECT a, LENGTH(a), COUNT(*) FROM t1 GROUP BY a WITH ROLLUP; -DROP TABLE t1; +DESC v1; +SELECT * FROM v1; -# End of 4.1 tests +DROP VIEW v1; +DROP TABLE t1; diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 94596fbbc0e..94ee2b1ca39 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -809,6 +809,21 @@ select ??; select ? from t1; --enable_ps_protocol drop table t1; + +# +# Bug#12651 +# (Crash on a PS including a subquery which is a select from a simple view) +# +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; + +DROP VIEW b12651_V1; +DROP TABLE b12651_T1, b12651_T2; + # # Bug#9359 "Prepared statements take snapshot of system vars at PREPARE # time" diff --git a/mysql-test/t/rpl_sp_effects.test b/mysql-test/t/rpl_sp_effects.test index f8e83eabe90..9da5723b993 100644 --- a/mysql-test/t/rpl_sp_effects.test +++ b/mysql-test/t/rpl_sp_effects.test @@ -152,4 +152,52 @@ drop procedure p1; drop function f1; drop table t1,t2; +# BUG#12637: User variables + SPs replication +create table t1 (a int); +delimiter //; +create procedure p1() +begin + insert into t1 values(@x); + set @x=@x+1; + insert into t1 values(@x); + if (f2()) then + insert into t1 values(1243); + end if; +end// + +create function f2() returns int +begin + insert into t1 values(@z); + set @z=@z+1; + insert into t1 values(@z); + return 0; +end// + +create function f1() returns int +begin + insert into t1 values(@y); + call p1(); + return 0; +end// + +delimiter ;// + +set @x=10; +set @y=20; +set @z=100; +select f1(); + +set @x=30; +call p1(); + +select 'master', a from t1; +sync_slave_with_master; +connection slave; +select 'slave', a from t1; + +connection master; +drop table t1; +drop function f1; +drop function f2; +drop procedure p1; sync_slave_with_master; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index fad01ac9acf..62687a869b7 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2465,3 +2465,25 @@ insert into t2 values ('b'),('c'),('d'); select a from t1 natural join t2; select * from t1 natural join t2 where a = 'b'; drop table t1, t2; + +# +# Bug #12977 Compare table names with qualifying field tables only +# for base tables, search all nested join operands of natural joins. +# + +CREATE TABLE t1 (`id` TINYINT); +CREATE TABLE t2 (`id` TINYINT); +CREATE TABLE t3 (`id` TINYINT); +INSERT INTO t1 VALUES (1),(2),(3); +INSERT INTO t2 VALUES (2); +INSERT INTO t3 VALUES (3); +-- error 1052 +SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); +-- error 1052 +SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id); +-- error 1052 +SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id); +-- error 1052 +SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id); + +drop table t1, t2, t3; diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test index abb927ab3b8..9f91c32c104 100644 --- a/mysql-test/t/sp-error.test +++ b/mysql-test/t/sp-error.test @@ -1099,6 +1099,36 @@ BEGIN OPTIMIZE TABLE t1; RETURN 1; END| +delimiter ;| + +# +# Bug##12995 "Inside function "Table 't4' was not locked with LOCK TABLES" +# +delimiter |; +--disable_warnings +DROP FUNCTION IF EXISTS bug12995| +--enable_warnings +--error ER_SP_BADSTATEMENT +CREATE FUNCTION bug12995() RETURNS INT +BEGIN + HANDLER t1 OPEN; + RETURN 1; +END| +--error ER_SP_BADSTATEMENT +CREATE FUNCTION bug12995() RETURNS INT +BEGIN + HANDLER t1 READ FIRST; + RETURN 1; +END| +--error ER_SP_BADSTATEMENT +CREATE FUNCTION bug12995() RETURNS INT +BEGIN + HANDLER t1 CLOSE; + RETURN 1; +END| +--error 1305 +SELECT bug12995()| +delimiter ;| # # BUG#NNNN: New bug synopsis diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index d52ebbbbf67..3d315fa12df 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -3877,29 +3877,23 @@ drop function bug10055| # consumption by passing large input parameter. # -# -# Note: the test is currenly disabled because of the -# Bug #12637: SP crashes the server if it has update query with user var -# & binlog is enabled. -# - --disable_warnings -#drop procedure if exists bug12297| +drop procedure if exists bug12297| --enable_warnings -#create procedure bug12297(lim int) -#begin -# set @x = 0; -# repeat -# insert into t1(id,data) -# values('aa', @x); -# set @x = @x + 1; -# until @x >= lim -# end repeat; -#end| +create procedure bug12297(lim int) +begin + set @x = 0; + repeat + insert into t1(id,data) + values('aa', @x); + set @x = @x + 1; + until @x >= lim + end repeat; +end| -#call bug12297(10)| -#drop procedure bug12297| +call bug12297(10)| +drop procedure bug12297| # # Bug #11247 "Stored procedures: Function calls in long loops leak memory" @@ -4044,6 +4038,32 @@ end| drop function bug9048| # +# Bug #12849 Stored Procedure: Crash on procedure call with CHAR type +# 'INOUT' parameter +# + +--disable_warnings +drop procedure if exists bug12849_1| +--enable_warnings +create procedure bug12849_1(inout x char) select x into x| +set @var='a'| +call bug12849_1(@var)| +select @var| +drop procedure bug12849_1| + +--disable_warnings +drop procedure if exists bug12849_2| +--enable_warnings +create procedure bug12849_2(inout foo varchar(15)) +begin +select concat(foo, foo) INTO foo; +end| +set @var='abcd'| +call bug12849_2(@var)| +select @var| +drop procedure bug12849_2| + +# # BUG#NNNN: New bug synopsis # #--disable_warnings diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test index f3be64506c7..55e0618a3e5 100644 --- a/mysql-test/t/type_newdecimal.test +++ b/mysql-test/t/type_newdecimal.test @@ -1015,3 +1015,25 @@ create table t1 ( f1 decimal (0,0) zerofill not null default 0); show create table t1; drop table t1; + +# +# Bug 12938 (arithmetic loop's zero) +# +--disable-warnings +drop procedure if exists wg2; +--enable-warnings +delimiter //; +create procedure wg2() +begin + declare v int default 1; + declare tdec decimal(5) default 0; + while v <= 9 do set tdec =tdec * 10; + select v, tdec; + set v = v + 1; + end while; +end// + +call wg2()// + +delimiter ;// +drop procedure wg2; diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 372e865467e..afd0fe23805 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -435,3 +435,12 @@ select @@max_heap_table_size > 0; --replace_column 1 # select @@have_innodb; + +# +# Bug #11775 Variable character_set_system does not exist (sometimes) +# +select @@character_set_system; +--error 1238 +set global character_set_system = latin1; +--error 1238 +set @@global.version_compile_os='234'; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index c5984f726f4..7cca98391a8 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2020,6 +2020,14 @@ drop view v1; drop table t1; # +# Bug #12922 if(sum(),...) with group from view returns wrong results +# +create table t1 (f1 int, f2 int,f3 int); +insert into t1 values (1,10,20),(2,0,0); +create view v1 as select * from t1; +select if(sum(f1)>1,f2,f3) from v1 group by f1; +drop view v1; +drop table t1; # BUG#12941 # create table t1 ( |