Bug #13410 HAVING clause filtering on qualified view column fails to resolve
Submitted: 22 Sep 2005 20:25 Modified: 5 Oct 2005 23:56
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S1 (Critical)
Version:5.0.14 OS:
Assigned to: Timour Katchaounov CPU Architecture:Any

[22 Sep 2005 20:25] Dean Ellis
Description:
HAVING clause against a qualified view column fails to resolve the column name.

How to repeat:
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
CREATE TABLE t1 ( a int, b int );
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
CREATE VIEW v1 AS SELECT a,b FROM t1;
SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a IN (1,2,3);
SELECT v1.a FROM v1 GROUP BY v1.a HAVING v1.a IN (1,2,3);

-- ERROR 1054 (42S22): Unknown column 'v1.a' in 'having clause'

Suggested fix:
n/a
[27 Sep 2005 6:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30362
[28 Sep 2005 1:27] Igor Babaev
ChangeSet
  1.1998 05/09/26 23:29:02 igor@rurik.mysql.com +3 -0
  item.cc:
    Fixed bug #13410.
    Fixed name resolution for qualified reference to a view column
    in the HAVING clause.
  view.result, view.test:
    Added a test case for bug #13410.

The fix will appear in 5.0.14
[29 Sep 2005 0:45] Dean Ellis
We need to add another SELECT to the test case above, as the patch does not fix cases where view name has been aliased.

New test case:

DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
CREATE TABLE t1 ( a int, b int );
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
CREATE VIEW v1 AS SELECT a,b FROM t1;
SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a IN (1,2,3);
SELECT t_1.a FROM t1 AS t_1 GROUP BY t_1.a HAVING t_1.a IN (1,2,3);
SELECT v1.a FROM v1 GROUP BY v1.a HAVING v1.a IN (1,2,3);
SELECT v_1.a FROM v1 AS v_1 GROUP BY v_1.a HAVING v_1.a IN (1,2,3);

-- ERROR 1054 (42S22): Unknown column 'v_1.a' in 'having clause'
[30 Sep 2005 17:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30583
[1 Oct 2005 6:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30597
[3 Oct 2005 19:40] Timour Katchaounov
Fixed in 5.0.15.
[5 Oct 2005 23:56] Paul Dubois
Noted in 5.0.15 changelog.