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

The select stmt may fail due to "having clause is ambiguous" unexpected

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.1.21, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.1.26, 5.5.57, 10.0.32, 10.2.8
    • Server
    • None
    • ubuntu 14.04

    Description

      For mariadb 10.1.21

      Create two tables for test:

      mysql> create table t1 (c1 int, c2 int);
      		 
      Query OK, 0 rows affected (0.08 sec)
      		 
       
      		 
      mysql> create table t2 (c1 int, c2 int);
      		 
      Query OK, 0 rows affected (0.04 sec)

      Then test the following sqls:

      mysql> select t1.c1 as c1 , t2.c2 as c1 from t1, t2 where t2.c2 > 5 having t1.c1 < 3;
      		 
      Empty set (0.00 sec)
      		 
       
      		 
      mysql> select t1.c1 as c1 , t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 having t1.c1 < 3;
      		 
      Empty set (0.00 sec)
      		 
       
      		 
      mysql> select t1.c1 as c1 , t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group by t1.c1 , t2.c2 having t1.c1 < 3;
      		 
      ERROR 1052 (23000): Column 't1.c1' in having clause is ambiguous

      Why the sql1 and sql2 can pass, but the sql3 fail due to "having clause is ambiguous"?
      The sql3 can pass for mariadb 10.0.27.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          gao1738 dennis created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          Status Open [ 1 ] Confirmed [ 10101 ]
          elenst Elena Stepanova made changes -
          Fix Version/s 5.5 [ 15800 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.1 [ 16100 ]
          Fix Version/s 10.2 [ 14601 ]
          Affects Version/s 5.5 [ 15800 ]
          Affects Version/s 10.0 [ 16000 ]
          Affects Version/s 10.1 [ 16100 ]
          Affects Version/s 10.2 [ 14601 ]
          Assignee Oleksandr Byelkin [ sanja ]
          serg Sergei Golubchik made changes -
          Description For mariadb 10.1.21

          *Create two tables for test:*

          mysql> create table t1 (c1 int, c2 int);
          Query OK, 0 rows affected (0.08 sec)

          mysql> create table t2 (c1 int, c2 int);
          Query OK, 0 rows affected (0.04 sec)


          *Then test the following sqls:*

          mysql> select t1.c1 as c1 , t2.c2 as c1 from t1, t2 where t2.c2 > 5 having t1.c1 < 3;
          Empty set (0.00 sec)

          mysql> select t1.c1 as c1 , t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 having t1.c1 < 3;
          Empty set (0.00 sec)

          mysql> select t1.c1 as c1 , t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group by t1.c1 , t2.c2 having t1.c1 < 3;
          ERROR 1052 (23000): Column 't1.c1' in having clause is ambiguous

          Why the sql1 and sql2 can pass, but the sql3 fail due to "having clause is ambiguous"?
          The sql3 can pass for mariadb 10.0.27.           		For mariadb 10.1.21

          *Create two tables for test:*
          {code:sql}
          mysql> create table t1 (c1 int, c2 int);
          Query OK, 0 rows affected (0.08 sec)

          mysql> create table t2 (c1 int, c2 int);
          Query OK, 0 rows affected (0.04 sec)
          {code}

          *Then test the following sqls:*
          {code:sql}
          mysql> select t1.c1 as c1 , t2.c2 as c1 from t1, t2 where t2.c2 > 5 having t1.c1 < 3;
          Empty set (0.00 sec)

          mysql> select t1.c1 as c1 , t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 having t1.c1 < 3;
          Empty set (0.00 sec)

          mysql> select t1.c1 as c1 , t2.c2 as c1 from t1, t2 where t1.c1 < 20 and t2.c2 > 5 group by t1.c1 , t2.c2 having t1.c1 < 3;
          ERROR 1052 (23000): Column 't1.c1' in having clause is ambiguous
          {code}
          Why the sql1 and sql2 can pass, but the sql3 fail due to "having clause is ambiguous"?
          The sql3 can pass for mariadb 10.0.27.
          serg Sergei Golubchik made changes -
          Status Confirmed [ 10101 ] In Progress [ 3 ]
          serg Sergei Golubchik made changes -
          Assignee Oleksandr Byelkin [ sanja ] Sergei Golubchik [ serg ]
          serg Sergei Golubchik made changes -
          Assignee Sergei Golubchik [ serg ] Oleksandr Byelkin [ sanja ]
          Status In Progress [ 3 ] In Review [ 10002 ]
          serg Sergei Golubchik made changes -
          Priority Major [ 3 ] Critical [ 2 ]
          sanja Oleksandr Byelkin made changes -
          Assignee Oleksandr Byelkin [ sanja ] Sergei Golubchik [ serg ]
          Status In Review [ 10002 ] Stalled [ 10000 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 5.5.57 [ 22539 ]
          Fix Version/s 10.0.32 [ 22504 ]
          Fix Version/s 10.1.26 [ 22553 ]
          Fix Version/s 10.2.8 [ 22544 ]
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 5.5 [ 15800 ]
          Fix Version/s 10.0 [ 16000 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 80306 ] MariaDB v4 [ 151948 ]

          People

            serg Sergei Golubchik
            gao1738 dennis
            Votes:
            0 Vote for this issue
            Watchers:
            6 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.