maria-developers team mailing list archive

Thread
Date
Re: [Commits] e446bf7: MDEV-12145: Wrong result (missing rows) on query with IN and EXISTS subqueries from InnoDB tables

To: maria-developers@xxxxxxxxxxxxxxxxxxx, Sergey Petrunya <psergey@xxxxxxxxxxxx>
From: Igor Babaev <igor@xxxxxxxxxxxx>
Date: Tue, 04 Apr 2017 10:17:56 -0700
In-reply-to: <20170404125519.16A70FF30AA@Varuns-MBP.lan>
User-agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.15) Gecko/20101026 SUSE/3.0.10 Thunderbird/3.0.10
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@xxxxxxxxxxx
> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits