From: Date: May 17 2006 7:19am
Subject: bk commit into 5.0 tree (igor:1.2145) BUG#19573
List-Archive: http://lists.mysql.com/commits/6490
X-Bug: 19573
Message-Id: <20060517051954.945A22C9242@rurik.mysql.com>

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2145 06/05/16 22:19:44 igor@stripped +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.

  sql/item.cc
    1.223 06/05/16 22:19:36 igor@stripped +10 -1
    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.  

  mysql-test/t/view.test
    1.144 06/05/16 22:19:36 igor@stripped +28 -0
    Added a test case for bug #19573.

  mysql-test/r/view.result
    1.157 06/05/16 22:19:36 igor@stripped +25 -0
    Added a test case for bug #19573.

  mysql-test/r/having.result
    1.26 06/05/16 22:19:36 igor@stripped +1 -1
    Adjusted results after fixing bug #19573.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev/mysql-5.0-2

--- 1.222/sql/item.cc	2006-05-15 16:44:39 -07:00
+++ 1.223/sql/item.cc	2006-05-16 22:19:36 -07:00
@@ -4840,7 +4840,16 @@
 void Item_ref::print(String *str)
 {
   if (ref)
-    (*ref)->print(str);
+  {
+    if ((*ref)->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF &&
+        name && alias_name_used)
+    {
+      THD *thd= current_thd;
+      append_identifier(thd, str, name, (uint) strlen(name));
+    }
+    else
+      (*ref)->print(str);
+  }
   else
     Item_ident::print(str);
 }

--- 1.156/mysql-test/r/view.result	2006-05-12 18:24:31 -07:00
+++ 1.157/mysql-test/r/view.result	2006-05-16 22:19:36 -07:00
@@ -2660,3 +2660,28 @@
 id	t	COUNT(*)
 DROP VIEW v1;
 DROP TABLE t1;
+CREATE TABLE t1(
+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 t1(fName, lName, DOB) VALUES
+('Hank', 'Hill', '1964-09-29'),
+('Tom', 'Adams', '1908-02-14'),
+('Homer', 'Simpson', '1968-03-05');
+CREATE VIEW v1 AS
+SELECT (year(now())-year(DOB)) AS Age
+FROM t1 HAVING Age < 75;
+SHOW CREATE VIEW v1;
+View	Create View
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select sql_no_cache (year(now()) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75)
+SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75;
+Age
+42
+38
+SELECT * FROM v1;
+Age
+42
+38
+DROP VIEW v1;
+DROP TABLE t1;

--- 1.143/mysql-test/t/view.test	2006-05-12 18:24:31 -07:00
+++ 1.144/mysql-test/t/view.test	2006-05-16 22:19:36 -07:00
@@ -2528,3 +2528,31 @@
 
 DROP VIEW v1;
 DROP TABLE t1;
+
+#
+# Bug #19573: VIEW with HAVING that refers an alias name
+# 
+
+CREATE TABLE t1(
+  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 t1(fName, lName, DOB) VALUES
+  ('Hank', 'Hill', '1964-09-29'),
+  ('Tom', 'Adams', '1908-02-14'),
+  ('Homer', 'Simpson', '1968-03-05');
+
+CREATE VIEW v1 AS
+  SELECT (year(now())-year(DOB)) AS Age
+    FROM t1 HAVING Age < 75; 
+SHOW CREATE VIEW v1;           
+
+SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75;
+SELECT * FROM v1;
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+

--- 1.25/mysql-test/r/having.result	2006-05-07 13:25:35 -07:00
+++ 1.26/mysql-test/r/having.result	2006-05-16 22:19:36 -07:00
@@ -12,7 +12,7 @@
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 Warnings:
-Note	1003	select count(`test`.`t1`.`a`) AS `b` from `test`.`t1` where 0 having (count(`test`.`t1`.`a`) >= 0)
+Note	1003	select count(`test`.`t1`.`a`) AS `b` from `test`.`t1` where 0 having (`b` >= 0)
 drop table t1;
 CREATE TABLE t1 (
 raw_id int(10) NOT NULL default '0',