Hi Roy,
Yes it can. The usual case would be where a sub-select returned more
than 0 or 1 rows - in that case the cardinality of the main join is
violated. In this scenario, setting noflatten would see execution
trigger a cardinality error. Presently, you would only see this with
SEjoins.
Regards,
Ian

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Roy
Hann
Sent: 22 September 2009 13:58
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] Wrong results from query flattening

A couple of us have been having an off-line conversation about the
possibilty of getting incorrect results as a result of using
query-flattening. Ingres does query flattening by default, but it can
be turned off using SET NOFLATTEN. The consequence of turning off
flattening will usually be vastly slower joins (2, 3, maybe 5 times
slower, or worse)--although there are certainly cases where
subquery joins will be faster.

I have to admit I've never knowingly encountered a case where flattening
gives incorrect results, but I've certainly seen the claim made more
that once. So now I am curious. Can flattening give incorrect results
in recognizable situations, or is this one of those superstitions that
has grown up around some ancient and long-since fixed bug(s)?

--
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.


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

Ian Kirkham wrote:

So if you write WHERE partnr = ( SELECT partnr ... ) intending the
subquery to be a scalar subquery, and it turns out it's not, flattening
causes the "=" to be silently treated as "IN"? So the result isn't
wrong exactly, it's just that a database design/declaration error goes
undetected (unless you coded only for the singleton query you were
expecting).

I can't say that is good behaviour, but I don't think I could recommend
always turning off flattening so as to be sure of never encountering
this provblem. SE-joins are hideously expensive. Maybe I could get
behind turning off flattening during testing, to provoke a detectable
error.

Actually, I now see why the person I was talking to was getting so
excited about this. If you routinely use cursors and assume you can
safely do just a single FETCH you will never know you have a problem. I
HATE cursors and will do almost anything to avoid them (until
the last possible moment). So I would never have this problem. Well done
me.

(http://www.rationalcommerce.com/imag...-mug-other.jpg)

--
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.

Hi Roy,
With appropriate uniqueness constraints in place, the flattening becomes
safe as the cardinality is then assured.
Agreed about SEjoins, I'm looking at addressing some of that to make
these checks doable on better joins.
Regards,
Ian

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Roy
Hann
Sent: 23 September 2009 09:38
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] Wrong results from query flattening

Ian Kirkham wrote:

So if you write WHERE partnr = ( SELECT partnr ... ) intending the
subquery to be a scalar subquery, and it turns out it's not, flattening
causes the "=" to be silently treated as "IN"? So the result isn't
wrong exactly, it's just that a database design/declaration error goes
undetected (unless you coded only for the singleton query you were
expecting).

I can't say that is good behaviour, but I don't think I could recommend
always turning off flattening so as to be sure of never encountering
this provblem. SE-joins are hideously expensive. Maybe I could get
behind turning off flattening during testing, to provoke a detectable
error.

Actually, I now see why the person I was talking to was getting so
excited about this. If you routinely use cursors and assume you can
safely do just a single FETCH you will never know you have a problem. I
HATE cursors and will do almost anything to avoid them (until
the last possible moment). So I would never have this problem. Well done
me.

(http://www.rationalcommerce.com/imag...-mug-other.jpg)

--
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.


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

Ian Kirkham wrote:

I don't think that is true. One can still code a non-scalar query in a
position where a scalar query is required, and Ingres won't notice it.
For example no unique constraint would prevent this bit of silliness:

select table_name from iitables
where table_reltid = ( select table_reltid from iitables
where table_reltid in ( 22,137,196 ) );

and it cheerfully returns 3 rows.

If you turn flattening off you get E_US1196 SELECT returned multiple
values.

Excellent. There has to be a better way to detect what is, after all,
just an application coding error, without crippling the server.

--
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.