MySQL Bugs: #23085: OR clause is checking more conditions than needed to satisfy

Bug #23085	OR clause is checking more conditions than needed to satisfy
Submitted:	8 Oct 2006 1:01	Modified:	13 Oct 2006 23:57
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22-Debian_0ubuntu6.06.2-log	OS:	Linux (Ubuntu 6.06)
Assigned to:		CPU Architecture:	Any

Description:
A LEFT JOIN with an OR in the WHERE clause should be stopping as soon as the clause is satisfied, but appears to continue to check more of the OR clauses even though the first is always true.

Please, see the attachment for scripts to create test tables to run the test query against.  Unfortunately I only have access to this database via phpMyAdmin, but I was able to trim the tables down to only a few rows and columns, export them, delete them, import them again and verify the behavior.

How to repeat:
After running the setup script I will attach, run this query:

SELECT ac. *
FROM test_1 AS ac
INNER JOIN test_2 AS ia ON ia.c_action = ac.c_title
AND ia.c_table = 'sc_membership_type'
LEFT OUTER JOIN test_3 AS pr ON pr.c_related_table = 'sc_membership_type'
AND pr.c_deleted <>1
AND pr.c_action = ac.c_title
AND pr.c_what_relates_to = 'table'
WHERE (
ac.c_apply_object =0
)
AND (
( 1 &1 <>0 )
)
LIMIT 0 , 30

This query produces three rows, which I expect.  It is joining to table test_3 but not selecting any data from it or checking it in the WHERE clause.

The LIMIT 0, 30 is added in by phpMyAdmin.  I don't know how to get it to stop doing that, and I don't know whether phpMyAdmin is running the query above (which it has re-formatted and LIMITed) or the query I enter.  The real query I'm entering in the query input window is as follows:

select ac.*
from
    test_1 as ac
    inner join test_2 as ia
        on ia.c_action = ac.c_title and ia.c_table ='sc_membership_type'
    left outer join test_3 as pr
        on pr.c_related_table = 'sc_membership_type'
            and pr.c_deleted <> 1
            and pr.c_action = ac.c_title
            and pr.c_what_relates_to = 'table'
where
    (ac.c_apply_object = 0) and (
        (1 & 1 <> 0))

Now for the query that doesn't work.  If I add the following into the WHERE clause, 

        or (pr.c_what_granted_to = 'user' and pr.c_who_granted_to = 1)
        or (pr.c_what_granted_to = 'group' and (pr.c_who_granted_to & 1 <> 0))

The query will only return one row instead of three.  Here is the full query with that addition:

select ac.*
from
    test_1 as ac
    inner join test_2 as ia
        on ia.c_action = ac.c_title and ia.c_table ='sc_membership_type'
    left outer join test_3 as pr
        on pr.c_related_table = 'sc_membership_type'
            and pr.c_deleted <> 1
            and pr.c_action = ac.c_title
            and pr.c_what_relates_to = 'table'
where
    (ac.c_apply_object = 0) and (
        (1 & 1 <> 0)
        or (pr.c_what_granted_to = 'user' and pr.c_who_granted_to = 1)
        or (pr.c_what_granted_to = 'group' and (pr.c_who_granted_to & 1 <> 0)))

This query should be returning all three rows, the same as the first query, because (1 & 1 <> 0) is always true.

Notice, it does return one row, and this row corresponds to the row in the test_3 table (so it seems to only be returning a row where the LEFT JOIN succeeds, even though the query shouldn't require that).  I don't know if that's helpful for figuring this bug out or not, but I thought I should mention it.

Script to set up tables and populate them with test data.

Attachment: setup.sql (text/x-sql), 1.70 KiB.

Thank you for the report.

I can not repeat it using last BK sources.

I tried it at work too,  5.0.21-log on Gentoo, and got the same wrong results.  To be more clear, the query returns

+----------+----------------+
| c_title  | c_apply_object |
+----------+----------------+
| list_all |              0 |
+----------+----------------+

but it should return

+---------------+----------------+
| c_title       | c_apply_object |
+---------------+----------------+
| create        |              0 |
| list_all      |              0 |
| list_owned_by |              0 |
+---------------+----------------+

So you got three rows in all cases?  Maybe it is fixed then.

>So you got three rows in all cases?  Maybe it is fixed then.
Yes, I've got three rows in all cases. Use current version accessible from http://dev.mysql.com/downloads/mysql/5.0.html