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

Unexpected error message when using an outer reference in HAVING

    Details

      Description

      When the HAVING clause contains a subquery that refers to an outer field and this field is not referred to in any of upper selects the server reports an unexpected error message.
      The problem can be reproduced with the following test case:

      create table t1 (a int, b int);
      create table t2 (c int, d int);
      insert into t1 values (1,1), (1,3), (2,1);
      insert into t2 values (5,2), (1,3), (5,1);
      select a from t1 group by a having exists (select * from t2 where t1.b=t2.d);
      

      When executing the above query one gets an unexpected error message:

      MariaDB [test]> select a from t1 group by a having exists (select * from t2 where t1.b=t2.d);
      ERROR 1054 (42S22): Unknown column 'test.t1.b' in 'where clause'
      

      Compare with:

      MariaDB [test]> select a,b from t1 group by a having exists (select * from t2 where t1.b=t2.d);
      +------+------+
      | a    | b    |
      +------+------+
      |    1 |    1 |
      |    2 |    1 |
      +------+------+
      

      With

      set SQL_MODE='only_full_group_by';
      

      one still gets an improper error message:

      MariaDB [test]> select a from t1 group by a having exists (select * from t2 where t1.b=t2.d);
      ERROR 1054 (42S22): Unknown column 'test.t1.b' in 'where clause'
      

      Compare with

      MariaDB [test]> select a,b from t1 group by a having exists (select * from t2 where t1.b=t2.d);
      ERROR 1055 (42000): 'test.t1.b' isn't in GROUP BY
      

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              igor Igor Babaev
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: