Date: 2010-08-21 23:39:15 +0200
From: @skinkie
To: SQL devs <>
Version: 11.3.3 (Apr2011-SP1) [obsolete]
CC: @njnes, @elpetrak

Last updated: 2013-01-22 09:29:07 +0100

Comment 14749

Date: 2010-08-21 23:39:15 +0200
From: @skinkie

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US) AppleWebKit/534.3 (KHTML, like Gecko) Chrome/6.0.472.36 Safari/534.3
Build Identifier:

I'm running:
select * from kvk_extra_nieuw_tmp INTERSECT select * from kvk_extra_nieuw;

That query returns: 677

Afterwards I'm running:
select count(*) from (select * from kvk_extra_nieuw_tmp INTERSECT select * from kvk_extra_nieuw) as x;

+------+
| L5 |
+======+
| 890 |
+------+
1 tuple (151.951ms)

Anyone care to elaborate where the difference might come from?

Reproducible: Always

MonetDB server v5.21.0 (64-bit), based on kernel v1.39.0 (64-bit oids)
Not released
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved
Visit http://monetdb.cwi.nl/ for further information
Found 3.9GiB available memory, 2 available cpu cores
Configured for prefix: /opt/monetdb-testing
Libraries:
libpcre: 8.02 2010-03-19 (compiled with 8.02)
openssl: OpenSSL 1.0.0a 1 Jun 2010 (compiled with OpenSSL 1.0.0a 1 Jun 2010)
libxml2: 2.7.7 (compiled with 2.7.7)
Compiled by: skinkie@openkvk (x86_64-unknown-linux-gnu)
Compilation: gcc -Wall -Wextra -fno-strict-aliasing -g -Werror-implicit-function-declaration -Werror -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wp,-D_FORTIFY_SOURCE=2
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -IPA -m elf_x86_64

Comment 14750

Date: 2010-08-21 23:39:36 +0200
From: @skinkie

Created attachment 29
MAL trace of queries

Attached file: mal.txt (text/plain, 44030 bytes)
Description: MAL trace of queries

Comment 14751

Date: 2010-08-22 10:27:43 +0200
From: @drstmane

Could you please run both queries single threaded --- i.e., either start mserver5 with "--set gdk_nr_threads=1" or in your client session before running the queries execute "set optimizer='sequential_pipe';" --- and report the results?

If they are the same as reported before (or differently unexpected), could you please attach the traces (TRACE not EXPLAIN) for both queries run single-threaded?

In case the single threaded case works fine and as expected, could you please attach the traces (TRACE not EXPLAIN) for both queries run multi-threaded?

Thanks!

Comment 14752

Date: 2010-08-22 17:29:45 +0200
From: @skinkie

Created attachment 30
EXPLAIN trace

(In reply to comment 2)

Could you please run both queries single threaded --- i.e., either start
mserver5 with "--set gdk_nr_threads=1" or in your client session before running
the queries execute "set optimizer='sequential_pipe';" --- and report the
results?

Traced now with gdk_nr_threads=1 and readonly. Same results. Please that although the queries are the same the content is different.

I do wonder if this has anything to do with the distinct operation occuring in the right hand side of the intersect.

Attached file: trace2.txt.gz (application/x-gzip, 286709 bytes)
Description: EXPLAIN trace

Comment 15137

Date: 2010-10-28 13:31:51 +0200
From: @grobian

intersect all doesn't change a single thing here, does it?

Comment 15287

Date: 2010-12-05 14:10:03 +0100
From: @njnes

could you supply the data and dml statements?

Comment 15682

Date: 2011-03-28 17:36:30 +0200
From: @sjoerdmullender

The Mar2011 version has been released.

Comment 16361

Date: 2011-09-30 11:30:35 +0200
From: @sjoerdmullender

See comment 5.

Comment 18120

Date: 2012-11-27 15:29:31 +0100
From: @skinkie

I can't find back the original database, and neither with a simple example or real data I can make it break using Oct2012.

But with a very elaborative example I am able to break this.

select postcode, plaats from kvk intersect select postcode, gemeentenaam from adres;

397259 vs 231241

I'll contact Niels for uploading the data.

Comment 18129

Date: 2012-11-27 15:46:05 +0100
From: @elpetrak

I added a test for this case. It works fine for me. (the data is included in the test)

Comment 18163

Date: 2012-11-27 19:13:14 +0100
From: @njnes

optimizer reduced the intersect in case of the count to only work on postcodes. This will give the wrong count.

Fixed by dce optimizer fixes. Test can be changed in to 'plan' output.

Comment 18205

Date: 2012-11-28 13:45:47 +0100
From: @elpetrak

Changeset c2bd663db4ba made by Eleni Petraki petraki@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Bug #2659: tuples INTERSECT vs count (... INTERSECT ...) differs.

Comment 18363

Date: 2013-01-22 09:29:07 +0100
From: @sjoerdmullender

Oct2012-SP3 has been released.