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.

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

Ingres Forums wrote:

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 (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Ingres Forums
Sent: 16 December 2009 14:09
To: info-ingres (AT) kettleriverconsulting (DOT) 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:
http://community.ingres.com/forum/sh...ad.php?t=11499

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

Hi Marty,

Attached are some results for Solaris. All seems well behaved FSM joins.
But without data or statistics, I'm not sure how useful the QEPs will be.

Paul

Hi Paul,

Thanks for that. If the Cart-Prod forms you'll see it without data or
stats.

This seems more and more like a Linux thing as 9.2.0 on Windows also has
no trouble with the 'Bad Version'.

Marty

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Paul
White
Sent: 16 December 2009 22:32
To: 'Ingres and related product discussion forum'
Subject: Re: [Info-Ingres] Cart-Prod or not Cart-Prod...'Why?'is
thequestion.

Hi Marty,

Attached are some results for Solaris. All seems well behaved FSM
joins.
But without data or statistics, I'm not sure how useful the QEPs will
be.

Paul