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= ¤t_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#24653 | igor | 26 Jan |
