3100 Tor Didriksen 2011-05-18 [merge]
Automerge opt-team => trunk
modified:
mysql-test/include/mrr_tests.inc
mysql-test/include/subquery.inc
mysql-test/r/func_in_all.result
mysql-test/r/innodb_mrr.result
mysql-test/r/innodb_mrr_all.result
mysql-test/r/innodb_mrr_cost.result
mysql-test/r/innodb_mrr_cost_all.result
mysql-test/r/innodb_mrr_cost_icp.result
mysql-test/r/innodb_mrr_icp.result
mysql-test/r/innodb_mrr_none.result
mysql-test/r/myisam_mrr.result
mysql-test/r/myisam_mrr_all.result
mysql-test/r/myisam_mrr_cost.result
mysql-test/r/myisam_mrr_cost_all.result
mysql-test/r/myisam_mrr_cost_icp.result
mysql-test/r/myisam_mrr_icp.result
mysql-test/r/myisam_mrr_none.result
mysql-test/r/subquery_all.result
mysql-test/r/subquery_all_jcl6.result
mysql-test/r/subquery_nomat_nosj.result
mysql-test/r/subquery_nomat_nosj_jcl6.result
mysql-test/r/subquery_none.result
mysql-test/r/subquery_none_jcl6.result
sql/filesort.cc
sql/filesort_utils.cc
sql/ha_ndbcluster.cc
sql/handler.cc
sql/item_cmpfunc.cc
sql/item_func.h
sql/opt_range.cc
sql/sql_const.h
sql/sql_select.cc
sql/uniques.cc
3099 Tor Didriksen 2011-05-18
Bug#12552221 - MEMORY LEAK IN UPDATE_REF_AND_KEYS
Allocate array in memroot rather than heap.
Fixes the leak, and also yields a 1-2% performance gain with sysbench if you have lots of cpus/threads.
@ sql/mem_root_array.h
A typesafe replacement for DYNAMIC_ARRAY.
We use MEM_ROOT for allocating storage, rather than the C++ heap.
The interface is chosen to be similar to std::vector.
@ sql/sql_select.cc
Use Key_use_array rather than DYNAMIC_ARRAY.
@ sql/sql_select.h
Use Key_use_array rather than DYNAMIC_ARRAY.
@ sql/sql_test.cc
Use Key_use_array rather than DYNAMIC_ARRAY.
@ sql/sql_test.h
Use Key_use_array rather than DYNAMIC_ARRAY.
@ unittest/gunit/dynarray-t.cc
Unit tests for Mem_root_array.
Also performance testing comparing DYNAMIC_ARRAY with std::vector and Mem_root_array.
added:
sql/mem_root_array.h
unittest/gunit/dynarray-t.cc
modified:
sql/sql_select.cc
sql/sql_select.h
sql/sql_test.cc
sql/sql_test.h
unittest/gunit/CMakeLists.txt
=== modified file 'mysql-test/include/mrr_tests.inc'
--- a/mysql-test/include/mrr_tests.inc 2011-01-07 08:38:31 +0000
+++ b/mysql-test/include/mrr_tests.inc 2011-05-05 11:10:39 +0000
@@ -377,3 +377,36 @@ eval EXPLAIN $query;
eval $query;
DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+--echo #
+
+# This test should run with join cache level 0
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+
+CREATE TABLE t1 (
+ pk INTEGER,
+ c1 VARCHAR(1) NOT NULL,
+ PRIMARY KEY (pk)
+);
+
+CREATE TABLE t2 (
+ c1 VARCHAR(1) NOT NULL
+);
+
+INSERT INTO t2 VALUES ('v'), ('c');
+
+let query=
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1,t2;
+
+# Restore join cache level to its original value
+set optimizer_join_cache_level= @save_join_cache_level;
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc 2011-04-15 08:11:49 +0000
+++ b/mysql-test/include/subquery.inc 2011-05-05 07:41:53 +0000
@@ -4740,8 +4740,6 @@ CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN (
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
DROP VIEW v1, v2;
---echo End of 5.1 tests.
-
#
# Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN on index
#
@@ -5101,6 +5099,54 @@ SELECT 1 FROM
DROP TABLE t1;
+--echo #
+--echo # Bug#11764086: Null left operand to NOT IN in WHERE clause
+--echo # behaves differently than real NULL
+--echo #
+
+CREATE TABLE parent (id int);
+INSERT INTO parent VALUES (1), (2);
+
+CREATE TABLE child (parent_id int, other int);
+INSERT INTO child VALUES (1,NULL);
+
+--echo # Offending query (c.parent_id is NULL for null-complemented rows only)
+
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+ SELECT parent_id
+ FROM child
+ WHERE parent_id = 3
+ );
+
+--echo # Some syntactic variations with IS FALSE and IS NOT TRUE
+
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+ SELECT parent_id
+ FROM child
+ WHERE parent_id = 3
+ ) IS NOT TRUE;
+
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+ SELECT parent_id
+ FROM child
+ WHERE parent_id = 3
+ ) IS FALSE;
+
+DROP TABLE parent, child;
+
+--echo # End of test for bug#11764086.
--echo End of 5.5 tests.
=== modified file 'mysql-test/r/func_in_all.result'
--- a/mysql-test/r/func_in_all.result 2011-04-12 10:31:30 +0000
+++ b/mysql-test/r/func_in_all.result 2011-04-28 11:53:14 +0000
@@ -806,5 +806,16 @@ WHERE 1+NULL NOT IN
(SELECT i FROM subq WHERE subq.pk = t1.pk);
pk i
DROP TABLE t1,subq;
+#
+# Bug #11766270 59343: YEAR(4): INCORRECT RESULT AND VALGRIND WARNINGS WITH MIN/MAX, UNION
+#
+CREATE TABLE t1(f1 YEAR(4));
+INSERT INTO t1 VALUES (0000),(2001);
+(SELECT MAX(f1) FROM t1) UNION (SELECT MAX(f1) FROM t1);
+Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
+def MAX(f1) MAX(f1) 13 4 4 Y 32864 0 63
+MAX(f1)
+2001
+DROP TABLE t1;
End of 5.1 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/innodb_mrr.result'
--- a/mysql-test/r/innodb_mrr.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/innodb_mrr.result 2011-05-05 11:10:39 +0000
@@ -543,6 +543,32 @@ ORDER BY i1;
i1
DROP TABLE t1, t2;
#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
+#
# Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
#
SET AUTOCOMMIT=0;
=== modified file 'mysql-test/r/innodb_mrr_all.result'
--- a/mysql-test/r/innodb_mrr_all.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/innodb_mrr_all.result 2011-05-05 11:10:39 +0000
@@ -543,6 +543,32 @@ ORDER BY i1;
i1
DROP TABLE t1, t2;
#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
+#
# Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
#
SET AUTOCOMMIT=0;
=== modified file 'mysql-test/r/innodb_mrr_cost.result'
--- a/mysql-test/r/innodb_mrr_cost.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/innodb_mrr_cost.result 2011-05-05 11:10:39 +0000
@@ -543,6 +543,32 @@ ORDER BY i1;
i1
DROP TABLE t1, t2;
#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
+#
# Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
#
SET AUTOCOMMIT=0;
=== modified file 'mysql-test/r/innodb_mrr_cost_all.result'
--- a/mysql-test/r/innodb_mrr_cost_all.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/innodb_mrr_cost_all.result 2011-05-05 11:10:39 +0000
@@ -543,6 +543,32 @@ ORDER BY i1;
i1
DROP TABLE t1, t2;
#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
+#
# Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
#
SET AUTOCOMMIT=0;
=== modified file 'mysql-test/r/innodb_mrr_cost_icp.result'
--- a/mysql-test/r/innodb_mrr_cost_icp.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/innodb_mrr_cost_icp.result 2011-05-05 11:10:39 +0000
@@ -543,6 +543,32 @@ ORDER BY i1;
i1
DROP TABLE t1, t2;
#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
+#
# Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
#
SET AUTOCOMMIT=0;
=== modified file 'mysql-test/r/innodb_mrr_icp.result'
--- a/mysql-test/r/innodb_mrr_icp.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/innodb_mrr_icp.result 2011-05-05 11:10:39 +0000
@@ -543,6 +543,32 @@ ORDER BY i1;
i1
DROP TABLE t1, t2;
#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
+#
# Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
#
SET AUTOCOMMIT=0;
=== modified file 'mysql-test/r/innodb_mrr_none.result'
--- a/mysql-test/r/innodb_mrr_none.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/innodb_mrr_none.result 2011-05-05 11:10:39 +0000
@@ -542,6 +542,32 @@ ORDER BY i1;
i1
DROP TABLE t1, t2;
#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
+#
# Bug#41029 "MRR: SELECT FOR UPDATE fails to lock gaps (InnoDB table)"
#
SET AUTOCOMMIT=0;
=== modified file 'mysql-test/r/myisam_mrr.result'
--- a/mysql-test/r/myisam_mrr.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/myisam_mrr.result 2011-05-05 11:10:39 +0000
@@ -544,5 +544,31 @@ AND t2.pk IS NULL
ORDER BY i1;
i1
DROP TABLE t1, t2;
+#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using MRR
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_mrr_all.result'
--- a/mysql-test/r/myisam_mrr_all.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/myisam_mrr_all.result 2011-05-05 11:10:39 +0000
@@ -544,5 +544,31 @@ AND t2.pk IS NULL
ORDER BY i1;
i1
DROP TABLE t1, t2;
+#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where; Using MRR
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_mrr_cost.result'
--- a/mysql-test/r/myisam_mrr_cost.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/myisam_mrr_cost.result 2011-05-05 11:10:39 +0000
@@ -544,5 +544,31 @@ AND t2.pk IS NULL
ORDER BY i1;
i1
DROP TABLE t1, t2;
+#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_mrr_cost_all.result'
--- a/mysql-test/r/myisam_mrr_cost_all.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/myisam_mrr_cost_all.result 2011-05-05 11:10:39 +0000
@@ -544,5 +544,31 @@ AND t2.pk IS NULL
ORDER BY i1;
i1
DROP TABLE t1, t2;
+#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_mrr_cost_icp.result'
--- a/mysql-test/r/myisam_mrr_cost_icp.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/myisam_mrr_cost_icp.result 2011-05-05 11:10:39 +0000
@@ -544,5 +544,31 @@ AND t2.pk IS NULL
ORDER BY i1;
i1
DROP TABLE t1, t2;
+#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_mrr_icp.result'
--- a/mysql-test/r/myisam_mrr_icp.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/myisam_mrr_icp.result 2011-05-05 11:10:39 +0000
@@ -544,5 +544,31 @@ AND t2.pk IS NULL
ORDER BY i1;
i1
DROP TABLE t1, t2;
+#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using where; Using MRR
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
set optimizer_switch=default;
=== modified file 'mysql-test/r/myisam_mrr_none.result'
--- a/mysql-test/r/myisam_mrr_none.result 2011-01-07 08:38:31 +0000
+++ b/mysql-test/r/myisam_mrr_none.result 2011-05-05 11:10:39 +0000
@@ -543,5 +543,31 @@ AND t2.pk IS NULL
ORDER BY i1;
i1
DROP TABLE t1, t2;
+#
+# Bug#12321461: CRASH IN DSMRR_IMPL::DSMRR_INIT ON SELECT STRAIGHT_JOIN
+#
+set @save_join_cache_level= @@optimizer_join_cache_level;
+set optimizer_join_cache_level=0;
+CREATE TABLE t1 (
+pk INTEGER,
+c1 VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk)
+);
+CREATE TABLE t2 (
+c1 VARCHAR(1) NOT NULL
+);
+INSERT INTO t2 VALUES ('v'), ('c');
+EXPLAIN SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where
+SELECT STRAIGHT_JOIN t1.c1
+FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1
+WHERE t1.pk > 176;
+c1
+DROP TABLE t1,t2;
+set optimizer_join_cache_level= @save_join_cache_level;
set @@read_rnd_buffer_size= @read_rnd_buffer_size_save;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result 2011-04-15 08:11:49 +0000
+++ b/mysql-test/r/subquery_all.result 2011-05-05 07:41:53 +0000
@@ -5900,7 +5900,6 @@ Note 1249 Select 2 was reduced during op
CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
DROP VIEW v1, v2;
-End of 5.1 tests.
Set up test tables.
CREATE TABLE t1 (
t1_id INT UNSIGNED,
@@ -6242,6 +6241,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# Bug#11764086: Null left operand to NOT IN in WHERE clause
+# behaves differently than real NULL
+#
+CREATE TABLE parent (id int);
+INSERT INTO parent VALUES (1), (2);
+CREATE TABLE child (parent_id int, other int);
+INSERT INTO child VALUES (1,NULL);
+# Offending query (c.parent_id is NULL for null-complemented rows only)
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+);
+id parent_id
+1 1
+2 NULL
+# Some syntactic variations with IS FALSE and IS NOT TRUE
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS NOT TRUE;
+id parent_id
+1 1
+2 NULL
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS FALSE;
+id parent_id
+1 1
+2 NULL
+DROP TABLE parent, child;
+# End of test for bug#11764086.
End of 5.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'mysql-test/r/subquery_all_jcl6.result'
--- a/mysql-test/r/subquery_all_jcl6.result 2011-03-29 08:10:26 +0000
+++ b/mysql-test/r/subquery_all_jcl6.result 2011-05-05 07:41:53 +0000
@@ -5904,7 +5904,6 @@ Note 1249 Select 2 was reduced during op
CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
DROP VIEW v1, v2;
-End of 5.1 tests.
Set up test tables.
CREATE TABLE t1 (
t1_id INT UNSIGNED,
@@ -6217,6 +6216,24 @@ id select_type table type possible_keys
DROP TABLE t2;
DROP TABLE t1;
#
+# Bug #11765713 58705:
+# OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
+# CREATED BY OPT_SUM_QUERY
+#
+CREATE TABLE t1(a INT NOT NULL, KEY (a));
+INSERT INTO t1 VALUES (0), (1);
+SELECT 1 as foo FROM t1 WHERE a < SOME
+(SELECT a FROM t1 WHERE a <=>
+(SELECT a FROM t1)
+);
+ERROR 21000: Subquery returns more than 1 row
+SELECT 1 as foo FROM t1 WHERE a < SOME
+(SELECT a FROM t1 WHERE a <=>
+(SELECT a FROM t1 where a is null)
+);
+foo
+DROP TABLE t1;
+#
# Bug #57704: Cleanup code dies with void TABLE::set_keyread(bool):
# Assertion `file' failed.
#
@@ -6228,6 +6245,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# Bug#11764086: Null left operand to NOT IN in WHERE clause
+# behaves differently than real NULL
+#
+CREATE TABLE parent (id int);
+INSERT INTO parent VALUES (1), (2);
+CREATE TABLE child (parent_id int, other int);
+INSERT INTO child VALUES (1,NULL);
+# Offending query (c.parent_id is NULL for null-complemented rows only)
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+);
+id parent_id
+1 1
+2 NULL
+# Some syntactic variations with IS FALSE and IS NOT TRUE
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS NOT TRUE;
+id parent_id
+1 1
+2 NULL
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS FALSE;
+id parent_id
+1 1
+2 NULL
+DROP TABLE parent, child;
+# End of test for bug#11764086.
End of 5.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result 2011-04-15 08:11:49 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result 2011-05-05 07:41:53 +0000
@@ -5900,7 +5900,6 @@ Note 1249 Select 2 was reduced during op
CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
DROP VIEW v1, v2;
-End of 5.1 tests.
Set up test tables.
CREATE TABLE t1 (
t1_id INT UNSIGNED,
@@ -6242,6 +6241,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# Bug#11764086: Null left operand to NOT IN in WHERE clause
+# behaves differently than real NULL
+#
+CREATE TABLE parent (id int);
+INSERT INTO parent VALUES (1), (2);
+CREATE TABLE child (parent_id int, other int);
+INSERT INTO child VALUES (1,NULL);
+# Offending query (c.parent_id is NULL for null-complemented rows only)
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+);
+id parent_id
+1 1
+2 NULL
+# Some syntactic variations with IS FALSE and IS NOT TRUE
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS NOT TRUE;
+id parent_id
+1 1
+2 NULL
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS FALSE;
+id parent_id
+1 1
+2 NULL
+DROP TABLE parent, child;
+# End of test for bug#11764086.
End of 5.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result'
--- a/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-03-29 08:10:26 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-05-05 07:41:53 +0000
@@ -5904,7 +5904,6 @@ Note 1249 Select 2 was reduced during op
CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
DROP VIEW v1, v2;
-End of 5.1 tests.
Set up test tables.
CREATE TABLE t1 (
t1_id INT UNSIGNED,
@@ -6217,6 +6216,24 @@ id select_type table type possible_keys
DROP TABLE t2;
DROP TABLE t1;
#
+# Bug #11765713 58705:
+# OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
+# CREATED BY OPT_SUM_QUERY
+#
+CREATE TABLE t1(a INT NOT NULL, KEY (a));
+INSERT INTO t1 VALUES (0), (1);
+SELECT 1 as foo FROM t1 WHERE a < SOME
+(SELECT a FROM t1 WHERE a <=>
+(SELECT a FROM t1)
+);
+ERROR 21000: Subquery returns more than 1 row
+SELECT 1 as foo FROM t1 WHERE a < SOME
+(SELECT a FROM t1 WHERE a <=>
+(SELECT a FROM t1 where a is null)
+);
+foo
+DROP TABLE t1;
+#
# Bug #57704: Cleanup code dies with void TABLE::set_keyread(bool):
# Assertion `file' failed.
#
@@ -6228,6 +6245,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# Bug#11764086: Null left operand to NOT IN in WHERE clause
+# behaves differently than real NULL
+#
+CREATE TABLE parent (id int);
+INSERT INTO parent VALUES (1), (2);
+CREATE TABLE child (parent_id int, other int);
+INSERT INTO child VALUES (1,NULL);
+# Offending query (c.parent_id is NULL for null-complemented rows only)
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+);
+id parent_id
+1 1
+2 NULL
+# Some syntactic variations with IS FALSE and IS NOT TRUE
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS NOT TRUE;
+id parent_id
+1 1
+2 NULL
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS FALSE;
+id parent_id
+1 1
+2 NULL
+DROP TABLE parent, child;
+# End of test for bug#11764086.
End of 5.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result 2011-04-15 08:11:49 +0000
+++ b/mysql-test/r/subquery_none.result 2011-05-05 07:41:53 +0000
@@ -5899,7 +5899,6 @@ Note 1249 Select 2 was reduced during op
CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
DROP VIEW v1, v2;
-End of 5.1 tests.
Set up test tables.
CREATE TABLE t1 (
t1_id INT UNSIGNED,
@@ -6241,6 +6240,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# Bug#11764086: Null left operand to NOT IN in WHERE clause
+# behaves differently than real NULL
+#
+CREATE TABLE parent (id int);
+INSERT INTO parent VALUES (1), (2);
+CREATE TABLE child (parent_id int, other int);
+INSERT INTO child VALUES (1,NULL);
+# Offending query (c.parent_id is NULL for null-complemented rows only)
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+);
+id parent_id
+1 1
+2 NULL
+# Some syntactic variations with IS FALSE and IS NOT TRUE
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS NOT TRUE;
+id parent_id
+1 1
+2 NULL
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS FALSE;
+id parent_id
+1 1
+2 NULL
+DROP TABLE parent, child;
+# End of test for bug#11764086.
End of 5.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'mysql-test/r/subquery_none_jcl6.result'
--- a/mysql-test/r/subquery_none_jcl6.result 2011-02-14 11:21:26 +0000
+++ b/mysql-test/r/subquery_none_jcl6.result 2011-05-05 07:41:53 +0000
@@ -5903,7 +5903,6 @@ Note 1249 Select 2 was reduced during op
CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
DROP VIEW v1, v2;
-End of 5.1 tests.
Set up test tables.
CREATE TABLE t1 (
t1_id INT UNSIGNED,
@@ -6216,6 +6215,24 @@ id select_type table type possible_keys
DROP TABLE t2;
DROP TABLE t1;
#
+# Bug #11765713 58705:
+# OPTIMIZER LET ENGINE DEPEND ON UNINITIALIZED VALUES
+# CREATED BY OPT_SUM_QUERY
+#
+CREATE TABLE t1(a INT NOT NULL, KEY (a));
+INSERT INTO t1 VALUES (0), (1);
+SELECT 1 as foo FROM t1 WHERE a < SOME
+(SELECT a FROM t1 WHERE a <=>
+(SELECT a FROM t1)
+);
+ERROR 21000: Subquery returns more than 1 row
+SELECT 1 as foo FROM t1 WHERE a < SOME
+(SELECT a FROM t1 WHERE a <=>
+(SELECT a FROM t1 where a is null)
+);
+foo
+DROP TABLE t1;
+#
# Bug #57704: Cleanup code dies with void TABLE::set_keyread(bool):
# Assertion `file' failed.
#
@@ -6227,6 +6244,54 @@ SELECT 1 FROM
1) FROM t1) AS e;
ERROR 21000: Operand should contain 1 column(s)
DROP TABLE t1;
+#
+# Bug#11764086: Null left operand to NOT IN in WHERE clause
+# behaves differently than real NULL
+#
+CREATE TABLE parent (id int);
+INSERT INTO parent VALUES (1), (2);
+CREATE TABLE child (parent_id int, other int);
+INSERT INTO child VALUES (1,NULL);
+# Offending query (c.parent_id is NULL for null-complemented rows only)
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id NOT IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+);
+id parent_id
+1 1
+2 NULL
+# Some syntactic variations with IS FALSE and IS NOT TRUE
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS NOT TRUE;
+id parent_id
+1 1
+2 NULL
+SELECT p.id, c.parent_id
+FROM parent p
+LEFT JOIN child c
+ON p.id = c.parent_id
+WHERE c.parent_id IN (
+SELECT parent_id
+FROM child
+WHERE parent_id = 3
+) IS FALSE;
+id parent_id
+1 1
+2 NULL
+DROP TABLE parent, child;
+# End of test for bug#11764086.
End of 5.5 tests.
#
# BUG#48920: COUNT DISTINCT returns 1 for NULL values when in a subquery
=== modified file 'sql/filesort.cc'
--- a/sql/filesort.cc 2011-04-04 08:47:25 +0000
+++ b/sql/filesort.cc 2011-05-02 13:22:25 +0000
@@ -1226,7 +1226,7 @@ bool check_if_pq_applicable(Sort_param *
row_length);
/*
PQ has cost:
- (insert + qsort) * log(queue size) / TIME_FOR_COMPARE_ROWID +
+ (insert + qsort) * log(queue size) * ROWID_COMPARE_COST +
cost of file lookup afterwards.
The lookup cost is a bit pessimistic: we take scan_time and assume
that on average we find the row after scanning half of the file.
@@ -1235,7 +1235,7 @@ bool check_if_pq_applicable(Sort_param *
*/
const double pq_cpu_cost=
(PQ_slowness * num_rows + param->max_keys_per_buffer) *
- log((double) param->max_keys_per_buffer) / TIME_FOR_COMPARE_ROWID;
+ log((double) param->max_keys_per_buffer) * ROWID_COMPARE_COST;
const double pq_io_cost=
param->max_rows * table->file->scan_time() / 2.0;
const double pq_cost= pq_cpu_cost + pq_io_cost;
=== modified file 'sql/filesort_utils.cc'
--- a/sql/filesort_utils.cc 2010-12-17 09:41:21 +0000
+++ b/sql/filesort_utils.cc 2011-05-02 13:22:25 +0000
@@ -26,8 +26,7 @@ double get_merge_cost(ha_rows num_elemen
{
return
2.0 * ((double) num_elements * elem_size) / IO_SIZE
- + (double) num_elements * log((double) num_buffers) /
- (TIME_FOR_COMPARE_ROWID * M_LN2);
+ + num_elements * log((double) num_buffers) * ROWID_COMPARE_COST / M_LN2;
}
}
@@ -49,8 +48,7 @@ double get_merge_many_buffs_cost_fast(ha
// Calculate CPU cost of sorting buffers.
total_cost=
( num_buffers * num_keys_per_buffer * log(1.0 + num_keys_per_buffer) +
- last_n_elems * log(1.0 + last_n_elems) )
- / TIME_FOR_COMPARE_ROWID;
+ last_n_elems * log(1.0 + last_n_elems) ) * ROWID_COMPARE_COST;
// Simulate behavior of merge_many_buff().
while (num_buffers >= MERGEBUFF2)
=== modified file 'sql/ha_ndbcluster.cc'
--- a/sql/ha_ndbcluster.cc 2011-05-12 17:29:19 +0000
+++ b/sql/ha_ndbcluster.cc 2011-05-18 10:43:46 +0000
@@ -9042,7 +9042,7 @@ ha_ndbcluster::multi_range_read_info_con
cost->io_count= index_only_read_time(keyno, total_rows);
else
cost->io_count= read_time(keyno, n_ranges, total_rows);
- cost->cpu_cost= (double) total_rows / TIME_FOR_COMPARE + 0.01;
+ cost->cpu_cost= total_rows * ROW_EVALUATE_COST + 0.01;
}
return total_rows;
}
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2011-05-16 14:30:54 +0000
+++ b/sql/handler.cc 2011-05-18 10:43:46 +0000
@@ -4557,7 +4557,7 @@ handler::multi_range_read_info_const(uin
cost->io_count= index_only_read_time(keyno, total_rows);
else
cost->io_count= read_time(keyno, n_ranges, total_rows);
- cost->cpu_cost= (double) total_rows / TIME_FOR_COMPARE + 0.01;
+ cost->cpu_cost= total_rows * ROW_EVALUATE_COST + 0.01;
}
return total_rows;
}
@@ -4782,19 +4782,19 @@ int DsMrr_impl::dsmrr_init(handler *h_ar
/*
This assert will hit if we have pushed an index condition to the
primary key index and then "change our mind" and use a different
- index for retrieving data with MRR.
-
- This assert is too strict for the existing code. If an index
- condition has been pushed on the primary index the existing code
- does not clean up information about the pushed index condition when
- the index scan is completed. Disables the assert until we have
- a fix for better cleaning up after a pushed index condition.
+ index for retrieving data with MRR. One of the following criteria
+ must be true:
+ 1. We have not pushed an index conditon on this handler.
+ 2. We have pushed an index condition and this is on the currently used
+ index.
+ 3. We have pushed an index condition but this is not for the primary key.
+ 4. We have pushed an index condition and this has been transferred to
+ the clone (h2) of the handler object.
*/
- /*
DBUG_ASSERT(!h->pushed_idx_cond ||
h->pushed_idx_cond_keyno == h->active_index ||
- h->pushed_idx_cond_keyno != table->s->primary_key);
- */
+ h->pushed_idx_cond_keyno != table->s->primary_key ||
+ (h2 && h->pushed_idx_cond_keyno == h2->active_index));
rowids_buf= buf->buffer;
@@ -4867,7 +4867,26 @@ int DsMrr_impl::dsmrr_init(handler *h_ar
/*
We get here when the access alternates betwen MRR scan(s) and non-MRR
scans.
+ */
+ /*
+ Verify consistency between the two handler objects:
+ 1. The main handler should either use the primary key or not have an
+ active index at this point since the clone handler (h2) is used for
+ reading the index.
+ 2. The index used for ICP should be the same for the two handlers or
+ it should not be set on the clone handler (h2).
+ 3. The ICP function should be the same for the two handlers or it should
+ not be set for the clone handler (h2).
+ */
+ DBUG_ASSERT(h->active_index == table->s->primary_key ||
+ h->active_index == MAX_KEY);
+ DBUG_ASSERT(h->pushed_idx_cond_keyno == h2->pushed_idx_cond_keyno ||
+ h2->pushed_idx_cond_keyno == MAX_KEY);
+ DBUG_ASSERT(h->pushed_idx_cond == h2->pushed_idx_cond ||
+ h2->pushed_idx_cond == NULL);
+
+ /*
Calling h->index_end() will invoke dsmrr_close() for this object,
which will delete h2. We need to keep it, so save put it away and dont
let it be deleted:
@@ -5303,7 +5322,7 @@ void get_sort_and_sweep_cost(TABLE *tabl
{
get_sweep_read_cost(table, nrows, FALSE, cost);
/* Add cost of qsort call: n * log2(n) * cost(rowid_comparison) */
- double cmp_op= rows2double(nrows) * (1.0 / TIME_FOR_COMPARE_ROWID);
+ double cmp_op= rows2double(nrows) * ROWID_COMPARE_COST;
if (cmp_op < 3)
cmp_op= 3;
cost->cpu_cost += cmp_op * log2(cmp_op);
=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc 2011-05-06 13:32:53 +0000
+++ b/sql/item_cmpfunc.cc 2011-05-18 10:43:46 +0000
@@ -1764,6 +1764,17 @@ bool Item_in_optimizer::fix_fields(THD *
with_sum_func= with_sum_func || args[1]->with_sum_func;
used_tables_cache|= args[1]->used_tables();
not_null_tables_cache|= args[1]->not_null_tables();
+
+ if (!sub->is_top_level_item())
+ {
+ /*
+ This is a NOT IN subquery predicate (or equivalent). Null values passed
+ from outer tables and used in the left-hand expression of the predicate
+ must be considered in the evaluation, hence filter out these tables
+ from the set of null-rejecting tables.
+ */
+ not_null_tables_cache&= ~args[0]->not_null_tables();
+ }
const_item_cache&= args[1]->const_item();
fixed= 1;
return FALSE;
@@ -1791,6 +1802,7 @@ void Item_in_optimizer::fix_after_pullou
const_item_cache&= args[1]->const_item();
}
+
/**
The implementation of optimized \<outer expression\> [NOT] IN \<subquery\>
predicates. The implementation works as follows.
=== modified file 'sql/item_func.h'
--- a/sql/item_func.h 2011-04-15 09:04:21 +0000
+++ b/sql/item_func.h 2011-05-05 07:41:53 +0000
@@ -37,7 +37,11 @@ protected:
uint allowed_arg_cols;
public:
uint arg_count;
- table_map used_tables_cache, not_null_tables_cache;
+ /// Value used in calculation of result of used_tables()
+ table_map used_tables_cache;
+ /// Value used in calculation of result of not_null_tables()
+ table_map not_null_tables_cache;
+ /// Value used in calculation of result of const_item()
bool const_item_cache;
enum Functype { UNKNOWN_FUNC,EQ_FUNC,EQUAL_FUNC,NE_FUNC,LT_FUNC,LE_FUNC,
GE_FUNC,GT_FUNC,FT_FUNC,
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2011-05-06 13:26:31 +0000
+++ b/sql/opt_range.cc 2011-05-18 10:43:46 +0000
@@ -2223,8 +2223,8 @@ int SQL_SELECT::test_quick_select(THD *t
records= head->file->stats.records;
if (!records)
records++; /* purecov: inspected */
- scan_time= (double) records / TIME_FOR_COMPARE + 1;
- read_time= (double) head->file->scan_time() + scan_time + 1.1;
+ scan_time= records * ROW_EVALUATE_COST + 1;
+ read_time= head->file->scan_time() + scan_time + 1.1;
if (head->force_index)
scan_time= read_time= DBL_MAX;
if (limit < records)
@@ -2324,7 +2324,7 @@ int SQL_SELECT::test_quick_select(THD *t
double key_read_time=
param.table->file->index_only_read_time(key_for_use,
rows2double(records)) +
- (double) records / TIME_FOR_COMPARE;
+ records * ROW_EVALUATE_COST;
DBUG_PRINT("info", ("'all'+'using index' scan will be using key %d, "
"read time %g", key_for_use, key_read_time));
if (key_read_time < read_time)
@@ -3877,7 +3877,7 @@ TABLE_READ_PLAN *get_best_disjunct_quick
Add one ROWID comparison for each row retrieved on non-CPK scan. (it
is done in QUICK_RANGE_SELECT::row_in_ranges)
*/
- imerge_cost += non_cpk_scan_records / TIME_FOR_COMPARE_ROWID;
+ imerge_cost += non_cpk_scan_records * ROWID_COMPARE_COST;
}
/* Calculate cost(rowid_to_row_scan) */
@@ -3966,7 +3966,7 @@ skip_to_ror_scan:
cost= param->table->file->
read_time(param->real_keynr[(*cur_child)->key_idx], 1,
(*cur_child)->records) +
- rows2double((*cur_child)->records) / TIME_FOR_COMPARE;
+ rows2double((*cur_child)->records) * ROW_EVALUATE_COST;
}
else
cost= read_time;
@@ -4013,8 +4013,8 @@ skip_to_ror_scan:
get_sweep_read_cost(param->table, roru_total_records, is_interrupted,
&sweep_cost);
roru_total_cost= roru_index_costs +
- rows2double(roru_total_records)*log((double)n_child_scans) /
- (TIME_FOR_COMPARE_ROWID * M_LN2) +
+ rows2double(roru_total_records) *
+ log((double)n_child_scans) * ROWID_COMPARE_COST / M_LN2 +
sweep_cost.total_cost();
}
@@ -4469,11 +4469,11 @@ static bool ror_intersect_add(ROR_INTERS
{
/*
CPK scan is used to filter out rows. We apply filtering for
- each record of every scan. Assuming 1/TIME_FOR_COMPARE_ROWID
+ each record of every scan. Assuming ROWID_COMPARE_COST
per check this gives us:
*/
- info->index_scan_costs += rows2double(info->index_records) /
- TIME_FOR_COMPARE_ROWID;
+ info->index_scan_costs += rows2double(info->index_records) *
+ ROWID_COMPARE_COST;
}
else
{
@@ -4856,9 +4856,9 @@ TRP_ROR_INTERSECT *get_best_covering_ror
tree->ror_scans, ror_scan_mark););
/* Add priority queue use cost. */
- total_cost += rows2double(records)*
- log((double)(ror_scan_mark - tree->ror_scans)) /
- (TIME_FOR_COMPARE_ROWID * M_LN2);
+ total_cost += rows2double(records) *
+ log((double)(ror_scan_mark - tree->ror_scans)) *
+ ROWID_COMPARE_COST / M_LN2;
DBUG_PRINT("info", ("Covering ROR-intersect full cost: %g", total_cost));
if (total_cost > read_time)
@@ -10776,7 +10776,7 @@ void cost_group_min_max(TABLE* table, KE
no CPU cost. We leave it here to make this cost comparable to that of index
scan as computed in SQL_SELECT::test_quick_select().
*/
- cpu_cost= (double) num_groups / TIME_FOR_COMPARE;
+ cpu_cost= num_groups * ROW_EVALUATE_COST;
*read_cost= io_cost + cpu_cost;
*records= num_groups;
=== modified file 'sql/sql_const.h'
--- a/sql/sql_const.h 2010-12-17 09:41:21 +0000
+++ b/sql/sql_const.h 2011-05-02 13:22:25 +0000
@@ -158,16 +158,15 @@
/**
The following is used to decide if MySQL should use table scanning
- instead of reading with keys. The number says how many evaluation of the
- WHERE clause is comparable to reading one extra row from a table.
+ instead of reading with keys. The number says how costly evaluation of the
+ filter condition for a row is compared to reading one extra row from a table.
*/
-#define TIME_FOR_COMPARE 5.0 // 5 compares == one read
+#define ROW_EVALUATE_COST 0.20
/**
- Number of comparisons of table rowids equivalent to reading one row from a
- table.
+ Cost of comparing a rowid compared to reading one row from a table.
*/
-#define TIME_FOR_COMPARE_ROWID (TIME_FOR_COMPARE*2.0)
+#define ROWID_COMPARE_COST 0.10 // Half the cost of a general row comparison
/*
For sequential disk seeks the cost formula is:
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-05-18 08:29:46 +0000
+++ b/sql/sql_select.cc 2011-05-18 10:43:46 +0000
@@ -7063,10 +7063,10 @@ best_access_path(JOIN *join,
POSITION *pos,
POSITION *loose_scan_pos)
{
- THD *thd= join->thd;
+ THD *const thd= join->thd;
Key_use *best_key= NULL;
uint best_max_key_part= 0;
- my_bool found_constraint= 0;
+ bool found_constraint= false;
double best= DBL_MAX;
double best_time= DBL_MAX;
double records= DBL_MAX;
@@ -7079,7 +7079,7 @@ best_access_path(JOIN *join,
double best_quick_records= DBL_MAX;
table_map best_ref_depends_map= 0;
double tmp;
- bool best_uses_jbuf= FALSE;
+ bool best_uses_jbuf= false;
Loose_scan_opt loose_scan_opt;
DBUG_ENTER("best_access_path");
@@ -7092,28 +7092,27 @@ best_access_path(JOIN *join,
*/
if (unlikely(s->keyuse != NULL))
{ /* Use key if possible */
- TABLE *table= s->table;
- Key_use *keyuse;
+ TABLE *const table= s->table;
double best_records= DBL_MAX;
- uint max_key_part=0;
/* Test how we can use keys */
ha_rows rec=
s->records/MATCHING_ROWS_IN_OTHER_TABLE; // Assumed records/key
- for (keyuse=s->keyuse ; keyuse->table == table ;)
+ for (Key_use *keyuse=s->keyuse; keyuse->table == table; )
{
key_part_map found_part= 0;
table_map found_ref= 0;
- uint key= keyuse->key;
- KEY *keyinfo= table->key_info+key;
- bool ft_key= (keyuse->keypart == FT_KEYPART);
+ const uint key= keyuse->key;
+ uint max_key_part= 0;
+ KEY *const keyinfo= table->key_info+key;
+ const bool ft_key= (keyuse->keypart == FT_KEYPART);
/* Bitmap of keyparts where the ref access is over 'keypart=const': */
key_part_map const_part= 0;
/* The or-null keypart in ref-or-null access: */
key_part_map ref_or_null_part= 0;
/* Calculate how many key segments of the current key we can use */
- Key_use *start_key= keyuse;
+ Key_use *const start_key= keyuse;
loose_scan_opt.next_ref_key();
DBUG_PRINT("info", ("Considering ref access on key %s",
@@ -7127,7 +7126,7 @@ best_access_path(JOIN *join,
do /* For each keypart */
{
- uint keypart= keyuse->keypart;
+ const uint keypart= keyuse->keypart;
table_map best_part_found_ref= 0;
double best_prev_record_reads= DBL_MAX;
@@ -7449,12 +7448,12 @@ best_access_path(JOIN *join,
loose_scan_opt.check_ref_access_part2(key, start_key, records, tmp);
} /* not ft_key */
- if (tmp < best_time - records/(double) TIME_FOR_COMPARE ||
+ if (tmp < best_time - records * ROW_EVALUATE_COST ||
(quick_matches_more_parts &&
quick_records < best_quick_records))
{
best_quick_records = quick_records;
- best_time= tmp + records/(double) TIME_FOR_COMPARE;
+ best_time= tmp + records * ROW_EVALUATE_COST;
best= tmp;
best_records= records;
best_key= start_key;
@@ -7539,7 +7538,7 @@ best_access_path(JOIN *join,
*/
tmp= record_count *
(s->quick->read_time +
- (s->found_records - rnd_records)/(double) TIME_FOR_COMPARE);
+ (s->found_records - rnd_records) * ROW_EVALUATE_COST);
loose_scan_opt.check_range_access(join, idx, s->quick);
}
@@ -7559,8 +7558,7 @@ best_access_path(JOIN *join,
- skip rows which does not satisfy join condition
*/
tmp= record_count *
- (tmp +
- (s->records - rnd_records)/(double) TIME_FOR_COMPARE);
+ (tmp + (s->records - rnd_records) * ROW_EVALUATE_COST);
}
else
{
@@ -7579,18 +7577,18 @@ best_access_path(JOIN *join,
we read the table (see flush_cached_records for details). Here we
take into account cost to read and skip these records.
*/
- tmp+= (s->records - rnd_records)/(double) TIME_FOR_COMPARE;
+ tmp+= (s->records - rnd_records) * ROW_EVALUATE_COST;
}
}
/*
We estimate the cost of evaluating WHERE clause for found records
- as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus
+ as record_count * rnd_records * ROW_EVALUATE_COST. This cost plus
tmp give us total cost of using TABLE SCAN
*/
if (best == DBL_MAX ||
- (tmp + record_count/(double) TIME_FOR_COMPARE*rnd_records <
- best + record_count/(double) TIME_FOR_COMPARE*records))
+ (tmp + (record_count * ROW_EVALUATE_COST * rnd_records) <
+ best + (record_count * ROW_EVALUATE_COST * records)))
{
/*
If the table has a range (s->quick is set) make_join_select()
@@ -7911,7 +7909,7 @@ void Optimize_table_order::optimize_stra
/* compute the cost of the new plan extended with 's' */
record_count*= join->positions[idx].records_read;
read_time+= join->positions[idx].read_time
- + record_count / (double) TIME_FOR_COMPARE;
+ + record_count * ROW_EVALUATE_COST;
advance_sj_state(join_tables, s, idx, &record_count, &read_time,
&loose_scan_pos);
@@ -8165,7 +8163,7 @@ bool Optimize_table_order::greedy_search
/* compute the cost of the new plan extended with 'best_table' */
record_count*= join->positions[idx].records_read;
read_time+= join->positions[idx].read_time
- + record_count / (double) TIME_FOR_COMPARE;
+ + record_count * ROW_EVALUATE_COST;
remaining_tables&= ~(best_table->table->map);
--size_remain;
@@ -8211,7 +8209,7 @@ void get_partial_join_cost(JOIN *join, u
{
record_count *= join->best_positions[i].records_read;
read_time += join->best_positions[i].read_time
- + record_count / (double) TIME_FOR_COMPARE;
+ + record_count * ROW_EVALUATE_COST;
}
}
*read_time_arg= read_time;
@@ -8390,7 +8388,7 @@ bool Optimize_table_order::best_extensio
current_record_count= record_count * position->records_read;
current_read_time= read_time
+ position->read_time
- + current_record_count / (double) TIME_FOR_COMPARE;
+ + current_record_count * ROW_EVALUATE_COST;
if (has_sj)
{
@@ -10659,19 +10657,17 @@ void revise_cache_usage(JOIN_TAB *join_t
}
-/*
+/**
Check whether a join buffer can be used to join the specified table
- SYNOPSIS
- check_join_cache_usage()
- tab joined table to check join buffer usage for
- join join for which the check is performed
- options options of the join
- no_jbuf_after don't use join buffering after table with this number
- icp_other_tables_ok OUT TRUE if condition pushdown supports
- other tables presence
+ @param tab joined table to check join buffer usage for
+ @param join join for which the check is performed
+ @param options options of the join
+ @param no_jbuf_after don't use join buffering after table with this number
+ @param icp_other_tables_ok[out] TRUE if condition pushdown supports
+ other tables presence
- DESCRIPTION
+ @details
The function finds out whether the table 'tab' can be joined using a join
buffer. This check is performed after the best execution plan for 'join'
has been chosen. If the function decides that a join buffer can be employed
@@ -10707,7 +10703,7 @@ void revise_cache_usage(JOIN_TAB *join_t
failure to do this results in an invocation of the function that destructs
the created object.
- NOTES
+ @note
An inner table of a nested outer join or a nested semi-join can be currently
joined only when a linked cache object is employed. In these cases setting
join cache level to an odd number results in denial of usage of any join
@@ -10719,7 +10715,7 @@ void revise_cache_usage(JOIN_TAB *join_t
an index. For these engines setting the value of join_cache_level to 5 or 6
results in that no join buffer is used to join the table.
- TODO
+ @todo
Support BKA inside SJ-Materialization nests. When doing this, we'll need
to only store sj-inner tables in the join buffer.
#if 0
@@ -10743,7 +10739,7 @@ void revise_cache_usage(JOIN_TAB *join_t
}
#endif
- RETURN
+ @return
Bitmap describing the chosen cache's properties:
1) the algorithm (JOIN_CACHE::ALG_NONE, JOIN_CACHE::ALG_BNL,
JOIN_CACHE::ALG_BKA, JOIN_CACHE::ALG_BKA_UNIQUE)
@@ -11243,33 +11239,28 @@ bool setup_sj_materialization(JOIN_TAB *
}
-/*
+/**
Plan refinement stage: do various setup things for the executor
- SYNOPSIS
- make_join_readinfo()
- join Join being processed
- options Join's options (checking for SELECT_DESCRIBE,
- SELECT_NO_JOIN_CACHE)
- no_jbuf_after Don't use join buffering after table with this number.
+ @param join Join being processed
+ @param options Join's options (checking for SELECT_DESCRIBE,
+ SELECT_NO_JOIN_CACHE)
+ @param no_jbuf_after Don't use join buffering after table with this number.
- DESCRIPTION
+ @return false if successful, true if error (Out of memory)
+
+ @details
Plan refinement stage: do various set ups for the executioner
- setup join buffering use
- push index conditions
- increment relevant counters
- etc
-
- RETURN
- FALSE - OK
- TRUE - Out of memory
*/
static bool
make_join_readinfo(JOIN *join, ulonglong options, uint no_jbuf_after)
{
- uint i, jcl;
- bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
+ const bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
uint first_sjm_table= MAX_TABLES;
uint last_sjm_table= MAX_TABLES;
@@ -11281,11 +11272,12 @@ make_join_readinfo(JOIN *join, ulonglong
if (setup_semijoin_dups_elimination(join, options, no_jbuf_after))
DBUG_RETURN(TRUE); /* purecov: inspected */
- for (i=join->const_tables ; i < join->tables ; i++)
+ for (uint i= join->const_tables; i < join->tables; i++)
{
- JOIN_TAB *tab=join->join_tab+i;
- TABLE *table=tab->table;
+ JOIN_TAB *const tab= join->join_tab+i;
+ TABLE *const table= tab->table;
bool icp_other_tables_ok;
+ uint jcl;
tab->read_record.table= table;
tab->read_record.file=table->file;
tab->read_record.unlock_row= rr_unlock_row;
@@ -11436,12 +11428,12 @@ make_join_readinfo(JOIN *join, ulonglong
If a join buffer is used to join a table the ordering by an index
for the first non-constant table cannot be employed anymore.
*/
- for (i=join->const_tables ; i < join->tables ; i++)
+ for (uint i= join->const_tables; i < join->tables; i++)
{
- JOIN_TAB *tab=join->join_tab+i;
+ JOIN_TAB *const tab=join->join_tab + i;
if (tab->use_join_cache)
{
- JOIN_TAB *sort_by_tab= join->get_sort_by_join_tab();
+ JOIN_TAB *const sort_by_tab= join->get_sort_by_join_tab();
if (sort_by_tab)
{
join->need_tmp= 1;
=== modified file 'sql/uniques.cc'
--- a/sql/uniques.cc 2011-03-09 20:54:55 +0000
+++ b/sql/uniques.cc 2011-05-02 13:22:25 +0000
@@ -122,7 +122,7 @@ inline double log2_n_fact(double x)
the same length, so each of total_buf_size elements will be added to a sort
heap with (n_buffers-1) elements. This gives the comparison cost:
- total_buf_elems* log2(n_buffers) / TIME_FOR_COMPARE_ROWID;
+ total_buf_elems * log2(n_buffers) * ROWID_COMPARE_COST;
*/
static double get_merge_buffers_cost(uint *buff_elems, uint elem_size,
@@ -137,7 +137,7 @@ static double get_merge_buffers_cost(uin
/* Using log2(n)=log(n)/log(2) formula */
return 2*((double)total_buf_elems*elem_size) / IO_SIZE +
- total_buf_elems*log((double) n_buffers) / (TIME_FOR_COMPARE_ROWID * M_LN2);
+ total_buf_elems*log((double) n_buffers) * ROWID_COMPARE_COST / M_LN2;
}
@@ -267,7 +267,6 @@ double Unique::get_use_cost(uint *buffer
ulong max_elements_in_tree;
ulong last_tree_elems;
int n_full_trees; /* number of trees in unique - 1 */
- double result;
max_elements_in_tree= ((ulong) max_in_memory_size /
ALIGN_SIZE(sizeof(TREE_ELEMENT)+key_size));
@@ -276,10 +275,10 @@ double Unique::get_use_cost(uint *buffer
last_tree_elems= nkeys % max_elements_in_tree;
/* Calculate cost of creating trees */
- result= 2*log2_n_fact(last_tree_elems + 1.0);
+ double result= 2 * log2_n_fact(last_tree_elems + 1.0);
if (n_full_trees)
result+= n_full_trees * log2_n_fact(max_elements_in_tree + 1.0);
- result /= TIME_FOR_COMPARE_ROWID;
+ result*= ROWID_COMPARE_COST;
DBUG_PRINT("info",("unique trees sizes: %u=%u*%lu + %lu", nkeys,
n_full_trees, n_full_trees?max_elements_in_tree:0,
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (tor.didriksen:3099 to 3100) | Tor Didriksen | 19 May |
