[Info-Ingres] Cart-Prod or not Cart-Prod...'Why?' is the question.

Hi All,



Having recently had a veritable orgy of upgrading several installations
from 9.1.x to 9.2.0 I was surprised to find that a query which had been
taking about twenty seconds on the old version was now not finishing and
(mercifully) failed after producing about 30G of workfile before
exhausting the disk.



The query in question is actually flawed, a quick rewrite solved the
problem...but I'm seeing similar things elsewhere...

The 'Bad version' of the query produces a Cart-Prod as the top node in
the hash function aggregate when used in 9.2.0 but in version 9.1.x
produced either a FSM or Hash join. The 'Good version' behaves correctly
in all versions.



I've attached a simple test case...Just do this:

createdb bowtest

sql bowtest < copy.in

test_this.sh



And check if Cart-Prods are found. The scripts has both the 'Good' and
'Bad' versions of the query.



So far I get Cart-Prods on II 9.2.0 (a64.lnx/143)NPTL + p13556 and II
9.0.4 (a64.lnx/105)NPTL + p12707. But the 'Bad Version' behaves itself
on II 9.1.2 (a64.lnx/100)NPTL + p13390, II 9.1.1 (a64.lnx/103)NPTL +
p13001 and II 9.1.1 (a64.lnx/103)NPTL + p13140.



Martin Bowes



This part of mail contained an attachment with prohibited file name:

MIME type: application/octet-stream
File name: test_this.sh
File size: 1.68 kB


The attachment was removed by Kerio MailServer 6.7.2
at secure1.fndtn.com.

[dejan]

Can you please attach needed file(s) at some "paste" site? I recommend excellent codepad . There is also ingres private pastebin - collaborative debugging tool which is commonly used by IRC ( irc://irc.freenode.org/ingres ) people.

Ingres Forums wrote:

> Can you please attach needed file(s) at some "paste" site? I recommend
> excellent 'codepad' (http://codepad.org) . There is also 'ingres private
> pastebin - collaborative debugging tool' (http://ingres.pastebin.com)
> which is commonly used by IRC ( irc://irc.freenode.org/ingres ) people.

On the subject of pastebin.com, make sure you remember it is .com. I
recently made the fatal mistake of thinking it was in .org. That takes
you to an identical-looking site that hosts some extremely aggressive
and destructive malware which defeated my virus checker (ZoneAlarm). I
am still recovering from it.

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

OK,

The script (test_this.sh) is loaded as:
http://ingres.pastebin.com/m69f785f0


The copy.in is loaded as: http://ingres.pastebin.com/d38dd4e4

Marty
-----Original Message-----
From: info-ingres-bounces@kettleriverconsulting.com
[mailto:info-ingres-bounces@kettleriverconsulting.com] On Behalf Of
Ingres Forums
Sent: 16 December 2009 14:09
To: info-ingres@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Cart-Prod or not Cart-Prod...'Why?' is
thequestion.


Can you please attach needed file(s) at some "paste" site? I recommend
excellent 'codepad' (http://codepad.org) . There is also 'ingres private
pastebin - collaborative debugging tool' (http://ingres.pastebin.com)
which is commonly used by IRC ( irc://irc.freenode.org/ingres ) people.


--
dejan
------------------------------------------------------------------------
dejan's Profile:
http://community.ingres.com/forum/me...p?userid=13077
View this thread:
[Info-Ingres] Cart-Prod or not Cart-Prod...'Why?' is the question.

_______________________________________________
Info-Ingres mailing list
Info-Ingres@kettleriverconsulting.com
http://ext-cando.kettleriverconsulti...fo/info-ingres