Allow the direct use of WITH CTEs with the IN operator in Drift files

[frankvollebregt]

Describe the bug
Right now, when you define a CTE using the WITH keyword, and want to use it in an IN operator directly, the build_runner fails and outputs

Expected opening parenthesis for tuple

In other words, the following works in SQLite:

WITH names AS (
  SELECT name FROM persons
)

SELECT
  *,
  'John' IN names AS john_exists
FROM my_table

Whereas in Drift, this would result in the error above, and you need to do the following instead:

WITH names AS (
  SELECT name FROM persons
)

SELECT
  *,
  'John' IN (SELECT * FROM names) AS john_exists
FROM my_table

Nothing very major or broken, but it'd be nice if I was able to directly use the CTEs in my query

Full sample to illustrate the problem

CREATE TABLE cars (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  wheels INTEGER DEFAULT 4,
  model TEXT
);

CREATE TABLE colors (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  color TEXT
);

INSERT INTO colors (color) VALUES
('red'),
('green'),
('blue'),
('yellow'),
('black'),
('brown');

INSERT INTO cars (model, wheels)
VALUES ('Reliant Robin', 3), ('Opel Vectra', 4), ('Volkswagen ID.3', 4);

WITH colors_with_b AS (
  SELECT color FROM colors WHERE color LIKE 'b%'
)

SELECT
   model, 
  'blue' IN colors_with_b AS available_in_blue,
  'green' IN colors_with_b AS available_in_green
FROM cars;

[simolus3]

Oh nice, I didn't know that was a thing! This will be allowed with the next release.

[frankvollebregt]

Neither did I: I found out by accident, but it's pretty useful. Thank you for the quick turnaround on this one!

It only works if there's only a single column in the CTE, from what I understand.

As in, this works:

WITH names AS (
  SELECT name FROM persons
)

SELECT
  *,
  'John' IN names AS john_exists
FROM my_table

Whereas this does not:

WITH names AS (
  SELECT name, surname FROM persons
)

SELECT
  *,
  'John' IN names AS john_exists
FROM my_table

But from what I understand looking at your commit, you do include such a column comparison already, so it should all be good :)

[frankvollebregt]

@simolus3 hey, unfortunately this does not work properly in the 2.17 release. The column name in the generated data class is incorrect (the data contained therein is fine, though)

It looks like it gets resolved to the textual content of the whole line, i.e. johninnamesASjohnExists, instead of just everything after AS, i.e. johnExists.

[simolus3]

Thanks for the report, I've released version 0.35.1 of sqlparser to fix this.