February 2017 - developers - lists.mariadb.org

Re: [Maria-developers] [Commits] b13cee8: MDEV-11078: NULL NOT IN (non-empty subquery) should never return results
by Sergei Golubchik 27 Feb '17

27 Feb '17

Hi, Varun! Really, copy-pasted *8* times? Why didn't you make it a method of Item_in_subselect? On Feb 27, Varun wrote: > revision-id: b13cee83f9cfcffa98d227c492411a9acec85f42 (mariadb-10.2.3-282-gb13cee8) > parent(s): b513e3711743e3ff24b3d14794a0fba87fd18c31 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2017-02-27 22:10:35 +0530 > message: > > MDEV-11078: NULL NOT IN (non-empty subquery) should never return results > > Setting the value of cond guard variables during the creation of Tricond Item for null values > > --- > mysql-test/r/subselect4.result | 34 +++++++++++++++++++++++++++++ > mysql-test/t/subselect4.test | 20 +++++++++++++++++ > sql/item_subselect.cc | 49 ++++++++++++++++++++++++++++++++++++++++++ > 3 files changed, 103 insertions(+) > > diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc > index 94bc71c..acdbabd 100644 > --- a/sql/item_subselect.cc > +++ b/sql/item_subselect.cc > @@ -2113,6 +2113,13 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > We can encounter "NULL IN (SELECT ...)". Wrap the added condition > within a trig_cond. > */ > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > + > item= new (thd->mem_root) Item_func_trig_cond(thd, item, get_cond_guard(0)); > } > > @@ -2137,6 +2144,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > having= new (thd->mem_root) Item_is_not_null_test(thd, this, having); > if (left_expr->maybe_null) > { > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > if (!(having= new (thd->mem_root) Item_func_trig_cond(thd, having, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2155,6 +2168,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > */ > if (!abort_on_null && left_expr->maybe_null) > { > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > if (!(item= new (thd->mem_root) Item_func_trig_cond(thd, item, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2184,6 +2203,12 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > (char *)"<result>")); > if (!abort_on_null && left_expr->maybe_null) > { > + if(left_expr->basic_const_item() && !left_expr->is_expensive()) > + { > + left_expr->is_null(); > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + } > if (!(new_having= new (thd->mem_root) Item_func_trig_cond(thd, new_having, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2383,6 +2408,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > Item_cond_or(thd, item_eq, item_isnull); > if (!abort_on_null && left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(col_item= new (thd->mem_root) > Item_func_trig_cond(thd, col_item, get_cond_guard(i)))) > DBUG_RETURN(true); > @@ -2400,6 +2431,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > (char *)"<list ref>")); > if (!abort_on_null && left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(item_nnull_test= > new (thd->mem_root) > Item_func_trig_cond(thd, item_nnull_test, get_cond_guard(i)))) > @@ -2460,6 +2497,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > item= new (thd->mem_root) Item_cond_or(thd, item, item_isnull); > if (left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(item= new (thd->mem_root) > Item_func_trig_cond(thd, item, get_cond_guard(i)))) > DBUG_RETURN(true); > @@ -2471,6 +2514,12 @@ Item_in_subselect::create_row_in_to_exists_cond(JOIN * join, > } > if (!abort_on_null && left_expr->element_index(i)->maybe_null) > { > + if(left_expr->element_index(i)->basic_const_item() > + && !left_expr->element_index(i)->is_expensive()) > + { > + if(left_expr->element_index(i)->is_null()) > + set_cond_guard_var(i,FALSE); > + } > if (!(item= new (thd->mem_root) > Item_func_trig_cond(thd, item, get_cond_guard(i)))) > DBUG_RETURN(true); Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

[Maria-developers] On constructing ref access from mis-matched charset comparisons
by Sergey Petrunia 27 Feb '17

27 Feb '17

Hi Alexander, I've got a question about mis-matched charset comparisons and ref access method. == Short form == I know that VARCHAR comparisons over mis-matching charsets cannot be used for constructing index lookups into the index over the "narrower" character set column. But is this a real limitation or just an optimizer deficiency? If it is the latter, do people hit it in the real world sufficiently often to warrant lifting it? == Long form == === Example dataset === create table t0 ( a varchar(32) character set utf8 collate utf8_general_ci ); create table t1 ( a varchar(32) character set latin1 collate latin1_swedish_ci, col1 varchar(32), key(a) ); create table t2 ( a varchar(32) character set utf8 collate utf8_general_ci, col1 varchar(32), key(a) ); insert into t0 select a from ten; insert into t1 select a,a from one_k; insert into t2 select a,a from one_k; === Queries === MariaDB [test]> explain -> select * from t0, t2 where t0.a=t2.a; +------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+ | 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 10 | Using where | | 1 | SIMPLE | t2 | ref | a | a | 99 | test.t0.a | 1 | | +------+-------------+-------+------+---------------+------+---------+-----------+------+-------------+ Ok, good. MariaDB [test]> explain -> select * from t0, t1 where t0.a=t1.a; +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ 2 rows in set (0.00 sec) Oops, the index t1(a) is not used. FORMAT=JSON shows the reason: "attached_condition": "(t0.a = convert(t1.a using utf8))" Still it feels like the optimizer could * Try to convert the value of t0.a into latin1. * If it can't be represented in latin1, then we know that no row in t1 has t1.a=t0.a. * make a lookup on t1.a= convert(t0.a using latin1) , using t1.a's collation rules. Is this true [for some charsets] ? If yes, is it worth doing? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

3 2

[Maria-developers] bb-10.2-compatibility : delete compatibility
by jerome brauge 27 Feb '17

27 Feb '17

Hello Alexander, Can you add a task for delete statement with same table as source and target in sql_mode=oracle Example : create or replace table t1 (c1 int, c2 int); delete from t1 where c1 in (select b.c1 from t1 b where b.c2=0); Failed with : Error: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data SQLState: HY000 ErrorCode: 1093 Regards, Jérôme.

2 1

Re: [Maria-developers] [Commits] c6d172f: MDEV-11078: NULL NOT IN (non-empty subquery) should never return results
by Sergey Petrunia 27 Feb '17

27 Feb '17

Hi Varun, Things that look apparently wrong: 1. The patch checks left_expr->null_value without first having made a call that will fill that member. The calling convention is something like this: item->val_XXX() // val_str or val_int() // check item->null_value. If one doesn't need string or integer value, there is item->is_null() call. 2. The patch checks null_value for any kind of left_expr. This is wrong. For example, consider a query: select t1.col IN (select t2.col FROM t2 ) from t1 Here, it doesn't make any sense to check {t1.col}->null_value at query planning/rewrite time. The value (and NULL-ness) of t1.col is different for different rows of t1. As discussed on Slack: please only check for NULL value for items which are - constant, - and "not expensive" and so have a fixed value which we are allowed to compute at query optimization phase. 3. I see the patch handles single-value comparisons like expr IN (select inner_expr FROM ...) but what about tuple-based comparisons like: (expr1, expr2) IN (select inner_expr1, inner_expr2 FROM ...) ? I guess they should have a similar issue. If they do not, we need - an explanation why - test coverage On Sun, Feb 26, 2017 at 09:04:40PM +0530, Varun wrote: > revision-id: c6d172f785f31641832087a432d1966b67efc47a (mariadb-10.2.3-283-gc6d172f) > parent(s): 78153cf641aea41166d3e79ae99b57b154f6a027 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2017-02-26 21:03:29 +0530 > message: > > MDEV-11078: NULL NOT IN (non-empty subquery) should never return results > > Setting the value of cond guard variables during the creation of Tricond Item for null values > > --- > mysql-test/r/subselect4.result | 31 +++++++++++++++++++++++++++++++ > mysql-test/t/subselect4.test | 18 ++++++++++++++++++ > sql/item_subselect.cc | 10 ++++++++++ > 3 files changed, 59 insertions(+) > > diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result > index 6bbb80c..c3bd50a 100644 > --- a/mysql-test/r/subselect4.result > +++ b/mysql-test/r/subselect4.result > @@ -2454,3 +2454,34 @@ x > c1 > 1 > drop table t1; > +# > +# MDEV-11078: NULL NOT IN (non-empty subquery) should never return results > +# > +create table t1(a int,b int); > +create table t2(a int,b int); > +insert into t1 value (NULL,2); > +select 1 in (1,2, NULL); > +1 in (1,2, NULL) > +1 > +select (null) in (select 1 from t1); > +(null) in (select 1 from t1) > +NULL > +select (null) in (select 1 from t2); > +(null) in (select 1 from t2) > +0 > +select 1 in (select 1 from t1); > +1 in (select 1 from t1) > +1 > +select 1 in (select 1 from t2); > +1 in (select 1 from t2) > +0 > +select 1 from dual where null in (select 1 from t1); > +1 > +select 1 from dual where null in (select 1 from t2); > +1 > +select 1 from dual where null not in (select 1 from t1); > +1 > +select 1 from dual where null not in (select 1 from t2); > +1 > +1 > +drop table t1,t2; > diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test > index 253160c..785b79d 100644 > --- a/mysql-test/t/subselect4.test > +++ b/mysql-test/t/subselect4.test > @@ -2009,3 +2009,21 @@ insert into t1 values(2,1),(1,2); > select (select c1 from t1 group by c1,c2 order by c1 limit 1) as x; > (select c1 from t1 group by c1,c2 order by c1 limit 1); > drop table t1; > + > +--echo # > +--echo # MDEV-11078: NULL NOT IN (non-empty subquery) should never return results > +--echo # > + > +create table t1(a int,b int); > +create table t2(a int,b int); > +insert into t1 value (NULL,2); > +select 1 in (1,2, NULL); > +select (null) in (select 1 from t1); > +select (null) in (select 1 from t2); > +select 1 in (select 1 from t1); > +select 1 in (select 1 from t2); > +select 1 from dual where null in (select 1 from t1); > +select 1 from dual where null in (select 1 from t2); > +select 1 from dual where null not in (select 1 from t1); > +select 1 from dual where null not in (select 1 from t2); > +drop table t1,t2; > diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc > index 94bc71c..b9c798e 100644 > --- a/sql/item_subselect.cc > +++ b/sql/item_subselect.cc > @@ -2113,6 +2113,8 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > We can encounter "NULL IN (SELECT ...)". Wrap the added condition > within a trig_cond. > */ > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > item= new (thd->mem_root) Item_func_trig_cond(thd, item, get_cond_guard(0)); > } > > @@ -2137,6 +2139,9 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > having= new (thd->mem_root) Item_is_not_null_test(thd, this, having); > if (left_expr->maybe_null) > { > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > + > if (!(having= new (thd->mem_root) Item_func_trig_cond(thd, having, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2155,6 +2160,9 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > */ > if (!abort_on_null && left_expr->maybe_null) > { > + if(left_expr->null_value) > + set_cond_guard_var(0, FALSE); > + > if (!(item= new (thd->mem_root) Item_func_trig_cond(thd, item, > get_cond_guard(0)))) > DBUG_RETURN(true); > @@ -2184,6 +2192,8 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN *join, > (char *)"<result>")); > if (!abort_on_null && left_expr->maybe_null) > { > + if(left_expr->null_value) > + set_cond_guard_var(0,FALSE); > if (!(new_having= new (thd->mem_root) Item_func_trig_cond(thd, new_having, > get_cond_guard(0)))) > DBUG_RETURN(true); > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] an observation
by AugustQ 22 Feb '17

22 Feb '17

Hi, by playing with the code I think I found something interesting. My environment: MariaDB 10.0.10, MyISAM-engine I played with a table-scan, no index is defined on this table. When I execute a SQL-statement that forces the server to do a second table- scan on a table this 2nd table-scan will be slow. The reason for this behaviour is the usage of a buffer: during the 1st scan this buffer is filled, used and filled again until the whole table is processed. At the end of the 1st scan it contains the last bytes of the file. When a 2nd scan is started the reading of the table starts from the beginning of the file but the buffer and all associated variables are not reset: the buffer still contains the bytes from the end of the file, the request cannot be fulfilled by the buffer so the request has to be handled by reading the bytes directly from the file using the read()-function of the Std-library. This takes much more time then simply copying the bytes from the internal buffer. My idea is: somewhere in the code this situation must be detected and the buffer (and all associated variables) reset to initial values. rein it_io_cache() looks like the right candidate for this. I assume the engine Maria will show the same effect (I didn't check other engines). Some questions: is my observation correct? Do you think this needs to be fixed? Or is there no further development on this part of the code? Thanks Regards AugustQ PS: you will find more details on this topic here: http://augustq.blogs pot.com/2017/01/subselect-execution-3.html

3 6

[Maria-developers] engine condition pushdown
by AugustQ 22 Feb '17

22 Feb '17

Hi, by playing with a SQL-statement I encountered a problem. I'm not sure if this is a bug or if I did not use the correct method to get the information I expected. So let me please explain the situation in which I encountered this problem. But it will take some lines of text to come to the point. My environment: MariaDB 10.0.10 source-code-version on Linux. The statement: select SQL_NO_CACHE B.PZN, B.ArtikelText from TestSmall B where exists ( select 1 from TestBig A where A.Hersteller = '36367' and A.PZN = B.PZN) and B.Hersteller = '00020' Engine used is MyISAM but the effect should be independent of any engine (not checked). No indexes exist on the tables involved. You will find a lot more of details here: http://augustq.blogspot.com/2 017/02/subselect-execution-4.html Execution of this (silly) statement is done by a table-scan on the table TestSmall. When a record is found the matches the WHERE-condition the server switches over to the table TestBig and does a table-scan on this table. When a match is found it returns to the table TestSmall, otherwise the table TestBig is read to the end and then the server returns to the table TestSmall. The server continues scanning the table TestSmall, switching over to TestBig, returning to TestSmall, continues reading TestSmall and so on, until it reaches the end of the table TestSmall. Of interest for me was the condition tree which can be inspected by looking at the function cond_push() (in my case ha_myisam::cond_push()). So let's start. The function cond_push() is initally called for the tables TestSmall and TestBig, this looks good. Then the server starts reading the table TestSmall and therefore the function cond_push() is called again. This looks good too. Now a matching record is found in TestSmall: it switches over to the table TestBig and calls cond_push() with the condition-tree for this table. This tree looks like this: COND-ITEM args: 0 type=[COND_AND_FUNC] FUNC-ITEM [=] args: 2 type=[EQ_FUNC] FIELD-ITEM [TestOpt] [A] [Hersteller] name=<Hersteller> STRING-ITEM str_length=<5> str_value=<36367> name=<36367> FUNC-ITEM [=] args: 2 type=[EQ_FUNC] FIELD-ITEM [TestOpt] [A] [PZN] name=<PZN> FIELD-ITEM [TestOpt] [B] [PZN] name=<PZN> The last line is of interest (I've marked it in bold). For the table with the alias B (= TestSmall) no value is given for the column PZN but this value is available. This is the first point of interest. Let's continue with the operation. When the table TestBig is read to the end, the scanning of TestSmall continues and a matching record is found over there the server again switches over to TestBig. Before it starts reading the records from this table the function cond_push() is called again. This is the output of the condition-tree, restricted to the line of interest (the last line): FIELD-ITEM [TestOpt] [B] [PZN] str_length=<2> str_value=<37> name=<PZN> So let's continue: when it switches to TestBig for the 3rd time this output looks like: FIELD-ITEM [TestOpt] [B] [PZN] str_length=<7> str_value=<222 > name=<PZN> Enough of data. I can deliver more examples if needed or you may look into my text. Judgement: The output of the first case des not show the value of the column PZN from the current record in TestSmall although the value is available. The output in the second case shows the value 37 (because of the length eq. 2) but the correct value would be 3717968 (and length eq. 7). The output in the third case shows the value 222 followed by 4 spaces (length eq. 7), but the correct value is 222 (length eq. 3). What I see is the correct value (exclude the first example presented here) but the length-information is one row behind (one row of TestSmall). For accessing the information presented here I used COND->str_value. So the question is: - the information is available but I used the wrong function/variable? OR - is this a bug in the code? What do you think? Thanks AugustQ

2 4

Re: [Maria-developers] 2c36ba67240: MySQL Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY - REQUIRES FLUSH PRIVILEGES
by Sergei Golubchik 22 Feb '17

22 Feb '17

Hi, Oleksandr! On Jan 10, Oleksandr Byelkin wrote: > revision-id: 2c36ba67240850d909bf75fc36346261f37019fb (mariadb-10.0.28-43-g2c36ba67240) > parent(s): eed319b6fb543849046c8009c38575455e173dc2 > committer: Oleksandr Byelkin > timestamp: 2017-01-10 16:02:42 +0100 > message: > > MySQL Bug#12766319 - 61865: RENAME USER DOES NOT WORK CORRECTLY - REQUIRES FLUSH PRIVILEGES > > Port & fix of MySQL patch for the problem. Don't mix the refactoring and a bug fix. I don't see from this big patch what the bug fix was. Please split in two (or more) commits. Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

1 0

Re: [Maria-developers] 0310b44: MDEV-10418 Assertion `m_extra_cache' failed in ha_partition::late_extra_cache(uint)
by Sergei Golubchik 21 Feb '17

21 Feb '17

Hi, Alexey! On Dec 20, Alexey Botchkov wrote: > revision-id: 0310b444dba1b9cbd6921963dd3bff689ca23a24 (mariadb-5.5.53-34-g0310b44) > parent(s): f23b41b9b8a30e0e54a1ec7a8923057b0e57e0f5 > committer: Alexey Botchkov > timestamp: 2016-12-20 00:24:20 +0400 > message: > > MDEV-10418 Assertion `m_extra_cache' failed in ha_partition::late_extra_cache(uint) > > The m_extra_prepare_for_update parameter should be set to FALSE > as the query ends. This seems fairly obvious, that it should. But why MySQL doesn't have this bug (despite not resetting m_extra_prepare_for_update)? > diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc > index 15fa7d1..4374354 100644 > --- a/sql/ha_partition.cc > +++ b/sql/ha_partition.cc > @@ -6667,6 +6667,7 @@ int ha_partition::reset(void) > DBUG_ENTER("ha_partition::reset"); > if (m_part_info) > bitmap_set_all(&m_part_info->used_partitions); > + m_extra_prepare_for_update= FALSE; > file= m_file; > do > { Regards, Sergei Chief Architect MariaDB and security(a)mariadb.org

2 2

Re: [Maria-developers] [Commits] e0a1e63: MDEV-9619: Assertion `null_ref_table' failed in virtual table_map Item_direct_view_ref::used_tables() const on 2nd execution of PS
by Sergey Petrunia 21 Feb '17

21 Feb '17

Hi Sanja, Two questions: 1. Why does the patch define Item_ptr type twice? 2. (The big one) In class Item_in_subselect, I see your comment made in 2015: ... public: Item *left_expr; /* Important for PS/SP: left_expr_orig is the item that left_expr originally pointed at. That item is allocated on the statement arena, while left_expr could later be changed to something on the execution arena. */ Item *left_expr_orig; Considering this comment, is it really safe to keep pointers to left_expr? Should left_expr_orig be used instead? On Sat, Feb 18, 2017 at 05:47:32PM +0100, Oleksandr Byelkin wrote: > revision-id: e0a1e632e4e6084cd989f217943618f4e9ac9179 (mariadb-5.5.54-25-ge0a1e63) > parent(s): 29d78dbb44ee9890b6bc28873344f20fc9157928 > committer: Oleksandr Byelkin > timestamp: 2017-02-18 17:47:31 +0100 > message: > > MDEV-9619: Assertion `null_ref_table' failed in virtual table_map Item_direct_view_ref::used_tables() const on 2nd execution of PS > > Refer left expression indirectly in case it changes from execution to execution. > > --- > mysql-test/r/ps.result | 24 ++++++++++++++++++++++++ > mysql-test/t/ps.test | 22 ++++++++++++++++++++++ > sql/item.h | 2 ++ > sql/opt_subselect.cc | 10 +++++----- > sql/table.h | 3 ++- > 5 files changed, 55 insertions(+), 6 deletions(-) > > diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result > index f954583..7233fe8 100644 > --- a/mysql-test/r/ps.result > +++ b/mysql-test/r/ps.result > @@ -4173,4 +4173,28 @@ Warnings: > Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0 > deallocate prepare stmt2; > drop table t1; > +# > +# MDEV-9619: Assertion `null_ref_table' failed in virtual > +# table_map Item_direct_view_ref::used_tables() const on 2nd > +# execution of PS > +# > +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; > +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; > +INSERT INTO t1 VALUES ('a'),('b'); > +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; > +INSERT INTO t2 VALUES ('c'),('d'); > +PREPARE stmt FROM "SELECT * FROM v1 WHERE f1 = SOME ( SELECT f2 FROM t2 )"; > +EXECUTE stmt; > +f1 > +EXECUTE stmt; > +f1 > +insert into t1 values ('c'); > +EXECUTE stmt; > +f1 > +c > +EXECUTE stmt; > +f1 > +c > +drop view v1; > +drop table t1,t2; > # End of 5.5 tests > diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test > index cfd810f..627a9bf 100644 > --- a/mysql-test/t/ps.test > +++ b/mysql-test/t/ps.test > @@ -3714,4 +3714,26 @@ deallocate prepare stmt2; > > drop table t1; > > +--echo # > +--echo # MDEV-9619: Assertion `null_ref_table' failed in virtual > +--echo # table_map Item_direct_view_ref::used_tables() const on 2nd > +--echo # execution of PS > +--echo # > + > +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; > +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; > +INSERT INTO t1 VALUES ('a'),('b'); > + > +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; > +INSERT INTO t2 VALUES ('c'),('d'); > + > +PREPARE stmt FROM "SELECT * FROM v1 WHERE f1 = SOME ( SELECT f2 FROM t2 )"; > +EXECUTE stmt; > +EXECUTE stmt; > +insert into t1 values ('c'); > +EXECUTE stmt; > +EXECUTE stmt; > + > +drop view v1; > +drop table t1,t2; > --echo # End of 5.5 tests > diff --git a/sql/item.h b/sql/item.h > index 89155ac..dff7bc4 100644 > --- a/sql/item.h > +++ b/sql/item.h > @@ -1543,6 +1543,8 @@ class Item { > virtual void mark_as_condition_AND_part(TABLE_LIST *embedding) {}; > }; > > +typedef Item (*Item_ptr); > + > > /** > Compare two Items for List<Item>::add_unique() > diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc > index 5137d8a..7f26d01 100644 > --- a/sql/opt_subselect.cc > +++ b/sql/opt_subselect.cc > @@ -1602,7 +1602,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) > > if (subq_pred->left_expr->cols() == 1) > { > - nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr); > + nested_join->sj_outer_expr_list.push_back(&subq_pred->left_expr); > /* > Create Item_func_eq. Note that > 1. this is done on the statement, not execution, arena > @@ -1624,7 +1624,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) > for (uint i= 0; i < subq_pred->left_expr->cols(); i++) > { > nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr-> > - element_index(i)); > + addr(i)); > Item_func_eq *item_eq= > new Item_func_eq(subq_pred->left_expr->element_index(i), > subq_lex->ref_pointer_array[i]); > @@ -3181,8 +3181,8 @@ void restore_prev_sj_state(const table_map remaining_tables, > ulonglong get_bound_sj_equalities(TABLE_LIST *sj_nest, > table_map remaining_tables) > { > - List_iterator<Item> li(sj_nest->nested_join->sj_outer_expr_list); > - Item *item; > + List_iterator<Item_ptr> li(sj_nest->nested_join->sj_outer_expr_list); > + Item **item; > uint i= 0; > ulonglong res= 0; > while ((item= li++)) > @@ -3193,7 +3193,7 @@ ulonglong get_bound_sj_equalities(TABLE_LIST *sj_nest, > class and see if there is an element that is bound? > (this is an optional feature) > */ > - if (!(item->used_tables() & remaining_tables)) > + if (!(item[0]->used_tables() & remaining_tables)) > { > res |= 1ULL << i; > } > diff --git a/sql/table.h b/sql/table.h > index dde01a8..c981243 100644 > --- a/sql/table.h > +++ b/sql/table.h > @@ -33,6 +33,7 @@ > /* Structs that defines the TABLE */ > > class Item; /* Needed by ORDER */ > +typedef Item (*Item_ptr); > class Item_subselect; > class Item_field; > class GRANT_TABLE; > @@ -2348,7 +2349,7 @@ typedef struct st_nested_join > table_map sj_depends_on; > /* Outer non-trivially correlated tables */ > table_map sj_corr_tables; > - List<Item> sj_outer_expr_list; > + List<Item_ptr> sj_outer_expr_list; > /** > True if this join nest node is completely covered by the query execution > plan. This means two things. > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MDEV-10697 - bb-10.2-compatibility
by jerome brauge 20 Feb '17

20 Feb '17

Hello, I've wrote a patch for MDEV-10697 (goto statement for sql_mode=oracle). If someone can review it. Thanks. Best regards, Jérôme.

2 8