how to structure queries like SELECT a.col IN ( b.col FROM b ) FROM a ?
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:'.
SELECT COUNT(*) AS count, 1 AS new_column FROM table_one WHERE this_column IN (SELECT this_column FROM table_two) UNION ALL SELECT COUNT(*) AS count, 0 AS new_column FROM table_one WHERE this_column NOT IN (SELECT this_column FROM table_two);
or with a join:
SELECT COUNT(*) AS count, (CASE WHEN t2.this_column IS NULL THEN 0 ELSE 1 END) AS new_column FROM table_one t1 LEFT OUTER JOIN table_two t2 ON t1.this_column = t2.this_column GROUP BY new_column;
On 16-11-2016 12:35, Anthony Damico 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:'.
# 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:'.
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
ooof, i guess i might need to implement a join after all. seems like the cleaner option.. thanks Martin for taking the time to help me out!
On Wed, Nov 16, 2016 at 8:32 AM, dinther dinther@monetdbsolutions.com wrote:
SELECT COUNT(*) AS count, 1 AS new_column FROM table_one WHERE this_column IN (SELECT this_column FROM table_two) UNION ALL SELECT COUNT(*) AS count, 0 AS new_column FROM table_one WHERE this_column NOT IN (SELECT this_column FROM table_two);
or with a join:
SELECT COUNT(*) AS count, (CASE WHEN t2.this_column IS NULL THEN 0 ELSE 1 END) AS new_column FROM table_one t1 LEFT OUTER JOIN table_two t2 ON t1.this_column = t2.this_column GROUP BY new_column;
On 16-11-2016 12:35, Anthony Damico 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:'.
# 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:'.
users-list mailing listusers-list@monetdb.orghttps://www.monetdb.org/mailman/listinfo/users-list
Revisiting your question and query: 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 ;
You can use sub-queries in the select-list, but they need to return a scalar value (and preferably be correlated) for each row of table_one. So use for instance: SELECT this_column, (SELECT COUNT(*) FROM table_two t2 WHERE t2.this_column = table_one.this_column) AS new_column FROM table_one;
For the derivation of whether a match of this_column value exists in t2 or not, use: SELECT this_column, (CASE WHEN (SELECT COUNT(*) FROM table_two t2 WHERE t2.this_column = table_one.this_column) = 0 THEN 0 ELSE 1 END) AS new_column FROM table_one; +-------------+------------+ | this_column | new_column | +=============+============+ | 1 | 1 | | 2 | 1 | | 3 | 0 | | 4 | 0 | | 5 | 0 | +-------------+------------+ 5 tuples (37.294ms)
You can use groupings on this query result, such as: SELECT COUNT(*), (CASE WHEN (SELECT COUNT(*) FROM table_two t2 WHERE t2.this_column = table_one.this_column) = 0 THEN 0 ELSE 1 END) AS new_column FROM table_one GROUP BY new_column;
This query unfortunately does not return a result in the Jun2016-SP2 release, which is a defect. Thanks for reporting this in Bugzilla.
To (temporarily) workaround this defect problem, I found you can use a view. Create it via: CREATE VIEW table_one_vw AS SELECT this_column, (CASE WHEN (SELECT COUNT(*) FROM table_two t2 WHERE t2.this_column = table_one.this_column) = 0 THEN 0 ELSE 1 END) AS new_column FROM table_one;
and next do the grouping on the view: SELECT COUNT(*), new_column FROM table_one_vw GROUP BY new_column;
or with multiple aggregations per group: SELECT COUNT(*) AS count, MIN(this_column) AS min_this_column, MAX(this_column) AS max_this_column, AVG(this_column) AS avg_this_column, SUM(this_column) AS sum_this_column, new_column FROM table_one_vw GROUP BY new_column; +-------+----------+----------+--------------------------+----------+----------+ | count | min_this | max_this | avg_this_column | sum_this | new_colu | : : _column : _column : : _column : mn : +=======+==========+==========+==========================+==========+==========+ | 2 | 1 | 2 | 1.5 | 3 | 1 | | 3 | 3 | 5 | 4 | 12 | 0 | +-------+----------+----------+--------------------------+----------+----------+ 2 tuples (50.325ms)
These solutions give correct results, even if the values in this_column contain duplicates in table_one or in table_two or in both. Try it.
If you do not want to create a view (once), you can also use a WITH common table expression construct: WITH table_one_cte AS (SELECT this_column, (CASE (SELECT COUNT(*) FROM table_two t2 WHERE t2.this_column = table_one.this_column) WHEN 0 THEN 0 ELSE 1 END) AS new_column FROM table_one) SELECT COUNT(*) AS count, MIN(this_column) AS min_this_column, MAX(this_column) AS max_this_column, AVG(this_column) AS avg_this_column, SUM(this_column) AS sum_this_column, new_column FROM table_one_cte GROUP BY new_column;
Note, the below suggested join-query:
SELECT COUNT(*) AS count, (CASE WHEN t2.this_column IS NULL THEN 0 ELSE 1 END) AS new_column FROM table_one t1 LEFT OUTER JOIN table_two t2 ON t1.this_column = t2.this_column GROUP BY new_column;
will NOT give your desired results if this_column values in table_two contain duplicates!
The below UNION ALL query however works okay, even if the values in this_column contain duplicates in table_one or in table_two or in both.
Martin van Dinther
On 16-11-2016 15:11, Anthony Damico wrote:
ooof, i guess i might need to implement a join after all. seems like the cleaner option.. thanks Martin for taking the time to help me out!
On Wed, Nov 16, 2016 at 8:32 AM, dinther <dinther@monetdbsolutions.com mailto:dinther@monetdbsolutions.com> wrote:
SELECT COUNT(*) AS count, 1 AS new_column FROM table_one WHERE this_column IN (SELECT this_column FROM table_two) UNION ALL SELECT COUNT(*) AS count, 0 AS new_column FROM table_one WHERE this_column NOT IN (SELECT this_column FROM table_two); or with a join: SELECT COUNT(*) AS count, (CASE WHEN t2.this_column IS NULL THEN 0 ELSE 1 END) AS new_column FROM table_one t1 LEFT OUTER JOIN table_two t2 ON t1.this_column = t2.this_column GROUP BY new_column; On 16-11-2016 12:35, Anthony Damico 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:'. # 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:'. _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> https://www.monetdb.org/mailman/listinfo/users-list <https://www.monetdb.org/mailman/listinfo/users-list>
Martin, Anthony,
the latter (join) is only "equivalent" to the first or to Anthony's "wish" if table_two.this_column is unique, i.e., has no duplicate values (more precisely, no duplicate values that also occur also in table_one.this_column).
Best, Stefan
----- On Nov 16, 2016, at 2:32 PM, dinther dinther@monetdbsolutions.com wrote:
SELECT COUNT(*) AS count, 1 AS new_column FROM table_one WHERE this_column IN (SELECT this_column FROM table_two) UNION ALL SELECT COUNT(*) AS count, 0 AS new_column FROM table_one WHERE this_column NOT IN (SELECT this_column FROM table_two);
or with a join:
SELECT COUNT(*) AS count, (CASE WHEN t2.this_column IS NULL THEN 0 ELSE 1 END) AS new_column FROM table_one t1 LEFT OUTER JOIN table_two t2 ON t1.this_column = t2.this_column GROUP BY new_column;
On 16-11-2016 12:35, Anthony Damico 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:'.
# 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:'.
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
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
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
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
thanks a lot for your help, Stefan! hope i got everything reasonably well filed--
https://www.monetdb.org/bugzilla/show_bug.cgi?id=6121 https://www.monetdb.org/bugzilla/show_bug.cgi?id=6122
https://github.com/hannesmuehleisen/MonetDBLite/issues/84
On Wed, Nov 16, 2016 at 9:39 AM, Stefan Manegold Stefan.Manegold@cwi.nl wrote:
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
participants (3)
-
Anthony Damico -
dinther -
Stefan Manegold