diff options
Diffstat (limited to 'mysql-test')
67 files changed, 1478 insertions, 45 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_row_basic.test b/mysql-test/extra/rpl_tests/rpl_row_basic.test index 7812ff6339f..c7570de3aba 100644 --- a/mysql-test/extra/rpl_tests/rpl_row_basic.test +++ b/mysql-test/extra/rpl_tests/rpl_row_basic.test @@ -2,17 +2,19 @@ # Basic tests of row-level logging # ---disable_query_log ---disable_result_log -# Add suppression for expected warning(s) in error log -call mtr.add_suppression("Can't find record in 't.'"); ---enable_query_log ---enable_result_log - # # First we test tables with only an index. # +#BUG#12662190 - COM_COMMIT IS NOT INCREMENTED FROM THE BINARY LOGS ON SLAVE, COM_BEGIN IS +#Testing command counters -BEFORE +#Storing the before counts of Slave +connection slave; +let $slave_com_commit_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_commit', Value, 1); +let $slave_com_insert_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_insert', Value, 1); +let $slave_com_delete_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_delete', Value, 1); +let $slave_com_update_before= query_get_value(SHOW GLOBAL STATUS LIKE 'com_update', Value, 1); + connection master; eval CREATE TABLE t1 (C1 CHAR(1), C2 CHAR(1), INDEX (C1)$extra_index_t1) ENGINE = $type ; SELECT * FROM t1; @@ -47,6 +49,32 @@ SELECT * FROM t1 ORDER BY C1,C2; sync_slave_with_master; SELECT * FROM t1 ORDER BY C1,C2; +#BUG#12662190 - COM_COMMIT IS NOT INCREMENTED FROM THE BINARY LOGS ON SLAVE, COM_BEGIN IS +#Testing command counters -AFTER +#Storing the after counts of Slave +connection slave; +let $slave_com_commit_after= query_get_value(SHOW GLOBAL STATUS LIKE 'com_commit', Value, 1); +let $slave_com_insert_after= query_get_value(SHOW GLOBAL STATUS LIKE 'com_insert', Value, 1); +let $slave_com_delete_after= query_get_value(SHOW GLOBAL STATUS LIKE 'com_delete', Value, 1); +let $slave_com_update_after= query_get_value(SHOW GLOBAL STATUS LIKE 'com_update', Value, 1); + +#Commit count check +--let $assert_text= Counter for COM_COMMIT is consistent with the number of actual commits +--let $assert_cond= $slave_com_commit_after - $slave_com_commit_before = 4 +--source include/assert.inc +#Insert count check +--let $assert_text= Counter for COM_INSERT is consistent with the number of actual inserts +--let $assert_cond= $slave_com_insert_after - $slave_com_insert_before = 2 +--source include/assert.inc +#Delete count check +--let $assert_text= Counter for COM_DELETE is consistent with the number of actual deletes +--let $assert_cond= $slave_com_delete_after - $slave_com_delete_before = 1 +--source include/assert.inc +#Update count check +--let $assert_text= Counter for COM_UPDATE is consistent with the number of actual updates +--let $assert_cond= $slave_com_update_after - $slave_com_update_before = 1 +--source include/assert.inc + # Testing update with a condition that does not match any rows, but # which has a match for the index. connection master; diff --git a/mysql-test/r/blackhole.result b/mysql-test/r/blackhole.result new file mode 100644 index 00000000000..317070ca08e --- /dev/null +++ b/mysql-test/r/blackhole.result @@ -0,0 +1,11 @@ +# +# Bug #11880012: INDEX_SUBQUERY, BLACKHOLE, +# HANG IN PREPARING WITH 100% CPU USAGE +# +CREATE TABLE t1(a INT NOT NULL); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (a INT UNSIGNED, b INT, UNIQUE KEY (a, b)) ENGINE=BLACKHOLE; +SELECT 1 FROM t1 WHERE a = ANY (SELECT a FROM t2); +1 +DROP TABLE t1, t2; +End of 5.5 tests diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index a1eba2701f3..23417b16698 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -728,6 +728,15 @@ length(CONVERT(repeat('a',2048), CHAR(2049))) Warnings: Warning 1301 Result of cast_as_char() was larger than max_allowed_packet (2048) - truncated SET @@GLOBAL.max_allowed_packet=default; +# +# Bug#13519724 63793: CRASH IN DTCOLLATION::SET(DTCOLLATION &SET) +# +CREATE TABLE t1 (a VARCHAR(50)); +SELECT a FROM t1 +WHERE CAST(a as BINARY)=x'62736D697468' +AND CAST(a AS BINARY)=x'65736D697468'; +a +DROP TABLE t1; End of 5.1 tests select cast("2101-00-01 02:03:04" as datetime); cast("2101-00-01 02:03:04" as datetime) diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 176d39c50a9..86b95e94a81 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2097,6 +2097,24 @@ a b drop table t1; set optimizer_switch=@save978847_optimizer_switch; # +# LP bug998516 Server hangs on INSERT .. SELECT with derived_merge, +# FROM subquery, UNION +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +INSERT INTO t1 SELECT * FROM ( SELECT * FROM t1 ) AS alias UNION SELECT * FROM t2; +select * from t1; +a +1 +2 +1 +2 +3 +4 +drop table t1,t2; +# # end of 5.3 tests # set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/r/errors.result b/mysql-test/r/errors.result index 24ace7eb849..8560e6176c4 100644 --- a/mysql-test/r/errors.result +++ b/mysql-test/r/errors.result @@ -146,3 +146,17 @@ ERROR 22003: BIGINT value is out of range in '(-(73) * -(2465717823867977728))' # # End Bug#57882 # +CREATE TABLE t1 (a INT); +CREATE TABLE t2(a INT PRIMARY KEY, b INT); +SELECT '' AS b FROM t1 GROUP BY VALUES(b); +ERROR 42S22: Unknown column '' in 'VALUES() function' +REPLACE t2(b) SELECT '' AS b FROM t1 GROUP BY VALUES(b); +ERROR 42S22: Unknown column '' in 'VALUES() function' +UPDATE t2 SET a=(SELECT '' AS b FROM t1 GROUP BY VALUES(b)); +ERROR 42S22: Unknown column '' in 'VALUES() function' +INSERT INTO t2 VALUES (1,0) ON DUPLICATE KEY UPDATE +b=(SELECT '' AS b FROM t1 GROUP BY VALUES(b)); +ERROR 42S22: Unknown column '' in 'VALUES() function' +INSERT INTO t2(a,b) VALUES (1,0) ON DUPLICATE KEY UPDATE +b=(SELECT VALUES(a)+2 FROM t1); +DROP TABLE t1, t2; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 0f50f913ab3..c067ff02574 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -692,8 +692,10 @@ PREPARE stmt FROM WHERE t1.f1 GROUP BY t1.f1))'; EXECUTE stmt; 1 +1 EXECUTE stmt; 1 +1 DEALLOCATE PREPARE stmt; DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/func_group_innodb.result b/mysql-test/r/func_group_innodb.result index 71f1c6faef0..00baa5f990a 100644 --- a/mysql-test/r/func_group_innodb.result +++ b/mysql-test/r/func_group_innodb.result @@ -146,6 +146,19 @@ count(*) min(7) max(7) 0 NULL NULL drop table t1m, t1i, t2m, t2i; # +# Bug#13723054 CRASH WITH MIN/MAX AFTER QUICK_GROUP_MIN_MAX_SELECT::NEXT_MIN +# +CREATE TABLE t1(a BLOB, b VARCHAR(255) CHARSET LATIN1, c INT, +KEY(b, c, a(765))) ENGINE=INNODB; +INSERT INTO t1(a, b, c) VALUES ('', 'a', 0), ('', 'a', null), ('', 'a', 0); +SELECT MIN(c) FROM t1 GROUP BY b; +MIN(c) +0 +EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL b 263 NULL 4 Using index for group-by +DROP TABLE t1; +# # Bug #57954: BIT_AND function returns incorrect results when # semijoin=on CREATE TABLE c ( diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 3d5ca1805b7..c7f548ae2bc 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -217,3 +217,20 @@ foo Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'a' Warning 1292 Truncated incorrect DOUBLE value: 'a' +# +# LP bug#998321 Simple query with IF expression causes an +# assertion abort (see also mysql Bug#12620084) +# +SELECT if(0, (SELECT min('hello')), NULL); +if(0, (SELECT min('hello')), NULL) +NULL +SELECT if(1, (SELECT min('hello')), NULL); +if(1, (SELECT min('hello')), NULL) +hello +SELECT if(0, NULL, (SELECT min('hello'))); +if(0, NULL, (SELECT min('hello'))) +hello +SELECT if(1, NULL, (SELECT min('hello'))); +if(1, NULL, (SELECT min('hello'))) +NULL +End of 5.2 tests diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 83db8bf34af..ca48177b1b5 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -1430,6 +1430,19 @@ count(*) DROP DATABASE gis_ogs; USE test; # +# BUG#12414917 - ISCLOSED() CRASHES ON 64-BIT BUILDS +# +SELECT ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20))); +ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20))) +NULL +# +# BUG#12537203 - CRASH WHEN SUBSELECTING GLOBAL VARIABLES IN +# GEOMETRY FUNCTION ARGUMENTS +# +SELECT GEOMETRYCOLLECTION((SELECT @@OLD)); +ERROR 22007: Illegal non geometric '' value found during parsing +End of 5.1 tests +# # Bug#11908153: CRASH AND/OR VALGRIND ERRORS IN FIELD_BLOB::GET_KEY_IMAGE # CREATE TABLE g1 diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 74e9cd3caaa..222977e5106 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -2021,8 +2021,88 @@ zzz 3 4 8 -#TODO: in merge with 5.3 add original test suite +SET @save_optimizer_switch967242=@@optimizer_switch; +SET optimizer_switch = 'in_to_exists=on'; +SELECT t2_1.b +FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2 +ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c ) +WHERE +( SELECT COUNT(*) FROM t2 ) IS NOT NULL +OR a = t2_1.c +GROUP BY t2_1.b; +b +0 +3 +4 +8 +SET optimizer_switch=@save_optimizer_switch967242; drop table t1, t2; +# +# Bug#12578908: SELECT SQL_BUFFER_RESULT OUTPUTS TOO MANY +# ROWS WHEN GROUP IS OPTIMIZED AWAY +# +CREATE TABLE t1 (col1 int, col2 int) ; +INSERT INTO t1 VALUES (10,1),(11,7); +CREATE TABLE t2 (col1 int, col2 int) ; +INSERT INTO t2 VALUES (10,8); + +EXPLAIN SELECT SQL_BUFFER_RESULT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 Using temporary +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT SQL_BUFFER_RESULT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; +col2 +8 + +EXPLAIN SELECT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; +col2 +8 + +DROP TABLE t1,t2; +# +# BUG#12640437: USING SQL_BUFFER_RESULT RESULTS IN A +# DIFFERENT QUERY OUTPUT +# +CREATE TABLE t1 ( +a int, +b varchar(1), +KEY (b,a) +); +INSERT INTO t1 VALUES (1,NULL),(0,'a'); + +EXPLAIN SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range b b 9 NULL 2 Using where; Using index for group-by; Using temporary + +SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; +MIN(a) b +0 a + +EXPLAIN SELECT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range b b 9 NULL 2 Using where; Using index for group-by + +SELECT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; +MIN(a) b +0 a + +DROP TABLE t1; +# +# LP bug#993726 Wrong result from a query with ALL subquery predicate in WHERE +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0); +SELECT 1 FROM t1 WHERE 1 > ALL(SELECT 1 FROM t1 WHERE a!=0); +1 +1 +SELECT max(1) FROM t1 WHERE a!=0; +max(1) +NULL +drop table t1; # End of 5.2 tests # # lp:872702: Crash in add_ref_to_table_cond() when grouping by a PK diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index ebe8255fd8b..e5c788b0cc8 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1855,6 +1855,30 @@ f1 f1 f2 DROP TABLE t1,t2; End of 5.1 tests # +# LP BUG#994392: Wrong result with RIGHT/LEFT JOIN and ALL subquery +# predicate in WHERE condition. +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(9); +CREATE TABLE t2(b INT); +INSERT INTO t2 VALUES(8); +CREATE TABLE t3(c INT); +INSERT INTO t3 VALUES(3); +SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); +b c +NULL 3 +SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); +c b +3 NULL +SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); +b c +NULL 3 +SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); +c b +3 NULL +drop table t1,t2,t3; +End of 5.2 tests +# # LP bug #813447: LEFT JOIN with single-row inner table and # a subquery in ON expression # diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 3d0e4cbfc79..d93b16b5c4c 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1866,6 +1866,30 @@ f1 f1 f2 DROP TABLE t1,t2; End of 5.1 tests # +# LP BUG#994392: Wrong result with RIGHT/LEFT JOIN and ALL subquery +# predicate in WHERE condition. +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(9); +CREATE TABLE t2(b INT); +INSERT INTO t2 VALUES(8); +CREATE TABLE t3(c INT); +INSERT INTO t3 VALUES(3); +SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); +b c +NULL 3 +SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); +c b +3 NULL +SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); +b c +NULL 3 +SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); +c b +3 NULL +drop table t1,t2,t3; +End of 5.2 tests +# # LP bug #813447: LEFT JOIN with single-row inner table and # a subquery in ON expression # diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result index f438a3884eb..1a6f8534cc2 100644 --- a/mysql-test/r/log_state.result +++ b/mysql-test/r/log_state.result @@ -320,22 +320,22 @@ TRUNCATE TABLE mysql.slow_log; CREATE TABLE t1 (a INT); CREATE TABLE t2 (b INT, PRIMARY KEY (b)); INSERT INTO t2 VALUES (3),(4); -INSERT INTO t1 VALUES (1+sleep(.01)),(2); -INSERT INTO t1 SELECT b+sleep(.01) from t2; -UPDATE t1 SET a=a+sleep(.01) WHERE a>2; -UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC; -UPDATE t2 set b=b+sleep(.01) limit 1; -UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2); -DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2; +INSERT INTO t1 VALUES (1+sleep(.02)),(2); +INSERT INTO t1 SELECT b+sleep(.02) from t2; +UPDATE t1 SET a=a+sleep(.02) WHERE a>2; +UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC; +UPDATE t2 set b=b+sleep(.02) limit 1; +UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2); +DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2; SELECT rows_examined,sql_text FROM mysql.slow_log; rows_examined sql_text -0 INSERT INTO t1 VALUES (1+sleep(.01)),(2) -2 INSERT INTO t1 SELECT b+sleep(.01) from t2 -4 UPDATE t1 SET a=a+sleep(.01) WHERE a>2 -8 UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC -1 UPDATE t2 set b=b+sleep(.01) limit 1 -4 UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2) -6 DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2 +0 INSERT INTO t1 VALUES (1+sleep(.02)),(2) +2 INSERT INTO t1 SELECT b+sleep(.02) from t2 +4 UPDATE t1 SET a=a+sleep(.02) WHERE a>2 +8 UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC +1 UPDATE t2 set b=b+sleep(.02) limit 1 +4 UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2) +6 DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2 DROP TABLE t1,t2; TRUNCATE TABLE mysql.slow_log; # end of bug#49756 diff --git a/mysql-test/r/mysqlslap.result b/mysql-test/r/mysqlslap.result index 9defe1d6093..069c9182de2 100644 --- a/mysql-test/r/mysqlslap.result +++ b/mysql-test/r/mysqlslap.result @@ -241,3 +241,13 @@ mysql performance_schema test DROP DATABASE bug58090; +# +# Bug #11766072 - 59107: MYSQLSLAP CRASHES IF STARTED WITH NO ARGUMENTS ON WINDOWS +# +Benchmark + Average number of seconds to run all queries: TIME seconds + Minimum number of seconds to run all queries: TIME seconds + Maximum number of seconds to run all queries: TIME seconds + Number of clients running queries: 1 + Average number of queries per client: 0 + diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index ce0926c6bc1..27131438d16 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -3940,3 +3940,107 @@ Handler_read_rnd_deleted 0 Handler_read_rnd_next 0 deallocate prepare st; drop table t1; +# +# LP bug#993459 Execution of PS for a query with GROUP BY +# returns wrong result (see also mysql bug#13805127) +# +PREPARE s1 FROM +" +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2 +"; + +SET @x = 1; +SELECT c1, t2.c2, count(c3) +FROM +( +SELECT 3 as c2 FROM dual WHERE @x = 1 +UNION +SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 +) AS t1, +( +SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual +UNION +SELECT '2012-03-01 02:00:00', 3, 2 FROM dual +UNION +SELECT '2012-03-01 01:99345900:00', 2, 1 FROM dual +) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +c1 c2 count(c3) +2012-03-01 01:00:00 3 1 +2012-03-01 01:99345900:00 2 1 +2012-03-01 02:00:00 3 1 + +EXECUTE s1; +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 + +SET @x = 2; +SELECT c1, t2.c2, count(c3) +FROM +( +SELECT 3 as c2 FROM dual WHERE @x = 1 +UNION +SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 +) AS t1, +( +SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual +UNION +SELECT '2012-03-01 02:00:00', 3, 2 FROM dual +UNION +SELECT '2012-03-01 01:00:00', 2, 1 FROM dual +) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 + +EXECUTE s1; +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 + +SET @x = 1; +SELECT c1, t2.c2, count(c3) +FROM +( +SELECT 3 as c2 FROM dual WHERE @x = 1 +UNION +SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 +) AS t1, +( +SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual +UNION +SELECT '2012-03-01 02:00:00', 3, 2 FROM dual +UNION +SELECT '2012-03-01 01:00:00', 2, 1 FROM dual +) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 + +EXECUTE s1; +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 +DEALLOCATE PREPARE s1; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 3533001ccec..30d5ca00bc0 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -5291,3 +5291,15 @@ DROP TABLE t1; DROP VIEW view_t1; # End of test BUG#63020 SET optimizer_switch=@save_optimizer_switch; +# +# LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed +# in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK +# +CREATE TABLE t1 ( a INT(6) ZEROFILL ); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 ( b INT PRIMARY KEY ); +INSERT INTO t2 VALUES (3),(4); +SELECT * FROM t1, t2 WHERE a=3 AND a=b; +a b +drop table t1,t2; +End of 5.3 tests diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index a5416eb6718..a9c74afdd9c 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -5302,6 +5302,18 @@ DROP TABLE t1; DROP VIEW view_t1; # End of test BUG#63020 SET optimizer_switch=@save_optimizer_switch; +# +# LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed +# in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK +# +CREATE TABLE t1 ( a INT(6) ZEROFILL ); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 ( b INT PRIMARY KEY ); +INSERT INTO t2 VALUES (3),(4); +SELECT * FROM t1, t2 WHERE a=3 AND a=b; +a b +drop table t1,t2; +End of 5.3 tests set join_cache_level=default; show variables like 'join_cache_level'; Variable_name Value diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index 3533001ccec..30d5ca00bc0 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -5291,3 +5291,15 @@ DROP TABLE t1; DROP VIEW view_t1; # End of test BUG#63020 SET optimizer_switch=@save_optimizer_switch; +# +# LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed +# in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK +# +CREATE TABLE t1 ( a INT(6) ZEROFILL ); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 ( b INT PRIMARY KEY ); +INSERT INTO t2 VALUES (3),(4); +SELECT * FROM t1, t2 WHERE a=3 AND a=b; +a b +drop table t1,t2; +End of 5.3 tests diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index d52318c6c5b..05f9000bb33 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -7807,3 +7807,45 @@ Warnings: Error 1424 Recursive stored functions and triggers are not allowed. Error 1305 FUNCTION test.f1 does not exist DROP FUNCTION f1; +# ------------------------------------------------------------------ +# -- End of 5.1 tests +# ------------------------------------------------------------------ +# +# LP bug#993459 Execution of PS for a query with GROUP BY +# returns wrong result (see also mysql bug#13805127) +# +CREATE PROCEDURE p1(x INT UNSIGNED) +BEGIN +SELECT c1, t2.c2, count(c3) +FROM +( +SELECT 3 as c2 FROM dual WHERE x = 1 +UNION +SELECT 2 FROM dual WHERE x = 1 OR x = 2 +) AS t1, +( +SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual +UNION +SELECT '2012-03-01 02:00:00', 3, 2 FROM dual +UNION +SELECT '2012-03-01 01:00:00', 2, 1 FROM dual +) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2 +; +END| + +CALL p1(1); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 +CALL p1(2); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +CALL p1(1); +c1 c2 count(c3) +2012-03-01 01:00:00 2 1 +2012-03-01 01:00:00 3 1 +2012-03-01 02:00:00 3 1 +DROP PROCEDURE p1; diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index dca872f0bbd..2d72f919e47 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -5597,6 +5597,15 @@ SELECT 1 FROM ERROR 21000: Operand should contain 1 column(s) DROP TABLE t1; # +# Bug#13721076 CRASH WITH TIME TYPE/TIMESTAMP() AND WARNINGS IN SUBQUERY +# +CREATE TABLE t1(a TIME NOT NULL); +INSERT INTO t1 VALUES ('00:00:32'); +SELECT 1 FROM t1 WHERE a > +(SELECT timestamp(a) AS a FROM t1); +1 +DROP TABLE t1; +# # No BUG#, a case brought from 5.2's innodb_mysql_lock.test # create table t1 (i int not null primary key); diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index 60faea8853f..889a09aaeb1 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -259,6 +259,66 @@ Warnings: Warning 1292 Incorrect datetime value: '0' DROP TABLE t1; # +# Bug #11766300 59387: FAILING ASSERTION: CURSOR->POS_STATE == 1997660512 (BTR_PCUR_IS_POSITIONE +# +CREATE TABLE t1 (a INT) ENGINE=INNODB; +INSERT INTO t1 VALUES (0); +CREATE TABLE t2 (d BINARY(2), PRIMARY KEY (d(1)), UNIQUE KEY (d)) ENGINE=INNODB; +SELECT 1 FROM t1 WHERE NOT EXISTS +(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); +1 +1 +EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS +(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 1 Using where +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY,d d 2 func 1 Using where +3 DEPENDENT SUBQUERY t2 index NULL d 2 NULL 1 Using where; Using index +DROP TABLE t2; +CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB; +INSERT INTO t2 VALUES (1, 1); +SELECT 1 FROM t1 +WHERE a != (SELECT 1 FROM t2 WHERE a <=> b OR a > '' AND 6 = 7 ORDER BY b, c); +1 +DROP TABLE t1, t2; +# +# Bug #13639204 64111: CRASH ON SELECT SUBQUERY WITH NON UNIQUE +# INDEX +# +CREATE TABLE t1 ( +id int +) ENGINE=InnoDB; +INSERT INTO t1 (id) VALUES (11); +CREATE TABLE t2 ( +t1_id int, +position int, +KEY t1_id (t1_id), +KEY t1_id_position (t1_id,position) +) ENGINE=InnoDB; +EXPLAIN SELECT +(SELECT position FROM t2 +WHERE t2.t1_id = t1.id +ORDER BY t2.t1_id , t2.position +LIMIT 10,1 +) AS maxkey +FROM t1 +LIMIT 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ref t1_id,t1_id_position t1_id_position 5 test.t1.id 1 Using where; Using index +SELECT +(SELECT position FROM t2 +WHERE t2.t1_id = t1.id +ORDER BY t2.t1_id , t2.position +LIMIT 10,1 +) AS maxkey +FROM t1 +LIMIT 1; +maxkey +NULL +DROP TABLE t1,t2; +End of 5.1 tests +# # lp:827416 Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries # CREATE TABLE t3 ( b int) ENGINE=InnoDB; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 86442df1e8b..da2a76626dc 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1986,6 +1986,19 @@ y z DROP VIEW v2; DROP TABLE t1, t2; +# +# BUG#1000269: Wrong result (extra rows) with semijoin+materialization, IN subqueries, join_cache_level>0 +# +CREATE TABLE t1 (a1 VARCHAR(1), a2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('b','b'),('e','e'); +CREATE TABLE t2 (b1 VARCHAR(1), b2 VARCHAR(1), KEY(b1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('v','v'),('s','s'),('l','l'), ('y','y'),('c','c'),('i','i'); +SELECT * FROM t1, t2 WHERE b1 IN ( SELECT b2 FROM t2 WHERE b1 > 'o' ) AND ( b1 < 'l' OR a1 IN ('b','c') ); +a1 a2 b1 b2 +b b v v +b b s s +b b y y +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_no_mat.result b/mysql-test/r/subselect_no_mat.result index d5c7084c48d..fb488ec2e6a 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -5599,6 +5599,15 @@ SELECT 1 FROM ERROR 21000: Operand should contain 1 column(s) DROP TABLE t1; # +# Bug#13721076 CRASH WITH TIME TYPE/TIMESTAMP() AND WARNINGS IN SUBQUERY +# +CREATE TABLE t1(a TIME NOT NULL); +INSERT INTO t1 VALUES ('00:00:32'); +SELECT 1 FROM t1 WHERE a > +(SELECT timestamp(a) AS a FROM t1); +1 +DROP TABLE t1; +# # No BUG#, a case brought from 5.2's innodb_mysql_lock.test # create table t1 (i int not null primary key); diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 7b44b1a1f5d..3c421b7335a 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -5595,6 +5595,15 @@ SELECT 1 FROM ERROR 21000: Operand should contain 1 column(s) DROP TABLE t1; # +# Bug#13721076 CRASH WITH TIME TYPE/TIMESTAMP() AND WARNINGS IN SUBQUERY +# +CREATE TABLE t1(a TIME NOT NULL); +INSERT INTO t1 VALUES ('00:00:32'); +SELECT 1 FROM t1 WHERE a > +(SELECT timestamp(a) AS a FROM t1); +1 +DROP TABLE t1; +# # No BUG#, a case brought from 5.2's innodb_mysql_lock.test # create table t1 (i int not null primary key); diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index 9b24af0dc3a..dcf1ad0a7dc 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -5603,6 +5603,15 @@ SELECT 1 FROM ERROR 21000: Operand should contain 1 column(s) DROP TABLE t1; # +# Bug#13721076 CRASH WITH TIME TYPE/TIMESTAMP() AND WARNINGS IN SUBQUERY +# +CREATE TABLE t1(a TIME NOT NULL); +INSERT INTO t1 VALUES ('00:00:32'); +SELECT 1 FROM t1 WHERE a > +(SELECT timestamp(a) AS a FROM t1); +1 +DROP TABLE t1; +# # No BUG#, a case brought from 5.2's innodb_mysql_lock.test # create table t1 (i int not null primary key); diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index ba42ff881fc..f40520fa0e6 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -5595,6 +5595,15 @@ SELECT 1 FROM ERROR 21000: Operand should contain 1 column(s) DROP TABLE t1; # +# Bug#13721076 CRASH WITH TIME TYPE/TIMESTAMP() AND WARNINGS IN SUBQUERY +# +CREATE TABLE t1(a TIME NOT NULL); +INSERT INTO t1 VALUES ('00:00:32'); +SELECT 1 FROM t1 WHERE a > +(SELECT timestamp(a) AS a FROM t1); +1 +DROP TABLE t1; +# # No BUG#, a case brought from 5.2's innodb_mysql_lock.test # create table t1 (i int not null primary key); diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index f8ebe0d07a9..1ceeb541a85 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2711,4 +2711,46 @@ DROP VIEW v1; DROP TABLE t1; set @@join_cache_level= @tmp_jcl_978479; set @@optimizer_switch= @tmp_os_978479; +# +# BUG#998236: Assertion failure or valgrind errors at best_access_path ... +# +CREATE TABLE t1 (a1 VARCHAR(3), a2 VARCHAR(35), KEY(a1,a2)) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('USA','Arvada'),('USA','Athens'),('USA','Atlanta'),('USA','Augusta'), +('USA','Aurora'),('USA','Aurora'),('USA','Austin'),('USA','Bakersfield'), +('USA','Baltimore'),('USA','Baton'),('USA','Beaumont'),('USA','Bellevue'), +('USA','Berkeley'),('USA','Billings'),('USA','Birmingham'),('USA','Boise'), +('USA','Boston'),('USA','Boulder'),('USA','Bridgeport'),('USA','Brockton'), +('USA','Brownsville'),('USA','Buffalo'),('USA','Burbank'),('USA','Cambridge'), +('USA','Cape'),('USA','Carrollton'),('USA','Carson'),('USA','Cary'), +('USA','Cedar'),('USA','Chandler'),('USA','Charleston'),('USA','Charlotte'), +('USA','Chattanooga'),('USA','Chesapeake'),('USA','Chicago'),('USA','Chula'), +('USA','Cincinnati'),('USA','Citrus'),('USA','Clarksville'),('USA','Clearwater'), +('USA','Cleveland'),('USA','Colorado'),('USA','Columbia'),('USA','Columbus'), +('USA','Columbus'),('USA','Compton'),('USA','Concord'),('USA','Coral'), +('USA','Corona'),('USA','Corpus'),('USA','Costa'),('USA','Dallas'),('USA','Daly'), +('USA','Davenport'),('USA','Dayton'),('USA','Denver'),('USA','DesMoines'), +('USA','Detroit'),('USA','Downey'),('USA','Durham'),('USA','East'),('USA','ElCajon'), +('USA','ElMonte'),('USA','ElPaso'),('USA','Elgin'),('USA','Elizabeth'), +('USA','Erie'),('USA','Escondido'),('USA','Eugene'),('USA','Evansville'), +('USA','Fairfield'),('USA','Fall'),('USA','Fayetteville'),('USA','Flint'), +('USA','Fontana'),('USA','FortCollins'),('USA','FortLauderdale'),('USA','FortWayne'), +('USA','FortWorth'),('USA','Fremont'),('USA','Fresno'),('USA','Fullerton'), +('USA','Gainesville'),('USA','GardenGrove'),('USA','Garland'),('USA','Gary'), +('USA','Gilbert'),('USA','Glendale'),('USA','Glendale'),('USA','GrandPrairie'), +('USA','GrandRapids'),('USA','Green Bay'),('USA','Greensboro'),('USA','Hampton'), +('USA','Hartford'),('USA','Hayward'),('USA','Henderson'),('USA','Hialeah'), +('USA','Inglewood'),('USA','Livonia'); +CREATE TABLE t3 (c1 VARCHAR(3), c2 VARCHAR(16), PRIMARY KEY (c1,c2)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('USA','Chinese'), ('USA','English'); +CREATE TABLE t2 (b1 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); +SELECT alias1.a1 AS field1 +FROM t1 AS alias1, t1 AS alias2 +WHERE alias1.a2 IN ( SELECT a1 FROM t1, t2 ) +AND alias1.a1 IS NULL +AND ( alias1.a1, alias2.a1 ) IN ( SELECT c1, c1 FROM t3 ) +GROUP BY field1; +field1 +DROP TABLE t1,t3,t2; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index c0a9287483d..31400c95959 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2725,6 +2725,48 @@ DROP VIEW v1; DROP TABLE t1; set @@join_cache_level= @tmp_jcl_978479; set @@optimizer_switch= @tmp_os_978479; +# +# BUG#998236: Assertion failure or valgrind errors at best_access_path ... +# +CREATE TABLE t1 (a1 VARCHAR(3), a2 VARCHAR(35), KEY(a1,a2)) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('USA','Arvada'),('USA','Athens'),('USA','Atlanta'),('USA','Augusta'), +('USA','Aurora'),('USA','Aurora'),('USA','Austin'),('USA','Bakersfield'), +('USA','Baltimore'),('USA','Baton'),('USA','Beaumont'),('USA','Bellevue'), +('USA','Berkeley'),('USA','Billings'),('USA','Birmingham'),('USA','Boise'), +('USA','Boston'),('USA','Boulder'),('USA','Bridgeport'),('USA','Brockton'), +('USA','Brownsville'),('USA','Buffalo'),('USA','Burbank'),('USA','Cambridge'), +('USA','Cape'),('USA','Carrollton'),('USA','Carson'),('USA','Cary'), +('USA','Cedar'),('USA','Chandler'),('USA','Charleston'),('USA','Charlotte'), +('USA','Chattanooga'),('USA','Chesapeake'),('USA','Chicago'),('USA','Chula'), +('USA','Cincinnati'),('USA','Citrus'),('USA','Clarksville'),('USA','Clearwater'), +('USA','Cleveland'),('USA','Colorado'),('USA','Columbia'),('USA','Columbus'), +('USA','Columbus'),('USA','Compton'),('USA','Concord'),('USA','Coral'), +('USA','Corona'),('USA','Corpus'),('USA','Costa'),('USA','Dallas'),('USA','Daly'), +('USA','Davenport'),('USA','Dayton'),('USA','Denver'),('USA','DesMoines'), +('USA','Detroit'),('USA','Downey'),('USA','Durham'),('USA','East'),('USA','ElCajon'), +('USA','ElMonte'),('USA','ElPaso'),('USA','Elgin'),('USA','Elizabeth'), +('USA','Erie'),('USA','Escondido'),('USA','Eugene'),('USA','Evansville'), +('USA','Fairfield'),('USA','Fall'),('USA','Fayetteville'),('USA','Flint'), +('USA','Fontana'),('USA','FortCollins'),('USA','FortLauderdale'),('USA','FortWayne'), +('USA','FortWorth'),('USA','Fremont'),('USA','Fresno'),('USA','Fullerton'), +('USA','Gainesville'),('USA','GardenGrove'),('USA','Garland'),('USA','Gary'), +('USA','Gilbert'),('USA','Glendale'),('USA','Glendale'),('USA','GrandPrairie'), +('USA','GrandRapids'),('USA','Green Bay'),('USA','Greensboro'),('USA','Hampton'), +('USA','Hartford'),('USA','Hayward'),('USA','Henderson'),('USA','Hialeah'), +('USA','Inglewood'),('USA','Livonia'); +CREATE TABLE t3 (c1 VARCHAR(3), c2 VARCHAR(16), PRIMARY KEY (c1,c2)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('USA','Chinese'), ('USA','English'); +CREATE TABLE t2 (b1 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); +SELECT alias1.a1 AS field1 +FROM t1 AS alias1, t1 AS alias2 +WHERE alias1.a2 IN ( SELECT a1 FROM t1, t2 ) +AND alias1.a1 IS NULL +AND ( alias1.a1, alias2.a1 ) IN ( SELECT c1, c1 FROM t3 ) +GROUP BY field1; +field1 +DROP TABLE t1,t3,t2; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index dc1d9b407fd..da18870bbeb 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2026,6 +2026,19 @@ y z DROP VIEW v2; DROP TABLE t1, t2; +# +# BUG#1000269: Wrong result (extra rows) with semijoin+materialization, IN subqueries, join_cache_level>0 +# +CREATE TABLE t1 (a1 VARCHAR(1), a2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('b','b'),('e','e'); +CREATE TABLE t2 (b1 VARCHAR(1), b2 VARCHAR(1), KEY(b1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('v','v'),('s','s'),('l','l'), ('y','y'),('c','c'),('i','i'); +SELECT * FROM t1, t2 WHERE b1 IN ( SELECT b2 FROM t2 WHERE b1 > 'o' ) AND ( b1 < 'l' OR a1 IN ('b','c') ); +a1 a2 b1 b2 +b b v v +b b s s +b b y y +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/table_elim.result b/mysql-test/r/table_elim.result index 69cd9792299..63b3304641b 100644 --- a/mysql-test/r/table_elim.result +++ b/mysql-test/r/table_elim.result @@ -570,6 +570,27 @@ id select_type table type possible_keys key key_len ref rows Extra # as that violates the "no interleaving of outer join nests" rule. DROP TABLE t1,t2,t3,t4,t5; # +# BUG#997747: Assertion `join->best_read < ((double)1.79..5e+308L)' +# failed in greedy_search with LEFT JOINs and unique keys +# +CREATE TABLE t1 (a1 INT); +CREATE TABLE t2 (b1 INT); +CREATE TABLE t3 (c1 INT, UNIQUE KEY(c1)); +CREATE TABLE t4 (d1 INT, UNIQUE KEY(d1)); +CREATE TABLE t5 (e1 INT); +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES (2),(3); +INSERT INTO t3 VALUES (3),(4); +INSERT INTO t4 VALUES (4),(5); +INSERT INTO t5 VALUES (5),(6); +SELECT a1 FROM t1 LEFT JOIN t2 LEFT JOIN t3 LEFT JOIN t4 +ON c1 = d1 ON d1 = b1 ON a1 = b1 +LEFT JOIN t5 ON a1 = e1 ; +a1 +1 +2 +DROP TABLE t1,t2,t3,t4,t5; +# # BUG#884184: Wrong result with RIGHT JOIN + derived_merge # CREATE TABLE t1 (a int(11), b varchar(1)) ; diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 3bf8a6a7126..accb88b7f15 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -485,4 +485,10 @@ f1 f2 1 4 DROP TRIGGER trg1; DROP TABLE t1; +# +# Bug #12408412: GROUP_CONCAT + ORDER BY + INPUT/OUTPUT +# SAME USER VARIABLE = CRASH +# +SET @bug12408412=1; +SELECT GROUP_CONCAT(@bug12408412 ORDER BY 1) INTO @bug12408412; End of 5.5 tests diff --git a/mysql-test/suite/binlog/r/binlog_unsafe.result b/mysql-test/suite/binlog/r/binlog_unsafe.result index 2f994e9f5fc..2cc1ebdd2d7 100644 --- a/mysql-test/suite/binlog/r/binlog_unsafe.result +++ b/mysql-test/suite/binlog/r/binlog_unsafe.result @@ -2678,6 +2678,8 @@ CREATE TABLE replace_table (a INT, b INT, PRIMARY KEY(a)); INSERT INTO replace_table values (1,1),(2,2); CREATE TABLE update_table (a INT, b INT, PRIMARY KEY(a)); INSERT INTO update_table values (1,1),(2,2); +CREATE TABLE insert_2_keys (a INT UNIQUE KEY, b INT UNIQUE KEY); +INSERT INTO insert_2_keys values (1, 1); INSERT IGNORE INTO insert_table SELECT * FROM filler_table; Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT IGNORE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave. @@ -2701,10 +2703,15 @@ Note 1592 Unsafe statement written to the binary log using statement format sinc CREATE TEMPORARY TABLE temp1 (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table; Warnings: Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE... REPLACE SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on master and the slave. +INSERT INTO insert_2_keys VALUES (1, 2) +ON DUPLICATE KEY UPDATE a=VALUES(a)+10, b=VALUES(b)+10; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe DROP TABLE filler_table; DROP TABLE insert_table; DROP TABLE update_table; DROP TABLE replace_table; DROP TABLE create_ignore_test; DROP TABLE create_replace_test; +DROP TABLE insert_2_keys; "End of tests" diff --git a/mysql-test/suite/binlog/t/binlog_unsafe.test b/mysql-test/suite/binlog/t/binlog_unsafe.test index 3353855fd0c..2de84a58875 100644 --- a/mysql-test/suite/binlog/t/binlog_unsafe.test +++ b/mysql-test/suite/binlog/t/binlog_unsafe.test @@ -17,6 +17,7 @@ # - CREATE TABLE [IGNORE/REPLACE] SELECT # - INSERT IGNORE...SELECT # - UPDATE IGNORE +# - INSERT... ON DUPLICATE KEY UPDATE on a table with two UNIQUE KEYS # # Note that statements that use stored functions, stored procedures, # triggers, views, or prepared statements that invoke unsafe @@ -715,6 +716,9 @@ DROP TABLE t1; #UPDATE IGNORE #CREATE TABLE... IGNORE SELECT #CREATE TABLE... REPLACE SELECT +# +###BUG 11765650 - 58637: MARK UPDATES THAT DEPEND ON ORDER OF TWO KEYS UNSAFE +#INSERT.... ON DUP KEY UPDATE on a table with more than one UNIQUE KEY #setup tables CREATE TABLE filler_table (a INT, b INT); @@ -724,6 +728,8 @@ CREATE TABLE replace_table (a INT, b INT, PRIMARY KEY(a)); INSERT INTO replace_table values (1,1),(2,2); CREATE TABLE update_table (a INT, b INT, PRIMARY KEY(a)); INSERT INTO update_table values (1,1),(2,2); +CREATE TABLE insert_2_keys (a INT UNIQUE KEY, b INT UNIQUE KEY); +INSERT INTO insert_2_keys values (1, 1); #INSERT IGNORE... SELECT INSERT IGNORE INTO insert_table SELECT * FROM filler_table; @@ -741,6 +747,10 @@ CREATE TABLE create_replace_test (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * #temporary tables should not throw the warning. CREATE TEMPORARY TABLE temp1 (a INT, b INT, PRIMARY KEY(b)) REPLACE SELECT * FROM filler_table; +#INSERT.... ON DUP KEY UPDATE on a table with more than one UNIQUE KEY +INSERT INTO insert_2_keys VALUES (1, 2) + ON DUPLICATE KEY UPDATE a=VALUES(a)+10, b=VALUES(b)+10; + ###clean up DROP TABLE filler_table; DROP TABLE insert_table; @@ -748,5 +758,6 @@ DROP TABLE update_table; DROP TABLE replace_table; DROP TABLE create_ignore_test; DROP TABLE create_replace_test; +DROP TABLE insert_2_keys; --echo "End of tests" diff --git a/mysql-test/suite/innodb/r/innodb_bug13635833.result b/mysql-test/suite/innodb/r/innodb_bug13635833.result new file mode 100644 index 00000000000..6a505bc94f2 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug13635833.result @@ -0,0 +1,45 @@ +SET DEBUG_SYNC='reset'; +create table t1 (f1 integer, key k1 (f1)) engine=innodb; +create table t2 (f1 int, f2 int, key(f1), key(f2)) engine=innodb; +create table t3 (f2 int, key(f2)) engine=innodb; +insert into t1 values (10); +insert into t2 values (10, 20); +insert into t3 values (20); +alter table t2 add constraint c1 foreign key (f1) +references t1(f1) on update cascade; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) DEFAULT NULL, + KEY `k1` (`f1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `f1` int(11) DEFAULT NULL, + `f2` int(11) DEFAULT NULL, + KEY `f1` (`f1`), + KEY `f2` (`f2`), + CONSTRAINT `c1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`) ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `f2` int(11) DEFAULT NULL, + KEY `f2` (`f2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SET DEBUG_SYNC='innodb_rename_table_ready SIGNAL update_can_proceed + WAIT_FOR dict_unfreeze'; +alter table t2 add constraint z1 foreign key (f2) +references t3(f2) on update cascade; +SET DEBUG_SYNC='innodb_row_update_for_mysql_begin + WAIT_FOR update_can_proceed'; +SET DEBUG_SYNC='innodb_dml_cascade_dict_unfreeze SIGNAL dict_unfreeze + WAIT_FOR foreign_free_cache'; +update ignore t1 set f1 = 20; +ERROR HY000: Error on rename of './test/t2' to '#sql2-temporary' (errno: 181) +SET DEBUG_SYNC='now SIGNAL foreign_free_cache'; +drop table t2; +drop table t1; +drop table t3; +SET DEBUG_SYNC='reset'; diff --git a/mysql-test/suite/innodb/r/innodb_bug60196.result b/mysql-test/suite/innodb/r/innodb_bug60196.result index 2a0af9257d3..411950b49dd 100755 --- a/mysql-test/suite/innodb/r/innodb_bug60196.result +++ b/mysql-test/suite/innodb/r/innodb_bug60196.result @@ -58,7 +58,7 @@ FK1_Key FK2_Key INSERT INTO Bug_60196 VALUES (2, 1); INSERT INTO Bug_60196 VALUES (2, 2); INSERT INTO Bug_60196 VALUES (2, 3); -SELECT * FROM Bug_60196 ORDER BY FK1_Key, FK2_Key; +SELECT * FROM Bug_60196; FK1_Key FK2_Key 1 1 1 2 diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test index cea9055b873..46a2e210c6a 100644 --- a/mysql-test/suite/innodb/t/innodb-index.test +++ b/mysql-test/suite/innodb/t/innodb-index.test @@ -1,5 +1,10 @@ -- source include/have_innodb.inc +if (`select plugin_auth_version <= "1.0.17-13.0" from information_schema.plugins where plugin_name='innodb'`) +{ + --skip Not fixed in XtraDB 1.0.17-13.0 or earlier +} + let $MYSQLD_DATADIR= `select @@datadir`; let $per_table=`select @@innodb_file_per_table`; diff --git a/mysql-test/suite/innodb/t/innodb-lock.test b/mysql-test/suite/innodb/t/innodb-lock.test index e3026ac505b..97ae56f933b 100644 --- a/mysql-test/suite/innodb/t/innodb-lock.test +++ b/mysql-test/suite/innodb/t/innodb-lock.test @@ -1,5 +1,10 @@ --source include/have_innodb.inc +if (`select plugin_auth_version <= "1.0.17-13.0" from information_schema.plugins where plugin_name='innodb'`) +{ + --skip Not fixed in XtraDB 1.0.17-13.0 or earlier +} + # # Check and select innodb lock type # diff --git a/mysql-test/suite/innodb/t/innodb_bug13635833.test b/mysql-test/suite/innodb/t/innodb_bug13635833.test new file mode 100644 index 00000000000..4bab6d09f89 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug13635833.test @@ -0,0 +1,69 @@ +--source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/not_embedded.inc + +if (`select plugin_auth_version <= "1.1.8-24.1" from information_schema.plugins where plugin_name='innodb'`) +{ + --skip Not fixed in XtraDB 1.1.8-24.1 or earlier +} + +SET DEBUG_SYNC='reset'; + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +create table t1 (f1 integer, key k1 (f1)) engine=innodb; +create table t2 (f1 int, f2 int, key(f1), key(f2)) engine=innodb; +create table t3 (f2 int, key(f2)) engine=innodb; + +insert into t1 values (10); +insert into t2 values (10, 20); +insert into t3 values (20); + +alter table t2 add constraint c1 foreign key (f1) + references t1(f1) on update cascade; + +show create table t1; +show create table t2; +show create table t3; + +SET DEBUG_SYNC='innodb_rename_table_ready SIGNAL update_can_proceed + WAIT_FOR dict_unfreeze'; + +--send +alter table t2 add constraint z1 foreign key (f2) + references t3(f2) on update cascade; + +connect (thr2,localhost,root,,); +connection thr2; + +SET DEBUG_SYNC='innodb_row_update_for_mysql_begin + WAIT_FOR update_can_proceed'; +SET DEBUG_SYNC='innodb_dml_cascade_dict_unfreeze SIGNAL dict_unfreeze + WAIT_FOR foreign_free_cache'; + +--send +update ignore t1 set f1 = 20; + +connection default; +--replace_regex /'[^']*test\/#sql2-[0-9a-f-]*'/'#sql2-temporary'/ +--error ER_ERROR_ON_RENAME +reap; + +SET DEBUG_SYNC='now SIGNAL foreign_free_cache'; + +connection thr2; +reap; +disconnect thr2; +--source include/wait_until_disconnected.inc + +connection default; + +drop table t2; +drop table t1; +drop table t3; + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc + +SET DEBUG_SYNC='reset'; diff --git a/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result b/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result index ea738b710fd..26c6d96e786 100644 --- a/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result +++ b/mysql-test/suite/rpl/r/rpl_known_bugs_detection.result @@ -5,6 +5,8 @@ call mtr.add_suppression("Unsafe statement written to the binary log using state CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, UNIQUE(b)); INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe SELECT * FROM t1; a b 1 10 diff --git a/mysql-test/suite/rpl/r/rpl_report_port.result b/mysql-test/suite/rpl/r/rpl_report_port.result index ebdd3f889f3..d9887f41ff9 100644 --- a/mysql-test/suite/rpl/r/rpl_report_port.result +++ b/mysql-test/suite/rpl/r/rpl_report_port.result @@ -1,8 +1,11 @@ include/master-slave.inc [connection master] -include/assert.inc [The default value shown for the slave's port number is the actual port number of the slave.] include/rpl_restart_server.inc [server_number=2 parameters: --report-port=9000] include/start_slave.inc [Slave restarted with the report-port set to some value] include/assert.inc [The value shown for the slave's port number is 9000 which is the value set for report-port.] +include/rpl_restart_server.inc [server_number=2 parameters: --report-port=] +include/start_slave.inc +[Slave restarted with the report-port set to the value of slave's port number] +include/assert.inc [The default value shown for the slave's port number is the actual port number of the slave.] include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result index c864850a238..6e2415ebcd8 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result @@ -49,6 +49,10 @@ A B A I X Y X Z +include/assert.inc [Counter for COM_COMMIT is consistent with the number of actual commits] +include/assert.inc [Counter for COM_INSERT is consistent with the number of actual inserts] +include/assert.inc [Counter for COM_DELETE is consistent with the number of actual deletes] +include/assert.inc [Counter for COM_UPDATE is consistent with the number of actual updates] UPDATE t1 SET c2 = 'Q' WHERE c1 = 'A' AND c2 = 'N'; SELECT * FROM t1 ORDER BY c1,c2; C1 C2 diff --git a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result index 0e00695500c..cba1b905d98 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_3innodb.result @@ -49,6 +49,10 @@ A B A I X Y X Z +include/assert.inc [Counter for COM_COMMIT is consistent with the number of actual commits] +include/assert.inc [Counter for COM_INSERT is consistent with the number of actual inserts] +include/assert.inc [Counter for COM_DELETE is consistent with the number of actual deletes] +include/assert.inc [Counter for COM_UPDATE is consistent with the number of actual updates] UPDATE t1 SET c2 = 'Q' WHERE c1 = 'A' AND c2 = 'N'; SELECT * FROM t1 ORDER BY c1,c2; C1 C2 diff --git a/mysql-test/suite/rpl/r/rpl_row_merge_engine.result b/mysql-test/suite/rpl/r/rpl_row_merge_engine.result new file mode 100644 index 00000000000..c61167e84e0 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_row_merge_engine.result @@ -0,0 +1,16 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1 (a int) ENGINE=MyISAM; +CREATE TABLE t2 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (4), (5), (6); +CREATE TABLE IF NOT EXISTS t1_merge LIKE t1; +ALTER TABLE t1_merge ENGINE=MERGE UNION (t2, t1); +include/diff_tables.inc [master:test.t1, slave:test.t1] +include/diff_tables.inc [master:test.t2, slave:test.t2] +UPDATE t1_merge SET a=10 WHERE a=1; +DELETE FROM t1_merge WHERE a=10; +include/diff_tables.inc [master:test.t1, slave:test.t1] +include/diff_tables.inc [master:test.t2, slave:test.t2] +DROP TABLE t1_merge, t1, t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_report_port-slave.opt b/mysql-test/suite/rpl/t/rpl_report_port-slave.opt deleted file mode 100644 index ffe2b2e7016..00000000000 --- a/mysql-test/suite/rpl/t/rpl_report_port-slave.opt +++ /dev/null @@ -1 +0,0 @@ ---report-port= diff --git a/mysql-test/suite/rpl/t/rpl_report_port.test b/mysql-test/suite/rpl/t/rpl_report_port.test index b1b71a2982c..379f9aed946 100644 --- a/mysql-test/suite/rpl/t/rpl_report_port.test +++ b/mysql-test/suite/rpl/t/rpl_report_port.test @@ -10,22 +10,18 @@ # #====Method==== # -# Start replication with report port not set.This will give the actual port -# number of the slave (ie. SLAVE_PORT) for the on doing SHOW SLAVE HOSTS on -# the master. -# Restart the slave server with report port set to 9000 and start the slave. +# Start replication with report port set to 9000 and restart the slave. # In this case on doing SHOW SLAVE HOSTS on the master, we get the port number # of the slave to be 9000. +# In the second case restart the slave server with report port not set. In this +# case on doing SHOW SLAVE HOSTS on the master, we get the actual port number +# of the slave (ie. SLAVE_PORT). source include/master-slave.inc; +source include/have_binlog_format_mixed.inc; connection master; ---let $report_port= query_get_value(SHOW SLAVE HOSTS, Port, 1) ---let assert_text= The default value shown for the slave's port number is the actual port number of the slave. ---let assert_cond= $report_port = "$SLAVE_MYPORT" ---source include/assert.inc - # Start the server with some value being passed to the report_port= <option> # this will be used incase we have to mask the value of the slave's port # number in certain situations. @@ -46,4 +42,25 @@ connection master; --let assert_cond= $report_port = "9000" --source include/assert.inc + +# Start the server with the report-port being passed with no value. So on SHOW SLAVE HOSTS +# on the master the value of slave's port should be the actual value of the slave port. + +--let $rpl_server_number= 2 +--let $rpl_server_parameters= --report-port= +--source include/rpl_restart_server.inc + +connection slave; +--source include/start_slave.inc + +--echo [Slave restarted with the report-port set to the value of slave's port number] + +connection master; + +# The value reported is the actual value of the slave's port. +--let $report_port= query_get_value(SHOW SLAVE HOSTS, Port, 1) +--let assert_text= The default value shown for the slave's port number is the actual port number of the slave. +--let assert_cond= $report_port = "$SLAVE_MYPORT" +--source include/assert.inc + --source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_merge_engine.test b/mysql-test/suite/rpl/t/rpl_row_merge_engine.test new file mode 100644 index 00000000000..5add8dc1cda --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_row_merge_engine.test @@ -0,0 +1,50 @@ +# +# BUG#47103 +# +# This test case checks whether the slave crashes or not when there is +# a merge table in use. +# +# Description +# =========== +# +# The test case creates two regular MyISAM tables on the master and +# one MERGE table. Then it populates the MyISAM tables, updates and +# deletes their contents through the merge table. Finally, the slave +# is synchronized with the master and (after the fix) it won't crash. +# +--source include/master-slave.inc +--source include/have_binlog_format_row.inc +--connection master + +CREATE TABLE t1 (a int) ENGINE=MyISAM; +CREATE TABLE t2 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (4), (5), (6); +CREATE TABLE IF NOT EXISTS t1_merge LIKE t1; +ALTER TABLE t1_merge ENGINE=MERGE UNION (t2, t1); + +--sync_slave_with_master + +--let diff_tables=master:test.t1, slave:test.t1 +--source include/diff_tables.inc + +--let diff_tables=master:test.t2, slave:test.t2 +--source include/diff_tables.inc + +--connection master +UPDATE t1_merge SET a=10 WHERE a=1; +DELETE FROM t1_merge WHERE a=10; + +--sync_slave_with_master +--connection master + +--let diff_tables=master:test.t1, slave:test.t1 +--source include/diff_tables.inc + +--let diff_tables=master:test.t2, slave:test.t2 +--source include/diff_tables.inc + +DROP TABLE t1_merge, t1, t2; +--sync_slave_with_master + +--source include/rpl_end.inc diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 1edee9abf1e..eade7ba721e 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1230,3 +1230,4 @@ rename table t2 to t1; execute stmt1; deallocate prepare stmt1; drop table t2; + diff --git a/mysql-test/t/blackhole.test b/mysql-test/t/blackhole.test new file mode 100644 index 00000000000..1451f7606e0 --- /dev/null +++ b/mysql-test/t/blackhole.test @@ -0,0 +1,21 @@ +# +# Tests for the BLACKHOLE storage engine +# + +--source include/have_blackhole.inc + +--echo # +--echo # Bug #11880012: INDEX_SUBQUERY, BLACKHOLE, +--echo # HANG IN PREPARING WITH 100% CPU USAGE +--echo # + +CREATE TABLE t1(a INT NOT NULL); +INSERT INTO t1 VALUES (1), (2), (3); +CREATE TABLE t2 (a INT UNSIGNED, b INT, UNIQUE KEY (a, b)) ENGINE=BLACKHOLE; + +SELECT 1 FROM t1 WHERE a = ANY (SELECT a FROM t2); + +DROP TABLE t1, t2; + +--echo End of 5.5 tests + diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index c8a5c968fe5..c39b7267f83 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -400,6 +400,18 @@ connection default; disconnect newconn; SET @@GLOBAL.max_allowed_packet=default; +--echo # +--echo # Bug#13519724 63793: CRASH IN DTCOLLATION::SET(DTCOLLATION &SET) +--echo # + +CREATE TABLE t1 (a VARCHAR(50)); + +SELECT a FROM t1 +WHERE CAST(a as BINARY)=x'62736D697468' + AND CAST(a AS BINARY)=x'65736D697468'; + +DROP TABLE t1; + --echo End of 5.1 tests select cast("2101-00-01 02:03:04" as datetime); diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 3320ca25136..30811be2934 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1437,6 +1437,19 @@ drop table t1; set optimizer_switch=@save978847_optimizer_switch; + +--echo # +--echo # LP bug998516 Server hangs on INSERT .. SELECT with derived_merge, +--echo # FROM subquery, UNION +--echo # +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (3),(4); +INSERT INTO t1 SELECT * FROM ( SELECT * FROM t1 ) AS alias UNION SELECT * FROM t2; +select * from t1; +drop table t1,t2; + --echo # --echo # end of 5.3 tests --echo # diff --git a/mysql-test/t/errors.test b/mysql-test/t/errors.test index 82822c87e89..82204e1bfac 100644 --- a/mysql-test/t/errors.test +++ b/mysql-test/t/errors.test @@ -171,3 +171,21 @@ SELECT UPDATEXML(-73 * -2465717823867977728,@@global.auto_increment_increment,nu --echo # --echo # End Bug#57882 --echo # + +# +# Bug #13031606 VALUES() IN A SELECT STATEMENT CRASHES SERVER +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2(a INT PRIMARY KEY, b INT); +--error ER_BAD_FIELD_ERROR +SELECT '' AS b FROM t1 GROUP BY VALUES(b); +--error ER_BAD_FIELD_ERROR +REPLACE t2(b) SELECT '' AS b FROM t1 GROUP BY VALUES(b); +--error ER_BAD_FIELD_ERROR +UPDATE t2 SET a=(SELECT '' AS b FROM t1 GROUP BY VALUES(b)); +--error ER_BAD_FIELD_ERROR +INSERT INTO t2 VALUES (1,0) ON DUPLICATE KEY UPDATE + b=(SELECT '' AS b FROM t1 GROUP BY VALUES(b)); +INSERT INTO t2(a,b) VALUES (1,0) ON DUPLICATE KEY UPDATE + b=(SELECT VALUES(a)+2 FROM t1); +DROP TABLE t1, t2; diff --git a/mysql-test/t/func_group_innodb.test b/mysql-test/t/func_group_innodb.test index bbc576b0fc7..ccf7aff6e6e 100644 --- a/mysql-test/t/func_group_innodb.test +++ b/mysql-test/t/func_group_innodb.test @@ -86,6 +86,19 @@ drop table t1m, t1i, t2m, t2i; --echo # +--echo # Bug#13723054 CRASH WITH MIN/MAX AFTER QUICK_GROUP_MIN_MAX_SELECT::NEXT_MIN +--echo # + +CREATE TABLE t1(a BLOB, b VARCHAR(255) CHARSET LATIN1, c INT, + KEY(b, c, a(765))) ENGINE=INNODB; +INSERT INTO t1(a, b, c) VALUES ('', 'a', 0), ('', 'a', null), ('', 'a', 0); + +SELECT MIN(c) FROM t1 GROUP BY b; +EXPLAIN SELECT MIN(c) FROM t1 GROUP BY b; + +DROP TABLE t1; + +--echo # --echo # Bug #57954: BIT_AND function returns incorrect results when --echo # semijoin=on diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test index 5414adddd43..2b89a618aa6 100644 --- a/mysql-test/t/func_if.test +++ b/mysql-test/t/func_if.test @@ -193,3 +193,20 @@ sum(distinct(if('a', (select adddate(elt(convert($nines,decimal(64,0)),count(*)), interval 1 day)) , .1))) as foo; + +--echo # +--echo # LP bug#998321 Simple query with IF expression causes an +--echo # assertion abort (see also mysql Bug#12620084) +--echo # + +SELECT if(0, (SELECT min('hello')), NULL); +SELECT if(1, (SELECT min('hello')), NULL); +SELECT if(0, NULL, (SELECT min('hello'))); +SELECT if(1, NULL, (SELECT min('hello'))); + +--echo End of 5.2 tests + +--disable_query_log +# Restore timezone to default +set time_zone= @@global.time_zone; +--enable_query_log diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index a559b16b1f0..f4c9de4e7da 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -1295,6 +1295,24 @@ WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1; DROP DATABASE gis_ogs; USE test; + +--echo # +--echo # BUG#12414917 - ISCLOSED() CRASHES ON 64-BIT BUILDS +--echo # +SELECT ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20))); + +--echo # +--echo # BUG#12537203 - CRASH WHEN SUBSELECTING GLOBAL VARIABLES IN +--echo # GEOMETRY FUNCTION ARGUMENTS +--echo # +--replace_regex /non geometric .* value/non geometric '' value/ +--error ER_ILLEGAL_VALUE_FOR_TYPE +SELECT GEOMETRYCOLLECTION((SELECT @@OLD)); + + +--echo End of 5.1 tests + + --echo # --echo # Bug#11908153: CRASH AND/OR VALGRIND ERRORS IN FIELD_BLOB::GET_KEY_IMAGE --echo # diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index a6054e0a28a..1226683ba03 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1374,10 +1374,82 @@ t2_1.b + 1 > 0 OR a = t2_1.c GROUP BY zzz; ---echo #TODO: in merge with 5.3 add original test suite +SET @save_optimizer_switch967242=@@optimizer_switch; +SET optimizer_switch = 'in_to_exists=on'; + +SELECT t2_1.b +FROM t1 JOIN t2 AS t2_1 JOIN t2 AS t2_2 + ON (t2_2.b = t2_1.b ) AND (t2_2.c = t2_1.c ) +WHERE + ( SELECT COUNT(*) FROM t2 ) IS NOT NULL + OR a = t2_1.c +GROUP BY t2_1.b; +SET optimizer_switch=@save_optimizer_switch967242; drop table t1, t2; +--echo # +--echo # Bug#12578908: SELECT SQL_BUFFER_RESULT OUTPUTS TOO MANY +--echo # ROWS WHEN GROUP IS OPTIMIZED AWAY +--echo # + +CREATE TABLE t1 (col1 int, col2 int) ; +INSERT INTO t1 VALUES (10,1),(11,7); + +CREATE TABLE t2 (col1 int, col2 int) ; +INSERT INTO t2 VALUES (10,8); + +let $q_body=t2.col2 FROM t2 JOIN t1 ON t1.col1 GROUP BY t2.col2; + +--echo +--eval EXPLAIN SELECT SQL_BUFFER_RESULT $q_body +--eval SELECT SQL_BUFFER_RESULT $q_body +--echo +--eval EXPLAIN SELECT $q_body +--eval SELECT $q_body + +--echo +DROP TABLE t1,t2; + +--echo # +--echo # BUG#12640437: USING SQL_BUFFER_RESULT RESULTS IN A +--echo # DIFFERENT QUERY OUTPUT +--echo # + +CREATE TABLE t1 ( + a int, + b varchar(1), + KEY (b,a) +); + +INSERT INTO t1 VALUES (1,NULL),(0,'a'); + +let $query= + SELECT SQL_BUFFER_RESULT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; + +--echo +--eval EXPLAIN $query +--echo +--eval $query + +let $query= SELECT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; +--echo +--eval EXPLAIN $query +--echo +--eval $query + +--echo +DROP TABLE t1; + +--echo # +--echo # LP bug#993726 Wrong result from a query with ALL subquery predicate in WHERE +--echo # +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0); +SELECT 1 FROM t1 WHERE 1 > ALL(SELECT 1 FROM t1 WHERE a!=0); +SELECT max(1) FROM t1 WHERE a!=0; +drop table t1; + --echo # End of 5.2 tests --echo # diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index a4469728c0f..44f4afd451b 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -1442,6 +1442,25 @@ DROP TABLE t1,t2; --echo End of 5.1 tests --echo # +--echo # LP BUG#994392: Wrong result with RIGHT/LEFT JOIN and ALL subquery +--echo # predicate in WHERE condition. +--echo # + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(9); +CREATE TABLE t2(b INT); +INSERT INTO t2 VALUES(8); +CREATE TABLE t3(c INT); +INSERT INTO t3 VALUES(3); +SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); +SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b < ALL(SELECT a FROM t1 WHERE a <= 7); +SELECT * FROM t2 RIGHT JOIN t3 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); +SELECT * FROM t3 LEFT JOIN t2 ON(c = b) WHERE b not in (SELECT a FROM t1 WHERE a <= 7); +drop table t1,t2,t3; + +--echo End of 5.2 tests + +--echo # --echo # LP bug #813447: LEFT JOIN with single-row inner table and --echo # a subquery in ON expression --echo # diff --git a/mysql-test/t/log_state.test b/mysql-test/t/log_state.test index d47044fbbee..e8f0bf8c511 100644 --- a/mysql-test/t/log_state.test +++ b/mysql-test/t/log_state.test @@ -332,13 +332,13 @@ CREATE TABLE t2 (b INT, PRIMARY KEY (b)); INSERT INTO t2 VALUES (3),(4); connect (con2,localhost,root,,); -INSERT INTO t1 VALUES (1+sleep(.01)),(2); -INSERT INTO t1 SELECT b+sleep(.01) from t2; -UPDATE t1 SET a=a+sleep(.01) WHERE a>2; -UPDATE t1 SET a=a+sleep(.01) ORDER BY a DESC; -UPDATE t2 set b=b+sleep(.01) limit 1; -UPDATE t1 SET a=a+sleep(.01) WHERE a in (SELECT b from t2); -DELETE FROM t1 WHERE a=a+sleep(.01) ORDER BY a LIMIT 2; +INSERT INTO t1 VALUES (1+sleep(.02)),(2); +INSERT INTO t1 SELECT b+sleep(.02) from t2; +UPDATE t1 SET a=a+sleep(.02) WHERE a>2; +UPDATE t1 SET a=a+sleep(.02) ORDER BY a DESC; +UPDATE t2 set b=b+sleep(.02) limit 1; +UPDATE t1 SET a=a+sleep(.02) WHERE a in (SELECT b from t2); +DELETE FROM t1 WHERE a=a+sleep(.02) ORDER BY a LIMIT 2; SELECT rows_examined,sql_text FROM mysql.slow_log; disconnect con2; diff --git a/mysql-test/t/mysqlslap.test b/mysql-test/t/mysqlslap.test index 757d2813483..69eaae76409 100644 --- a/mysql-test/t/mysqlslap.test +++ b/mysql-test/t/mysqlslap.test @@ -1,6 +1,5 @@ # Can't run test of external client with embedded server --source include/not_embedded.inc ---source include/not_windows.inc --exec $MYSQL_SLAP --silent --concurrency=5 --iterations=20 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql @@ -37,7 +36,7 @@ --exec $MYSQL_SLAP --only-print --delimiter=";" --query="select * from t1;select * from t2" --create="CREATE TABLE t1 (id int, name varchar(64)); create table t2(foo1 varchar(32), foo2 varchar(32)); INSERT INTO t1 VALUES (1, 'This is a test'); insert into t2 values ('test', 'test2')" --engine="heap,myisam" --post-query="SHOW TABLES" --pre-query="SHOW TABLES"; - --exec $MYSQL_SLAP --only-print --delimiter=";" --query="select * from t1;select * from t2" --create="CREATE TABLE t1 (id int, name varchar(64)); create table t2(foo1 varchar(32), foo2 varchar(32)); INSERT INTO t1 VALUES (1, 'This is a test'); insert into t2 values ('test', 'test2')" --engine="heap,myisam" --post-query="SHOW TABLES" --pre-query="SHOW TABLES" --number-of-queries=6 --commit=1; + --exec $MYSQL_SLAP --only-print --delimiter=";" --query="select * from t1;select * from t2" --create="CREATE TABLE t1 (id int, name varchar(64)); create table t2(foo1 varchar(32), foo2 varchar(32)); INSERT INTO t1 VALUES (1, 'This is a test'); insert into t2 values ('test', 'test2')" --engine="heap,myisam" --post-query="SHOW TABLES" --pre-query="SHOW TABLES" --number-of-queries=6 --commit=1 --exec $MYSQL_SLAP --silent --concurrency=5 --iterations=1 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write --detach=2 @@ -68,3 +67,9 @@ SHOW DATABASES; SHOW DATABASES; DROP DATABASE bug58090; +--echo # +--echo # Bug #11766072 - 59107: MYSQLSLAP CRASHES IF STARTED WITH NO ARGUMENTS ON WINDOWS +--echo # + +--replace_regex /queries: [0-9]+.[0-9]+/queries: TIME/ +--exec $MYSQL_SLAP diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index b2f64b8172b..eed45597855 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -3505,6 +3505,94 @@ execute st; show status like '%Handler_read%'; deallocate prepare st; - drop table t1; +--echo # +--echo # LP bug#993459 Execution of PS for a query with GROUP BY +--echo # returns wrong result (see also mysql bug#13805127) +--echo # + +PREPARE s1 FROM +" +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2 +"; + +--echo +SET @x = 1; +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:99345900:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +--echo +EXECUTE s1; + +--echo +SET @x = 2; +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +--echo +EXECUTE s1; + +--echo +SET @x = 1; +SELECT c1, t2.c2, count(c3) +FROM + ( + SELECT 3 as c2 FROM dual WHERE @x = 1 + UNION + SELECT 2 FROM dual WHERE @x = 1 OR @x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 +WHERE t2.c2 = t1.c2 +GROUP BY c1, c2; +--echo +EXECUTE s1; + +DEALLOCATE PREPARE s1; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 44fb3ba46f5..3a85ca3a3a9 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -4447,3 +4447,17 @@ DROP VIEW view_t1; SET optimizer_switch=@save_optimizer_switch; +--echo # +--echo # LP bug#994275 Assertion `real->type() == Item::FIELD_ITEM' failed +--echo # in add_not_null_conds(JOIN*) with JOIN, ZEROFILL column, PK +--echo # + +CREATE TABLE t1 ( a INT(6) ZEROFILL ); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 ( b INT PRIMARY KEY ); +INSERT INTO t2 VALUES (3),(4); +SELECT * FROM t1, t2 WHERE a=3 AND a=b; +drop table t1,t2; + +--echo End of 5.3 tests diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index ef99a8a521a..5d92fea0bf1 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -9105,3 +9105,43 @@ delimiter ;$ SELECT f1(); DROP FUNCTION f1; + +--echo # ------------------------------------------------------------------ +--echo # -- End of 5.1 tests +--echo # ------------------------------------------------------------------ + +--echo # +--echo # LP bug#993459 Execution of PS for a query with GROUP BY +--echo # returns wrong result (see also mysql bug#13805127) +--echo # +delimiter |; + +CREATE PROCEDURE p1(x INT UNSIGNED) +BEGIN + SELECT c1, t2.c2, count(c3) + FROM + ( + SELECT 3 as c2 FROM dual WHERE x = 1 + UNION + SELECT 2 FROM dual WHERE x = 1 OR x = 2 + ) AS t1, + ( + SELECT '2012-03-01 01:00:00' AS c1, 3 as c2, 1 as c3 FROM dual + UNION + SELECT '2012-03-01 02:00:00', 3, 2 FROM dual + UNION + SELECT '2012-03-01 01:00:00', 2, 1 FROM dual + ) AS t2 + WHERE t2.c2 = t1.c2 + GROUP BY c1, c2 + ; +END| + +delimiter ;| + +--echo +CALL p1(1); +CALL p1(2); +CALL p1(1); + +DROP PROCEDURE p1; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 6e98c064d94..05dc8161db3 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4723,6 +4723,18 @@ SELECT 1 FROM DROP TABLE t1; --echo # +--echo # Bug#13721076 CRASH WITH TIME TYPE/TIMESTAMP() AND WARNINGS IN SUBQUERY +--echo # + +CREATE TABLE t1(a TIME NOT NULL); +INSERT INTO t1 VALUES ('00:00:32'); + +SELECT 1 FROM t1 WHERE a > +(SELECT timestamp(a) AS a FROM t1); + +DROP TABLE t1; + +--echo # --echo # No BUG#, a case brought from 5.2's innodb_mysql_lock.test --echo # diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index 27ec36854f4..e390c9e2211 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -253,6 +253,62 @@ SELECT * FROM t1 WHERE b < (SELECT CAST(a as date) FROM t1 GROUP BY a); DROP TABLE t1; --echo # +--echo # Bug #11766300 59387: FAILING ASSERTION: CURSOR->POS_STATE == 1997660512 (BTR_PCUR_IS_POSITIONE +--echo # + +CREATE TABLE t1 (a INT) ENGINE=INNODB; +INSERT INTO t1 VALUES (0); +CREATE TABLE t2 (d BINARY(2), PRIMARY KEY (d(1)), UNIQUE KEY (d)) ENGINE=INNODB; + +SELECT 1 FROM t1 WHERE NOT EXISTS +(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); + +EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS +(SELECT 1 FROM t2 WHERE d = (SELECT d FROM t2 WHERE a >= 1) ORDER BY d); + +DROP TABLE t2; + +CREATE TABLE t2 (b INT, c INT, UNIQUE KEY (b), UNIQUE KEY (b, c )) ENGINE=INNODB; +INSERT INTO t2 VALUES (1, 1); + +SELECT 1 FROM t1 +WHERE a != (SELECT 1 FROM t2 WHERE a <=> b OR a > '' AND 6 = 7 ORDER BY b, c); + +DROP TABLE t1, t2; + +--echo # +--echo # Bug #13639204 64111: CRASH ON SELECT SUBQUERY WITH NON UNIQUE +--echo # INDEX +--echo # +CREATE TABLE t1 ( +id int +) ENGINE=InnoDB; +INSERT INTO t1 (id) VALUES (11); + +CREATE TABLE t2 ( +t1_id int, +position int, +KEY t1_id (t1_id), +KEY t1_id_position (t1_id,position) +) ENGINE=InnoDB; + +let $query=SELECT +(SELECT position FROM t2 +WHERE t2.t1_id = t1.id +ORDER BY t2.t1_id , t2.position +LIMIT 10,1 +) AS maxkey +FROM t1 +LIMIT 1; + +eval EXPLAIN $query; +eval $query; + +DROP TABLE t1,t2; + +--echo End of 5.1 tests + +--echo # --echo # lp:827416 Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries --echo # diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 7f1181bb562..63bb29a2e13 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2398,6 +2398,52 @@ DROP TABLE t1; set @@join_cache_level= @tmp_jcl_978479; set @@optimizer_switch= @tmp_os_978479; +--echo # +--echo # BUG#998236: Assertion failure or valgrind errors at best_access_path ... +--echo # +CREATE TABLE t1 (a1 VARCHAR(3), a2 VARCHAR(35), KEY(a1,a2)) ENGINE=MyISAM; +INSERT INTO t1 VALUES +('USA','Arvada'),('USA','Athens'),('USA','Atlanta'),('USA','Augusta'), +('USA','Aurora'),('USA','Aurora'),('USA','Austin'),('USA','Bakersfield'), +('USA','Baltimore'),('USA','Baton'),('USA','Beaumont'),('USA','Bellevue'), +('USA','Berkeley'),('USA','Billings'),('USA','Birmingham'),('USA','Boise'), +('USA','Boston'),('USA','Boulder'),('USA','Bridgeport'),('USA','Brockton'), +('USA','Brownsville'),('USA','Buffalo'),('USA','Burbank'),('USA','Cambridge'), +('USA','Cape'),('USA','Carrollton'),('USA','Carson'),('USA','Cary'), +('USA','Cedar'),('USA','Chandler'),('USA','Charleston'),('USA','Charlotte'), +('USA','Chattanooga'),('USA','Chesapeake'),('USA','Chicago'),('USA','Chula'), +('USA','Cincinnati'),('USA','Citrus'),('USA','Clarksville'),('USA','Clearwater'), +('USA','Cleveland'),('USA','Colorado'),('USA','Columbia'),('USA','Columbus'), +('USA','Columbus'),('USA','Compton'),('USA','Concord'),('USA','Coral'), +('USA','Corona'),('USA','Corpus'),('USA','Costa'),('USA','Dallas'),('USA','Daly'), +('USA','Davenport'),('USA','Dayton'),('USA','Denver'),('USA','DesMoines'), +('USA','Detroit'),('USA','Downey'),('USA','Durham'),('USA','East'),('USA','ElCajon'), +('USA','ElMonte'),('USA','ElPaso'),('USA','Elgin'),('USA','Elizabeth'), +('USA','Erie'),('USA','Escondido'),('USA','Eugene'),('USA','Evansville'), +('USA','Fairfield'),('USA','Fall'),('USA','Fayetteville'),('USA','Flint'), +('USA','Fontana'),('USA','FortCollins'),('USA','FortLauderdale'),('USA','FortWayne'), +('USA','FortWorth'),('USA','Fremont'),('USA','Fresno'),('USA','Fullerton'), +('USA','Gainesville'),('USA','GardenGrove'),('USA','Garland'),('USA','Gary'), +('USA','Gilbert'),('USA','Glendale'),('USA','Glendale'),('USA','GrandPrairie'), +('USA','GrandRapids'),('USA','Green Bay'),('USA','Greensboro'),('USA','Hampton'), +('USA','Hartford'),('USA','Hayward'),('USA','Henderson'),('USA','Hialeah'), +('USA','Inglewood'),('USA','Livonia'); + +CREATE TABLE t3 (c1 VARCHAR(3), c2 VARCHAR(16), PRIMARY KEY (c1,c2)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('USA','Chinese'), ('USA','English'); + +# Not reproducible with 2 rows +CREATE TABLE t2 (b1 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1); + +SELECT alias1.a1 AS field1 +FROM t1 AS alias1, t1 AS alias2 +WHERE alias1.a2 IN ( SELECT a1 FROM t1, t2 ) +AND alias1.a1 IS NULL +AND ( alias1.a1, alias2.a1 ) IN ( SELECT c1, c1 FROM t3 ) +GROUP BY field1; + +DROP TABLE t1,t3,t2; # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index a077e9b5af5..4c40b2b5487 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1664,6 +1664,19 @@ EXECUTE ps; DROP VIEW v2; DROP TABLE t1, t2; +--echo # +--echo # BUG#1000269: Wrong result (extra rows) with semijoin+materialization, IN subqueries, join_cache_level>0 +--echo # +CREATE TABLE t1 (a1 VARCHAR(1), a2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('b','b'),('e','e'); + +CREATE TABLE t2 (b1 VARCHAR(1), b2 VARCHAR(1), KEY(b1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('v','v'),('s','s'),('l','l'), ('y','y'),('c','c'),('i','i'); + +SELECT * FROM t1, t2 WHERE b1 IN ( SELECT b2 FROM t2 WHERE b1 > 'o' ) AND ( b1 < 'l' OR a1 IN ('b','c') ); + +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; diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index 26b98c9023b..2cca6f501f0 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -504,6 +504,28 @@ WHERE t3.f2 ; DROP TABLE t1,t2,t3,t4,t5; --echo # +--echo # BUG#997747: Assertion `join->best_read < ((double)1.79..5e+308L)' +--echo # failed in greedy_search with LEFT JOINs and unique keys +--echo # +CREATE TABLE t1 (a1 INT); +CREATE TABLE t2 (b1 INT); +CREATE TABLE t3 (c1 INT, UNIQUE KEY(c1)); +CREATE TABLE t4 (d1 INT, UNIQUE KEY(d1)); +CREATE TABLE t5 (e1 INT); + +INSERT INTO t1 VALUES (1),(2); +INSERT INTO t2 VALUES (2),(3); +INSERT INTO t3 VALUES (3),(4); +INSERT INTO t4 VALUES (4),(5); +INSERT INTO t5 VALUES (5),(6); + +SELECT a1 FROM t1 LEFT JOIN t2 LEFT JOIN t3 LEFT JOIN t4 +ON c1 = d1 ON d1 = b1 ON a1 = b1 +LEFT JOIN t5 ON a1 = e1 ; + +DROP TABLE t1,t2,t3,t4,t5; + +--echo # --echo # BUG#884184: Wrong result with RIGHT JOIN + derived_merge --echo # CREATE TABLE t1 (a int(11), b varchar(1)) ; diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 6a64343b609..fa23d118634 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -404,4 +404,13 @@ SELECT f1, f2 FROM t1 ORDER BY f2; DROP TRIGGER trg1; DROP TABLE t1; + +--echo # +--echo # Bug #12408412: GROUP_CONCAT + ORDER BY + INPUT/OUTPUT +--echo # SAME USER VARIABLE = CRASH +--echo # + +SET @bug12408412=1; +SELECT GROUP_CONCAT(@bug12408412 ORDER BY 1) INTO @bug12408412; + --echo End of 5.5 tests |