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