maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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