Hi Varun, On Fri, Jan 13, 2017 at 08:22:49PM +0530, Varun wrote:

revision-id: 905aac9c78068224839db551a84ed080167d1657 (mariadb-10.1.20-37-g905aac9) parent(s): ebb8c9fb26f86cff8c0d81bd2415f415cef952bb author: Varun Gupta committer: Varun Gupta timestamp: 2017-01-13 20:17:33 +0530 message:

MDEV-10232: Scalar result of subquery changes after adding an outer select stmt

In a subquery, we don't have to maintain order Added a fix such that order is considered when there is a limit clause.

--- mysql-test/r/subselect4.result | 12 ++++++++++++ mysql-test/t/subselect4.test | 10 ++++++++++ sql/sql_select.cc | 2 +- 3 files changed, 23 insertions(+), 1 deletion(-)

diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 87645d1..7764783 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2401,5 +2401,17 @@ SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1; x 0 drop table t1; +# +# MDEV-10232 Scalar result of subquery changes after adding an outer select stmt +# +create table t1(c1 int, c2 int, primary key(c2)); +insert into t1 values(2,1),(1,2); +select (select c1 from t1 group by c1,c2 order by c1 limit 1) as x; +x +1 +(select c1 from t1 group by c1,c2 order by c1 limit 1); +c1 +1 +drop table t1; SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 4eb9701..787c579 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1956,5 +1956,15 @@ SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1;

drop table t1;

+--echo # +--echo # MDEV-10232 Scalar result of subquery changes after adding an outer select stmt +--echo # + +create table t1(c1 int, c2 int, primary key(c2)); +insert into t1 values(2,1),(1,2); +select (select c1 from t1 group by c1,c2 order by c1 limit 1) as x; +(select c1 from t1 group by c1,c2 order by c1 limit 1); +drop table t1; + SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3f06ec8..13eda46 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1626,7 +1626,7 @@ JOIN::optimize_inner()

Above this change we still have a comment with this statement:

...

- if we are in a subquery, we don't have to maintain order

Please adjust it accordingly.

if (!order || test_if_subpart(group_list, order)) { if (skip_sort_order || - select_lex->master_unit()->item) // This is a subquery + select_lex->master_unit()->item && !select_limit) // This is a subquery order= NULL; else order= group_list;

So I'm trying with the patch: MariaDB [test]> explain select (select c1 from t1 group by c1,c2 order by c1 limit 1) as x; +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using filesort | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 2 rows in set (20.08 sec) Good. What if there is no LIMIT clause? MariaDB [test]> explain select (select c1 from t1 group by c1,c2 order by c1 ) as x; +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using filesort | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ 2 rows in set (6.06 sec) Bad. "Using filesort" should have been removed. The reason it fails is that "!select_limit" is not the right way to check the presense of LIMIT clause. Without limit, I have: (gdb) p/x select_limit $15 = 0xffffffffffffffff (the name of the constant to use is "HA_POS_ERROR") Another issue: I get this warning with the new code: || /home/psergey/dev-git/10.1/sql/sql_select.cc: In member function 'int JOIN::optimize_inner()': sql_select.cc|1629 col 45| warning: suggest parentheses around '&&' within '||' [-Wparentheses] || select_lex->master_unit()->item && !select_limit) // This is a subquery || ^ BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog