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

Get unexpected `Empty Set` for correlated subquery with aggregate functions

    XMLWordPrintable

Details

    • 10.1.15, 10.1.17-1

    Description

      Description:
      Output:
      =====

      mysql> create table t1(c1 int, c2 int, c3 int);
      Query OK, 0 rows affected (0.00 sec)
       
      mysql> insert into t1 values(1,1,1),(2,2,2),(3,3,3);
      Query OK, 3 rows affected (0.00 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      mysql> select * from t1;
      +------+------+------+
      | c1   | c2   | c3   |
      +------+------+------+
      |    1 |    1 |    1 |
      |    2 |    2 |    2 |
      |    3 |    3 |    3 |
      +------+------+------+
      3 rows in set (0.00 sec)
       
      mysql> create table t2(c1 int, c2 int);
      Query OK, 0 rows affected (0.00 sec)
       
      mysql> insert into t2 values(2,2);
      Query OK, 1 row affected (0.00 sec)
       
      mysql> select * from t2;
      +------+------+
      | c1   | c2   |
      +------+------+
      |    2 |    2 |
      +------+------+
      1 row in set (0.00 sec)
       
      mysql> select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt));
      Empty set (0.00 sec)
       
      mysql> select version();
      +-----------------+
      | version()       |
      +-----------------+
      | 10.1.10-MariaDB |
      +-----------------+
      1 row in set (0.00 sec)
      
      

      Problem:
      =====
      Since (select t.c1 as c from t2 ...) can only produce one value 2, the condition c1 > 2 should not produce `Empty Set` result.

      How to repeat:

      drop table if exists t1,t2;
      create table t1(c1 int, c2 int, c3 int);
      insert into t1 values(1,1,1),(2,2,2),(3,3,3);
      select * from t1;
      create table t2(c1 int, c2 int);
      insert into t2 values(2,2);
      select * from t2;
      select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt));
      
      

      Suggested fix:
      Non empty set is returned for the query.

      Attachments

        Activity

          People

            igor Igor Babaev
            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.