[Maria-developers] engine condition pushdown
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
Hi, AugustQ! First, there won't be an answer to your question here, sorry :( A couple of thoughts: cond->str_value is not the how you get the value of an Item. Item::str_value is kind of a internal storage in the Item, can be used as a cache or temporary value holder or anything. Or not used at all, it's up to the individual item implementation. For some items it might hold the value. Condition pushdown was originally created for engines like NDB, that store data remotely and need to send to the SQL executor for processing. With condition pushdown they could avoid sending data that will be anyway discarded by the server. So, if something isn't pushed down, it may be because pushing it wasn't helping in that particular use case. Now, if you'd provided a complete test case - an sql file that I could just feed into mysql command line client and it would create and populate tables and execute your query - I could've looked in a debugger and answer your question exactly. Without a test case - I tried to, but couldn't (read your blog too). On Feb 16, AugustQ wrote:
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?
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Hi Sergej, attached you will find a file with commands for generating the effect. It's not a script so please copy and paste the lines into your favourite front-end. Here is the result on my test: # the result in cond_push(): for TestSmall: OK for TestBig: OK start scanning TestSmall: OK 1st scanning TestBig: FIELD-ITEM [TestECP] [B] [PZN] name=<PZN> 2nd scanning TestBig: FIELD-ITEM [TestECP] [B] [PZN] str_length=<2> str_value=<37> name=<PZN> 3rd scanning TestBig: FIELD-ITEM [TestECP] [B] [PZN] str_length=<7> str_value=<222 > name=<PZN> 4th scanning TestBig: FIELD-ITEM [TestECP] [B] [PZN] str_length=<3> str_value=<371> name=<PZN> 5th scanning TestBig: FIELD-ITEM [TestECP] [B] [PZN] str_length=<7> str_value=<3717997> name=<PZN> as you cann see from the INSERT-statement above it is: 1st: is <>, should have been <12> 2nd: is <2>, should have been <3717968> 3rd: is <222 >, should have been <222> 4th: ia <371>, should have been <3717980> 5th: is <3717997>, this is correct!The data is almost identical to the data in my text.Hope this helps. AugustQ Am Sonntag, den 19.02.2017, 17:32 +0100 schrieb Sergei Golubchik:
Hi, AugustQ!
First, there won't be an answer to your question here, sorry :(
A couple of thoughts:
cond->str_value is not the how you get the value of an Item. Item::str_value is kind of a internal storage in the Item, can be used as a cache or temporary value holder or anything. Or not used at all, it's up to the individual item implementation. For some items it might hold the value.
Condition pushdown was originally created for engines like NDB, that store data remotely and need to send to the SQL executor for processing. With condition pushdown they could avoid sending data that will be anyway discarded by the server. So, if something isn't pushed down, it may be because pushing it wasn't helping in that particular use case.
Now, if you'd provided a complete test case - an sql file that I could just feed into mysql command line client and it would create and populate tables and execute your query - I could've looked in a debugger and answer your question exactly.
Without a test case - I tried to, but couldn't (read your blog too).
On Feb 16, AugustQ wrote:
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.c om/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?
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Hi, AugustQ! On Feb 21, AugustQ wrote:
Hi Sergej, attached you will find a file with commands for generating the effect. It's not a script so please copy and paste the lines into your favourite front-end. Here is the result on my test: # the result in cond_push(): for TestSmall: OK for TestBig: OK
as you cann see from the INSERT-statement above it is: 1st: is <>, should have been <12>
It is 12. Don't look at Item::str_value. In case of the Item_field, you need to look at item->field->ptr.
2nd: is <2>, should have been <3717968>
It's 3717968
3rd: is <222 >, should have been <222>
And so on, all values are as they should've been. Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Hi Sergei, I had a similar idea yesterday. A statement like this will do the trick: ((Item_field*)tempCond)->val_str(&tempCond->str_value); OK, problem solved. Thanks for your help. AugustQ Am Mittwoch, den 22.02.2017, 12:13 +0100 schrieb Sergei Golubchik:
Hi, AugustQ!
On Feb 21, AugustQ wrote:
Hi Sergej, attached you will find a file with commands for generating the effect. It's not a script so please copy and paste the lines into your favourite front-end. Here is the result on my test: # the result in cond_push(): for TestSmall: OK for TestBig: OK
as you cann see from the INSERT-statement above it is: 1st: is <>, should have been <12>
It is 12. Don't look at Item::str_value. In case of the Item_field, you need to look at item->field->ptr.
2nd: is <2>, should have been <3717968>
It's 3717968
3rd: is <222 >, should have been <222>
And so on, all values are as they should've been.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
participants (2)
-
AugustQ -
Sergei Golubchik