MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:igor Date:January 26 2007 2:44am
Subject:bk commit into 4.1 tree (igor:1.2599) BUG#24653
View as plain text  
Below is the list of changes that have just been committed into a local
4.1 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@stripped, 2007-01-25 18:44:35-08:00, igor@stripped +10 -0
  Fixed bug #24653.
  The bug report has demonstrated the following two problems.
  1. If an ORDER/GROUP BY list includes a constant expression being 
  optimized away and, at the same time, containing single-row
  subselects that return more that one row, no error is reported.
  Strictly speaking the standard allows to ignore error in this case.
  Yet, now a corresponding fatal error is reported in this case.
  2. If a query requires sorting by expressions containing single-row
  subselects that, however, return more than one row, then the execution
  of the query may cause a server crash. 
  To fix this some code has been added that blocks execution of a subselect
  item in case of a fatal error in the method Item_subselect::exec.

  mysql-test/r/subselect.result@stripped, 2007-01-25 18:44:31-08:00, igor@stripped +74 -0
    Added a test cases for bug #24653.

  mysql-test/t/subselect.test@stripped, 2007-01-25 18:44:31-08:00, igor@stripped +59 -0
    Added a test cases for bug #24653.

  sql/filesort.cc@stripped, 2007-01-25 18:44:31-08:00, igor@stripped +5 -1
    Fixed bug #24653.
    Added a check for fatal error after reading the next row from the table
    in the function find_all_keys.

  sql/item.cc@stripped, 2007-01-25 18:44:31-08:00, igor@stripped +1 -0
    Fixed bug #24653.
    Down-ported calculation of the attribute with_subselect of for Item objects.

  sql/item.h@stripped, 2007-01-25 18:44:32-08:00, igor@stripped +3 -0
    Fixed bug #24653.
    Down-ported calculation of the attribute with_subselect of for Item objects.

  sql/item_cmpfunc.cc@stripped, 2007-01-25 18:44:32-08:00, igor@stripped +4 -3
    Fixed bug #24653.
    Down-ported calculation of the attribute with_subselect of for Item objects.

  sql/item_cmpfunc.h@stripped, 2007-01-25 18:44:32-08:00, igor@stripped +2 -1
    Fixed bug #24653.
    Down-ported calculation of the attribute with_subselect of for Item objects.

  sql/item_func.cc@stripped, 2007-01-25 18:44:32-08:00, igor@stripped +1 -0
    Fixed bug #24653.
    Down-ported calculation of the attribute with_subselect of for Item objects.

  sql/item_subselect.cc@stripped, 2007-01-25 18:44:32-08:00, igor@stripped +4 -0
    Fixed bug #24653.
    Added a check for fatal error in the method Item_subselect::exec
    to block evaluation of subselects in erroneous situations.
    Down-ported calculation of the attribute with_subselect of for Item objects. 

  sql/sql_select.cc@stripped, 2007-01-25 18:44:32-08:00, igor@stripped +21 -0
    Fixed bug #24653.
    Added a check to verify that any constant expression used
    in ORDER BY and/or GROUP BY lists which is optimized away
    does not contain subselects returning more than one row.
    If it does a fatal error is reported.

# 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:	olga.mysql.com
# Root:	/home/igor/dev-opt/mysql-4.1-opt-bug24653

--- 1.94/sql/filesort.cc	2007-01-25 18:44:40 -08:00
+++ 1.95/sql/filesort.cc	2007-01-25 18:44:40 -08:00
@@ -387,7 +387,8 @@
   byte *ref_pos,*next_pos,ref_buff[MAX_REFLENGTH];
   my_off_t record;
   TABLE *sort_form;
-  volatile my_bool *killed= &current_thd->killed;
+  THD *thd= current_thd;
+  volatile my_bool *killed= &thd->killed;
   handler *file;
   DBUG_ENTER("find_all_keys");
   DBUG_PRINT("info",("using: %s",(select?select->quick?"ranges":"where":"every row")));
@@ -474,6 +475,9 @@
     }
     else
       file->unlock_row();
+    /* It does not make sense to read more keys in case of a fatal error */
+    if (thd->net.report_error)
+      DBUG_RETURN(HA_POS_ERROR);
   }
   (void) file->extra(HA_EXTRA_NO_CACHE);	/* End cacheing of records */
   if (!next_pos)

--- 1.235/sql/item.cc	2007-01-25 18:44:40 -08:00
+++ 1.236/sql/item.cc	2007-01-25 18:44:40 -08:00
@@ -47,6 +47,7 @@
   collation.set(&my_charset_bin, DERIVATION_COERCIBLE);
   name= 0;
   decimals= 0; max_length= 0;
+  with_subselect= 0;
 
   /* Put item in free list so that we can free all items at end */
   THD *thd= current_thd;

--- 1.196/sql/item.h	2007-01-25 18:44:40 -08:00
+++ 1.197/sql/item.h	2007-01-25 18:44:40 -08:00
@@ -142,6 +142,9 @@
   my_bool with_sum_func;
   my_bool fixed;                        /* If item fixed with fix_fields */
   DTCollation collation;
+  my_bool with_subselect;               /* If this item is a subselect or some
+                                           of its arguments is or contains a
+                                           subselect */
 
   // alloc & destruct is done as start of select using sql_alloc
   Item();

--- 1.217/sql/item_cmpfunc.cc	2007-01-25 18:44:40 -08:00
+++ 1.218/sql/item_cmpfunc.cc	2007-01-25 18:44:40 -08:00
@@ -2139,6 +2139,7 @@
     and_tables_cache&=      tmp_table_map;
     const_item_cache&=	    item->const_item();
     with_sum_func=	    with_sum_func || item->with_sum_func;
+    with_subselect|=        item->with_subselect;
     if (item->maybe_null)
       maybe_null=1;
   }
@@ -2351,7 +2352,7 @@
     Handle optimization if the argument can't be null
     This has to be here because of the test in update_used_tables().
   */
-  if (!used_tables_cache)
+  if (!used_tables_cache && !with_subselect)
     return cached_value;
   return args[0]->is_null() ? 1: 0;
 }
@@ -2360,7 +2361,7 @@
 {
   DBUG_ASSERT(fixed == 1);
   DBUG_ENTER("Item_is_not_null_test::val_int");
-  if (!used_tables_cache)
+  if (!used_tables_cache && !with_subselect)
   {
     owner->was_null|= (!cached_value);
     DBUG_PRINT("info", ("cached :%d", cached_value));
@@ -2387,7 +2388,7 @@
   else
   {
     args[0]->update_used_tables();
-    if (!(used_tables_cache=args[0]->used_tables()))
+    if (!(used_tables_cache=args[0]->used_tables()) && !with_subselect)
     {
       /* Remember if the value is always NULL or never NULL */
       cached_value= (longlong) !args[0]->is_null();

--- 1.119/sql/item_cmpfunc.h	2007-01-25 18:44:40 -08:00
+++ 1.120/sql/item_cmpfunc.h	2007-01-25 18:44:40 -08:00
@@ -843,7 +843,8 @@
     else
     {
       args[0]->update_used_tables();
-      if ((const_item_cache= !(used_tables_cache= args[0]->used_tables())))
+      if ((const_item_cache= !(used_tables_cache= args[0]->used_tables())) &&
+           !with_subselect)
       {
 	/* Remember if the value is always NULL or never NULL */
 	cached_value= (longlong) args[0]->is_null();

--- 1.270/sql/item_func.cc	2007-01-25 18:44:40 -08:00
+++ 1.271/sql/item_func.cc	2007-01-25 18:44:40 -08:00
@@ -177,6 +177,7 @@
       used_tables_cache|=     item->used_tables();
       not_null_tables_cache|= item->not_null_tables();
       const_item_cache&=      item->const_item();
+      with_subselect|=        item->with_subselect;
     }
   }
   fix_length_and_dec();

--- 1.465/sql/sql_select.cc	2007-01-25 18:44:40 -08:00
+++ 1.466/sql/sql_select.cc	2007-01-25 18:44:41 -08:00
@@ -644,6 +644,13 @@
   {
     ORDER *org_order= order;
     order=remove_const(this, order,conds,1, &simple_order);
+    if (thd->net.report_error)
+    {
+      error= 1;
+      DBUG_PRINT("error",("Error from remove_const"));
+      DBUG_RETURN(1);
+    }
+
     /*
       If we are using ORDER BY NULL or ORDER BY const_expression,
       return result in any order (even if we are using a GROUP BY)
@@ -747,6 +754,12 @@
     group_list= remove_const(this, (old_group_list= group_list), conds,
                              rollup.state == ROLLUP::STATE_NONE,
 			     &simple_group);
+    if (thd->net.report_error)
+    {
+      error= 1;
+      DBUG_PRINT("error",("Error from remove_const"));
+      DBUG_RETURN(1);
+    }
     if (old_group_list && !group_list)
       select_distinct= 0;
   }
@@ -763,6 +776,12 @@
   {
     group_list= procedure->group= remove_const(this, procedure->group, conds,
 					       1, &simple_group);
+    if (thd->net.report_error)
+    {
+      error= 1;
+      DBUG_PRINT("error",("Error from remove_const"));
+      DBUG_RETURN(1);
+    }   
     calc_group_buffer(this, group_list);
   }
 
@@ -4428,6 +4447,8 @@
       *simple_order=0;				// Must do a temp table to sort
     else if (!(order_tables & not_const_tables))
     {
+      if (order->item[0]->with_subselect)
+        order->item[0]->val_str(&order->item[0]->str_value);
       DBUG_PRINT("info",("removing: %s", order->item[0]->full_name()));
       continue;					// skip const item
     }

--- 1.186/mysql-test/r/subselect.result	2007-01-25 18:44:41 -08:00
+++ 1.187/mysql-test/r/subselect.result	2007-01-25 18:44:41 -08:00
@@ -3026,3 +3026,77 @@
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
 2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
 DROP TABLE t1;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (2), (4), (1), (3);
+CREATE TABLE t2 (b int, c int);
+INSERT INTO t2 VALUES
+(2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
+a
+2
+4
+1
+3
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);
+ERROR 21000: Subquery returns more than 1 row
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;
+a
+1
+2
+3
+4
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
+ERROR 21000: Subquery returns more than 1 row
+SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
+b	MAX(c)
+1	4
+2	2
+4	4
+SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
+ERROR 21000: Subquery returns more than 1 row
+SELECT a FROM t1 GROUP BY a
+HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
+(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
+a
+1
+2
+3
+4
+SELECT a FROM t1 GROUP BY a
+HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
+(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
+ERROR 21000: Subquery returns more than 1 row
+SELECT a FROM t1 GROUP BY a
+HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
+(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
+a
+4
+SELECT a FROM t1 GROUP BY a
+HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
+(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
+ERROR 21000: Subquery returns more than 1 row
+SELECT a FROM t1 
+ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
+(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
+a
+2
+4
+1
+3
+SELECT a FROM t1 
+ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
+(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
+ERROR 21000: Subquery returns more than 1 row
+SELECT a FROM t1 
+ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
+(SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
+a
+2
+1
+3
+4
+SELECT a FROM t1 
+ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
+(SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
+ERROR 21000: Subquery returns more than 1 row
+DROP TABLE t1,t2;

--- 1.163/mysql-test/t/subselect.test	2007-01-25 18:44:41 -08:00
+++ 1.164/mysql-test/t/subselect.test	2007-01-25 18:44:41 -08:00
@@ -1993,4 +1993,63 @@
 EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
 
 DROP TABLE t1;
+
+#
+# Bug 24653: sorting by expressions containing subselects 
+#            that return more than one row
+#
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (2), (4), (1), (3);
+
+CREATE TABLE t2 (b int, c int);
+INSERT INTO t2 VALUES
+  (2,1), (1,3), (2,1), (4,4), (2,2), (1,4);
+
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2 );
+--error 1242   
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1);  
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 2), a;  
+--error 1242   
+SELECT a FROM t1 ORDER BY (SELECT c FROM t2 WHERE b > 1), a;
+ 
+SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 2);
+--error 1242
+SELECT b, MAX(c) FROM t2 GROUP BY b, (SELECT c FROM t2 WHERE b > 1);
+
+
+SELECT a FROM t1 GROUP BY a
+  HAVING IFNULL((SELECT b FROM t2 WHERE b > 2),
+                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
+--error 1242
+SELECT a FROM t1 GROUP BY a
+  HAVING IFNULL((SELECT b FROM t2 WHERE b > 1),
+                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
+
+SELECT a FROM t1 GROUP BY a
+  HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
+                (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b)) > 3;
+--error 1242 
+SELECT a FROM t1 GROUP BY a
+  HAVING IFNULL((SELECT b FROM t2 WHERE b > 4),
+                (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b)) > 3;
+
+SELECT a FROM t1 
+  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 2),
+                  (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
+--error 1242
+SELECT a FROM t1 
+  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 1),
+                  (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
+
+SELECT a FROM t1 
+  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
+                  (SELECT c FROM t2 WHERE c=a AND b > 2 ORDER BY b));
+--error 1242
+SELECT a FROM t1 
+  ORDER BY IFNULL((SELECT b FROM t2 WHERE b > 4),
+                  (SELECT c FROM t2 WHERE c=a AND b > 1 ORDER BY b));
+
+DROP TABLE t1,t2; 
+
 # End of 4.1 tests

--- 1.147/sql/item_subselect.cc	2007-01-25 18:44:41 -08:00
+++ 1.148/sql/item_subselect.cc	2007-01-25 18:44:41 -08:00
@@ -39,6 +39,7 @@
   engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0),
   const_item_cache(1), engine_changed(0), changed(0)
 {
+  with_subselect= 1;
   reset();
   /*
     item value is NULL if select_subselect not changed this value
@@ -201,6 +202,9 @@
     mem root
   */
   thd->mem_root= &thd->main_mem_root;
+  if (thd->net.report_error)
+  /* Do not execute subselect in case of a fatal error */
+    return 1;
   res= engine->exec();
   thd->mem_root= old_root;
 
Thread
bk commit into 4.1 tree (igor:1.2599) BUG#24653igor26 Jan