[MDEV-12489] The select stmt may fail due to "having clause is ambiguous" unexpected - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.1.21, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.1.26, 5.5.57, 10.0.32, 10.2.8
Component/s: Server
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.

Attachments

Activity

Ascending order - Click to sort in descending order

dennis created issue - 2017-04-12 10:11

Alice Sherepa added a comment - 2017-04-16 13:26

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 |

+-----------------+

Alice Sherepa added a comment - 2017-04-16 13:26 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 | + -----------------+

Alice Sherepa added a comment - 2017-04-16 22:23

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)

Alice Sherepa added a comment - 2017-04-16 22:23 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)

Elena Stepanova made changes - 2017-05-01 22:36

Field	Original Value	New Value
Status	Open [ 1 ]	Confirmed [ 10101 ]

Elena Stepanova added a comment - 2017-05-01 22:38 - edited

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;

Elena Stepanova added a comment - 2017-05-01 22:38 - edited 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;

Elena Stepanova made changes - 2017-05-01 22:38

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 ]

Sergei Golubchik made changes - 2017-07-12 11:22

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.

Sergei Golubchik made changes - 2017-07-12 12:22

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Sergei Golubchik made changes - 2017-07-12 12:22

Assignee

Oleksandr Byelkin [ sanja ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2017-07-12 12:23

Assignee	Sergei Golubchik [ serg ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2017-07-12 12:23

Priority

Major [ 3 ]

Critical [ 2 ]

Oleksandr Byelkin added a comment - 2017-07-12 16:27

OK to push!

Oleksandr Byelkin added a comment - 2017-07-12 16:27 OK to push!

Oleksandr Byelkin made changes - 2017-07-12 16:27

Assignee	Oleksandr Byelkin [ sanja ]	Sergei Golubchik [ serg ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Sergei Golubchik made changes - 2017-07-12 16:55

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 ]

Sergei Golubchik made changes - 2021-12-06 21:44

Workflow

MariaDB v3 [ 80306 ]

MariaDB v4 [ 151948 ]

People

Assignee:: Sergei Golubchik

Reporter:: dennis

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2017-04-12 10:11

Updated:: 2017-07-12 16:55

Resolved:: 2017-07-12 16:55

Git Integration

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