diff options
author | Sergey Petrunya <psergey@askmonty.org> | 2012-06-20 15:01:28 +0400 |
---|---|---|
committer | Sergey Petrunya <psergey@askmonty.org> | 2012-06-20 15:01:28 +0400 |
commit | 8c762965d389dcd9e74c743aaf2a89bd8c18545f (patch) | |
tree | a266816b7b2a7e5f2266fa46b7b8627aa5543773 /mysql-test | |
parent | 0b93b444b6c4de6b219fd3a4b3d5fa2e388dc211 (diff) | |
parent | 584d923c3292395a2e8288adcf4795992789f27c (diff) | |
download | mariadb-git-8c762965d389dcd9e74c743aaf2a89bd8c18545f.tar.gz |
Merge 5.3 -> 5.5
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/r/derived_view.result | 4 | ||||
-rw-r--r-- | mysql-test/r/func_in.result | 10 | ||||
-rw-r--r-- | mysql-test/r/func_test.result | 41 | ||||
-rw-r--r-- | mysql-test/r/mysqltest_256.result | 1 | ||||
-rw-r--r-- | mysql-test/r/negation_elimination.result | 4 | ||||
-rw-r--r-- | mysql-test/r/ps.result | 65 | ||||
-rw-r--r-- | mysql-test/r/subselect_mat_cost_bugs.result | 4 | ||||
-rw-r--r-- | mysql-test/r/user_var.result | 7 | ||||
-rw-r--r-- | mysql-test/r/view.result | 4 | ||||
-rw-r--r-- | mysql-test/t/func_in.test | 11 | ||||
-rw-r--r-- | mysql-test/t/func_test.test | 20 | ||||
-rw-r--r-- | mysql-test/t/mysqltest_256.test | 17 | ||||
-rw-r--r-- | mysql-test/t/ps.test | 75 | ||||
-rw-r--r-- | mysql-test/t/user_var.test | 11 |
14 files changed, 258 insertions, 16 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 86b95e94a81..ba01db4a66f 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1682,7 +1682,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and `test`.`t2`.`b`) +Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t2`.`b` <> 0)) SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b AND t.c = t1.a; @@ -1713,7 +1713,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`b` = `test`.`t2`.`a`)) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and `test`.`t2`.`b`) +Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`b` = `test`.`t2`.`a`)) where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t2`.`b` <> 0)) SELECT t.b, t.c, t1.a FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t WHERE t.b AND t.c = t1.a; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 0a87f57726d..800b9a976b4 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -789,3 +789,13 @@ DROP TABLE t1; # End of test BUG#13012483 # End of 5.1 tests +# +# LP bug#992380 Crash when creating PS for a query with +# subquery in WHERE (see also mysql bug#13012483) +# +CREATE TABLE t1 (a INT); +PREPARE s FROM "SELECT 1 FROM t1 WHERE 1 < ALL (SELECT @:= (1 IN (SELECT 1 FROM t1)) FROM t1)"; +EXECUTE s; +1 +DROP TABLE t1; +# End of 5.3 tests diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index 81b7994af97..2c1c416472f 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -279,3 +279,44 @@ NULL SELECT GREATEST(1.5E+2,1.3E+2,NULL) FROM DUAL; GREATEST(1.5E+2,1.3E+2,NULL) NULL +create table t1 (a int); +insert into t1 values (1), (100), (0), (NULL); +select not a from t1; +not a +0 +0 +1 +NULL +explain extended select not a from t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 select (`test`.`t1`.`a` = 0) AS `not a` from `test`.`t1` +select * from t1 where not a; +a +0 +explain extended select * from t1 where not a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 0) +select not (a+0) from t1; +not (a+0) +0 +0 +1 +NULL +explain extended select not (a+0) from t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 select (not((`test`.`t1`.`a` + 0))) AS `not (a+0)` from `test`.`t1` +select * from t1 where not (a+0); +a +0 +explain extended select * from t1 where not (a+0); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not((`test`.`t1`.`a` + 0))) +drop table t1; diff --git a/mysql-test/r/mysqltest_256.result b/mysql-test/r/mysqltest_256.result new file mode 100644 index 00000000000..043c7208382 --- /dev/null +++ b/mysql-test/r/mysqltest_256.result @@ -0,0 +1 @@ +# Done diff --git a/mysql-test/r/negation_elimination.result b/mysql-test/r/negation_elimination.result index 5b09b0fc511..aea6518b676 100644 --- a/mysql-test/r/negation_elimination.result +++ b/mysql-test/r/negation_elimination.result @@ -321,7 +321,7 @@ select * from t1 where not(NULL or a); a explain select * from t1 where not(NULL and a); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL a 5 NULL 21 Using where; Using index +1 SIMPLE t1 ref a a 5 const 1 Using index select * from t1 where not(NULL and a); a 0 @@ -502,5 +502,5 @@ explain extended select a, not(not(a)), not(a <= 2 and not(a)), not(a not like " id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 5 NULL 4 100.00 Using where; Using index Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like '1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where (`test`.`t1`.`a` <> 0) having (`test`.`t1`.`a` <> 0) +Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or (`test`.`t1`.`a` <> 0)) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like '1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where (`test`.`t1`.`a` <> 0) having (`test`.`t1`.`a` <> 0) drop table t1; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 82a944cc81b..7629775b892 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -255,15 +255,15 @@ deallocate prepare StMt1; deallocate prepare Stmt1; ERROR HY000: Unknown prepared statement handler (Stmt1) given to DEALLOCATE PREPARE set names utf8; -prepare `ü` from 'select 1234'; -execute `ü` ; +prepare `ü` from 'select 1234'; +execute `ü` ; 1234 1234 set names latin1; -execute `ü`; +execute `ü`; 1234 1234 -deallocate prepare `ü`; +deallocate prepare `ü`; set names default; create table t1 (a varchar(10)) charset=utf8; insert into t1 (a) values ('yahoo'); @@ -3055,6 +3055,62 @@ DEALLOCATE PREPARE stmt; DROP TABLE t1; End of 5.1 tests. +# +# LP bug#1001500 Crash on the second execution of the PS for +# a query with degenerated conjunctive condition +# (see also mysql bug#12582849) +# +CREATE TABLE t1 ( +pk INTEGER AUTO_INCREMENT, +col_int_nokey INTEGER, +col_int_key INTEGER, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +PRIMARY KEY (pk), +KEY (col_int_key), +KEY (col_varchar_key, col_int_key) +); +INSERT INTO t1 ( +col_int_key, col_int_nokey, +col_varchar_key, col_varchar_nokey +) VALUES +(4, 2, 'v', 'v'), +(62, 150, 'v', 'v'); +CREATE TABLE t2 ( +pk INTEGER AUTO_INCREMENT, +col_int_nokey INTEGER, +col_int_key INTEGER, +col_varchar_key VARCHAR(1), +col_varchar_nokey VARCHAR(1), +PRIMARY KEY (pk), +KEY (col_int_key), +KEY (col_varchar_key, col_int_key) +); +INSERT INTO t2 ( +col_int_key, col_int_nokey, +col_varchar_key, col_varchar_nokey +) VALUES +(8, NULL, 'x', 'x'), +(7, 8, 'd', 'd'); +PREPARE stmt FROM ' +SELECT + ( SELECT MAX( SQ1_alias2 .col_int_nokey ) AS SQ1_field1 + FROM ( t2 AS SQ1_alias1 RIGHT JOIN t1 AS SQ1_alias2 + ON ( SQ1_alias2.col_varchar_key = SQ1_alias1.col_varchar_nokey ) + ) + WHERE SQ1_alias2.pk < alias1.col_int_nokey OR alias1.pk + ) AS field1 +FROM ( t1 AS alias1 JOIN t2 AS alias2 ON alias2.pk ) +GROUP BY field1 +'; +EXECUTE stmt; +field1 +150 +EXECUTE stmt; +field1 +150 +DEALLOCATE PREPARE stmt; +DROP TABLE t1, t2; # # WL#4435: Support OUT-parameters in prepared statements. @@ -4044,3 +4100,4 @@ c1 c2 count(c3) 2012-03-01 01:00:00 3 1 2012-03-01 02:00:00 3 1 DEALLOCATE PREPARE s1; +# End of 5.3 tests diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index 635e868dd31..5a38fe7ca72 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -209,8 +209,8 @@ WHERE t1.f1 AND alias2.f10 ORDER BY field1 ; field1 Warnings: -Warning 1292 Truncated incorrect INTEGER value: 'f' -Warning 1292 Truncated incorrect INTEGER value: 'd' +Warning 1292 Truncated incorrect DOUBLE value: 'f' +Warning 1292 Truncated incorrect DOUBLE value: 'd' set optimizer_switch=@tmp_optimizer_switch; drop table t1,t2; # diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index accb88b7f15..e98dda46061 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -454,6 +454,13 @@ SELECT (@v:=a) <> (@v:=1) FROM t1; (@v:=a) <> (@v:=1) 1 DROP TABLE t1; +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (1), (2); +SELECT DISTINCT @a:=MIN(t1.a) FROM t1, t1 AS t2 +GROUP BY @b:=(SELECT COUNT(*) > t2.a); +@a:=MIN(t1.a) +1 +DROP TABLE t1; End of 5.1 tests DROP TABLE IF EXISTS t1; CREATE TABLE t1(f1 INT AUTO_INCREMENT, PRIMARY KEY(f1)); diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index ae4c61a1dc7..008f8f41ada 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4324,13 +4324,13 @@ MM ZZ ZZ Warnings: -Warning 1292 Truncated incorrect INTEGER value: 'VV' +Warning 1292 Truncated incorrect DOUBLE value: 'VV' EXPLAIN EXTENDED SELECT * FROM v1 WHERE a > 'JJ' OR a AND a = 'VV'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 range a a 13 NULL 4 100.00 Using where; Using index Warnings: -Warning 1292 Truncated incorrect INTEGER value: 'VV' +Warning 1292 Truncated incorrect DOUBLE value: 'VV' Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > 'JJ') DROP VIEW v1; DROP TABLE t1; diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 97bd628e2f9..e6d991e097d 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -577,3 +577,14 @@ DROP TABLE t1; --echo # --echo End of 5.1 tests +--echo # +--echo # LP bug#992380 Crash when creating PS for a query with +--echo # subquery in WHERE (see also mysql bug#13012483) +--echo # +CREATE TABLE t1 (a INT); +PREPARE s FROM "SELECT 1 FROM t1 WHERE 1 < ALL (SELECT @:= (1 IN (SELECT 1 FROM t1)) FROM t1)"; +EXECUTE s; + +DROP TABLE t1; + +--echo # End of 5.3 tests diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index f697e0b477a..6a99b975e81 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -160,3 +160,23 @@ SELECT LEAST(1.1,1.2,NULL,1.0) FROM DUAL; SELECT GREATEST(1.5E+2,1.3E+2,NULL) FROM DUAL; # End of 4.1 tests + +# +# test of replacing NOT <field> +# +create table t1 (a int); +insert into t1 values (1), (100), (0), (NULL); + +select not a from t1; +explain extended select not a from t1; + +select * from t1 where not a; +explain extended select * from t1 where not a; + +select not (a+0) from t1; +explain extended select not (a+0) from t1; + +select * from t1 where not (a+0); +explain extended select * from t1 where not (a+0); + +drop table t1; diff --git a/mysql-test/t/mysqltest_256.test b/mysql-test/t/mysqltest_256.test new file mode 100644 index 00000000000..fd9447cd2d8 --- /dev/null +++ b/mysql-test/t/mysqltest_256.test @@ -0,0 +1,17 @@ +# +# MDEV-256 lp:995501 - mysqltest attempts to parse Perl code inside a block +# with false condition, gets confused and throws wrong errors +# + +let $run = 0; +if ($run) +{ + --perl + foreach (1) + { + print "In perl\n"; + } + EOF + SELECT 1; +} +--echo # Done diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index eed45597855..d955b46469c 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -279,11 +279,11 @@ deallocate prepare Stmt1; # also check that statement names are in right charset. set names utf8; -prepare `ü` from 'select 1234'; -execute `ü` ; +prepare `ü` from 'select 1234'; +execute `ü` ; set names latin1; -execute `ü`; -deallocate prepare `ü`; +execute `ü`; +deallocate prepare `ü`; set names default; @@ -3124,6 +3124,71 @@ DROP TABLE t1; --echo --echo End of 5.1 tests. +--echo # +--echo # LP bug#1001500 Crash on the second execution of the PS for +--echo # a query with degenerated conjunctive condition +--echo # (see also mysql bug#12582849) +--echo # + +CREATE TABLE t1 ( + pk INTEGER AUTO_INCREMENT, + col_int_nokey INTEGER, + col_int_key INTEGER, + + col_varchar_key VARCHAR(1), + col_varchar_nokey VARCHAR(1), + + PRIMARY KEY (pk), + KEY (col_int_key), + KEY (col_varchar_key, col_int_key) +); + +INSERT INTO t1 ( + col_int_key, col_int_nokey, + col_varchar_key, col_varchar_nokey +) VALUES +(4, 2, 'v', 'v'), +(62, 150, 'v', 'v'); + +CREATE TABLE t2 ( + pk INTEGER AUTO_INCREMENT, + col_int_nokey INTEGER, + col_int_key INTEGER, + + col_varchar_key VARCHAR(1), + col_varchar_nokey VARCHAR(1), + + PRIMARY KEY (pk), + KEY (col_int_key), + KEY (col_varchar_key, col_int_key) +); + +INSERT INTO t2 ( + col_int_key, col_int_nokey, + col_varchar_key, col_varchar_nokey +) VALUES +(8, NULL, 'x', 'x'), +(7, 8, 'd', 'd'); + +PREPARE stmt FROM ' +SELECT + ( SELECT MAX( SQ1_alias2 .col_int_nokey ) AS SQ1_field1 + FROM ( t2 AS SQ1_alias1 RIGHT JOIN t1 AS SQ1_alias2 + ON ( SQ1_alias2.col_varchar_key = SQ1_alias1.col_varchar_nokey ) + ) + WHERE SQ1_alias2.pk < alias1.col_int_nokey OR alias1.pk + ) AS field1 +FROM ( t1 AS alias1 JOIN t2 AS alias2 ON alias2.pk ) +GROUP BY field1 +'; + +EXECUTE stmt; +EXECUTE stmt; + +DEALLOCATE PREPARE stmt; + +DROP TABLE t1, t2; + ########################################################################### --echo @@ -3596,3 +3661,5 @@ GROUP BY c1, c2; EXECUTE s1; DEALLOCATE PREPARE s1; + +--echo # End of 5.3 tests diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index fa23d118634..f509a1ad0f2 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -363,6 +363,17 @@ SELECT (@v:=a) <> (@v:=1) FROM t1; DROP TABLE t1; +# +# LP BUG#1001506 Crash on a query with GROUP BY and user variables +# MySQL Bug #11764372 57197: EVEN MORE USER VARIABLE CRASHING FUN +# + +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES (1), (2); +SELECT DISTINCT @a:=MIN(t1.a) FROM t1, t1 AS t2 +GROUP BY @b:=(SELECT COUNT(*) > t2.a); +DROP TABLE t1; + --echo End of 5.1 tests # |