summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/having.result65
-rw-r--r--mysql-test/t/having.test68
-rw-r--r--mysys/mf_keycache.c9
-rw-r--r--sql/sql_select.cc20
4 files changed, 152 insertions, 10 deletions
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index fc2df2b7fc2..a397592ae66 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -570,3 +570,68 @@ ORDER BY t1.f1;
f1
DROP TABLE t1,t2;
End of 5.1 tests
+#
+# LP bug:938518 HAVING does not reject the result of aggregation
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY, a INT);
+INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0);
+SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1;
+MIN(t.pk)
+NULL
+SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1 HAVING MIN(t.pk) < 10;
+MIN(t.pk)
+drop table t1;
+#
+# LP bug #791761: MAX over an empty join + HAVING
+#
+CREATE TABLE t1 (a int, b int , KEY (b)) ;
+INSERT INTO t1 VALUES (3,1);
+CREATE TABLE t2 (a int NOT NULL ) ;
+INSERT INTO t2 VALUES (29);
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a > 0 HAVING MAX(t1.b) <> 6;
+MAX(t1.b)
+1
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a > 0 HAVING MAX(t1.b) IS NULL;
+MAX(t1.b)
+EXPLAIN
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a < 0 HAVING MAX(t1.b) <> 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a < 0 HAVING MAX(t1.b) <> 6;
+MAX(t1.b)
+CREATE TABLE t3 ( f3 int) ;
+INSERT INTO t3 VALUES (NULL);
+SELECT MAX(t1.b) AS f FROM t1 JOIN t2 ON t2.a != 0
+WHERE (SELECT f3 FROM t3) <> 0 HAVING f <> 6 ;
+f
+DROP TABLE t1,t2,t3;
+#
+# LP bug:806955 HAVING not observed with aggregate +subquery
+#
+CREATE TABLE t1 (f3 int, f10 varchar(1), f11 int, KEY (f10) );
+INSERT INTO t1 VALUES (NULL,'a',0),(8,'b',0);
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (7);
+CREATE TABLE t3 (f3 int);
+INSERT INTO t3 VALUES (0),(8);
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin=off,materialization=off';
+SELECT MIN( t1.f10 ) AS field1
+FROM t1 , t2
+WHERE t2.f2 IN ( SELECT f3 FROM t3 )
+HAVING field1 < 's';
+field1
+explain extended
+SELECT MIN( t1.f10 ) AS field1
+FROM t1 , t2
+WHERE t2.f2 IN ( SELECT f3 FROM t3 )
+HAVING field1 < 's';
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
+1 PRIMARY t1 index NULL f10 4 NULL 2 100.00 Using index
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
+Warnings:
+Note 1003 select min(`test`.`t1`.`f10`) AS `field1` from `test`.`t1` join `test`.`t2` where <expr_cache><7>(<in_optimizer>(7,<exists>(select `test`.`t3`.`f3` from `test`.`t3` where (<cache>(7) = `test`.`t3`.`f3`)))) having (`field1` < 's')
+set optimizer_switch=@save_optimizer_switch;
+drop table t1,t2,t3;
+End of 5.2 tests
diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test
index 2ed8b40b858..3edf449c92d 100644
--- a/mysql-test/t/having.test
+++ b/mysql-test/t/having.test
@@ -589,5 +589,71 @@ ORDER BY t1.f1;
DROP TABLE t1,t2;
-
--echo End of 5.1 tests
+
+--echo #
+--echo # LP bug:938518 HAVING does not reject the result of aggregation
+--echo #
+CREATE TABLE t1 (pk INT PRIMARY KEY, a INT);
+INSERT INTO t1 VALUES (2,7), (4,7), (6,2), (17,0);
+SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1;
+SELECT MIN(t.pk) FROM t1, t1 as t WHERE t1.pk = 1 HAVING MIN(t.pk) < 10;
+drop table t1;
+
+--echo #
+--echo # LP bug #791761: MAX over an empty join + HAVING
+--echo #
+
+CREATE TABLE t1 (a int, b int , KEY (b)) ;
+INSERT INTO t1 VALUES (3,1);
+
+CREATE TABLE t2 (a int NOT NULL ) ;
+INSERT INTO t2 VALUES (29);
+
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a > 0 HAVING MAX(t1.b) <> 6;
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a > 0 HAVING MAX(t1.b) IS NULL;
+
+EXPLAIN
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a < 0 HAVING MAX(t1.b) <> 6;
+SELECT MAX(t1.b) FROM t1,t2 WHERE t2.a < 0 HAVING MAX(t1.b) <> 6;
+
+CREATE TABLE t3 ( f3 int) ;
+INSERT INTO t3 VALUES (NULL);
+
+SELECT MAX(t1.b) AS f FROM t1 JOIN t2 ON t2.a != 0
+ WHERE (SELECT f3 FROM t3) <> 0 HAVING f <> 6 ;
+
+DROP TABLE t1,t2,t3;
+
+--echo #
+--echo # LP bug:806955 HAVING not observed with aggregate +subquery
+--echo #
+
+CREATE TABLE t1 (f3 int, f10 varchar(1), f11 int, KEY (f10) );
+INSERT INTO t1 VALUES (NULL,'a',0),(8,'b',0);
+
+CREATE TABLE t2 (f2 int);
+INSERT INTO t2 VALUES (7);
+
+CREATE TABLE t3 (f3 int);
+INSERT INTO t3 VALUES (0),(8);
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='semijoin=off,materialization=off';
+
+SELECT MIN( t1.f10 ) AS field1
+FROM t1 , t2
+WHERE t2.f2 IN ( SELECT f3 FROM t3 )
+HAVING field1 < 's';
+
+explain extended
+SELECT MIN( t1.f10 ) AS field1
+FROM t1 , t2
+WHERE t2.f2 IN ( SELECT f3 FROM t3 )
+HAVING field1 < 's';
+
+set optimizer_switch=@save_optimizer_switch;
+
+drop table t1,t2,t3;
+
+--echo End of 5.2 tests
diff --git a/mysys/mf_keycache.c b/mysys/mf_keycache.c
index a038061d9d0..e0d495ce8e0 100644
--- a/mysys/mf_keycache.c
+++ b/mysys/mf_keycache.c
@@ -5945,6 +5945,10 @@ int init_key_cache(KEY_CACHE *keycache, uint key_cache_block_size,
((PARTITIONED_KEY_CACHE_CB *) keycache_cb)->partitions :
0;
DBUG_ASSERT(partitions <= MAX_KEY_CACHE_PARTITIONS);
+ keycache->key_cache_mem_size=
+ keycache->partitions ?
+ ((PARTITIONED_KEY_CACHE_CB *) keycache_cb)->key_cache_mem_size :
+ ((SIMPLE_KEY_CACHE_CB *) keycache_cb)->key_cache_mem_size;
if (blocks > 0)
keycache->can_be_used= 1;
return blocks;
@@ -6008,6 +6012,11 @@ int resize_key_cache(KEY_CACHE *keycache, uint key_cache_block_size,
((PARTITIONED_KEY_CACHE_CB *)(keycache->keycache_cb))->partitions;
}
+ keycache->key_cache_mem_size=
+ keycache->partitions ?
+ ((PARTITIONED_KEY_CACHE_CB *)(keycache->keycache_cb))->key_cache_mem_size :
+ ((SIMPLE_KEY_CACHE_CB *)(keycache->keycache_cb))->key_cache_mem_size;
+
keycache->can_be_used= (blocks >= 0);
}
return blocks;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d5a23374455..fcbde9e27d3 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -105,7 +105,7 @@ static int return_zero_rows(JOIN *join, select_result *res,
List<TABLE_LIST> &tables,
List<Item> &fields, bool send_row,
ulonglong select_options, const char *info,
- Item *having);
+ Item *having, List<Item> &all_fields);
static COND *build_equal_items(THD *thd, COND *cond,
COND_EQUAL *inherited,
List<TABLE_LIST> *join_list,
@@ -2177,7 +2177,7 @@ JOIN::exec()
send_row_on_empty_set(),
select_options,
zero_result_cause,
- having ? having : tmp_having);
+ having ? having : tmp_having, all_fields);
DBUG_VOID_RETURN;
}
@@ -10440,7 +10440,7 @@ remove_const(JOIN *join,ORDER *first_order, COND *cond,
static int
return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables,
List<Item> &fields, bool send_row, ulonglong select_options,
- const char *info, Item *having)
+ const char *info, Item *having, List<Item> &all_fields)
{
DBUG_ENTER("return_zero_rows");
@@ -10470,6 +10470,14 @@ return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables,
if (!table->is_jtbm())
mark_as_null_row(table->table); // All fields are NULL
}
+ List_iterator_fast<Item> it(all_fields);
+ Item *item;
+ /*
+ Inform all items (especially aggregating) to calculate HAVING correctly,
+ also we will need it for sending results.
+ */
+ while ((item= it++))
+ item->no_rows_in_result();
if (having && having->val_int() == 0)
send_row=0;
}
@@ -10478,13 +10486,7 @@ return_zero_rows(JOIN *join, select_result *result, List<TABLE_LIST> &tables,
{
bool send_error= FALSE;
if (send_row)
- {
- List_iterator_fast<Item> it(fields);
- Item *item;
- while ((item= it++))
- item->no_rows_in_result();
send_error= result->send_data(fields) > 0;
- }
if (!send_error)
result->send_eof(); // Should be safe
}
View Lorry Controller Status