Comment 8 for bug 586051

Hi Prafulla,

I'll take a look at this patch today. Thanks for looking at this!

On Fri, Aug 6, 2010 at 7:42 AM, Prafulla Tekawade <<email address hidden>
> wrote:

> ** Changed in: drizzle
> Assignee: (unassigned) => Prafulla Tekawade (prafulla-tekawade)
>
> --
> Server allowing JOIN on NULL values in certain cases if query includes
> ORDER BY clause
> https://bugs.launchpad.net/bugs/586051
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in A Lightweight SQL Database for Cloud and Web: Confirmed
>
> Bug description:
> The server is allowing a JOIN on NULL values for certain cases if an ORDER
> BY clause is included.
> >From the test case:
> SELECT table2 .`col_int` field3 FROM i table1 JOIN b table2 ON table1
> .`col_int_key` = table2 .`col_int_key` WHERE table1 .`pk` IN ( 4 )
> ORDER BY field3
>
> vs.
>
> SELECT table2 .`col_int` field3 FROM i table1 JOIN b table2 ON table1
> .`col_int_key` = table2 .`col_int_key` WHERE table1 .`pk` IN ( 4 ) /*
> TRANSFORM_OUTCOME_UNORDERED_MATCH */
>
> Produces this diff of result sets:
> # 2010-05-26T15:38:30 --- /tmp//randgen10880-1274902710-server0.dump
> 2010-05-26 15:38:30.930254321 -0400
> # 2010-05-26T15:38:30 +++ /tmp//randgen10880-1274902710-server1.dump
> 2010-05-26 15:38:30.930254321 -0400
> # 2010-05-26T15:38:30 @@ -1 +0,0 @@
> # 2010-05-26T15:38:30 -7
>
> Here we can see what happened (the full result row):
> SELECT table2 .`col_int` field3, table1.col_int_key, table2.col_int_key,
> table1.pk FROM i table1 JOIN b table2 ON table1 .`col_int_key` =
> table2 .`col_int_key` WHERE table1 .`pk` IN ( 4 ) ORDER BY field3;
> +--------+-------------+-------------+----+
> | field3 | col_int_key | col_int_key | pk |
> +--------+-------------+-------------+----+
> | 7 | NULL | NULL | 4 |
> +--------+-------------+-------------+----+
>
> That JOIN should not have happened. If we don't have the ORDER BY clause,
> the query properly returns 0 rows.
>
> EXPLAIN output:
> query with ORDER BY
> # |1|SIMPLE|table1|const|PRIMARY,col_int_key|PRIMARY|4|const|1|Using
> filesort|
> # |1|SIMPLE|table2|ref|col_int_key|col_int_key|5|const|1|Using where|
> # */
>
>
> query without ORDER BY
> # |1|SIMPLE|table1|const|PRIMARY,col_int_key|PRIMARY|4|const|1||
> # |1|SIMPLE|table2|ref|col_int_key|col_int_key|5|const|1||
> # */
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/drizzle/+bug/586051/+subscribe
>