The following query works in SQL Server but not in SQLite 3.8.7 and I would like to know why.
Table
l | r
0 | 10
0 | 2
8 | 10
Query
SELECT s1.* FROM Segments AS s1
LEFT JOIN Segments AS s2
ON ((s2.l <= s1.l AND s2.r > s1.r)
OR (s2.l < s1.l AND s2.r >= s1.r));
Expected output
s1.l | s1.r | s2.l | s2.r
0 | 10 | null | null
0 | 2 | 0 | 10
8 | 10 | 0 | 10
However I got
s1.l | s1.r | s2.l | s2.r
0 | 10 | 0 | 2
0 | 2 | 0 | 10
8 | 10 | 0 | 10
And when I switched the expression order i.e
((s2.l < s1.l AND s2.r >= s1.r) (s2.l <= s1.l AND s2.r > s1.r))
I got
s1.l | s1.r | s2.l | s2.r
0 | 10 | 8 | 10
0 | 2 | 0 | 10
8 | 10 | 0 | 10
This was solved by using | instead of OR, but I am wondering why OR did not work?
Thanks
Aucun commentaire:
Enregistrer un commentaire