tuples INTERSECT vs count (... INTERSECT ...) differs #2659
Last updated: 2013-01-22 09:29:07 +0100
Date: 2010-08-21 23:39:15 +0200
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
That query returns: 677
Afterwards I'm running:
Anyone care to elaborate where the difference might come from?
MonetDB server v5.21.0 (64-bit), based on kernel v1.39.0 (64-bit oids)
Date: 2010-08-21 23:39:36 +0200
Created attachment 29
Date: 2010-08-22 10:27:43 +0200
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?
Date: 2010-08-22 17:29:45 +0200
Created attachment 30
(In reply to comment 2)
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.
Date: 2010-10-28 13:31:51 +0200
intersect all doesn't change a single thing here, does it?
Date: 2010-12-05 14:10:03 +0100
could you supply the data and dml statements?
Date: 2011-03-28 17:36:30 +0200
The Mar2011 version has been released.
Date: 2011-09-30 11:30:35 +0200
See comment 5.
Date: 2012-11-27 15:29:31 +0100
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.
Date: 2012-11-27 15:46:05 +0100
I added a test for this case. It works fine for me. (the data is included in the test)
Date: 2012-11-27 19:13:14 +0100
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.
Date: 2012-11-28 13:45:47 +0100
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=c2bd663db4ba
Date: 2013-01-22 09:29:07 +0100
Oct2012-SP3 has been released.
The text was updated successfully, but these errors were encountered: