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/2017/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:

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):

So let's continue: when it switches to TestBig for the 3rd time this output looks like:

Enough of data. I can deliver more examples if needed or you may look into my text.

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.