diff options
author | Igor Babaev <igor@askmonty.org> | 2012-03-17 01:26:58 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2012-03-17 01:26:58 -0700 |
commit | 5d954e7cd0f9f4106d848a7d7fc8fbce35668785 (patch) | |
tree | bd812fb49b473d759c5054fe89d12e4dc1e50a59 /mysql-test | |
parent | d110e0377dda1bb9f5a5c36745d3a6feb8e8a1d4 (diff) | |
parent | 5338a28912589f1169b66b880a489ec5636bcd83 (diff) | |
download | mariadb-git-5d954e7cd0f9f4106d848a7d7fc8fbce35668785.tar.gz |
Merge 5.3->5.5
Diffstat (limited to 'mysql-test')
-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 | ||||
-rw-r--r-- | mysql-test/std_data/t917689.ARZ | bin | 0 -> 8687 bytes | |||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_corrupt_bit.result | 1 | ||||
-rw-r--r-- | mysql-test/suite/maria/r/maria-ucs2.result | 39 | ||||
-rw-r--r-- | mysql-test/suite/maria/r/maria3.result | 31 | ||||
-rw-r--r-- | mysql-test/suite/maria/t/maria-ucs2.test | 51 | ||||
-rw-r--r-- | mysql-test/suite/maria/t/maria3.test | 23 | ||||
-rw-r--r-- | mysql-test/suite/vcol/r/vcol_misc.result | 13 | ||||
-rw-r--r-- | mysql-test/suite/vcol/t/vcol_misc.test | 14 | ||||
-rw-r--r-- | mysql-test/t/archive.test | 11 | ||||
-rw-r--r-- | mysql-test/t/derived_view.test | 35 | ||||
-rw-r--r-- | mysql-test/t/func_group.test | 33 | ||||
-rw-r--r-- | mysql-test/t/group_by.test | 52 | ||||
-rw-r--r-- | mysql-test/t/sp.test | 5 | ||||
-rw-r--r-- | mysql-test/t/subselect_sj_mat.test | 22 |
20 files changed, 450 insertions, 81 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; diff --git a/mysql-test/std_data/t917689.ARZ b/mysql-test/std_data/t917689.ARZ Binary files differnew file mode 100644 index 00000000000..4770ca0c257 --- /dev/null +++ b/mysql-test/std_data/t917689.ARZ diff --git a/mysql-test/suite/innodb/r/innodb_corrupt_bit.result b/mysql-test/suite/innodb/r/innodb_corrupt_bit.result index 7e8792bb5b4..13b1e24a9ed 100644 --- a/mysql-test/suite/innodb/r/innodb_corrupt_bit.result +++ b/mysql-test/suite/innodb/r/innodb_corrupt_bit.result @@ -46,6 +46,7 @@ ERROR HY000: Index corrupt_bit_test_ā is corrupted show warnings; Level Code Message Warning 179 InnoDB: Index "idxē" for table "test"."corrupt_bit_test_ā" is marked as corrupted +Warning 179 Got error 179 when reading table `test`.`corrupt_bit_test_ā` Error 1712 Index corrupt_bit_test_ā is corrupted insert into corrupt_bit_test_ā values (10001, "a", 20001, 20001); select * from corrupt_bit_test_ā use index(primary) where a = 10001; diff --git a/mysql-test/suite/maria/r/maria-ucs2.result b/mysql-test/suite/maria/r/maria-ucs2.result new file mode 100644 index 00000000000..e7258f21d4f --- /dev/null +++ b/mysql-test/suite/maria/r/maria-ucs2.result @@ -0,0 +1,39 @@ +select * from INFORMATION_SCHEMA.ENGINES where ENGINE="ARIA"; +ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS +Aria YES Crash-safe tables with MyISAM heritage NO NO NO +set global storage_engine=aria; +set session storage_engine=aria; +drop table if exists t1; +SET SQL_WARNINGS=1; +CREATE TABLE t1 ( a VARCHAR(800),KEY(a) ) +ENGINE=Aria DEFAULT CHARACTER SET latin1; +INSERT INTO t1 VALUES +(REPEAT('abc ',200)), (REPEAT('def ',200)), +(REPEAT('ghi ',200)), (REPEAT('jkl ',200)); +INSERT INTO t1 SELECT * FROM t1; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +ALTER TABLE t1 MODIFY a VARCHAR(800) CHARSET `ucs2`; +Warnings: +Warning 1071 Specified key was too long; max key length is 1000 bytes +Warning 1071 Specified key was too long; max key length is 1000 bytes +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +SHOW CREATE table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(800) CHARACTER SET ucs2 DEFAULT NULL, + KEY `a` (`a`(500)) +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(800),KEY(a)) ENGINE=Aria CHARACTER SET ucs2; +Warnings: +Warning 1071 Specified key was too long; max key length is 1000 bytes +INSERT INTO t1 VALUES (REPEAT('abc ',200)); +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +# End of 5.2 tests diff --git a/mysql-test/suite/maria/r/maria3.result b/mysql-test/suite/maria/r/maria3.result index 63f5af57396..37613875f38 100644 --- a/mysql-test/suite/maria/r/maria3.result +++ b/mysql-test/suite/maria/r/maria3.result @@ -392,6 +392,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +set global aria_page_checksum=0; drop table t1; set global aria_log_file_size=4294967296; Warnings: @@ -508,7 +509,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `n` int(11) NOT NULL, `c` char(1) DEFAULT NULL -) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 drop table t1; create table t1 (n int not null, c char(1)) engine=aria transactional=1; alter table t1 engine=myisam; @@ -520,7 +521,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `n` int(11) NOT NULL, `c` char(1) DEFAULT NULL -) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=1 +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 TRANSACTIONAL=1 drop table t1; create table t1 (n int not null, c char(1)) engine=myisam transactional=1; Warnings: @@ -531,7 +532,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `n` int(11) NOT NULL, `c` char(1) DEFAULT NULL -) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=1 +) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 TRANSACTIONAL=1 drop table t1; create table t1 (a int, key(a)) transactional=0; insert into t1 values (0),(1),(2),(3),(4); @@ -644,29 +645,7 @@ a b c d e f g h i j 1 A B C D 1 M H 2 Abcdefghi E F G 2 N H drop table t1,t2; -CREATE TABLE t1 ( a VARCHAR(800),KEY(a) ) -ENGINE=Aria DEFAULT CHARACTER SET latin1; -INSERT INTO t1 VALUES -(REPEAT('abc ',200)), (REPEAT('def ',200)), -(REPEAT('ghi ',200)), (REPEAT('jkl ',200)); -INSERT INTO t1 SELECT * FROM t1; -CHECK TABLE t1; -Table Op Msg_type Msg_text -test.t1 check status OK -ALTER TABLE t1 MODIFY a VARCHAR(800) CHARSET `ucs2`; -Warnings: -Warning 1071 Specified key was too long; max key length is 1000 bytes -Warning 1071 Specified key was too long; max key length is 1000 bytes -CHECK TABLE t1; -Table Op Msg_type Msg_text -test.t1 check status OK -SHOW CREATE table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` varchar(800) CHARACTER SET ucs2 DEFAULT NULL, - KEY `a` (`a`(500)) -) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 -DROP TABLE t1; +# End of 5.1 tests create table t1 (a int) engine=aria; lock table t1 write; drop table t1; diff --git a/mysql-test/suite/maria/t/maria-ucs2.test b/mysql-test/suite/maria/t/maria-ucs2.test new file mode 100644 index 00000000000..fed67d780e9 --- /dev/null +++ b/mysql-test/suite/maria/t/maria-ucs2.test @@ -0,0 +1,51 @@ +-- source include/have_maria.inc +-- source include/have_ucs2.inc + +select * from INFORMATION_SCHEMA.ENGINES where ENGINE="ARIA"; + +let $default_engine=`select @@global.storage_engine`; +set global storage_engine=aria; +set session storage_engine=aria; + +# Initialise +--disable_warnings +drop table if exists t1; +--enable_warnings +SET SQL_WARNINGS=1; + +# +# bug#905716: Assertion `page->size <= share->max_index_block_size' +# + +CREATE TABLE t1 ( a VARCHAR(800),KEY(a) ) + ENGINE=Aria DEFAULT CHARACTER SET latin1; +INSERT INTO t1 VALUES + (REPEAT('abc ',200)), (REPEAT('def ',200)), + (REPEAT('ghi ',200)), (REPEAT('jkl ',200)); +INSERT INTO t1 SELECT * FROM t1; +# check table is not needed to reproduce the problem, +# but shows that by this time the table appears to be okay. +CHECK TABLE t1; +ALTER TABLE t1 MODIFY a VARCHAR(800) CHARSET `ucs2`; +CHECK TABLE t1; +SHOW CREATE table t1; +DROP TABLE t1; + +# +# BUG#905782 Assertion `pageno < ((1ULL) << 40)' failed at ma_pagecache.c +# Issue was too long key +# + +CREATE TABLE t1 (a VARCHAR(800),KEY(a)) ENGINE=Aria CHARACTER SET ucs2; +INSERT INTO t1 VALUES (REPEAT('abc ',200)); +CHECK TABLE t1; +DROP TABLE t1; + +--echo # End of 5.2 tests + + +--disable_result_log +--disable_query_log +eval set global storage_engine=$default_engine; +--enable_result_log +--enable_query_log diff --git a/mysql-test/suite/maria/t/maria3.test b/mysql-test/suite/maria/t/maria3.test index 62073b10102..f1d95a15ba5 100644 --- a/mysql-test/suite/maria/t/maria3.test +++ b/mysql-test/suite/maria/t/maria3.test @@ -304,6 +304,7 @@ drop table t1; set global aria_page_checksum=1; create table t1 (a int); show create table t1; +set global aria_page_checksum=0; drop table t1; # @@ -553,27 +554,7 @@ INSERT INTO t2 VALUES (1,'M','','H'), SELECT * FROM t1, t2 WHERE a = g ORDER BY b; drop table t1,t2; -# End of 5.1 tests - -# -# bug#905716: Assertion `page->size <= share->max_index_block_size' -# - -CREATE TABLE t1 ( a VARCHAR(800),KEY(a) ) - ENGINE=Aria DEFAULT CHARACTER SET latin1; -INSERT INTO t1 VALUES - (REPEAT('abc ',200)), (REPEAT('def ',200)), - (REPEAT('ghi ',200)), (REPEAT('jkl ',200)); -INSERT INTO t1 SELECT * FROM t1; -# check table is not needed to reproduce the problem, -# but shows that by this time the table appears to be okay. -CHECK TABLE t1; -ALTER TABLE t1 MODIFY a VARCHAR(800) CHARSET `ucs2`; -CHECK TABLE t1; -SHOW CREATE table t1; -DROP TABLE t1; - -# End of 5.2 tests +--echo # End of 5.1 tests create table t1 (a int) engine=aria; lock table t1 write; diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result index 693ea0d9174..c27b36089c6 100644 --- a/mysql-test/suite/vcol/r/vcol_misc.result +++ b/mysql-test/suite/vcol/r/vcol_misc.result @@ -133,6 +133,19 @@ t1 CREATE TABLE `t1` ( `v` char(32) CHARACTER SET ucs2 AS (a) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (a int, b int as (a+1) VIRTUAL); +SELECT table_schema, table_name, column_name, column_type, extra +FROM information_schema.columns WHERE table_name = 't1'; +table_schema table_name column_name column_type extra +test t1 a int(11) +test t1 b int(11) +SELECT table_schema, table_name, column_name, column_type, extra +FROM information_schema.columns WHERE table_name = 't2'; +table_schema table_name column_name column_type extra +test t2 a int(11) +test t2 b int(11) VIRTUAL +DROP TABLE t1,t2; create table t1 (a int, b int); insert into t1 values (3, 30), (4, 20), (1, 20); create table t2 (c int, d int, v int as (d+1), index idx(c)); diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test index f87cb5fbec8..6f576f61513 100644 --- a/mysql-test/suite/vcol/t/vcol_misc.test +++ b/mysql-test/suite/vcol/t/vcol_misc.test @@ -142,6 +142,20 @@ SHOW CREATE TABLE t1; DROP TABLE t1; # +# Bug#930814: no info in information schema for tables with virtual columns +# + +CREATE TABLE t1 (a int, b int); +CREATE TABLE t2 (a int, b int as (a+1) VIRTUAL); + +SELECT table_schema, table_name, column_name, column_type, extra + FROM information_schema.columns WHERE table_name = 't1'; +SELECT table_schema, table_name, column_name, column_type, extra + FROM information_schema.columns WHERE table_name = 't2'; + +DROP TABLE t1,t2; + +# # SELECT that uses a virtual column and executed with BKA # diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index d979ba509d5..5dd85bf1aba 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1769,3 +1769,14 @@ CHECKSUM TABLE t1 EXTENDED; FLUSH TABLE t1; OPTIMIZE TABLE t1; DROP TABLE t1; + +--echo # +--echo # BUG#917689 Using wrong archive table causes crash +--echo # +create table t1 (a int, b char(50)) engine=archive; +--remove_file $MYSQLD_DATADIR/test/t1.ARZ +copy_file std_data/t917689.ARZ $MYSQLD_DATADIR/test/t1.ARZ; +--error 1194 +select * from t1; +show warnings; +drop table t1; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 9660dd1e5f5..d1ed2ff5ba6 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1345,7 +1345,40 @@ SELECT COUNT(*) > 0 DROP TABLE t1; -set SESSION optimizer_switch= @save_optimizer_switch; +SET SESSION optimizer_switch= @save_optimizer_switch; + +--echo # +--echo # LP BUG#953649: crash when estimating the cost of a look-up +--echo # into a derived table to be materialized +--echo # + +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; +SELECT * FROM t1, v WHERE a = b; + +SET SESSION optimizer_switch='derived_merge=on'; +SET SESSION optimizer_switch='derived_with_keys=on'; +EXPLAIN +SELECT * FROM t1, v WHERE a = b; +SELECT * FROM t1, v WHERE a = b; + +SET SESSION optimizer_switch= @save_optimizer_switch; + +DROP VIEW v; +DROP TABLE t1,t2; # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 7a098c44e5b..89c6c0fe534 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1198,6 +1198,39 @@ SELECT (SELECT MIN(t1.a) FROM t1,t2 WHERE t2.a = t3.b) FROM t3; DROP TABLE t1,t2,t3; --echo # +--echo # Bug #884175: MIN/MAX for short varchar = long const +--echo # + +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'; +SELECT MAX(f1) FROM t1 WHERE f1 = 'abc'; + +EXPLAIN +SELECT MAX(f2) FROM t1 WHERE f2 = 'abc'; +SELECT MAX(f2) FROM t1 WHERE f2 = 'abc'; + +EXPLAIN +SELECT MIN(f1) FROM t1 WHERE f1 >= 'abc'; +SELECT MIN(f1) FROM t1 WHERE f1 >= 'abc'; + +EXPLAIN +SELECT MIN(f2) FROM t1 WHERE f2 >= 'abc'; +SELECT MIN(f2) FROM t1 WHERE f2 >= 'abc'; + +EXPLAIN +SELECT MIN(f1) FROM t1 WHERE f1 BETWEEN 'abc' AND 'b' ; +SELECT MIN(f1) FROM t1 WHERE f1 BETWEEN 'abc' AND 'b' ; + +EXPLAIN +SELECT MIN(f2) FROM t1 WHERE f2 BETWEEN 'abc' AND 'b' ; +SELECT MIN(f2) FROM t1 WHERE f2 BETWEEN 'abc' AND 'b' ; + +DROP TABLE t1; + + --echo End of 5.2 tests --echo # diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 9e9df515bd2..db8bfce2320 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1324,27 +1324,23 @@ SET SQL_BIG_TABLES=0; --echo # End of 5.1 tests --echo # ---echo # Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00 +--echo # LP bug#694450 Wrong result with non-standard GROUP BY + ORDER BY --echo # -CREATE TABLE t1 (f1 int, f2 DATE); - -INSERT INTO t1 VALUES (1,'2004-04-19'), (1,'0000-00-00'), (1,'2004-04-18'), -(2,'2004-05-19'), (2,'0001-01-01'), (3,'2004-04-10'); - -SELECT MIN(f2),MAX(f2) FROM t1; -SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP BY 1; +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'); -DROP TABLE t1; +# This must return an error, but instead returns 1 row +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; -CREATE TABLE t1 ( f1 int, f2 time); -INSERT INTO t1 VALUES (1,'01:27:35'), (1,'06:11:01'), (2,'19:53:05'), -(2,'21:44:25'), (3,'10:55:12'), (3,'05:45:11'), (4,'00:25:00'); +# This returns several rows +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 ; +SET SESSION SQL_MODE=default; +drop table t1; -SELECT MIN(f2),MAX(f2) FROM t1; -SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP BY 1; +--echo # End of 5.2 tests -DROP TABLE t1; ---echo #End of test#49771 --echo # --echo # lp:872702: Crash in add_ref_to_table_cond() when grouping by a PK --echo # @@ -1362,3 +1358,27 @@ WHERE a = ( GROUP BY 1; DROP TABLE t1, t2; +--echo # End of 5.3 tests + +--echo # +--echo # Bug#49771: Incorrect MIN (date) when minimum value is 0000-00-00 +--echo # +CREATE TABLE t1 (f1 int, f2 DATE); + +INSERT INTO t1 VALUES (1,'2004-04-19'), (1,'0000-00-00'), (1,'2004-04-18'), +(2,'2004-05-19'), (2,'0001-01-01'), (3,'2004-04-10'); + +SELECT MIN(f2),MAX(f2) FROM t1; +SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP BY 1; + +DROP TABLE t1; + +CREATE TABLE t1 ( f1 int, f2 time); +INSERT INTO t1 VALUES (1,'01:27:35'), (1,'06:11:01'), (2,'19:53:05'), +(2,'21:44:25'), (3,'10:55:12'), (3,'05:45:11'), (4,'00:25:00'); + +SELECT MIN(f2),MAX(f2) FROM t1; +SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP BY 1; + +DROP TABLE t1; +--echo #End of test#49771 diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 09deb057ea0..cafa7f0a551 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -194,12 +194,15 @@ begin insert into test.t1 values (x, z); end| +let $start_value= `SELECT @@max_join_size`| call mixset("mixset", 19)| show variables like 'max_join_size'| select id,data,@z from t1| delete from t1| drop procedure mixset| - +--disable_query_log +eval SET @@max_join_size= $start_value| +--enable_query_log # Multiple CALL statements, one with OUT parameter. --disable_warnings diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index ba260b7d8c3..a077e9b5af5 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1642,6 +1642,28 @@ SET join_cache_level=@save_join_cache_level; DROP TABLE t1,t2; +--echo # +--echo # BUG#952297: Server crashes on 2nd execution of PS in Field::is_null with semijoin+materialization +--echo # +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; +EXECUTE ps; + +DROP VIEW v2; +DROP TABLE t1, t2; + --echo # This must be at the end: set optimizer_switch=@subselect_sj_mat_tmp; set join_cache_level=@save_join_cache_level; |