diff options
| -rw-r--r-- | mysql-test/r/insert_select.result | 31 | ||||
| -rw-r--r-- | mysql-test/t/insert_select.test | 25 | ||||
| -rw-r--r-- | sql/sql_select.cc | 3 |
3 files changed, 57 insertions, 2 deletions
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 08a865e0b94..8cb94072818 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -688,7 +688,16 @@ ERROR 42S22: Unknown column 't2.x' in 'field list' drop table t1,t2; CREATE TABLE t1 (a int PRIMARY KEY); INSERT INTO t1 values (1), (2); +flush status; INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1; +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 1 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 1 DROP TABLE t1; CREATE TABLE t1 (x int, y int); CREATE TABLE t2 (z int, y int); @@ -773,3 +782,25 @@ d 20 20 DROP TABLE t1,t2; +CREATE TABLE t1 ( +id INT AUTO_INCREMENT PRIMARY KEY, +prev_id INT, +join_id INT DEFAULT 0); +INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2); +SELECT * FROM t1; +id prev_id join_id +1 NULL 0 +2 1 0 +3 2 0 +CREATE TABLE t2 (join_id INT); +INSERT INTO t2 (join_id) VALUES (0); +INSERT INTO t1 (prev_id) SELECT id +FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id +ORDER BY id DESC LIMIT 1; +SELECT * FROM t1; +id prev_id join_id +1 NULL 0 +2 1 0 +3 2 0 +4 3 0 +DROP TABLE t1,t2; diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index bbc51be6dc9..bf6dae96847 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -233,7 +233,9 @@ drop table t1,t2; CREATE TABLE t1 (a int PRIMARY KEY); INSERT INTO t1 values (1), (2); +flush status; INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1; +show status like 'Handler_read%'; DROP TABLE t1; @@ -332,3 +334,26 @@ INSERT INTO t2 (d) SELECT * FROM t2; DROP TABLE t1,t2; + +# +# Bug #29095: incorrect pushing of LIMIT into the temporary +# table ignoring ORDER BY clause +# + +CREATE TABLE t1 ( + id INT AUTO_INCREMENT PRIMARY KEY, + prev_id INT, + join_id INT DEFAULT 0); + +INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2); +SELECT * FROM t1; + +CREATE TABLE t2 (join_id INT); +INSERT INTO t2 (join_id) VALUES (0); + +INSERT INTO t1 (prev_id) SELECT id + FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id + ORDER BY id DESC LIMIT 1; +SELECT * FROM t1; + +DROP TABLE t1,t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f4450bf393f..85df73bb31a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1347,8 +1347,7 @@ JOIN::optimize() there are aggregate functions, because in all these cases we need all result rows. */ - ha_rows tmp_rows_limit= ((order == 0 || skip_sort_order || - test(select_options & OPTION_BUFFER_RESULT)) && + ha_rows tmp_rows_limit= ((order == 0 || skip_sort_order) && !tmp_group && !thd->lex->current_select->with_sum_func) ? select_limit : HA_POS_ERROR; |