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.
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.