hi monetdb team,

i've pasted a minimal reproducible example of a few SQL queries that i had expected to work -- only about half of them do work, and the other half just error with 'ParseException:SQLparser:'

my goal is to get the very last line working, so if anybody could suggest a hack or a workaround that doesn't involve a JOIN, i would appreciate it. thanks all!!

CREATE TABLE table_one (this_column INTEGER) ;
INSERT INTO table_one VALUES (1) ;
INSERT INTO table_one VALUES (2) ;
INSERT INTO table_one VALUES (3) ;
INSERT INTO table_one VALUES (4) ;
INSERT INTO table_one VALUES (5) ;

CREATE TABLE table_two (this_column INTEGER) ;
INSERT INTO table_two VALUES (1) ;
INSERT INTO table_two VALUES (2) ;

# works as expected
SELECT SUM( ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) ) FROM table_one ;
SELECT MAX( ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) ) FROM table_one ;

# fails
SELECT ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) AS new_column FROM table_one ;

# Server says 'ParseException:SQLparser:'.

# works
SELECT AVG( CASE WHEN table_one.this_column IN ( SELECT this_column FROM table_two ) THEN 1 ELSE 0 END ) AS new_column FROM table_one ;

# fails
SELECT ( CASE WHEN ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) THEN 1 ELSE 0 END ) AS new_column FROM table_one ;

# Server says 'ParseException:SQLparser:'.

# fails [[this query is the one i would like to implement]]
SELECT COUNT(*) , ( CASE WHEN ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) THEN 1 ELSE 0 END ) AS new_column FROM table_one GROUP BY new_column ;

# Server says 'ParseException:SQLparser:'.