Varun, 1. The mdev # in the test case should be 12145 2. Could you please explain why do you need

@@ -9735,8 +9735,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) !(used_tables & tab->emb_sj_nest->sj_inner_tables)) { save_used_tables= used_tables; - used_tables= join->const_table_map | OUTER_REF_TABLE_BIT | - RAND_TABLE_BIT; + used_tables= join->const_table_map | RAND_TABLE_BIT; }

? I checked your test case on 5.5 only with ;

*/ if (tab == join->join_tab + join->top_join_tab_count - 1) - current_map|= OUTER_REF_TABLE_BIT | RAND_TABLE_BIT; + current_map|= RAND_TABLE_BIT; used_tables|=current_map;

if (tab->type == JT_REF && tab->quick &&

and it passed. If the first change is really needed you have to produce a test case where it is really critical. If this is just cosmetic change the comment should clearly say about it. 3. I wonder why the patch is applied to 10.2 if it's reported for 5.5-10.2. Regards, Igor. On 04/04/2017 05:55 AM, Varun wrote:

revision-id: e446bf7dc49b96a09e11199ad3e05fade6490279 (mariadb-10.2.3-396-ge446bf7) parent(s): 6d417a0bad205a6bacfee10dbc46dd631b093e75 author: Varun Gupta committer: Varun Gupta timestamp: 2017-04-04 18:24:43 +0530 message:

MDEV-12145: Wrong result (missing rows) on query with IN and EXISTS subqueries from InnoDB tables

Tables that are within SJ-Materialization nests cannot have their conditions referring to preceding non-const tables. But in this issue this was happening. Fixed by not allowing used_tables to allow outer tables

--- mysql-test/r/subselect_sj2.result | 26 ++++++++++++++++++++++++++ mysql-test/r/subselect_sj2_jcl6.result | 26 ++++++++++++++++++++++++++ mysql-test/r/subselect_sj2_mat.result | 26 ++++++++++++++++++++++++++ mysql-test/t/subselect_sj2.test | 24 ++++++++++++++++++++++++ sql/sql_select.cc | 5 ++--- 5 files changed, 104 insertions(+), 3 deletions(-)

diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result index 948be57..14032ec 100644 --- a/mysql-test/r/subselect_sj2.result +++ b/mysql-test/r/subselect_sj2.result @@ -1331,5 +1331,31 @@ WHERE a IN ( SELECT b FROM t2 LEFT JOIN v3 ON ( c = b ) ) ; a pk b DROP TABLE t1,t2,t3; DROP VIEW v3; +# +# MDEV-11108: Assertion `uniq_tuple_length_arg <= table->file->max_key_length()' failed in SJ_TMP_TABLE::create_sj_weedout_tmp_table +# +CREATE TABLE t1 (f1 INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8),(7),(1); +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=InnoDB; +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); +CREATE TABLE t4 (i4 INT, f4 INT, KEY(i4)) ENGINE=InnoDB; +INSERT INTO t4 VALUES (0,0),(4,0),(0,2),(1,0),(2,1),(2,7),(6,3),(8,7),(8,1),(1,0),(7,2),(6,0),(8,1); +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) ); +f1 +6 +ANALYZE TABLE t1,t2,t3,t4; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +test.t4 analyze status OK +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) ); +f1 +6 +DROP VIEW v3; +DROP TABLE t1, t2, t3, t4; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; diff --git a/mysql-test/r/subselect_sj2_jcl6.result b/mysql-test/r/subselect_sj2_jcl6.result index 2955307..2aa0605 100644 --- a/mysql-test/r/subselect_sj2_jcl6.result +++ b/mysql-test/r/subselect_sj2_jcl6.result @@ -1346,6 +1346,32 @@ WHERE a IN ( SELECT b FROM t2 LEFT JOIN v3 ON ( c = b ) ) ; a pk b DROP TABLE t1,t2,t3; DROP VIEW v3; +# +# MDEV-11108: Assertion `uniq_tuple_length_arg <= table->file->max_key_length()' failed in SJ_TMP_TABLE::create_sj_weedout_tmp_table +# +CREATE TABLE t1 (f1 INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8),(7),(1); +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=InnoDB; +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); +CREATE TABLE t4 (i4 INT, f4 INT, KEY(i4)) ENGINE=InnoDB; +INSERT INTO t4 VALUES (0,0),(4,0),(0,2),(1,0),(2,1),(2,7),(6,3),(8,7),(8,1),(1,0),(7,2),(6,0),(8,1); +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) ); +f1 +6 +ANALYZE TABLE t1,t2,t3,t4; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +test.t4 analyze status OK +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) ); +f1 +6 +DROP VIEW v3; +DROP TABLE t1, t2, t3, t4; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; # diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index a067422..1ea020a 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1333,6 +1333,32 @@ WHERE a IN ( SELECT b FROM t2 LEFT JOIN v3 ON ( c = b ) ) ; a pk b DROP TABLE t1,t2,t3; DROP VIEW v3; +# +# MDEV-11108: Assertion `uniq_tuple_length_arg <= table->file->max_key_length()' failed in SJ_TMP_TABLE::create_sj_weedout_tmp_table +# +CREATE TABLE t1 (f1 INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8),(7),(1); +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=InnoDB; +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); +CREATE TABLE t4 (i4 INT, f4 INT, KEY(i4)) ENGINE=InnoDB; +INSERT INTO t4 VALUES (0,0),(4,0),(0,2),(1,0),(2,1),(2,7),(6,3),(8,7),(8,1),(1,0),(7,2),(6,0),(8,1); +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) ); +f1 +6 +ANALYZE TABLE t1,t2,t3,t4; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +test.t4 analyze status OK +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) ); +f1 +6 +DROP VIEW v3; +DROP TABLE t1, t2, t3, t4; # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; set optimizer_switch=default; diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index a948b08..5b745ca 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -1464,5 +1464,29 @@ WHERE a IN ( SELECT b FROM t2 LEFT JOIN v3 ON ( c = b ) ) ; DROP TABLE t1,t2,t3; DROP VIEW v3;

+--echo # +--echo # MDEV-11108: Assertion `uniq_tuple_length_arg <= table->file->max_key_length()' failed in SJ_TMP_TABLE::create_sj_weedout_tmp_table +--echo # + +CREATE TABLE t1 (f1 INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (4),(6); + +CREATE TABLE t2 (i2 INT, KEY(i2)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8),(7),(1); + +CREATE TABLE t3 (f3 INT, i3 INT, KEY(i3)) ENGINE=InnoDB; +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; +INSERT INTO t3 VALUES (8,0),(6,3),(2,8),(3,8),(1,6),(0,0),(1,0),(1,5); + +CREATE TABLE t4 (i4 INT, f4 INT, KEY(i4)) ENGINE=InnoDB; +INSERT INTO t4 VALUES (0,0),(4,0),(0,2),(1,0),(2,1),(2,7),(6,3),(8,7),(8,1),(1,0),(7,2),(6,0),(8,1); + +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) ); +ANALYZE TABLE t1,t2,t3,t4; +SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2, t3 WHERE i3 = i2 AND f1 IN ( SELECT f3 FROM v3, t4 WHERE i4 = f3 AND f4 <> f3 ) ); + +DROP VIEW v3; +DROP TABLE t1, t2, t3, t4; + --echo # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 151e341..135ad9a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9735,8 +9735,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) !(used_tables & tab->emb_sj_nest->sj_inner_tables)) { save_used_tables= used_tables; - used_tables= join->const_table_map | OUTER_REF_TABLE_BIT | - RAND_TABLE_BIT; + used_tables= join->const_table_map | RAND_TABLE_BIT; }

/* @@ -9744,7 +9743,7 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) It solve problem with select like SELECT * FROM t1 WHERE rand() > 0.5 */ if (tab == join->join_tab + join->top_join_tab_count - 1) - current_map|= OUTER_REF_TABLE_BIT | RAND_TABLE_BIT; + current_map|= RAND_TABLE_BIT; used_tables|=current_map;

if (tab->type == JT_REF && tab->quick && _______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits