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

having without group by in exists/in subselect returns ER_BAD_FIELD_ERROR (1054)

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.1
    • 10.4, 10.5, 10.6, 10.11
    • None
    • None

    Description

      Not sure how important or practical this is, because having is normally used with group by, but just in case it is a bug worth addressing, here's the mtr case

      11.1 4e5b771e980edfdad5c5414aa62c81d409d585a4

      create table t1 (a1 int, a2 char(8));
      create table t2 (b1 int, b2 char(8));
       
      insert into t1 values (0, '2 - 00');
      insert into t1 values (1, '2 - 01');
      insert into t1 values (2, '2 - 02');
       
      insert into t2 values (1, '2 - 01');
      insert into t2 values (1, '2 - 01');
      insert into t2 values (2, '2 - 02');
      insert into t2 values (2, '2 - 02');
      insert into t2 values (3, '2 - 03');
       
      # ok
      select * from t1 having a1 > 0;
      # ok
      select * from t2 where exists (select * from t1 where a1 > 0);
      # ER_BAD_FIELD_ERROR (1054): Unknown column 'a1' in 'having clause'
      --error ER_BAD_FIELD_ERROR
      select * from t2 where exists (select * from t1 having a1 > 0);
       
      # ok
      select * from t2 where exists (select * from t1 where a1 = b1);
      # ER_BAD_FIELD_ERROR (1054): Unknown column 'a1' in 'having clause'
      --error ER_BAD_FIELD_ERROR
      select * from t2 where exists (select * from t1 having a1 = b1);
      # ok
      select * from t2 where b2 in (select a2 from t1 group by a1 having a1 = b1);
      # ok
      select * from t2 where b1 in (select a1 from t1 group by a1 having a1 = b1);
       
      drop table t1, t2;
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            ycp Yuchen Pei
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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