MySQL Forums
Forum List  »  General

Re: XOR returns true when OR returns false
Posted by: Peter Brawley
Date: August 16, 2016 12:04PM

The Select statement in your View (edited for readability) ...

CREATE VIEW ldz_from_rec AS 
SELECT 
  l.id AS id, 
  p.Region AS region, 
  l.LDZ_Id AS LDZ_Id, 
  l.LDZ_Exit AS LDZ_Exit 
FROM gas_postcode p 
JOIN gas_postcode_ldz l ON p.Postcode = LEFT(l.Postcode, 2) 
GROUP BY p.Region, l.LDZ_Id, l.LDZ_Exit ;

returns an empty set, so this dataset can't be used to debug the problem query.

There's a more basic problem with the View on which the query is based. Against SQL standards, MySQL allows non-aggregating, non-aggregated columns in Group By queries. Such column results are accurate, however, if and only if there is a unique value of the non-aggregating, non-aggregated column(s) for each unique Group By combination. When that's the case, results in the non-aggregating, non-aggregated column are accurate, but when it isn't, they are arbitrary. We're talking about the ID column. So: what is Group By supposed to achieve in that View?

The query effectively fetches such IDs from a join of this problematic View with a Select Distinct Region subset of itself, hiding the join in a correlated subquery. Which is why I say the query is funky.

To find out if the problematic Group By is the source of the XOR-OR anomaly, lose the Group By in the View in favour of a Select Distinct.

If that doesn't fix the problem, find out if the funky correlated subquery pseudo-join is the source of the problem by rewriting it as a conventional join.

And to further investigate what's going on, inspect the results of ...

select 
  (select region from ldz_from_rec order by ldz_exit asc limit 1) as ldz_from_rec_region,
  distinct1.region as distinct_region
from ( SELECT DISTINCT region FROM ldz_from_rec ) as Distinct1 ;



Edited 2 time(s). Last edit at 08/16/2016 01:19PM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
Re: XOR returns true when OR returns false
August 16, 2016 12:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.