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
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