Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10232

Scalar result of subquery changes after adding an outer select stmt

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1.10, 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.1.22, 10.2.4
    • Optimizer
    • None
    • 10.1.15, 10.1.21

    Description

      Output：
      ===

      mysql> create table t1(c1 int, c2 int, primary key(c2));
      		 
      Query OK, 0 rows affected (0.01 sec)
      		 
       
      		 
      mysql> insert into t1 values(2,1),(1,2);
      		 
      (select c1 frQuery OK, 2 rows affected (0.00 sec)
      		 
      Records: 2  Duplicates: 0  Warnings: 0
      		 
       
      		 
      mysql> select (select c1 from t1 group by c1,c2 order by c1 limit 1) as x;
      		 
      +------+
      		 
      | x    |
      		 
      +------+
      		 
      |    2 |
      		 
      +------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      mysql> (select c1 from t1 group by c1,c2 order by c1 limit 1);
      		 
      +------+
      		 
      | c1   |
      		 
      +------+
      		 
      |    1 |
      		 
      +------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      mysql> select version();
      		 
      +---------------------+
      		 
      | version()           |
      		 
      +---------------------+
      		 
      | 10.1.10-MariaDB-log |
      		 
      +---------------------+
      		 
      1 row in set (0.00 sec)

      Recreate:
      ===

       
      		 
      drop table t1;
      		 
      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);

      Problem:
      ===
      1 is expected for both queries.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          Thanks for the report and the test case.

          Reproducible on MariaDB 10.0, 10.1, 10.2, not reproducible on MariaDB 5.5.
          Reproducible on MySQL 5.6, but not on 5.5 or 5.7.

          elenst Elena Stepanova added a comment - Thanks for the report and the test case. Reproducible on MariaDB 10.0, 10.1, 10.2, not reproducible on MariaDB 5.5. Reproducible on MySQL 5.6, but not on 5.5 or 5.7.
          varun Varun Gupta (Inactive) added a comment - - edited 

          EXPLAIN 
          (select c1 from t1 group by c1,c2 order by c1 limit 1);
          		 
          id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
          		 
          1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using filesort
          		 
          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
          		 
           

          varun Varun Gupta (Inactive) added a comment - - edited EXPLAIN (select c1 from t1 group by c1,c2 order by c1 limit 1 ); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using filesort 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  
          psergei Sergei Petrunia added a comment -

          Review feedback provided over email

          psergei Sergei Petrunia added a comment - Review feedback provided over email
          psergei Sergei Petrunia added a comment -

          Ok to push

          psergei Sergei Petrunia added a comment - Ok to push

          People

            varun Varun Gupta (Inactive)
            dylan Dylan Su
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.