Bug #19573 Views with complex expressions in first column gives errors when selected
Submitted: 5 May 2006 22:39 Modified: 26 May 2006 16:52
Reporter: Erica Moss Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.21 OS:Windows (win32)
Assigned to: Igor Babaev CPU Architecture:Any

[5 May 2006 22:39] Erica Moss
Description:
Created a view with the compound expression (year(now())-year(DOB)) AS Age for the first column.  This yielded the error:
"ERROR 1356 (HY000): View 'test.v1' references invalid table(s) or column(s) or f
unction(s) or definer/invoker of view lack rights to use them"

However if the same expression is used as the 2nd or higher column then the SELECT will succeed.

Also if the same complex expression is used in a plain SELECT statement then it will succeed.

How to repeat:
USE test;
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1, v2, v3;

CREATE TABLE users(
	fName VARCHAR(25) NOT NULL,
	lName VARCHAR(25) NOT NULL,
	DOB  DATE NOT NULL,
	uID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
 
INSERT INTO users(fName, lName, DOB) VALUES
	('Hank', 'Hill', '1964-09-29'),
	('Homer', 'Simpson', '1968-03-05');

CREATE VIEW v1 AS SELECT
      (year(now())-year(DOB)) AS Age
FROM users HAVING Age < 75;

-- This causes an error:

SELECT * FROM v1;

-- This also causes an error:

CREATE VIEW v2 AS SELECT
      year(now())-year(DOB) AS Age
FROM users HAVING Age < 75;

SELECT * FROM v1;

-- However these don't cause an error:

SELECT (year(now())-year(DOB)) AS Age
FROM users HAVING Age < 75;

SELECT year(now())-year(DOB) AS Age
FROM users HAVING Age < 75;

-- This also doesn't cause an error
CREATE VIEW v3 AS SELECT
      DOB,
      (year(now())-year(DOB)) AS Age
FROM users HAVING Age < 75;

SELECT * FROM v3;

DROP VIEW v1;
DROP VIEW v2;
DROP VIEW v3;
DROP TABLE users;
[7 May 2006 11:26] Alexander Keremidarski
Verified against:
ChangeSet@1.2118, 2006-05-06 19:45:24+04:00, kroki@mysql.com
[17 May 2006 5:20] 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/commits/6490
[21 May 2006 5:54] Igor Babaev
ChangeSet
  1.2145 06/05/16 22:19:44 igor@rurik.mysql.com +4 -0
  Fixed bug #19573.
  The select statement that specified a view could be
  slightly changed when the view was saved in a frm file.
  In particular references to an alias name in the HAVING
  clause could be substituted for the expression named by
  this alias.
  This could result in an error message for a query of
  the form SELECT * FROM <view>. Yet no such message
  appeared when executing the query specifying the view.

The fix will appear 5.0.22 and 5.1.10
[26 May 2006 16:52] Paul DuBois
Noted in 5.0.23, 5.1.10 changelogs.

A view definition that referred to an alias in the HAVING clause
could be saved in the .frm file with the alias replaced by the
expression that it referred to, causing failure of subsequent SELECT
* FROM view_name statements.