Initially I didn't plan to write this text. My intention was to write how the server executes a special statement but by collecting the data for my text I found something that was more interesting than my initial idea (the same happened with the last text). I decided to put my plan aside and write about my observation.
As this text is about engine condition pushdown (ECP) implemented in MariaDB so please look into an older post of mine with the information about this topic: WHERE. I will use the information presented there and especially the piece of code described in this text.
I want to show and describe what the engine condition pushdown reports on my SQL-statement and to make it short: I don't like the result. Maybe it's a bug in the code, maybe I did something wrong. Let's start with the example.
warning
Before I describe my observation let me issue a warning: the functions, hierarchies and data-examples shown here are valid for the environment on my PC. If you do your own tests this may look a bit different.environment
Again I'm using my usual environment which I already described in one of my older posts. It's the same tables, the same data in the tables etc.. I've changed the SQL-statement and therefore my focus changes. I'm looking in the handling of the extreme-situation when no indexes are defined on the tables (silly, but the server has to handle this somehow). In this text I want to describe the contents of the condition-tree; as this happens in the layer above the storage-engine I think my description is valid for all engines but I didn't check this. Nevertheless I want to tell you I've used the MyISAM-engine for my tests.the SQL-statement
For this text this is the statement I want to inspect:MariaDB [TestOpt]> set optimizer_switch='engine_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)
MariaDB [TestOpt]> 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';
No result given here, I will focus on the data in the condition-tree.
the condition-tree
As condition-pushdown is enabled for this test the function ha_myisam::cond_push() is called and it is called multiple times as I will show you soon. Using the code given here I will receive this output in the console-window of Eclipse:table_name = <TestSmall> <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'>
COND-ITEM args: 0 type=[COND_AND_FUNC]
FUNC-ITEM [=] args: 2 type=[EQ_FUNC]
FIELD-ITEM [TestOpt] [B] [Hersteller] name=<Hersteller>
STRING-ITEM str_length=<5> str_value=<00020> name=<00020>
SUBSELECT-ITEM
table_name = <TestBig> <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'>
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>
This text will appear before any data is read from the tables.
This form looks ugly so I will present the same data (= the condition-tree) in a better form:
for the table TestSmall:
and for the table TestBig1):
These 2 trees were given to the function ha_myisam::cond_push() before any data was read from the tables. So let's go to the execution-stage.
executing the statement
As this is the same statement as in my last post so please look into this text for a description of the steps for executing this statement.The execution begins with a table-scan on the table TestSmall. Before starting this table-scan the function cond_push() is called again with the condition-tree for this table which is a little bit modified, but this is not of interest here. Then reading the table TestSmall starts and when a record matching the condition is found the server switches over to the table TestBig and searches for corresponding records in this table. As described the only way to do this is by a table-scan. Before this table-scan starts cond_push() is called again, this time with the condition-tree for the table TestBig (the call is done in init_read_record()). And this condition-tree is of interest, it looks like this:
table_name = <TestBig> <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'>
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>
Of interest are the last 2 lines, for this reason I've marked them in bold. For the next examples I will only present these lines.If you compare this output with the output presented some lines above you will not see any difference. Instead of explaining what I expected I would like to let the server continue it's work: it starts reading record after record from TestBig and when a match is found it stops reading TestBig and returns to TestSmall. There it continues reading.
When the next match is found in TestSmall it will switch over to the table TestBig again. Before reading the first record from TestBig it calls cond_push(). The line of interest now looks like this:
FIELD-ITEM [TestOpt] [B] [PZN] str_length=<2> str_value=<37> name=<PZN>
Something has changed in the condition-tree, I've marked this part in bold: the line contains a value from the column PZN of the current record in TestSmall (aka B). Let's loop the server through the records in TestBig, maybe until the end when no match is found. It then returns to the table TestSmall and continues reading records from this table.
OK, this behaviour repeats, in total 60 times (for my data here, as described in my last post). So I want to present only these 2 lines from the output of the condition-tree and only for the first 5 calls2):
1st match (repeated):
FIELD-ITEM [TestOpt] [A] [PZN] name=<PZN>
FIELD-ITEM [TestOpt] [B] [PZN] name=<PZN>
2nd match:
FIELD-ITEM [TestOpt] [A] [PZN] str_length=<6> str_value=< > name=<PZN>
FIELD-ITEM [TestOpt] [B] [PZN] str_length=<2> str_value=<37> name=<PZN>
3rd match:
FIELD-ITEM [TestOpt] [A] [PZN] str_length=<6> str_value=< > name=<PZN>
FIELD-ITEM [TestOpt] [B] [PZN] str_length=<7> str_value=<222 > name=<PZN>
4th match:
FIELD-ITEM [TestOpt] [A] [PZN] str_length=<3> str_value=<< > name=<PZN>
FIELD-ITEM [TestOpt] [B] [PZN] str_length=<3> str_value=<371> name=<PZN>
5th match:
FIELD-ITEM [TestOpt] [A] [PZN] str_length=<6> str_value=< > name=<PZN>
FIELD-ITEM [TestOpt] [B] [PZN] str_length=<7> str_value=<3717997> name=<PZN>
and so on
For getting the information presented above I used the member-functions of the Item-class (aka COND-class) of derived classes especially the member str_value (of type String). If this is the wrong way to access the information please drop me a line. Thanks.
So let's look at the data and compare these values with the data in the box above:
MariaDB [TestOpt]> select * from TestSmall where Hersteller = '00020' limit 5;
+---------+---------+------+------+--------------------------------+----------------------------+------------+
| Id | PZN | EVP | HAP | ArtikelBez | ArtikelText | Hersteller |
+---------+---------+------+------+--------------------------------+----------------------------+------------+
| 1002100 | 12 | 3.95 | 1.83 | TINY TOON ADVENTURES KIND 1 ST | TINY TOON ADVENTURES KIND | 00020 |
| 1025266 | 3717968 | 0.00 | 2.90 | PRESSOTHERM KALT/WA 13X14 1 ST | PRESSOTHERM KALT/WA 13X14 | 00020 |
| 1025267 | 222 | 0.00 | 4.45 | PRESSOTHERM KALT/WA 12X29 1 ST | PRESSOTHERM KALT/WA 12X29 | 00020 |
| 1025268 | 3717980 | 0.00 | 6.30 | PRESSOTHERM KALT/WA 16X26 1 ST | PRESSOTHERM KALT/WA 16X26 | 00020 |
| 1025269 | 3717997 | 0.00 | 6.30 | PRESSOTHERM KALT/WA 20X20 1 ST | PRESSOTHERM KALT/WA 20X20 | 00020 |
+---------+---------+------+------+--------------------------------+----------------------------+------------+
5 rows in set (0.06 sec)
MariaDB [TestOpt]>
expectations
So what did I expect? Engine condition pushdown gives the storage engine a chance to use the information in the WHERE-clause of the statement to speed-up a query. I like the idea.In my case here the function cond_push() is initially called for the table TestSmall and then for the table TestBig. Then the server starts reading the table TestSmall and calls cond_push() again. Agreed. When it switches over to the table TestBig it calls cond_push() again, giving it the condition-tree for this table. Again: agreed. But if I look at the 5 outputs of the condition-tree I do not see the values found in the PZN-column shown above.
inspecting the condition-tree
The function cond_push() is called before any real access of the data-file happens When this happens the first time a value of 12 is found in the column PZN of the table TestSmall and I expected to see this value in the corresponding line:output found:
FIELD-ITEM [TestOpt] [A] [PZN] name=<PZN>
FIELD-ITEM [TestOpt] [B] [PZN] name=<PZN>
output expected:
FIELD-ITEM [TestOpt] [A] [PZN] str_length=<6> str_value=< > name=<PZN>
FIELD-ITEM [TestOpt] [B] [PZN] str_length=<2> str_value=<12> name=<PZN>
So let me show in short form the output (the last line of the condition-tree-output) of all 5 calls of cond_push() that I would expect:
1st match:
FIELD-ITEM [TestOpt] [B] [PZN] str_length=<2> str_value=<12> name=<PZN>
2nd match:
FIELD-ITEM [TestOpt] [B] [PZN] str_length=<7> str_value=<3717968> name=<PZN>
3rd match:
FIELD-ITEM [TestOpt] [B] [PZN] str_length=<3> str_value=<222> name=<PZN>
4th match:
FIELD-ITEM [TestOpt] [B] [PZN] str_length=<7> str_value=<3717980> name=<PZN>
5th match:
FIELD-ITEM [TestOpt] [B] [PZN] str_length=<7> str_value=<3717997> name=<PZN>
and so on
Please compare these lines with the real output, presented some lines above. These are the differences:
- the value from the current record of TestSmall is given in the correct form in the condition-tree representing the WHERE-condition for TestBig
- currently the length-information is incorrect, it's one row behind. This should be the length of the string in the current row, e.g. compare the length-information for the 3rd match: actual it's 7 but it should be 3.
evaluation of the results
Engine condition pushdown gives the storage-engine the information which it can use internally to speed-up the request. It should be given the correct information and all the information available. For the statement presented here this means that when cond_push() is called before the first access of the table TestBig the condition-tree should contain the entry 12 (= the value of the column PZN of the current row of TestSmall in my example). And for the 2nd access the tree should contain the value 3717968 (= value of the column PZN of the then current row of the table TestSmall. And so on.conclusion
I see these 2 possibilities:- it's a bug in the code
- the information is there but I used the wrong function for accessing it
I will ask the developers.
update
2017/05/10: problem fixed. You will find a corrected version of the code presented here in this file: WHERE.cc. With this code I got the the values as expected.correctness
This text is a simplified presentation of what's going on in the software. As I'm still learning the inner-workings of this software errors on my side can happen. In case something is wrong with my description please use the mail-function of this site and send me a mail describing my error. I will look at it. Thanks.some notes:
1) I had to give the equal-functions different names otherwise the code for drawing the tree would have some trouble with drawing the correct relations
2) let's ignore the first (initial call to cond_push(), I want to look at the calls before the table-scan starts
