how to structure queries like SELECT a.col IN ( b.col FROM b ) FROM a ?

older
Unable to install//access external...

Anthony Damico

16 Nov 2016 16 Nov '16

11:35 a.m.

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:'.

Attachments:

attachment.html (text/html — 1.9 KB)

Show replies by date

dinther

16 Nov 16 Nov

1:32 p.m.

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

Anthony Damico

2:11 p.m.

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

dinther

21 Nov 21 Nov

8:06 p.m.

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

Stefan Manegold

16 Nov 16 Nov

2:19 p.m.

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

Stefan Manegold

2:11 p.m.

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

Anthony Damico

2:26 p.m.

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

Stefan Manegold

2:39 p.m.

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

Anthony Damico

3:14 p.m.

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

3031

Age (days ago)

3036

Last active (days ago)

List overview

Download

8 comments

3 participants

participants (3)

Anthony Damico
dinther
Stefan Manegold

how to structure queries like SELECT a.col IN ( b.col FROM b ) FROM a ?

tags

participants (3)