count(*) in subquery results in 1 for query without results

[monetdb-team]

Date: 2012-02-15 15:59:49 +0100
From: @skinkie
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2012-03-16 14:56:53 +0100

Comment 16889

Date: 2012-02-15 15:59:49 +0100
From: @skinkie

User-Agent: Mozilla/5.0 (X11; Linux i686) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.11 Safari/535.19
Build Identifier:

I am trying to select a count in a subquery. This subquery seems to result in 0 rows. Though the count seems to operate on a row having 'null' values.

Reproducible: Always

Actual Results:

Crash:
select dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder, userstopcodebegin, istimingstop,
(select * from test as counter where
counter.dataownercode = test.dataownercode and
counter.lineplanningnumber = test.lineplanningnumber and
counter.journeypatterncode = test.journeypatterncode and
counter.timinglinkorder < test.timinglinkorder and
counter.userstopcodebegin = test.userstopcodebegin) as passagesequencenumber
from test order by dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder limit 20;

could not find (null).productformulatype
L22.L22
test.dataownercode
test.lineplanningnumber
test.journeypatterncode
test.timinglinkorder
test.userstopcodebegin
test.istimingstop
test.%TID%
test.dataownercode
test.lineplanningnumber
test.journeypatterncode
test.timinglinkorder
test.userstopcodebegin
test.istimingstop
mserver5: rel_bin.c:1695: rel2bin_project: Assertion `0' failed.
Aborted

Crashes in:

mserver5: rel_select.c:603: rel_project: Assertion `exps_card(rel->exps) <= rel->card' failed.

select dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder, userstopcodebegin, istimingstop,
(select count(*) from test as counter where
counter.dataownercode = test.dataownercode and
counter.lineplanningnumber = test.lineplanningnumber and
counter.journeypatterncode = test.journeypatterncode and
counter.timinglinkorder < test.timinglinkorder and
counter.userstopcodebegin = test.userstopcodebegin
group by dataownercode, lineplanningnumber, journeypatterncode) as passagesequencenumber
from test order by dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder limit 20;

Works but count is 1:
select dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder, userstopcodebegin, istimingstop,
(select count(*) from test as counter where
counter.dataownercode = test.dataownercode and
counter.lineplanningnumber = test.lineplanningnumber and
counter.journeypatterncode = test.journeypatterncode and
counter.timinglinkorder < test.timinglinkorder and
counter.userstopcodebegin = test.userstopcodebegin) as passagesequencenumber
from test order by dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder limit 20;

CREATE TABLE "sys"."test" (
"version" DECIMAL(2) NOT NULL,
"implicit" BOOLEAN NOT NULL,
"dataownercode" VARCHAR(10) NOT NULL,
"lineplanningnumber" VARCHAR(10) NOT NULL,
"journeypatterncode" VARCHAR(10) NOT NULL,
"timinglinkorder" DECIMAL(3) NOT NULL,
"userstopcodebegin" VARCHAR(10) NOT NULL,
"userstopcodeend" VARCHAR(10) NOT NULL,
"confinrelcode" VARCHAR(10) NOT NULL,
"destcode" VARCHAR(10) NOT NULL,
"istimingstop" BOOLEAN NOT NULL,
"displaypublicline" VARCHAR(4),
"productformulatype" VARCHAR(4)
);

1,true,CXX,A001,0,0,40000010,40004015,santro,A00100998,true,,34
1,true,CXX,A001,0,1,40004015,40004021,santro,A00100998,false,,34
1,true,CXX,A001,0,2,40004021,40002570,santro,A00100998,false,,34
1,true,CXX,A001,0,3,40002570,40002550,santro,A00100998,false,,34
1,true,CXX,A001,0,4,40002550,40002590,santro,A00100998,false,,34
1,true,CXX,A001,0,5,40002590,40002610,santro,A00100998,false,,34
1,true,CXX,A001,0,6,40002610,40002630,santro,A00100998,false,,34
1,true,CXX,A001,0,7,40002630,40002690,santro,A00100998,false,,34
1,true,CXX,A001,0,8,40002690,40002770,santro,A00100998,false,,34
1,true,CXX,A001,0,9,40002770,40009591,santro,A00100998,false,,34

Comment 16997

Date: 2012-02-26 10:59:18 +0100
From: @njnes

Changeset 7e4c8b3f36c6 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=7e4c8b3f36c6

Changeset description:

in case of correlated groupby we need to add the outer projection's
to the groupby and aggr expressions.
Fixes bug #3011.

Comment 17097

Date: 2012-03-16 14:56:53 +0100
From: @grobian

Released in Dec2011-SP2