Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SELECT a.col IN ( b.col FROM b ) FROM a statements with no error but no result #6121

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2016-11-16 15:54:52 +0100
From: Anthony Damico <>
To: SQL devs <>
Version: 11.23.13 (Jun2016-SP2)
CC: ajdamico, martin.van.dinther, @njnes

Last updated: 2017-02-09 14:53:25 +0100

Comment 24688

Date: 2016-11-16 15:54:52 +0100
From: Anthony Damico <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:49.0) Gecko/20100101 Firefox/49.0
Build Identifier:

using the example below, mserver window says

could not find table_one.this_column L2.L1

but mclient window returns no result and does not give an error

Reproducible: Always

Steps to Reproduce:

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

 related quaries that work without issue

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

 three examples with no proper error and no proper result

SELECT ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) AS new_column FROM table_one ;
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 ;
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 ;

Actual Results:

could not find table_one.this_column
L2.L1

in mserver window

Expected Results:

error or result?

thanks!

Comment 24697

Date: 2016-11-17 14:00:21 +0100
From: Martin van Dinther <<martin.van.dinther>>

when executing query:
SELECT ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) AS new_column FROM table_one ;
or 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 ;

on Jun2016 branch (MonetDB 5 server v11.23.14 with assertions enabled) I get assertion (on mserver5 console):

could not find table_one.this_column
L2.L1
mserver5: ../../../../dev/sql/backends/monet5/rel_bin.c:2391: rel2bin_project: Assertion `0' failed.

So related to problem as reported in 6119.

for query:
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 ;

on Jun2016 branch (MonetDB 5 server v11.23.14 with assertions enabled) I get assertion (on mserver5 console):

mserver5: ../../../../dev/sql/backends/monet5/rel_bin.c:2617: rel2bin_groupby: Assertion `0' failed.

Comment 24718

Date: 2016-11-24 17:47:29 +0100
From: MonetDB Mercurial Repository <>

Changeset 3449bb84e1fb made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=3449bb84e1fb

Changeset description:

Add test script for bug #6121

Comment 24750

Date: 2016-12-03 12:08:06 +0100
From: MonetDB Mercurial Repository <>

Changeset b72b05357cdd made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=b72b05357cdd

Changeset description:

fixes for bugs 6124, 6125 and 6121
The apply rename optimizer step has been rewriten.
Apply optimizer now create new relational operators (doesn't steal
the structures, ie safe against referenced subqueries).

Comment 24753

Date: 2016-12-03 14:58:25 +0100
From: @njnes

fixed in Dec2016 ( ie handle in-subquery within aggregate expression properly)

Comment 24995

Date: 2017-02-09 14:53:25 +0100
From: MonetDB Mercurial Repository <>

Changeset 9b7490e6a80f made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=9b7490e6a80f

Changeset description:

Update instructions have side effects.
Test sql/test/BugTracker-2016/select-in-from.Bug-6121 creates a plan
in which batcalc.min is calculated on two BATs, both of which are
appended to later on in the plan.  What you definitely don't want to
happen (and what this fixes) is that the append happens concurrently
with, or even before, the batcalc.min.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant