Hi Stefan, I filed that one as well ... Why didn't you tell me that there's a PLAN command? ;-) I've been struggling with EXPLAIN and so far I did not find it very useful, but PLAN looks promising .... Best regards, Daniel Stefan.Manegold@cwi.nl wrote:
On Thu, May 05, 2011 at 04:42:48PM +0200, Daniel Boesswetter wrote:
Hi,
speaking of optimizer bugs, I'm afraid I've got at least one more (some lines removed for readability):
sql>select count(distinct x) from a;
3
sql>select count(x) from a;
6
sql>select count(x), count(distinct x) from a;
6|6
The expected answer is "6|3".
"nice" catch ...
If everybody agrees, I'll file a bug?
yes please --- see also below ...
... indeed "embarrassing" ... :-(
Stefan
======== sql>select count(name) from tables; +------+ | L17 | +======+ | 37 | +------+ 1 tuple (3.446ms) sql>select count(distinct name) from tables; +------+ | L20 | +======+ | 31 | +------+ 1 tuple (4.475ms) sql>select count(name) , count(distinct name) from tables; +------+------+ | L21 | L22 | +======+======+ | 37 | 37 | +------+------+ 1 tuple (4.061ms) sql>PLAN select count(name) from tables; +------------------------------------------------------------------------------------------------------------------------------------------+ | rel | +==========================================================================================================================================+ | project ( | | | group by ( | | | | project ( | | | | | union ( | | | | | | project ( | | | | | | | select ( | | | | | | | | table(sys._tables) [ _tables.id as p.id, _tables.name as p.name, _tables.type as p.type, _tables.%TID% NOT NULL as p.%TID% ] | | | | | | | ) [ p.type < convert(2 from tinyint to smallint) ] | | | | | | ) [ p.id, p.name, p.type ], | | | | | | project ( | | | | | | | select ( | | | | | | | | table(tmp._tables) [ _tables.id as t.id, _tables.name as t.name, _tables.type as t.type, _tables.%TID% NOT NULL as t.%TID% ] | | | | | | | ) [ t.type < convert(2 from tinyint to smallint) ] | | | | | | ) [ t.id, t.name, t.type ] | | | | | ) [ id as tables.id, name as tables.name, type as tables.type ] | | | | ) [ tables.id, tables.name ] | | | ) [ ] [ count no nil (tables.name) as L23 ] | ^^^^^^^^^^^^ | ) [ L23 ] | +------------------------------------------------------------------------------------------------------------------------------------------+ 18 tuples (0.973ms) sql>PLAN select count(distinct name) from tables; +------------------------------------------------------------------------------------------------------------------------------------------+ | rel | +==========================================================================================================================================+ | project ( | | | group by ( | | | | project ( | | | | | union ( | | | | | | project ( | | | | | | | select ( | | | | | | | | table(sys._tables) [ _tables.id as p.id, _tables.name as p.name, _tables.type as p.type, _tables.%TID% NOT NULL as p.%TID% ] | | | | | | | ) [ p.type < convert(2 from tinyint to smallint) ] | | | | | | ) [ p.id, p.name, p.type ], | | | | | | project ( | | | | | | | select ( | | | | | | | | table(tmp._tables) [ _tables.id as t.id, _tables.name as t.name, _tables.type as t.type, _tables.%TID% NOT NULL as t.%TID% ] | | | | | | | ) [ t.type < convert(2 from tinyint to smallint) ] | | | | | | ) [ t.id, t.name, t.type ] | | | | | ) [ id as tables.id, name as tables.name, type as tables.type ] | | | | ) [ tables.id, tables.name ] | | | ) [ ] [ count unique no nil (tables.name) as L24 ] | ^^^^^^!!!!!!^^^^^^^^ | ) [ L24 ] | +------------------------------------------------------------------------------------------------------------------------------------------+ 18 tuples (0.874ms) sql>PLAN select count(name) , count(distinct name) from tables; +------------------------------------------------------------------------------------------------------------------------------------------+ | rel | +==========================================================================================================================================+ | project ( | | | group by ( | | | | project ( | | | | | union ( | | | | | | project ( | | | | | | | select ( | | | | | | | | table(sys._tables) [ _tables.id as p.id, _tables.name as p.name, _tables.type as p.type, _tables.%TID% NOT NULL as p.%TID% ] | | | | | | | ) [ p.type < convert(2 from tinyint to smallint) ] | | | | | | ) [ p.id, p.name, p.type ], | | | | | | project ( | | | | | | | select ( | | | | | | | | table(tmp._tables) [ _tables.id as t.id, _tables.name as t.name, _tables.type as t.type, _tables.%TID% NOT NULL as t.%TID% ] | | | | | | | ) [ t.type < convert(2 from tinyint to smallint) ] | | | | | | ) [ t.id, t.name, t.type ] | | | | | ) [ id as tables.id, name as tables.name, type as tables.type ] | | | | ) [ tables.id, tables.name ] | | | ) [ ] [ count no nil (tables.name) as L25, L25 as L26 ] | ^^^^^^^^^^^^ !!!!!!!!!! | ) [ L25, L26 ] | +------------------------------------------------------------------------------------------------------------------------------------------+ 18 tuples (0.351ms) ========
Daniel
Franck Routier wrote:
Hi,
Le jeudi 05 mai 2011 à 16:18 +0200, Stefan.Manegold@cwi.nl a écrit :
Hi,
please bear with us. We're also only humans with 24 hours to spend per day. ;-)
Sure Stefan, there was no intent to be rude or look demanding. I'm conscious this is a FOSS project, and a very interesting one. It's just that returning unreliable results is rather embarrassing for a database :-)
I suppose this is a bug; looks like it might be a too eager attempt to (in this case, i.e., after the join, incorrectly) exploit the uniqueness of the primary key of table c when determining the groups.
Could you --- Franck or Daniel --- please file a bug report using Daniel's detailed example?
I'll let Daniel do this as he picked it up first !
Thanks,
Franck
------------------------------------------------------------------------------ WhatsUp Gold - Download Free Network Management Software The most intuitive, comprehensive, and cost-effective network management toolset available today. Delivers lowest initial acquisition cost and overall TCO of any competing solution. http://p.sf.net/sfu/whatsupgold-sd _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
------------------------------------------------------------------------------ WhatsUp Gold - Download Free Network Management Software The most intuitive, comprehensive, and cost-effective network management toolset available today. Delivers lowest initial acquisition cost and overall TCO of any competing solution. http://p.sf.net/sfu/whatsupgold-sd _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users