Below is the list of changes that have just been committed into a local
5.1 repository of hf. When hf 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-03-22 11:43:23+04:00, holyfoot@hfmain.(none) +14 -0
Merge mysql.com:/home/hf/work/mrg/mysql-5.0-opt
into mysql.com:/home/hf/work/mrg/mysql-5.1-opt
MERGE: 1.1810.2373.166
mysql-test/r/subselect.result@stripped, 2007-03-22 11:25:24+04:00, holyfoot@hfmain.(none) +31 -31
merging
MERGE: 1.134.1.46
mysql-test/r/subselect3.result@stripped, 2007-03-22 11:21:15+04:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.1.1.5
mysql-test/r/union.result@stripped, 2007-03-22 11:21:15+04:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.80.1.10
mysql-test/t/subselect.test@stripped, 2007-03-22 11:26:57+04:00, holyfoot@hfmain.(none) +30 -28
merging
MERGE: 1.129.1.16
mysql-test/t/union.test@stripped, 2007-03-22 11:21:15+04:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.97.1.2
sql/item.h@stripped, 2007-03-22 11:21:15+04:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.183.1.40
sql/item_sum.cc@stripped, 2007-03-22 11:21:15+04:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.170.1.28
sql/item_sum.h@stripped, 2007-03-22 11:21:15+04:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.102.1.9
sql/opt_range.cc@stripped, 2007-03-22 11:21:15+04:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.159.1.83
sql/sql_base.cc@stripped, 2007-03-22 11:35:27+04:00, holyfoot@hfmain.(none) +0 -2
mergin
MERGE: 1.235.1.136
sql/sql_class.h@stripped, 2007-03-22 11:36:42+04:00, holyfoot@hfmain.(none) +1 -1
merging
MERGE: 1.230.1.96
sql/sql_insert.cc@stripped, 2007-03-22 11:21:16+04:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.146.1.80
sql/sql_select.cc@stripped, 2007-03-22 11:43:13+04:00, holyfoot@hfmain.(none) +2 -3
merging
MERGE: 1.312.1.187
sql/sql_yacc.yy@stripped, 2007-03-22 11:21:16+04:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.371.1.140
# 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: holyfoot
# Host: hfmain.(none)
# Root: /home/hf/work/mrg/mysql-5.1-opt/RESYNC
--- 1.230/sql/item.h 2007-03-22 11:43:30 +04:00
+++ 1.231/sql/item.h 2007-03-22 11:43:30 +04:00
@@ -2001,6 +2001,11 @@ public:
{
return depended_from ? OUTER_REF_TABLE_BIT : (*ref)->used_tables();
}
+ void update_used_tables()
+ {
+ if (!depended_from)
+ (*ref)->update_used_tables();
+ }
table_map not_null_tables() const { return (*ref)->not_null_tables(); }
void set_result_field(Field *field) { result_field= field; }
bool is_result_field() { return 1; }
--- 1.214/sql/item_sum.cc 2007-03-22 11:43:30 +04:00
+++ 1.215/sql/item_sum.cc 2007-03-22 11:43:30 +04:00
@@ -61,6 +61,7 @@ bool Item_sum::init_sum_func_check(THD *
/* Save a pointer to object to be used in items for nested set functions */
thd->lex->in_sum_func= this;
nest_level= thd->lex->current_select->nest_level;
+ nest_level_tables_count= thd->lex->current_select->join->tables;
ref_by= 0;
aggr_level= -1;
max_arg_level= -1;
@@ -176,6 +177,7 @@ bool Item_sum::check_sum_func(THD *thd,
*/
set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
}
+ update_used_tables();
thd->lex->in_sum_func= in_sum_func;
return FALSE;
}
@@ -267,12 +269,13 @@ bool Item_sum::register_sum_func(THD *th
sl= sl->master_unit()->outer_select() )
sl->master_unit()->item->with_sum_func= 1;
}
+ thd->lex->current_select->mark_as_dependent(aggr_sl);
return FALSE;
}
-Item_sum::Item_sum(List<Item> &list)
- :arg_count(list.elements)
+Item_sum::Item_sum(List<Item> &list) :arg_count(list.elements),
+ forced_const(FALSE)
{
if ((args=(Item**) sql_alloc(sizeof(Item*)*arg_count)))
{
@@ -296,7 +299,10 @@ Item_sum::Item_sum(List<Item> &list)
Item_sum::Item_sum(THD *thd, Item_sum *item):
Item_result_field(thd, item), arg_count(item->arg_count),
- quick_group(item->quick_group)
+ nest_level(item->nest_level), aggr_level(item->aggr_level),
+ quick_group(item->quick_group), used_tables_cache(item->used_tables_cache),
+ forced_const(item->forced_const),
+ nest_level_tables_count(item->nest_level_tables_count)
{
if (arg_count <= 2)
args=tmp_args;
@@ -429,6 +435,26 @@ Field *Item_sum::create_tmp_field(bool g
}
+void Item_sum::update_used_tables ()
+{
+ if (!forced_const)
+ {
+ used_tables_cache= 0;
+ for (uint i=0 ; i < arg_count ; i++)
+ {
+ args[i]->update_used_tables();
+ used_tables_cache|= args[i]->used_tables();
+ }
+
+ used_tables_cache&= PSEUDO_TABLE_BITS;
+
+ /* the aggregate function is aggregated into its local context */
+ if (aggr_level == nest_level)
+ used_tables_cache |= (1 << nest_level_tables_count) - 1;
+ }
+}
+
+
String *
Item_sum_num::val_str(String *str)
{
@@ -488,7 +514,7 @@ Item_sum_num::fix_fields(THD *thd, Item
Item_sum_hybrid::Item_sum_hybrid(THD *thd, Item_sum_hybrid *item)
:Item_sum(thd, item), value(item->value), hybrid_type(item->hybrid_type),
hybrid_field_type(item->hybrid_field_type), cmp_sign(item->cmp_sign),
- used_table_cache(item->used_table_cache), was_values(item->was_values)
+ was_values(item->was_values)
{
/* copy results from old value */
switch (hybrid_type) {
@@ -1082,7 +1108,6 @@ void Item_sum_count::cleanup()
DBUG_ENTER("Item_sum_count::cleanup");
count= 0;
Item_sum_int::cleanup();
- used_table_cache= ~(table_map) 0;
DBUG_VOID_RETURN;
}
@@ -1572,7 +1597,7 @@ void Item_sum_hybrid::cleanup()
{
DBUG_ENTER("Item_sum_hybrid::cleanup");
Item_sum::cleanup();
- used_table_cache= ~(table_map) 0;
+ forced_const= FALSE;
/*
by default it is TRUE to avoid TRUE reporting by
--- 1.116/sql/item_sum.h 2007-03-22 11:43:30 +04:00
+++ 1.117/sql/item_sum.h 2007-03-22 11:43:30 +04:00
@@ -215,7 +215,9 @@
TODO: to catch queries where the limit is exceeded to make the
code clean here.
-*/
+*/
+
+class st_select_lex;
class Item_sum :public Item_result_field
{
@@ -237,19 +239,26 @@ public:
int8 max_sum_func_level;/* max level of aggregation for embedded functions */
bool quick_group; /* If incremental update of fields */
+protected:
+ table_map used_tables_cache;
+ bool forced_const;
+ byte nest_level_tables_count;
+
+public:
+
void mark_as_sum_func();
- Item_sum() :arg_count(0), quick_group(1)
+ Item_sum() :arg_count(0), quick_group(1), forced_const(FALSE)
{
mark_as_sum_func();
}
- Item_sum(Item *a)
- :args(tmp_args), arg_count(1), quick_group(1)
+ Item_sum(Item *a) :args(tmp_args), arg_count(1), quick_group(1),
+ forced_const(FALSE)
{
args[0]=a;
mark_as_sum_func();
}
- Item_sum( Item *a, Item *b )
- :args(tmp_args), arg_count(2), quick_group(1)
+ Item_sum( Item *a, Item *b ) :args(tmp_args), arg_count(2), quick_group(1),
+ forced_const(FALSE)
{
args[0]=a; args[1]=b;
mark_as_sum_func();
@@ -319,10 +328,20 @@ public:
virtual const char *func_name() const= 0;
virtual Item *result_item(Field *field)
{ return new Item_field(field); }
- table_map used_tables() const { return ~(table_map) 0; } /* Not used */
- bool const_item() const { return 0; }
+ table_map used_tables() const { return used_tables_cache; }
+ void update_used_tables ();
+ void cleanup()
+ {
+ Item::cleanup();
+ forced_const= FALSE;
+ }
bool is_null() { return null_value; }
- void update_used_tables() { }
+ void make_const ()
+ {
+ used_tables_cache= 0;
+ forced_const= TRUE;
+ }
+ virtual bool const_item() const { return forced_const; }
void make_field(Send_field *field);
void print(String *str);
void fix_num_length_and_dec();
@@ -509,23 +528,23 @@ public:
class Item_sum_count :public Item_sum_int
{
longlong count;
- table_map used_table_cache;
public:
Item_sum_count(Item *item_par)
- :Item_sum_int(item_par),count(0),used_table_cache(~(table_map) 0)
+ :Item_sum_int(item_par),count(0)
{}
Item_sum_count(THD *thd, Item_sum_count *item)
- :Item_sum_int(thd, item), count(item->count),
- used_table_cache(item->used_table_cache)
+ :Item_sum_int(thd, item), count(item->count)
{}
- table_map used_tables() const { return used_table_cache; }
- bool const_item() const { return !used_table_cache; }
enum Sumfunctype sum_func () const { return COUNT_FUNC; }
void clear();
void no_rows_in_result() { count=0; }
bool add();
- void make_const(longlong count_arg) { count=count_arg; used_table_cache=0; }
+ void make_const(longlong count_arg)
+ {
+ count=count_arg;
+ Item_sum::make_const();
+ }
longlong val_int();
void reset_field();
void cleanup();
@@ -805,28 +824,22 @@ protected:
Item_result hybrid_type;
enum_field_types hybrid_field_type;
int cmp_sign;
- table_map used_table_cache;
bool was_values; // Set if we have found at least one row (for max/min only)
public:
Item_sum_hybrid(Item *item_par,int sign)
:Item_sum(item_par), sum(0.0), sum_int(0),
hybrid_type(INT_RESULT), hybrid_field_type(MYSQL_TYPE_LONGLONG),
- cmp_sign(sign), used_table_cache(~(table_map) 0),
- was_values(TRUE)
+ cmp_sign(sign), was_values(TRUE)
{ collation.set(&my_charset_bin); }
Item_sum_hybrid(THD *thd, Item_sum_hybrid *item);
bool fix_fields(THD *, Item **);
- table_map used_tables() const { return used_table_cache; }
- bool const_item() const { return !used_table_cache; }
-
void clear();
double val_real();
longlong val_int();
my_decimal *val_decimal(my_decimal *);
void reset_field();
String *val_str(String *);
- void make_const() { used_table_cache=0; }
bool keep_field_type(void) const { return 1; }
enum Item_result result_type () const { return hybrid_type; }
enum enum_field_types field_type() const { return hybrid_field_type; }
--- 1.268/sql/opt_range.cc 2007-03-22 11:43:30 +04:00
+++ 1.269/sql/opt_range.cc 2007-03-22 11:43:30 +04:00
@@ -8985,7 +8985,8 @@ get_best_group_min_max(PARAM *param, SEL
else
DBUG_RETURN(NULL);
- Item *expr= min_max_item->args[0]; /* The argument of MIN/MAX. */
+ /* The argument of MIN/MAX. */
+ Item *expr= min_max_item->args[0]->real_item();
if (expr->type() == Item::FIELD_ITEM) /* Is it an attribute? */
{
if (! min_max_arg_item)
@@ -9356,6 +9357,7 @@ check_group_min_max_predicates(COND *con
DBUG_ENTER("check_group_min_max_predicates");
DBUG_ASSERT(cond && min_max_arg_item);
+ cond= cond->real_item();
Item::Type cond_type= cond->type();
if (cond_type == Item::COND_ITEM) /* 'AND' or 'OR' */
{
@@ -9393,7 +9395,7 @@ check_group_min_max_predicates(COND *con
DBUG_PRINT("info", ("Analyzing: %s", pred->func_name()));
for (uint arg_idx= 0; arg_idx < pred->argument_count (); arg_idx++)
{
- cur_arg= arguments[arg_idx];
+ cur_arg= arguments[arg_idx]->real_item();
DBUG_PRINT("info", ("cur_arg: %s", cur_arg->full_name()));
if (cur_arg->type() == Item::FIELD_ITEM)
{
--- 1.347/sql/sql_class.h 2007-03-22 11:43:30 +04:00
+++ 1.348/sql/sql_class.h 2007-03-22 11:43:30 +04:00
@@ -1411,7 +1411,10 @@ public:
#ifdef WITH_PARTITION_STORAGE_ENGINE
partition_info *work_part_info;
#endif
-
+
+ /* pass up the count of "leaf" tables in a JOIN out of setup_tables() */
+ byte leaf_count;
+
THD();
~THD();
--- 1.254/sql/sql_insert.cc 2007-03-22 11:43:30 +04:00
+++ 1.255/sql/sql_insert.cc 2007-03-22 11:43:30 +04:00
@@ -2466,12 +2466,14 @@ bool mysql_insert_select_prepare(THD *th
DBUG_ASSERT(select_lex->leaf_tables != 0);
lex->leaf_tables_insert= select_lex->leaf_tables;
/* skip all leaf tables belonged to view where we are insert */
- for (first_select_leaf_table= select_lex->leaf_tables->next_leaf;
+ for (first_select_leaf_table= select_lex->leaf_tables->next_leaf,
+ thd->leaf_count --;
first_select_leaf_table &&
first_select_leaf_table->belong_to_view &&
first_select_leaf_table->belong_to_view ==
lex->leaf_tables_insert->belong_to_view;
- first_select_leaf_table= first_select_leaf_table->next_leaf)
+ first_select_leaf_table= first_select_leaf_table->next_leaf,
+ thd->leaf_count --)
{}
select_lex->leaf_tables= first_select_leaf_table;
DBUG_RETURN(FALSE);
--- 1.498/sql/sql_select.cc 2007-03-22 11:43:30 +04:00
+++ 1.499/sql/sql_select.cc 2007-03-22 11:43:30 +04:00
@@ -408,12 +408,14 @@ JOIN::prepare(Item ***rref_pointer_array
/* Check that all tables, fields, conds and order are ok */
- if ((!(select_options & OPTION_SETUP_TABLES_DONE) &&
- setup_tables_and_check_access(thd, &select_lex->context, join_list,
- tables_list,
- &select_lex->leaf_tables, FALSE,
- SELECT_ACL, SELECT_ACL)) ||
- setup_wild(thd, tables_list, fields_list, &all_fields, wild_num) ||
+ if (!(select_options & OPTION_SETUP_TABLES_DONE) &&
+ setup_tables_and_check_access(thd, &select_lex->context, join_list,
+ tables_list, &select_lex->leaf_tables,
+ FALSE, SELECT_ACL, SELECT_ACL))
+ DBUG_RETURN(-1);
+ tables= thd->leaf_count;
+
+ if (setup_wild(thd, tables_list, fields_list, &all_fields, wild_num) ||
select_lex->setup_ref_array(thd, og_num) ||
setup_fields(thd, (*rref_pointer_array), fields_list, MARK_COLUMNS_READ,
&all_fields, 1) ||
@@ -518,11 +520,6 @@ JOIN::prepare(Item ***rref_pointer_array
DBUG_RETURN(-1);
}
}
- TABLE_LIST *table_ptr;
- for (table_ptr= select_lex->leaf_tables;
- table_ptr;
- table_ptr= table_ptr->next_leaf)
- tables++;
}
{
/* Caclulate the number of groups */
@@ -6635,7 +6632,8 @@ static void update_depend_map(JOIN *join
order->item[0]->update_used_tables();
order->depend_map=depend_map=order->item[0]->used_tables();
// Not item_sum(), RAND() and no reference to table outside of sub select
- if (!(order->depend_map & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT)))
+ if (!(order->depend_map & (OUTER_REF_TABLE_BIT | RAND_TABLE_BIT))
+ && !order->item[0]->with_sum_func)
{
for (JOIN_TAB **tab=join->map2table;
depend_map ;
--- 1.552/sql/sql_yacc.yy 2007-03-22 11:43:30 +04:00
+++ 1.553/sql/sql_yacc.yy 2007-03-22 11:43:30 +04:00
@@ -11086,7 +11086,7 @@ union_list:
UNION_SYM union_option
{
LEX *lex=Lex;
- if (lex->exchange)
+ if (lex->result)
{
/* Only the last SELECT can have INTO...... */
my_error(ER_WRONG_USAGE, MYF(0), "UNION", "INTO");
--- 1.95/mysql-test/r/union.result 2007-03-22 11:43:30 +04:00
+++ 1.96/mysql-test/r/union.result 2007-03-22 11:43:30 +04:00
@@ -1437,4 +1437,12 @@ a
SELECT a FROM (SELECT a FROM t1 UNION SELECT a FROM t1 ORDER BY c) AS test;
ERROR 42S22: Unknown column 'c' in 'order clause'
DROP TABLE t1;
+(select 1 into @var) union (select 1);
+ERROR HY000: Incorrect usage of UNION and INTO
+(select 1) union (select 1 into @var);
+select @var;
+@var
+1
+(select 2) union (select 1 into @var);
+ERROR 42000: Result consisted of more than one row
End of 5.0 tests
--- 1.100/mysql-test/t/union.test 2007-03-22 11:43:30 +04:00
+++ 1.101/mysql-test/t/union.test 2007-03-22 11:43:30 +04:00
@@ -913,4 +913,13 @@ SELECT a FROM (SELECT a FROM t1 UNION SE
DROP TABLE t1;
+#
+# Bug#23345: Wrongly allowed INTO in a non-last select of a UNION.
+#
+--error 1221
+(select 1 into @var) union (select 1);
+(select 1) union (select 1 into @var);
+select @var;
+--error 1172
+(select 2) union (select 1 into @var);
--echo End of 5.0 tests
--- 1.174/mysql-test/r/subselect.result 2007-03-22 11:43:30 +04:00
+++ 1.175/mysql-test/r/subselect.result 2007-03-22 11:43:30 +04:00
@@ -3712,12 +3712,6 @@ bb 2
cc 3
dd 1
DROP TABLE t1,t2,t3;
-CREATE TABLE t1 (s1 char(1));
-INSERT INTO t1 VALUES ('a');
-SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
-s1
-a
-DROP TABLE t1;
CREATE TABLE t1(f1 int);
CREATE TABLE t2(f2 int, f21 int, f3 timestamp);
INSERT INTO t1 VALUES (1),(1),(2),(2);
@@ -3886,3 +3880,34 @@ this is a test. 3
this is a test. 1
this is a test. 2
DROP table t1;
+CREATE TABLE t1 (a int, b int);
+CREATE TABLE t2 (m int, n int);
+INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
+INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
+SELECT COUNT(*), a,
+(SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
+FROM t1 GROUP BY a;
+COUNT(*) a (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
+2 2 2
+3 3 3
+1 4 1
+SELECT COUNT(*), a,
+(SELECT MIN(m) FROM t2 WHERE m = count(*))
+FROM t1 GROUP BY a;
+COUNT(*) a (SELECT MIN(m) FROM t2 WHERE m = count(*))
+2 2 2
+3 3 3
+1 4 1
+SELECT COUNT(*), a
+FROM t1 GROUP BY a
+HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
+COUNT(*) a
+2 2
+3 3
+DROP TABLE t1,t2;
+CREATE TABLE t1 (s1 char(1));
+INSERT INTO t1 VALUES ('a');
+SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
+s1
+a
+DROP TABLE t1;
--- 1.148/mysql-test/t/subselect.test 2007-03-22 11:43:30 +04:00
+++ 1.149/mysql-test/t/subselect.test 2007-03-22 11:43:30 +04:00
@@ -2602,13 +2602,6 @@ SELECT * FROM t1
DROP TABLE t1,t2,t3;
-#
-# Bug#20835 (literal string with =any values)
-#
-CREATE TABLE t1 (s1 char(1));
-INSERT INTO t1 VALUES ('a');
-SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
-DROP TABLE t1;
#
# Bug#23800: Outer fields in correlated subqueries is used in a temporary
# table created for sorting.
@@ -2748,3 +2741,33 @@ SELECT * FROM (SELECT 'this is ' 'a test
DROP table t1;
+#
+# Bug #27257: COUNT(*) aggregated in outer query
+#
+
+CREATE TABLE t1 (a int, b int);
+CREATE TABLE t2 (m int, n int);
+INSERT INTO t1 VALUES (2,2), (2,2), (3,3), (3,3), (3,3), (4,4);
+INSERT INTO t2 VALUES (1,11), (2,22), (3,32), (4,44), (4,44);
+
+SELECT COUNT(*), a,
+ (SELECT m FROM t2 WHERE m = count(*) LIMIT 1)
+ FROM t1 GROUP BY a;
+
+SELECT COUNT(*), a,
+ (SELECT MIN(m) FROM t2 WHERE m = count(*))
+ FROM t1 GROUP BY a;
+
+SELECT COUNT(*), a
+ FROM t1 GROUP BY a
+ HAVING (SELECT MIN(m) FROM t2 WHERE m = count(*)) > 1;
+
+DROP TABLE t1,t2;
+
+#
+# Bug#20835 (literal string with =any values)
+#
+CREATE TABLE t1 (s1 char(1));
+INSERT INTO t1 VALUES ('a');
+SELECT * FROM t1 WHERE _utf8'a' = ANY (SELECT s1 FROM t1);
+DROP TABLE t1;
--- 1.6/mysql-test/r/subselect3.result 2007-03-22 11:43:30 +04:00
+++ 1.7/mysql-test/r/subselect3.result 2007-03-22 11:43:30 +04:00
@@ -645,3 +645,56 @@ a b Z
2 2 0
3 3 1
drop table t1,t2;
+CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
+INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
+(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'),
+(1,9,'m');
+CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
+INSERT INTO t2 SELECT * FROM t1;
+SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b))
+as test FROM t1 GROUP BY a;
+a MAX(b) test
+1 9 m
+2 3 h
+3 4 i
+SELECT * FROM t1 GROUP by t1.a
+HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c
+HAVING MAX(t2.b+t1.a) < 10));
+a b c
+SELECT a, AVG(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=AVG(t1.b))
+AS test FROM t1 GROUP BY a;
+a AVG(b) test
+1 4.0000 NULL
+2 2.0000 k
+3 2.5000 NULL
+SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c;
+a b c
+1 3 c
+2 3 h
+3 3 j
+1 4 d
+3 4 i
+1 9 m
+SELECT a, MAX(b),
+(SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)
+LIMIT 1)
+as cnt,
+(SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1)
+as t_b,
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1)
+as t_b,
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1)
+as t_b
+FROM t1 GROUP BY a;
+a MAX(b) cnt t_b t_b t_b
+1 9 1 9 m m
+2 3 1 3 h h
+3 4 1 4 i i
+SELECT a, MAX(b),
+(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test
+FROM t1 GROUP BY a;
+a MAX(b) test
+1 9 m
+2 3 h
+3 4 i
+DROP TABLE t1, t2;
| Thread |
|---|
| • bk commit into 5.1 tree (holyfoot:1.2506) | holyfoot | 22 Mar |
