From: Jorgen Loland
Date: June 24 2011 6:15am
Subject: Re: Patch for BUG#12619399
List-Archive: http://lists.mysql.com/commits/139802
Message-Id: <4E042B70.6020504@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

On 06/23/2011 04:04 PM, Roy Lyseng wrote:
> Hi Jørgen,
>
> thank you for fixing this problem.
>
> The bugfix is approved, but please consider changes in test cases and comments
> as indicated.
>
> I've also noted a reminder to myself on adding some guidelines for use of
> use_join_cache flag...
>
> On 23.06.11 11.27, Jorgen Loland wrote:
>> - NULL if the subquery returns rows after disabling subquery
>> conditions relying on the NULL outer values
>> - NULL if the subquery evaluates to NULL
>
> A table-valued subquery cannot evaluate to NULL (or UNKNOWN), its result is
> either an empty row set or a non-empty row set. Also beware that the logic is
> reverted when e.g a NOT IN operator is considered instead of IN.

Thanks. This comment effectively says what Item_in_optimizer::val_int() does. 
I'll fix this in the next commit.

> +
> +let query_all=
> +SELECT t3.c1 FROM t3
> +WHERE t3.c1 = ALL (SELECT t1.c2_key FROM t2 JOIN t1 ON t2.pk < t1.c1)
> +XOR TRUE;
>
> I think that you can simplify this test case by removing XOR. It should still be
> "top level". You may also add in a NOT IN query for completeness.

The reason for keeping the XOR is to *not* have top level. If I remove XOR, I'll 
have to replace it with something other than AND.

I'll add a test case for NOT IN for completeness even though some tests in 
subquery_all* already does this. In the NOT IN case, the XOR is not needed to 
get "not top level" behavior. I can also add an IN test without XOR to show that 
BKA can be used in that case.



-- 
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway