diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/archive.result | 11 | ||||
| -rw-r--r-- | mysql-test/r/derived_view.result | 38 | ||||
| -rw-r--r-- | mysql-test/r/func_group.result | 47 | ||||
| -rw-r--r-- | mysql-test/r/group_by.result | 61 | ||||
| -rw-r--r-- | mysql-test/r/subselect_mat.result | 22 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 22 |
6 files changed, 185 insertions, 16 deletions
diff --git a/mysql-test/r/archive.result b/mysql-test/r/archive.result index 88d59555f5b..b4c4aab621d 100644 --- a/mysql-test/r/archive.result +++ b/mysql-test/r/archive.result @@ -12851,3 +12851,14 @@ OPTIMIZE TABLE t1; Table Op Msg_type Msg_text test.t1 optimize status OK DROP TABLE t1; +# +# BUG#917689 Using wrong archive table causes crash +# +create table t1 (a int, b char(50)) engine=archive; +select * from t1; +ERROR HY000: Table 't1' is marked as crashed and should be repaired +show warnings; +Level Code Message +Warning 127 Got error 127 when reading table `test`.`t1` +Error 1194 Table 't1' is marked as crashed and should be repaired +drop table t1; diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index c60ef213e8f..5a3d547d6b3 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -1970,6 +1970,42 @@ ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA); COUNT(*) > 0 1 DROP TABLE t1; -set SESSION optimizer_switch= @save_optimizer_switch; +SET SESSION optimizer_switch= @save_optimizer_switch; +# +# LP BUG#953649: crash when estimating the cost of a look-up +# into a derived table to be materialized +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (132); +CREATE TABLE t2 (b int, c varchar(256)); +INSERT INTO t2 VALUES (132,'test1'), (120,'text2'), (132,'text3'); +CREATE VIEW v AS +SELECT b, GROUP_CONCAT(c) AS gc FROM t2 GROUP BY b; +SET @save_optimizer_switch=@@optimizer_switch; +SET SESSION optimizer_switch='derived_merge=off'; +SET SESSION optimizer_switch='derived_with_keys=off'; +EXPLAIN +SELECT * FROM t1, v WHERE a = b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using where +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort +SELECT * FROM t1, v WHERE a = b; +a b gc +132 132 test1,text3 +SET SESSION optimizer_switch='derived_merge=on'; +SET SESSION optimizer_switch='derived_with_keys=on'; +EXPLAIN +SELECT * FROM t1, v WHERE a = b; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY <derived2> ref key0 key0 5 const 0 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using filesort +SELECT * FROM t1, v WHERE a = b; +a b gc +132 132 test1,text3 +SET SESSION optimizer_switch= @save_optimizer_switch; +DROP VIEW v; +DROP TABLE t1,t2; set optimizer_switch=@exit_optimizer_switch; set join_cache_level=@exit_join_cache_level; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 6ac8a8bbd5f..c38201f6d9f 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1887,6 +1887,53 @@ NULL NULL DROP TABLE t1,t2,t3; # +# Bug #884175: MIN/MAX for short varchar = long const +# +CREATE TABLE t1 (f1 varchar(1), f2 varchar(1), KEY (f2)); +INSERT INTO t1 VALUES ('b', 'b'), ('a','a'); +EXPLAIN +SELECT MAX(f1) FROM t1 WHERE f1 = 'abc'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT MAX(f1) FROM t1 WHERE f1 = 'abc'; +MAX(f1) +NULL +EXPLAIN +SELECT MAX(f2) FROM t1 WHERE f2 = 'abc'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref f2 f2 4 const 1 Using where; Using index +SELECT MAX(f2) FROM t1 WHERE f2 = 'abc'; +MAX(f2) +NULL +EXPLAIN +SELECT MIN(f1) FROM t1 WHERE f1 >= 'abc'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT MIN(f1) FROM t1 WHERE f1 >= 'abc'; +MIN(f1) +b +EXPLAIN +SELECT MIN(f2) FROM t1 WHERE f2 >= 'abc'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index f2 f2 4 NULL 2 Using where; Using index +SELECT MIN(f2) FROM t1 WHERE f2 >= 'abc'; +MIN(f2) +b +EXPLAIN +SELECT MIN(f1) FROM t1 WHERE f1 BETWEEN 'abc' AND 'b' ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT MIN(f1) FROM t1 WHERE f1 BETWEEN 'abc' AND 'b' ; +MIN(f1) +b +EXPLAIN +SELECT MIN(f2) FROM t1 WHERE f2 BETWEEN 'abc' AND 'b' ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index f2 f2 4 NULL 2 Using where; Using index +SELECT MIN(f2) FROM t1 WHERE f2 BETWEEN 'abc' AND 'b' ; +MIN(f2) +b +DROP TABLE t1; End of 5.2 tests # # BUG#46680 - Assertion failed in file item_subselect.cc, diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index b57bc21524e..cffed9529d6 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1948,6 +1948,52 @@ DROP TABLE t1; SET SQL_BIG_TABLES=0; # End of 5.1 tests # +# LP bug#694450 Wrong result with non-standard GROUP BY + ORDER BY +# +SET SESSION SQL_MODE='ONLY_FULL_GROUP_BY'; +CREATE TABLE t1 ( +f1 int(11), f2 int(11), f3 datetime, f4 varchar(1), PRIMARY KEY (f1)) ; +INSERT IGNORE INTO t1 VALUES ('1','9','2004-10-11 18:13','x'),('2','5','2004-03-07 14:02','g'),('3','1','2004-04-09 09:38','o'),('4','0','1900-01-01 00:00','g'),('5','1','2009-02-19 02:05','v'); +SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ORDER BY alias1.f2 , field2; +field1 field2 +2004-10-11 18:13:00 1 +2009-02-19 02:05:00 5 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DOUBLE value: 'g' +Warning 1292 Truncated incorrect DOUBLE value: 'o' +Warning 1292 Truncated incorrect DOUBLE value: 'g' +Warning 1292 Truncated incorrect DOUBLE value: 'v' +SELECT alias2.f3 AS field1 , alias2.f1 AS field2 FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f1 = alias1.f2 AND alias2.f1 != alias1.f4 GROUP BY field1 , field2 ; +field1 field2 +2004-10-11 18:13:00 1 +2009-02-19 02:05:00 5 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +Warning 1292 Truncated incorrect DOUBLE value: 'g' +Warning 1292 Truncated incorrect DOUBLE value: 'o' +Warning 1292 Truncated incorrect DOUBLE value: 'g' +Warning 1292 Truncated incorrect DOUBLE value: 'v' +SET SESSION SQL_MODE=default; +drop table t1; +# End of 5.2 tests +# +# lp:872702: Crash in add_ref_to_table_cond() when grouping by a PK +# +CREATE TABLE t1 (a int, PRIMARY KEY (a)) ; +INSERT INTO t1 VALUES (14),(15),(16),(17),(18),(19),(20); +CREATE TABLE t2 (a int) ; +SELECT a +FROM t1 +WHERE a = ( +SELECT t2.a +FROM t2 +) OR t1.a = 73 +GROUP BY 1; +a +DROP TABLE t1, t2; +# End of 5.3 tests +# # Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00 # CREATE TABLE t1 (f1 int, f2 DATE); @@ -1976,18 +2022,3 @@ f1 MIN(f2) MAX(f2) 4 00:25:00 00:25:00 DROP TABLE t1; #End of test#49771 -# -# lp:872702: Crash in add_ref_to_table_cond() when grouping by a PK -# -CREATE TABLE t1 (a int, PRIMARY KEY (a)) ; -INSERT INTO t1 VALUES (14),(15),(16),(17),(18),(19),(20); -CREATE TABLE t2 (a int) ; -SELECT a -FROM t1 -WHERE a = ( -SELECT t2.a -FROM t2 -) OR t1.a = 73 -GROUP BY 1; -a -DROP TABLE t1, t2; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index b5e511e1f39..86442df1e8b 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1964,6 +1964,28 @@ a c SET optimizer_switch=@save_optimizer_switch; SET join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; +# +# BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization +# +CREATE TABLE t1 ( a VARCHAR(1) ); +INSERT INTO t1 VALUES ('y'),('z'); +CREATE TABLE t2 ( b VARCHAR(1), c VARCHAR(1) ); +INSERT INTO t2 VALUES ('v','v'),('v','v'); +CREATE VIEW v2 AS SELECT * FROM t2; +PREPARE ps FROM ' +SELECT a FROM t1, v2 +WHERE ( c, b ) IN ( SELECT b, b FROM t2 ) +GROUP BY a '; +EXECUTE ps; +a +y +z +EXECUTE ps; +a +y +z +DROP VIEW v2; +DROP TABLE t1, t2; # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index f06fe52393d..5729e86ce70 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2004,6 +2004,28 @@ a c SET optimizer_switch=@save_optimizer_switch; SET join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; +# +# BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization +# +CREATE TABLE t1 ( a VARCHAR(1) ); +INSERT INTO t1 VALUES ('y'),('z'); +CREATE TABLE t2 ( b VARCHAR(1), c VARCHAR(1) ); +INSERT INTO t2 VALUES ('v','v'),('v','v'); +CREATE VIEW v2 AS SELECT * FROM t2; +PREPARE ps FROM ' +SELECT a FROM t1, v2 +WHERE ( c, b ) IN ( SELECT b, b FROM t2 ) +GROUP BY a '; +EXECUTE ps; +a +y +z +EXECUTE ps; +a +y +z +DROP VIEW v2; +DROP TABLE t1, t2; # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; |