MySQL Bugs: #19573: Views with complex expressions in first column gives errors when selected

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

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;

Verified against:
ChangeSet@1.2118, 2006-05-06 19:45:24+04:00, kroki@mysql.com

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

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

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.