Hi Marty,

If you look at the transform, it was deemed invalid if either column was
nullable as there could be a NULL which would be IN the set but which
would be obscured by the transform to a LOJ. In fact, we ought to do the
transform if either column is NOT NULL which is slightly less
restrictive. However, to my mind this is somewhat of a grey area as the
NULL should not be joinable in other contexts.

Regards,

Ian



________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 06 October 2009 14:57
To: Ingres and related product discussion forum
Subject: [Info-Ingres] nullable fields and query transformation



Hi All,



We have a query....(see attached as well)

update pt_status set status_logged_screening = 0, last_updated = 'now'

where pt_id not in (

select candidate_id from screening_mailing where date_replied is not
null)

and status_logged_screening = 1;



In 2.6 the query was tranformed into a left outer join, but in 9.1.1 and
9.2.0 the query plan uses the more expensive SE join syntax and as a
consequence the query takes 10 times longer to execute.



After some investigation I have found that the problem appears to be
related to the nullability of the candidate_id field in
screening_mailing. If I alter the table to have candidate_id non
nullable then the left outer join transformation is used.



Does anyone have any idea why this should be so?



Martin Bowes

Hi Ian,



In this case the fields being joined are integer4's and not longs,
furthermore one of the fields is not nullable.



I'm inclined to raise an issue on this one, what are your thoughts?



Marty



From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Ian
Kirkham
Sent: 06 October 2009 15:40
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] nullable fields and query transformation



Hi Marty,

If you look at the transform, it was deemed invalid if either column was
nullable as there could be a NULL which would be IN the set but which
would be obscured by the transform to a LOJ. In fact, we ought to do the
transform if either column is NOT NULL which is slightly less
restrictive. However, to my mind this is somewhat of a grey area as the
NULL should not be joinable in other contexts.

Regards,

Ian



________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 06 October 2009 14:57
To: Ingres and related product discussion forum
Subject: [Info-Ingres] nullable fields and query transformation



Hi All,



We have a query....(see attached as well)

update pt_status set status_logged_screening = 0, last_updated = 'now'

where pt_id not in (

select candidate_id from screening_mailing where date_replied is not
null)

and status_logged_screening = 1;



In 2.6 the query was tranformed into a left outer join, but in 9.1.1 and
9.2.0 the query plan uses the more expensive SE join syntax and as a
consequence the query takes 10 times longer to execute.



After some investigation I have found that the problem appears to be
related to the nullability of the candidate_id field in
screening_mailing. If I alter the table to have candidate_id non
nullable then the left outer join transformation is used.



Does anyone have any idea why this should be so?



Martin Bowes

Ian Kirkham wrote:

It's hard to confidently say anything about nulls because the logic has
never be properly worked out. Furthermore SQL treats them
inconsistently depending on context; sometimes they are taken to mean
some kind of "missing" and other times they are taken to mean some
kind of "doesn't apply".

A rule of thumb that has never let me down is that null is just never
joinable, ever ever ever. (Expecting to join on one or more nulls is
not much different than expecting WHERE random() = random() to be true,
predictable and correct.)

--
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 Marty,

As part of scalar sub-queries the transform mentioned will be relaxed as
described but if you can't wait raise a bug.

Regards,

Ian



________________________________

From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 07 October 2009 10:30
To: Ingres and related product discussion forum
Cc: Ian Kirkham
Subject: RE: [Info-Ingres] nullable fields and query transformation



Hi Ian,



In this case the fields being joined are integer4's and not longs,
furthermore one of the fields is not nullable.



I'm inclined to raise an issue on this one, what are your thoughts?



Marty



From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Ian
Kirkham
Sent: 06 October 2009 15:40
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] nullable fields and query transformation



Hi Marty,

If you look at the transform, it was deemed invalid if either column was
nullable as there could be a NULL which would be IN the set but which
would be obscured by the transform to a LOJ. In fact, we ought to do the
transform if either column is NOT NULL which is slightly less
restrictive. However, to my mind this is somewhat of a grey area as the
NULL should not be joinable in other contexts.

Regards,

Ian



________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 06 October 2009 14:57
To: Ingres and related product discussion forum
Subject: [Info-Ingres] nullable fields and query transformation



Hi All,



We have a query....(see attached as well)

update pt_status set status_logged_screening = 0, last_updated = 'now'

where pt_id not in (

select candidate_id from screening_mailing where date_replied is not
null)

and status_logged_screening = 1;



In 2.6 the query was tranformed into a left outer join, but in 9.1.1 and
9.2.0 the query plan uses the more expensive SE join syntax and as a
consequence the query takes 10 times longer to execute.



After some investigation I have found that the problem appears to be
related to the nullability of the candidate_id field in
screening_mailing. If I alter the table to have candidate_id non
nullable then the left outer join transformation is used.



Does anyone have any idea why this should be so?



Martin Bowes