Hi Anthony,

I would consider the behavior I (and now also you) observer a bug
(no proper error or proper result). Please file a bug report.

In your MonetDBLite case, do you indeed only get "ParseException:SQLparser:",
or is the actual parse error given.
If the former, it's also a bug (error message is incomplete);
if the latter, could you share the actual parse error with us?

I don't know all details of the SQL standard well enough to tell
whether the IN-expression is allowed in the select-clause or not,
but I cannot instantly see a reason why it shouldn't be allowed.
Hence, it might indeed be a bug (or at least missing feature) in MonetDB.

another alternative for your and Martin's first query would, e.g., be

select count(*), 1 from (
    (select this_column from table_one)
    intersect
    (select this_column from table_two)
) as "existing"
union all
select count(*), 0 from (
    (select this_column from table_one)
    except
    (select this_column from table_two)
) as "missing";

Best,
Stefan

----- On Nov 16, 2016, at 3:26 PM, Anthony Damico ajdamico@gmail.com wrote:

> hi Stefan, that is very strange. i was using MonetDBLite through R, which does
> return an error. but now i just tried on 2016SP2 and got the same odd results
> as you.
>
> i'm not sure what the expected behavior here should be.. are any of my three
> queries that broke actually legal? is that a monetdb bug that it doesn't return
> anything on the client side?
>
> thanks
>
>
>
> On Wed, Nov 16, 2016 at 9:11 AM, Stefan Manegold < Stefan.Manegold@cwi.nl >
> wrote:
>
>
> Hi Anthony et al.,
>
> which version of MonetDB are you using?
>
> With the latest release (Jun2016 SP2),
> I get slightly differen behaviour
> (as detailed in-line below.
>
> ----- On Nov 16, 2016, at 12:35 PM, Anthony Damico ajdamico@gmail.com wrote:
>
>> 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:'.
>
> Query returns without error or output on mclient side, but mserver5 says
> "
> could not find table_one.this_column
> L2.L1
> "
> !??
>
>
>> # 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:'.
>>
>
> Same as above: no output or error on mclient side, but mserver5 says
> "
> could not find table_one.this_column
> L2.L1
> "
>
>>
>> # 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:'.
>>
>
> again same as above.
>
> Best.
> Stefan
>
>>
>> _______________________________________________
>> users-list mailing list
>> users-list@monetdb.org
>> https://www.monetdb.org/mailman/listinfo/users-list
>
> --
>| Stefan.Manegold@CWI.nl | DB Architectures (DA) |
>| www.CWI.nl/~manegold/ | Science Park 123 (L321) |
>| +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>
>
> _______________________________________________
> users-list mailing list
> users-list@monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list

--
| Stefan.Manegold@CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list