EXCEPT doesn't work at all with JOIN

[WhiteG00se]

Hello there,

it's me again. I found another bug. Before describing it, I just quickly wanna thank you for RBQL and Rainbow CSV. About 2 months ago I just searched for a VS Code extension to improve readably of .csv files. Would have never imagined anything with such amazing functionality; really improved my interactions with .csv files by multiple orders of magnitude.

On to the bug: I've create 2 test .csv files file1.csv

id1|data1
1|qwe
2|asd
3|zxc
4|qwe
5|asd

file2.csv

id2|data2
1|rty
2|fgh
3|vbn
4|rty
5|fgh

These two queries result in errors, so at least no incorrect results. SELECT * EXCEPT b.id2 JOIN file2.csv ON a.id1==b.id2

error: 'Unknown field in EXCEPT expression: "b.id2"'

SELECT * EXCEPT b.data2 JOIN file2.csv ON a.id1==b.id2

error: 'Unknown field in EXCEPT expression: "b.data2"'

What is more problematic tho is that the following 2 queries return incorrect results. Ofc isolated like this, it's pretty obvious, that the results are off; but in use it could potentially go by unnoticed in a bigger project.

SELECT * EXCEPT a.id1 JOIN file2.csv ON a.id1==b.id2 result (2 columns are missing):

data1 qwe asd zxc qwe asd

SELECT * EXCEPT a.data1 JOIN file2.csv ON a.id1==b.id2 result (2 columns are missing):

id1 1 2 3 4 5

Best regards Tobias

[mechatroner]

Hello again, and thank you so much for the feedback, and for the bug report!

EXCEPT is a niche feature, the only major SQL engine which supports it (that I am aware of) is Big Query: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_except. At some point I even removed EXCEPT, but then changed my mind and decided to keep it, so looks like I made a good decision if you (and maybe some other people too) find it useful. Saying that I think the best course of action, for now, is to explicitly disallow EXCEPT with JOIN statements until I find time to properly implement it.

[WhiteG00se]

I totally agree, if you disallow them being used together, the simple workaround is to run 2 consecutive queries, the 1st with SELECT * [...] JOIN [...] and the 2nd one with SELECT * EXCEPT [...] That's just mildly inconvenient, but no functionality is lost that way.

The important part is, as you also pointed out, to not produce incorrect results.

[mechatroner]

I fixed this in the master branch and will try to deploy the fix soon.