dimanche 3 mai 2015

SQLite: OR and |

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