bk commit into 4.1 tree (gkodinov:1.2538) BUG#22457

[kgeorge]

Below is the list of changes that have just been committed into a local
4.1 repository of kgeorge. When kgeorge 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@stripped, 2006-11-03 18:48:16+02:00, gkodinov@stripped +3 -0
  Bug #22457: Column alias in ORDER BY works, but not if in an expression
   The parser is allocating Item_field for references by name in ORDER BY
   expressions. Such expressions however may point not only to Item_field 
   in the select list (or to a table column) but also to an arbitrary Item. 
   This causes Item_field::fix_fields to throw an error about missing 
   column.
   The fix substitutes Item_field for the reference with an Item_ref when 
   not pointing to Item_field.

  mysql-test/r/order_by.result@stripped, 2006-11-03 18:48:10+02:00, gkodinov@stripped +27
-0
    Bug #22457: Column alias in ORDER BY works, but not if in an expression
     - test case

  mysql-test/t/order_by.test@stripped, 2006-11-03 18:48:10+02:00, gkodinov@stripped +16 -0
    Bug #22457: Column alias in ORDER BY works, but not if in an expression
     - test case

  sql/item.cc@stripped, 2006-11-03 18:48:11+02:00, gkodinov@stripped +30 -3
    Bug #22457: Column alias in ORDER BY works, but not if in an expression
     - transform the Item_field made by the parser into Item_ref if it 
       doesn't point to Item_field and it is in allowed context

# 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:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B22457-4.1-opt

--- 1.233/sql/item.cc	2006-09-25 15:24:04 +03:00
+++ 1.234/sql/item.cc	2006-11-03 18:48:11 +02:00
@@ -1761,10 +1761,37 @@
         Item** res= find_item_in_list(this, thd->lex->current_select->item_list,
                                       &counter, REPORT_EXCEPT_NOT_FOUND,
                                       &not_used);
-        if (res != (Item **)not_found_item && (*res)->type() ==
Item::FIELD_ITEM)
+        if (res != (Item **)not_found_item)
         {
-          set_field((*((Item_field**)res))->field);
-          return 0;
+          if ((*res)->type() == Item::FIELD_ITEM)
+          {
+            /*
+             It's an Item_field referencing another Item_field in the select
+             list.
+             use the field from the Item_field in the select list and leave
+             the Item_field instance in place.
+            */
+            set_field((*((Item_field**)res))->field);
+            return 0;
+          }
+          else
+          {
+            /*
+              It's not an Item_field in the select list so we must make a new
+              Item_ref to point to the Item in the select list and replace the
+              Item_field created by the parser with the new Item_ref.
+            */
+            Item_ref *rf= new Item_ref(db_name,table_name,field_name);
+            if (!rf)
+              return 1;
+            thd->change_item_tree(ref, rf);
+            /*
+              Because Item_ref never substitutes itself with other items 
+              in Item_ref::fix_fields(), we can safely use the original 
+              pointer to it even after fix_fields()
+             */
+            return rf->fix_fields(thd, tables, ref) ||  rf->check_cols(1);
+          }
         }
       }
 

--- 1.47/mysql-test/r/order_by.result	2006-04-21 08:15:34 +03:00
+++ 1.48/mysql-test/r/order_by.result	2006-11-03 18:48:10 +02:00
@@ -820,3 +820,30 @@
 20	1
 10	2
 DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
+num
+3
+2
+SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
+str
+test1
+test2
+SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
+num
+3
+2
+SELECT a + 1 AS num FROM t1 HAVING 30 - num;
+num
+2
+3
+SELECT a + 1 AS num, num + 1 FROM t1;
+ERROR 42S22: Unknown column 'num' in 'field list'
+SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
+num	(select num + 2 FROM t1 LIMIT 1)
+2	4
+3	5
+SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
+ERROR 42S22: Unknown column 'num' in 'on clause'
+DROP TABLE t1;

--- 1.34/mysql-test/t/order_by.test	2006-04-21 08:15:34 +03:00
+++ 1.35/mysql-test/t/order_by.test	2006-11-03 18:48:10 +02:00
@@ -559,4 +559,20 @@
 
 DROP TABLE t1;
 
+#
+# Bug #22457: Column alias in ORDER BY works, but not if in an expression
+#
+
+CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2);
+SELECT a + 1 AS num FROM t1 ORDER BY 30 - num;
+SELECT CONCAT('test', a) AS str FROM t1 ORDER BY UPPER(str);
+SELECT a + 1 AS num FROM t1 GROUP BY 30 - num;
+SELECT a + 1 AS num FROM t1 HAVING 30 - num;
+--error 1054
+SELECT a + 1 AS num, num + 1 FROM t1;
+SELECT a + 1 AS num, (select num + 2 FROM t1 LIMIT 1) FROM t1;
+--error 1054
+SELECT a.a + 1 AS num FROM t1 a JOIN t1 b ON num = b.a;
+DROP TABLE t1;
+
 # End of 4.1 tests