[MDEV-12489] The select stmt may fail due to "having clause is ambiguous" unexpected Created: 2017-04-12  Updated: 2017-07-12  Resolved: 2017-07-12

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 5.5, 10.0, 10.1, 10.1.21, 10.2
Fix Version/s: 10.1.26, 5.5.57, 10.0.32, 10.2.8

Type: Bug Priority: Critical
Reporter: dennis Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

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.

 Comments   
Comment by Alice Sherepa [ 2017-04-16 ]

I can repeat the problem in 10.1.22 and 10.0.30

MariaDB [test]> 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
 
MariaDB [test]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.0.30-MariaDB |
 
+-----------------+

Comment by Alice Sherepa [ 2017-04-16 ]

Problem doesn't occur in mysql 5.7.18 and was introduced in 10.0.29 as indicated below

Server version: 10.0.29-MariaDB MariaDB Server
 
MariaDB [test]>  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
 
 
 
 
 
Server version: 10.0.28-MariaDB MariaDB Server
 
MariaDB [test]>  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;
 
Empty set (0.00 sec)

Comment by Elena Stepanova [ 2017-05-01 ]

The change was introduced in 5.5 by this commit:

commit d67ef7a2fb3b52b3f61ce71dfe23cf4d610afc3c
 
Author: Oleksandr Byelkin <sanja@mariadb.com>
 
Date:   Mon Dec 5 17:37:54 2016 +0100
 
 
 
    MDEV-10663: Use of Inline table columns in HAVING clause throws 1463 Error
 
    
    check for VIEW/DERIVED fields

Test case from description

 
create table t1 (c1 int, c2 int);
 
create table t2 (c1 int, c2 int);
 
 
 
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;
 
 
 
drop table t1, t2;

Comment by Oleksandr Byelkin [ 2017-07-12 ]

OK to push!

Generated at Thu Feb 08 07:58:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.