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