April 2017 - developers - lists.mariadb.org

Re: [Maria-developers] [Commits] e446bf7: MDEV-12145: Wrong result (missing rows) on query with IN and EXISTS subqueries from InnoDB tables
by Igor Babaev 04 Apr '17

04 Apr '17

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(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

Re: [Maria-developers] MDEV-12238 Add Type_handler::Item_func_{plus|minus|mul|div|mod}_fix_length_and_dec()
by Alexander Barkov 04 Apr '17

04 Apr '17

Hello Alexey, Thanks for your review. Please find an updated version attached. On 03/19/2017 08:12 PM, Alexey Botchkov wrote: > Hi, Alexander. > > The patch is ok to push. > I only have two cosmetical recommendation. > 1. const Pair* find_pair() implementation belongs to sql_type.cc. A bit > too long for the .h file. Moved. > 2. I'd create the enum {NOT_COMMUTATIVE, COMMUTATIVE} and use it as the > parameter for the find_pair() and related functions. Makes code much > more understandable. As agreed on IRC, I created a new class Type_aggregator_commutative instead. Thanks! > > Best regards. > HF > > On Mon, Mar 13, 2017 at 3:41 PM, Alexander Barkov <bar(a)mariadb.org > <mailto:bar@mariadb.org>> wrote: > > Hello Alexey, > > Please review a patch for MDEV-12238. > > Thanks! > >

1 1

[Maria-developers] Please review a patch removing LEX::text_string_is_7bit
by Alexander Barkov 04 Apr '17

04 Apr '17

Hello Sergei, Can you please review a patch for bb-10.2-ext fixing the problem that in case of possible changes in look-ahead in rules using TEXT_STRING or NCHAR_STRING, Lex->text_string_is_7bit can get out of sync with $1/$2/$3, etc. Thanks!

2 2

[Maria-developers] JDBC getWarnings and stored procedure
by jerome brauge 03 Apr '17

03 Apr '17

Hello, When we call a stored procedure in JDBC which emit warnings, getWarnings() always returns null. I don't really think that the problem is in MariaDB Connector J because warning flag in the EOF packet is not set to true. Mysql client behaves in the same way, by default it shows warnings count issued by DML command but not for stored procedure. Is it a normal behavior ? Best regards, Jérôme.

1 0

[Maria-developers] Please review a patch for Mdev-12019
by Sachin Setiya 03 Apr '17

03 Apr '17

Hi Monty, Please review a patch for Mdev-12019. -- Regards Sachin Setiya Software Engineer at MariaDB

1 0

[Maria-developers] Errors in building debs
by Sergey Petrunia 01 Apr '17

01 Apr '17

Hi Vicențiu, I'm looking at the last build in bb-10.2-mariarocks http://buildbot.askmonty.org/buildbot/builders/kvm-deb-trusty-x86/builds/35… and I see this: + DH_BUILD_DDEBS=1 + echo + debian/autobake-deb.sh debian/autobake-deb.sh: line 80: [: i686: integer expression expected debian/autobake-deb.sh: line 80: [i686: command not found and then it eventually fails with this error: cp: cannot stat ‘debian/tmp/etc/mysql/conf.d/rocksdb_se.cnf’: No such file or I assume this is caused by your recent changes? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

2 1