diff options
44 files changed, 1060 insertions, 74 deletions
diff --git a/client/mysqltest.cc b/client/mysqltest.cc index 9e459743713..b93c9426deb 100644 --- a/client/mysqltest.cc +++ b/client/mysqltest.cc @@ -816,6 +816,9 @@ void handle_error(struct st_command*, void handle_no_error(struct st_command*); void revert_properties(); +static void handle_no_active_connection(struct st_command* command, + struct st_connection *cn, DYNAMIC_STRING *ds); + #ifdef EMBEDDED_LIBRARY #define EMB_SEND_QUERY 1 @@ -2562,6 +2565,19 @@ void var_query_set(VAR *var, const char *query, const char** query_end) DBUG_ENTER("var_query_set"); LINT_INIT(res); + if (!mysql) + { + struct st_command command; + memset(&command, 0, sizeof(command)); + command.query= (char*)query; + command.first_word_len= (*query_end - query); + command.first_argument= command.query + command.first_word_len; + command.end= (char*)*query_end; + command.abort_on_error= 1; /* avoid uninitialized variables */ + handle_no_active_connection(&command, cur_con, &ds_res); + DBUG_VOID_RETURN; + } + /* Only white space or ) allowed past ending ` */ while (end > query && *end != '`') { @@ -2741,6 +2757,12 @@ void var_set_query_get_value(struct st_command *command, VAR *var) DBUG_ENTER("var_set_query_get_value"); LINT_INIT(res); + if (!mysql) + { + handle_no_active_connection(command, cur_con, &ds_res); + DBUG_VOID_RETURN; + } + strip_parentheses(command); DBUG_PRINT("info", ("query: %s", command->query)); check_command_args(command, command->first_argument, query_get_value_args, @@ -2911,6 +2933,7 @@ void eval_expr(VAR *v, const char *p, const char **p_end, command.first_word_len= len; command.first_argument= command.query + len; command.end= (char*)*p_end; + command.abort_on_error= 1; /* avoid uninitialized variables */ var_set_query_get_value(&command, v); DBUG_VOID_RETURN; } @@ -7591,6 +7614,22 @@ int append_warnings(DYNAMIC_STRING *ds, MYSQL* mysql) /* + Handle situation where query is sent but there is no active connection + (e.g directly after disconnect). + + We emulate MySQL-compatible behaviour of sending something on a closed + connection. +*/ +static void handle_no_active_connection(struct st_command *command, + struct st_connection *cn, DYNAMIC_STRING *ds) +{ + handle_error(command, 2006, "MySQL server has gone away", "000000", ds); + cn->pending= FALSE; + var_set_errno(2006); +} + + +/* Run query using MySQL C API SYNOPSIS @@ -7616,11 +7655,7 @@ void run_query_normal(struct st_connection *cn, struct st_command *command, if (!mysql) { - /* Emulate old behaviour of sending something on a closed connection */ - handle_error(command, 2006, "MySQL server has gone away", - "000000", ds); - cn->pending= FALSE; - var_set_errno(2006); + handle_no_active_connection(command, cn, ds); DBUG_VOID_RETURN; } diff --git a/mysql-test/extra/rpl_tests/rpl_row_basic.test b/mysql-test/extra/rpl_tests/rpl_row_basic.test index 8b070999ecf..c7570de3aba 100644 --- a/mysql-test/extra/rpl_tests/rpl_row_basic.test +++ b/mysql-test/extra/rpl_tests/rpl_row_basic.test @@ -2,13 +2,6 @@ # 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. # @@ -16,11 +9,11 @@ call mtr.add_suppression("Can't find record in 't.'"); #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 -sync_slave_with_master; connection slave; -create temporary table stats -select variable_name as n, -variable_value as v from information_schema.global_status - where variable_name in ('com_commit','com_insert','com_delete','com_update'); +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 ; @@ -60,11 +53,27 @@ SELECT * FROM t1 ORDER BY C1,C2; #Testing command counters -AFTER #Storing the after counts of Slave connection slave; - -insert stats -select variable_name, variable_value from information_schema.global_status - where variable_name in ('com_commit','com_insert','com_delete','com_update'); -select n, sum(v) from stats group by n; +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. 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/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/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/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_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_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/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/rpl/r/rpl_row_basic_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result index 339fa43921c..6e2415ebcd8 100644 --- a/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_basic_2myisam.result @@ -1,8 +1,5 @@ include/master-slave.inc [connection master] -create temporary table stats -select variable_name as n, -variable_value as v from information_schema.global_status -where variable_name in ('com_commit','com_insert','com_delete','com_update'); CREATE TABLE t1 (C1 CHAR(1), C2 CHAR(1), INDEX (C1)) ENGINE = 'MYISAM' ; SELECT * FROM t1; C1 C2 @@ -52,15 +49,10 @@ A B A I X Y X Z -insert stats -select variable_name, variable_value from information_schema.global_status -where variable_name in ('com_commit','com_insert','com_delete','com_update'); -select n, sum(v) from stats group by n; -n sum(v) -COM_COMMIT 4 -COM_DELETE 1 -COM_INSERT 2 -COM_UPDATE 1 +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/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/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/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/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_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/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index b9740c7af03..de62bc49930 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -252,6 +252,7 @@ public: { with_subselect= true; } bool fix_fields(THD *, Item **); bool fix_left(THD *thd, Item **ref); + table_map not_null_tables() const { return 0; } bool is_null(); longlong val_int(); void cleanup(); @@ -503,6 +504,7 @@ public: {} virtual void top_level_item() { abort_on_null= 1; } bool is_top_level_item() { return abort_on_null; } + table_map not_null_tables() const { return 0; } longlong val_int(); enum Functype functype() const { return NOT_ALL_FUNC; } const char *func_name() const { return "<not>"; } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index c6ee117213d..965250170a5 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -13271,7 +13271,7 @@ int QUICK_GROUP_MIN_MAX_SELECT::next_min() if (min_max_arg_part && min_max_arg_part->field->is_null()) { /* Find the first subsequent record without NULL in the MIN/MAX field. */ - key_copy(tmp_record, record, index_info, 0); + key_copy(tmp_record, record, index_info, max_used_key_length); result= file->ha_index_read_map(record, tmp_record, make_keypart_map(real_key_parts), HA_READ_AFTER_KEY); diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 9bf1aaf4039..bab061a2ac7 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -3176,6 +3176,25 @@ at_sjmat_pos(const JOIN *join, table_map remaining_tables, const JOIN_TAB *tab, } +/* + Re-calculate values of join->best_positions[start..end].prefix_record_count +*/ + +static void recalculate_prefix_record_count(JOIN *join, uint start, uint end) +{ + for (uint j= start; j < end ;j++) + { + double prefix_count; + if (j == join->const_tables) + prefix_count= 1.0; + else + prefix_count= join->best_positions[j-1].prefix_record_count * + join->best_positions[j-1].records_read; + + join->best_positions[j].prefix_record_count= prefix_count; + } +} + /* Fix semi-join strategies for the picked join order @@ -3245,6 +3264,8 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) sjm->is_sj_scan= FALSE; memcpy(pos - sjm->tables + 1, sjm->positions, sizeof(POSITION) * sjm->tables); + recalculate_prefix_record_count(join, tablenr - sjm->tables + 1, + tablenr); first= tablenr - sjm->tables + 1; join->best_positions[first].n_sj_tables= sjm->tables; join->best_positions[first].sj_strategy= SJ_OPT_MATERIALIZE; @@ -3258,6 +3279,7 @@ void fix_semijoin_strategies_for_picked_join_order(JOIN *join) first= pos->sjmat_picker.sjm_scan_last_inner - sjm->tables + 1; memcpy(join->best_positions + first, sjm->positions, sizeof(POSITION) * sjm->tables); + recalculate_prefix_record_count(join, first, first + sjm->tables); join->best_positions[first].sj_strategy= SJ_OPT_MATERIALIZE_SCAN; join->best_positions[first].n_sj_tables= sjm->tables; /* diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 430c201d3e6..cbec039b3e4 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -415,7 +415,7 @@ int opt_sum_query(THD *thd, } removed_tables|= table->map; } - else if (!expr->const_item() || !is_exact_count) + else if (!expr->const_item() || !is_exact_count || conds) { /* The optimization is not applicable in both cases: @@ -425,6 +425,8 @@ int opt_sum_query(THD *thd, NULL if the query does not return any rows. Thus, if we are not able to determine if the query returns any rows, we can't apply the optimization and replace MIN/MAX with a constant. + (c) there is a WHERE clause. The WHERE conditions may result in + an empty result, but the clause cannot be taken into account here. */ const_result= 0; break; diff --git a/sql/slave.cc b/sql/slave.cc index 94af12472c1..5292595d157 100644 --- a/sql/slave.cc +++ b/sql/slave.cc @@ -1829,7 +1829,9 @@ Waiting for the slave SQL thread to free enough relay log space"); #endif if (rli->sql_force_rotate_relay) { + mysql_mutex_lock(&active_mi->data_lock); rotate_relay_log(rli->mi); + mysql_mutex_unlock(&active_mi->data_lock); rli->sql_force_rotate_relay= false; } @@ -5396,7 +5398,10 @@ int rotate_relay_log(Master_info* mi) output in SHOW SLAVE STATUS meanwhile. So we harvest now. If the log is closed, then this will just harvest the last writes, probably 0 as they probably have been harvested. + + Note that it needs to be protected by mi->data_lock. */ + mysql_mutex_assert_owner(&mi->data_lock); rli->relay_log.harvest_bytes_written(&rli->log_space_total); end: DBUG_RETURN(error); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 51b84f309c7..688108555d6 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1987,8 +1987,8 @@ next: ("convert merged to materialization to resolve the conflict")); derived->change_refs_to_fields(); derived->set_materialized_derived(); + goto retry; } - goto retry; } DBUG_RETURN(res); } diff --git a/sql/sql_reload.cc b/sql/sql_reload.cc index 1f3b1effff3..914b9026014 100644 --- a/sql/sql_reload.cc +++ b/sql/sql_reload.cc @@ -24,6 +24,7 @@ #include "sql_db.h" // my_dbopt_cleanup #include "hostname.h" // hostname_cache_refresh #include "sql_repl.h" // reset_master, reset_slave +#include "rpl_mi.h" // Master_info::data_lock #include "debug_sync.h" static void disable_checkpoints(THD *thd); @@ -156,10 +157,10 @@ bool reload_acl_and_cache(THD *thd, unsigned long options, if (options & REFRESH_RELAY_LOG) { #ifdef HAVE_REPLICATION - mysql_mutex_lock(&LOCK_active_mi); + mysql_mutex_lock(&active_mi->data_lock); if (rotate_relay_log(active_mi)) *write_to_binlog= -1; - mysql_mutex_unlock(&LOCK_active_mi); + mysql_mutex_unlock(&active_mi->data_lock); #endif } #ifdef HAVE_QUERY_CACHE diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 515a21505d9..f88a14c3312 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -114,7 +114,8 @@ static store_key *get_store_key(THD *thd, uint maybe_null); static bool make_outerjoin_info(JOIN *join); static Item* -make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, table_map sjm_tables); +make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, + table_map sjm_tables, bool inside_or_clause); static bool make_join_select(JOIN *join,SQL_SELECT *select,COND *item); static void revise_cache_usage(JOIN_TAB *join_tab); static bool make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after); @@ -1333,8 +1334,19 @@ JOIN::optimize() store_key *key_copy= tab->ref.key_copy[key_copy_index]; if (key_copy->type() == store_key::FIELD_STORE_KEY) { - store_key_field *field_copy= ((store_key_field *)key_copy); - field_copy->change_source_field((Item_field *) item); + if (item->basic_const_item()) + { + /* It is constant propagated here */ + tab->ref.key_copy[key_copy_index]= + new store_key_const_item(*tab->ref.key_copy[key_copy_index], + item); + } + else + { + store_key_field *field_copy= ((store_key_field *)key_copy); + DBUG_ASSERT(item->type() == Item::FIELD_ITEM); + field_copy->change_source_field((Item_field *) item); + } } } key_copy_index++; @@ -1523,7 +1535,6 @@ JOIN::optimize() simple_order=1; select_distinct= 0; // No need in distinct for 1 row group_optimized_away= 1; - implicit_grouping= TRUE; } calc_group_buffer(this, group_list); @@ -8009,7 +8020,31 @@ JOIN::make_simple_join(JOIN *parent, TABLE *temp_table) tmp_table_param.copy_field= tmp_table_param.copy_field_end=0; first_record= sort_and_group=0; send_records= (ha_rows) 0; - group= 0; + + if (group_optimized_away && !tmp_table_param.precomputed_group_by) + { + /* + If grouping has been optimized away, a temporary table is + normally not needed unless we're explicitly requested to create + one (e.g. due to a SQL_BUFFER_RESULT hint or INSERT ... SELECT). + + In this case (grouping was optimized away), temp_table was + created without a grouping expression and JOIN::exec() will not + perform the necessary grouping (by the use of end_send_group() + or end_write_group()) if JOIN::group is set to false. + + There is one exception: if the loose index scan access method is + used to read into the temporary table, grouping and aggregate + functions are handled. + */ + // the temporary table was explicitly requested + DBUG_ASSERT(test(select_options & OPTION_BUFFER_RESULT)); + // the temporary table does not have a grouping expression + DBUG_ASSERT(!temp_table->group); + } + else + group= false; + row_limit= unit->select_limit_cnt; do_send_rows= row_limit ? 1 : 0; @@ -8138,6 +8173,16 @@ static void add_not_null_conds(JOIN *join) Item *item= tab->ref.items[keypart]; Item *notnull; Item *real= item->real_item(); + if (real->basic_const_item()) + { + /* + It could be constant instead of field after constant + propagation. + */ + DBUG_ASSERT(real->is_expensive() || // prevent early expensive eval + !real->is_null()); // NULLs are not propagated + continue; + } DBUG_ASSERT(real->type() == Item::FIELD_ITEM); Item_field *not_null_item= (Item_field*)real; JOIN_TAB *referred_tab= not_null_item->field->table->reginfo.join_tab; @@ -8519,7 +8564,8 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) if (tab->bush_children) { // Reached the materialization tab - tmp= make_cond_after_sjm(cond, cond, save_used_tables, used_tables); + tmp= make_cond_after_sjm(cond, cond, save_used_tables, used_tables, + /*inside_or_clause=*/FALSE); used_tables= save_used_tables | used_tables; save_used_tables= 0; } @@ -17732,13 +17778,14 @@ make_cond_for_table_from_pred(THD *thd, Item *root_cond, Item *cond, */ static COND * make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, - table_map sjm_tables) + table_map sjm_tables, bool inside_or_clause) { /* We assume that conditions that refer to only join prefix tables or sjm_tables have already been checked. */ - if ((!(cond->used_tables() & ~tables) || + if (!inside_or_clause && + (!(cond->used_tables() & ~tables) || !(cond->used_tables() & ~sjm_tables))) return (COND*) 0; // Already checked @@ -17755,7 +17802,8 @@ make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, Item *item; while ((item=li++)) { - Item *fix=make_cond_after_sjm(root_cond, item, tables, sjm_tables); + Item *fix=make_cond_after_sjm(root_cond, item, tables, sjm_tables, + inside_or_clause); if (fix) new_cond->argument_list()->push_back(fix); } @@ -17785,7 +17833,8 @@ make_cond_after_sjm(Item *root_cond, Item *cond, table_map tables, Item *item; while ((item=li++)) { - Item *fix= make_cond_after_sjm(root_cond, item, tables, 0L); + Item *fix= make_cond_after_sjm(root_cond, item, tables, sjm_tables, + /*inside_or_clause= */TRUE); if (!fix) return (COND*) 0; // Always true new_cond->argument_list()->push_back(fix); @@ -18373,7 +18422,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, KEYUSE *keyuse= tab->keyuse; while (keyuse->key != new_ref_key && keyuse->table == tab->table) keyuse++; - if (create_ref_for_key(tab->join, tab, keyuse, FALSE, (tab->join->const_table_map | OUTER_REF_TABLE_BIT))) diff --git a/sql/sql_select.h b/sql/sql_select.h index 08ddc27e204..c4553148cc6 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1465,6 +1465,11 @@ public: to_field=field_arg->new_key_field(thd->mem_root, field_arg->table, ptr, null, 1); } + store_key(store_key &arg) + :Sql_alloc(), null_key(arg.null_key), to_field(arg.to_field), + null_ptr(arg.null_ptr), err(arg.err) + + {} virtual ~store_key() {} /** Not actually needed */ virtual enum Type type() const=0; virtual const char *name() const=0; @@ -1569,6 +1574,10 @@ public: null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ? &err : (uchar*) 0, length), item(item_arg), use_value(val) {} + store_key_item(store_key &arg, Item *new_item, bool val) + :store_key(arg), item(new_item), use_value(val) + {} + enum Type type() const { return ITEM_STORE_KEY; } const char *name() const { return "func"; } @@ -1614,11 +1623,14 @@ public: store_key_const_item(THD *thd, Field *to_field_arg, uchar *ptr, uchar *null_ptr_arg, uint length, Item *item_arg) - :store_key_item(thd, to_field_arg,ptr, + :store_key_item(thd, to_field_arg, ptr, null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ? &err : (uchar*) 0, length, item_arg, FALSE), inited(0) { } + store_key_const_item(store_key &arg, Item *new_item) + :store_key_item(arg, new_item, FALSE), inited(0) + {} enum Type type() const { return CONST_ITEM_STORE_KEY; } const char *name() const { return "const"; } |