From: Date: November 11 2005 10:40am
Subject: bk commit into 5.0 tree (timour:1.1954) BUG#14662
List-Archive: http://lists.mysql.com/internals/32173
X-Bug: 14662
Message-Id: <20051111094041.C5FD81A8476@lamia.home>

Below is the list of changes that have just been committed into a local
5.0 repository of timka. When timka 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.1954 05/11/11 11:40:35 timour@stripped +3 -0
  Fix for BUG#14662: view column in ORDER BY considered ambiguous if SELECT contains
  the same column as an aliased and as a non-aliased column.
  
  The problem was that Item_direct_view_ref::eq() was first comparing view columns
  by name, and in this case the name of one of them is different since it is aliased.

  sql/item.cc
    1.197 05/11/11 11:40:32 timour@stripped +20 -11
    Changed the way view column refenreces are compared. Two view columns are
    equal if they resolve to the same result field of a view.

  mysql-test/t/select.test
    1.91 05/11/11 11:40:32 timour@stripped +20 -0
    Added test for BUG#14662.

  mysql-test/r/select.result
    1.111 05/11/11 11:40:32 timour@stripped +42 -0
    Added test for BUG#14662.

# 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:	timour
# Host:	lamia.home
# Root:	/home/timka/mysql/src/5.0-bug-14662

--- 1.196/sql/item.cc	2005-11-01 15:54:16 +02:00
+++ 1.197/sql/item.cc	2005-11-11 11:40:32 +02:00
@@ -4938,8 +4938,7 @@
 }
 
 /*
-  Compare view field's name with item's name before call to referenced
-  item's eq()
+  Compare two view column references for equality.
 
   SYNOPSIS
     Item_direct_view_ref::eq()
@@ -4947,12 +4946,13 @@
     binary_cmp  make binary comparison
 
   DESCRIPTION
-    Consider queries:
-    create view v1 as select t1.f1 as f2, t1.f2 as f1 from t1;
-    select * from v1 order by f1;
-    In order to choose right field for sorting we need to compare
-    given item's name (f1) to view field's name prior to calling
-    referenced item's eq().
+    A view column reference is considered equal to another column
+    reference if the second one is a view column and if both column
+    references point to the same field. For views 'same field' means
+    the same Item_field object in the view translation table, where
+    the view translation table contains all result columns of the
+    view. This definition ensures that view columns are resolved
+    in the same manner as table columns.
 
   RETURN
     TRUE    Referenced item is equal to given item
@@ -4962,9 +4962,18 @@
 
 bool Item_direct_view_ref::eq(const Item *item, bool binary_cmp) const
 {
-  Item *it= ((Item *) item)->real_item();
-  return (!it->name || !my_strcasecmp(system_charset_info, it->name,
-          field_name)) && ref && (*ref)->real_item()->eq(it, binary_cmp);
+  if (item->type() == REF_ITEM)
+  {
+    Item_ref *item_ref= (Item_ref*) item;
+    if (item_ref->ref_type() == VIEW_REF)
+    {
+      Item *item_ref_ref= *(item_ref->ref);
+      DBUG_ASSERT((*ref)->type() == FIELD_ITEM &&
+                  (item_ref_ref->type() == FIELD_ITEM));
+      return (*ref == item_ref_ref);
+    }
+  }
+  return FALSE;
 }
 
 void Item_null_helper::print(String *str)

--- 1.110/mysql-test/r/select.result	2005-11-03 12:55:07 +02:00
+++ 1.111/mysql-test/r/select.result	2005-11-11 11:40:32 +02:00
@@ -3241,3 +3241,45 @@
 Warnings:
 Warning	1292	Incorrect date value: '2005-09-3a' for column 'f2' at row 1
 drop table t1;
+create table t1 (f1 int, f2 int);
+insert into t1 values (1, 30), (2, 20), (3, 10);
+create algorithm=merge view v1 as select f1, f2 from t1;
+create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1;
+create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1;
+select t1.f1 as x1, f1 from t1 order by t1.f1;
+x1	f1
+1	1
+2	2
+3	3
+select v1.f1 as x1, f1 from v1 order by v1.f1;
+x1	f1
+1	1
+2	2
+3	3
+select v2.f1 as x1, f1 from v2 order by v2.f1;
+x1	f1
+10	10
+20	20
+30	30
+select v3.f1 as x1, f1 from v3 order by v3.f1;
+x1	f1
+10	10
+20	20
+30	30
+select f1, f2, v1.f1 as x1 from v1 order by v1.f1;
+f1	f2	x1
+1	30	1
+2	20	2
+3	10	3
+select f1, f2, v2.f1 as x1 from v2 order by v2.f1;
+f1	f2	x1
+10	3	10
+20	2	20
+30	1	30
+select f1, f2, v3.f1 as x1 from v3 order by v3.f1;
+f1	f2	x1
+10	3	10
+20	2	20
+30	1	30
+drop table t1;
+drop view v1, v2, v3;

--- 1.90/mysql-test/t/select.test	2005-11-03 12:55:07 +02:00
+++ 1.91/mysql-test/t/select.test	2005-11-11 11:40:32 +02:00
@@ -2729,3 +2729,23 @@
 select * from t1 where f2 <= '2005-09-31';
 select * from t1 where f2 <= '2005-09-3a';
 drop table t1;
+
+#
+# Bug ##14662  	ORDER BY on column of a view, with an alias of the same
+# column causes ambiguous
+#
+
+create table t1 (f1 int, f2 int);
+insert into t1 values (1, 30), (2, 20), (3, 10);
+create algorithm=merge view v1 as select f1, f2 from t1;
+create algorithm=merge view v2 (f2, f1) as select f1, f2 from t1;
+create algorithm=merge view v3 as select t1.f1 as f2, t1.f2 as f1 from t1;
+select t1.f1 as x1, f1 from t1 order by t1.f1;
+select v1.f1 as x1, f1 from v1 order by v1.f1;
+select v2.f1 as x1, f1 from v2 order by v2.f1;
+select v3.f1 as x1, f1 from v3 order by v3.f1;
+select f1, f2, v1.f1 as x1 from v1 order by v1.f1;
+select f1, f2, v2.f1 as x1 from v2 order by v2.f1;
+select f1, f2, v3.f1 as x1 from v3 order by v3.f1;
+drop table t1;
+drop view v1, v2, v3;