Bug 6121 - SELECT a.col IN ( b.col FROM b ) FROM a statements with no error but no result
Summary: SELECT a.col IN ( b.col FROM b ) FROM a statements with no error but no result
Status: RESOLVED FIXED
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: 11.23.13 (Jun2016-SP2)
Hardware: All Windows
: Normal normal
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-11-16 15:54 CET by Anthony Damico
Modified: 2017-02-09 14:53 CET (History)
3 users (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Anthony Damico 2016-11-16 15:54:52 CET
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 1 Martin van Dinther cwiconfidential 2016-11-17 14:00:21 CET
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 2 MonetDB Mercurial Repository cwiconfidential 2016-11-24 17:47:29 CET
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://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3449bb84e1fb

Changeset description:

	Add test script for bug 6121
Comment 3 MonetDB Mercurial Repository cwiconfidential 2016-12-03 12:08:06 CET
Changeset b72b05357cdd, made by Niels Nes <niels@cwi.nl> in the MonetDB repo, refers to this bug.

For complete details, see http://dev.monetdb.org/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 4 Niels Nes cwiconfidential 2016-12-03 14:58:25 CET
fixed in Dec2016 ( ie handle in-subquery within aggregate expression properly)
Comment 5 MonetDB Mercurial Repository cwiconfidential 2017-02-09 14:53:25 CET
Changeset 9b7490e6a80f, made by Sjoerd Mullender <sjoerd@acm.org> in the MonetDB repo, refers to this bug.

For complete details, see http://dev.monetdb.org/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.