Can we see what OUTER is doing boys & girls?
A user on IDS 7.30 (Linux) asked why adding "and f.percent is NULL" to a query using Informix's old OUTER join syntax didn't filter out rows that had values — instead all rows came back with percent shown as NULL. Respondents explained that with the old OUTER syntax, conditions on the outer table are treated as part of the join, not as a post-join filter: non-matching rows are still returned from the preserved tables with NULLs in the outer columns. Workarounds given: select into a temp table and apply the IS NULL test there, or upgrade to 7.31+ and use ANSI JOIN...ON syntax so WHERE filters apply after the join. The poster accepted this.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'll be jiggered if I can!! This little problem is easy to work round. I was hoping that someone might be able to tell me what's going on so that I can avoid it in the future. 8O) IDS Linux Edition 7.30.UC7-1 & SQL 7.30.UC1. A simple query:- select p.a_id, p.p_id, f.percent from person p, person d, OUTER finance f where p.a_id = d.a_id and p.p_id = f.p_id and p.ptype = "P" and d.ptype = "D" and d.code[1,2] = "lp" ...returns... a_id p_id percent 4 7 100.000 15 28 100.000 65 118 So far, so good. The third row is a result of the OUTER. Now I add an extra condition to the query... and f.percent is NULL The query now returns... a_id p_id percent 4 7 15 28 65 118 Where did my values go? Why haven't the first two rows been excluded? What is it doing?? 8O( As I said, it's simple to work round. I simply put the results of the first query into a temporary table then apply the 'is NULL' condition to the table and... lo and behold, I get the result I expected. i.e. just the third row. Just curious, really. Can anyone enlighten me. Thanks in advance.
The first two rows have percent not null, so there will be NO MATCH for the WHERE clause in the table finance. But because of the OUTER, the two rows from the are in the result. HTH Bogdan Daryl Craig-Elliot wrote: > I'll be jiggered if I can!! > > This little problem is easy to work round. I was hoping that someone > might be able to tell me what's going on so that I can avoid it in the > future. 8O) > > IDS Linux Edition 7.30.UC7-1 & SQL 7.30.UC1. > > A simple query:- > > select > p.a_id, p.p_id, f.percent > from > person p, person d, OUTER finance f > where > p.a_id = d.a_id > and p.p_id = f.p_id > and p.ptype = "P" > and d.ptype = "D" > and d.code[1,2] = "lp" > > ...returns... > > a_id p_id percent > > 4 7 100.000 > 15 28 100.000 > 65 118 > > So far, so good. The third row is a result of the OUTER. > > Now I add an extra condition to the query... > > and f.percent is NULL > > The query now returns... > > a_id p_id percent > > 4 7 > 15 28 > 65 118 > > Where did my values go? Why haven't the first two rows been excluded? > > What is it doing?? 8O( > > As I said, it's simple to work round. I simply put the results of the > first query into a temporary table then apply the 'is NULL' condition to > the table and... lo and behold, I get the result I expected. i.e. just > the third row. > > Just curious, really. Can anyone enlighten me. > > Thanks in advance.
Just to make sure I'm getting this right... Are you saying that the first two rows are excluded by the 'NULL' condition then reincluded by the OUTER even though the explicit join exists? More perplexing is why the OUTER reincludes it with a NULL value even though NULLS are excluded! Have I got that right ? 8O) Many thanks. Daryl. Bogdan Neagu wrote: > The first two rows have percent not null, so there will be NO MATCH for the > WHERE clause in the table finance. But because of the OUTER, the two rows > from the are in the result. > > HTH > > Bogdan > > Daryl Craig-Elliot wrote: > > > I'll be jiggered if I can!! > > > > This little problem is easy to work round. I was hoping that someone > > might be able to tell me what's going on so that I can avoid it in the > > future. 8O) > > > > IDS Linux Edition 7.30.UC7-1 & SQL 7.30.UC1. > > > > A simple query:- > > > > select > > p.a_id, p.p_id, f.percent > > from > > person p, person d, OUTER finance f > > where > > p.a_id = d.a_id > > and p.p_id = f.p_id > > and p.ptype = "P" > > and d.ptype = "D" > > and d.code[1,2] = "lp" > > > > ...returns... > > > > a_id p_id percent > > > > 4 7 100.000 > > 15 28 100.000 > > 65 118 > > > > So far, so good. The third row is a result of the OUTER. > > > > Now I add an extra condition to the query... > > > > and f.percent is NULL > > > > The query now returns... > > > > a_id p_id percent > > > > 4 7 > > 15 28 > > 65 118 > > > > Where did my values go? Why haven't the first two rows been excluded? > > > > What is it doing?? 8O( > > > > As I said, it's simple to work round. I simply put the results of the > > first query into a temporary table then apply the 'is NULL' condition to > > the table and... lo and behold, I get the result I expected. i.e. just > > the third row. > > > > Just curious, really. Can anyone enlighten me. > > > > Thanks in advance.
> > and f.percent is NULL so NULLs are not excluded, not nulls are ! Bogdan Daryl Craig-Elliot wrote: > Just to make sure I'm getting this right... > > Are you saying that the first two rows are excluded by the 'NULL' condition > then reincluded by the OUTER even though the explicit join exists? > > More perplexing is why the OUTER reincludes it with a NULL value even though > NULLS are excluded! > > Have I got that right ? 8O) > > Many thanks. > > Daryl. > > Bogdan Neagu wrote: > > > The first two rows have percent not null, so there will be NO MATCH for the > > WHERE clause in the table finance. But because of the OUTER, the two rows > > from the are in the result. > > > > HTH > > > > Bogdan > > > > Daryl Craig-Elliot wrote: > > > > > I'll be jiggered if I can!! > > > > > > This little problem is easy to work round. I was hoping that someone > > > might be able to tell me what's going on so that I can avoid it in the > > > future. 8O) > > > > > > IDS Linux Edition 7.30.UC7-1 & SQL 7.30.UC1. > > > > > > A simple query:- > > > > > > select > > > p.a_id, p.p_id, f.percent > > > from > > > person p, person d, OUTER finance f > > > where > > > p.a_id = d.a_id > > > and p.p_id = f.p_id > > > and p.ptype = "P" > > > and d.ptype = "D" > > > and d.code[1,2] = "lp" > > > > > > ...returns... > > > > > > a_id p_id percent > > > > > > 4 7 100.000 > > > 15 28 100.000 > > > 65 118 > > > > > > So far, so good. The third row is a result of the OUTER. > > > > > > Now I add an extra condition to the query... > > > > > > and f.percent is NULL > > > > > > The query now returns... > > > > > > a_id p_id percent > > > > > > 4 7 > > > 15 28 > > > 65 118 > > > > > > Where did my values go? Why haven't the first two rows been excluded? > > > > > > What is it doing?? 8O( > > > > > > As I said, it's simple to work round. I simply put the results of the > > > first query into a temporary table then apply the 'is NULL' condition to > > > the table and... lo and behold, I get the result I expected. i.e. just > > > the third row. > > > > > > Just curious, really. Can anyone enlighten me. > > > > > > Thanks in advance.
Bogdan Neagu wrote: > > > and f.percent is NULL > > so NULLs are not excluded, not nulls are ! Doh!! Sorry, I was getting myself confused. 8O| But... up to that point am I understanding it right? That is, that the OUTER forces their inclusion even though the joined row exists? Daryl. > > > Bogdan > > Daryl Craig-Elliot wrote: > > > Just to make sure I'm getting this right... > > > > Are you saying that the first two rows are excluded by the 'NULL' condition > > then reincluded by the OUTER even though the explicit join exists? > > > > More perplexing is why the OUTER reincludes it with a NULL value even though > > NULLS are excluded! > > > > Have I got that right ? 8O) > > > > Many thanks. > > > > Daryl. > > > > Bogdan Neagu wrote: > > > > > The first two rows have percent not null, so there will be NO MATCH for the > > > WHERE clause in the table finance. But because of the OUTER, the two rows > > > from the are in the result. > > > > > > HTH > > > > > > Bogdan > > > > > > Daryl Craig-Elliot wrote: > > > > > > > I'll be jiggered if I can!! > > > > > > > > This little problem is easy to work round. I was hoping that someone > > > > might be able to tell me what's going on so that I can avoid it in the > > > > future. 8O) > > > > > > > > IDS Linux Edition 7.30.UC7-1 & SQL 7.30.UC1. > > > > > > > > A simple query:- > > > > > > > > select > > > > p.a_id, p.p_id, f.percent > > > > from > > > > person p, person d, OUTER finance f > > > > where > > > > p.a_id = d.a_id > > > > and p.p_id = f.p_id > > > > and p.ptype = "P" > > > > and d.ptype = "D" > > > > and d.code[1,2] = "lp" > > > > > > > > ...returns... > > > > > > > > a_id p_id percent > > > > > > > > 4 7 100.000 > > > > 15 28 100.000 > > > > 65 118 > > > > > > > > So far, so good. The third row is a result of the OUTER. > > > > > > > > Now I add an extra condition to the query... > > > > > > > > and f.percent is NULL > > > > > > > > The query now returns... > > > > > > > > a_id p_id percent > > > > > > > > 4 7 > > > > 15 28 > > > > 65 118 > > > > > > > > Where did my values go? Why haven't the first two rows been excluded? > > > > > > > > What is it doing?? 8O( > > > > > > > > As I said, it's simple to work round. I simply put the results of the > > > > first query into a temporary table then apply the 'is NULL' condition to > > > > the table and... lo and behold, I get the result I expected. i.e. just > > > > the third row. > > > > > > > > Just curious, really. Can anyone enlighten me. > > > > > > > > Thanks in advance.
Daryl Craig-Elliot wrote: > A simple query:- > > select > p.a_id, p.p_id, f.percent > from > person p, person d, OUTER finance f > where > p.a_id = d.a_id > and p.p_id = f.p_id > and p.ptype = "P" > and d.ptype = "D" > and d.code[1,2] = "lp" > > ...returns... > > a_id p_id percent > > 4 7 100.000 > 15 28 100.000 > 65 118 > > So far, so good. The third row is a result of the OUTER. > > Now I add an extra condition to the query... > > and f.percent is NULL Conceptually (and, possibly, physically), a query that includes an OUTER is executed as follows First, all references to the OUTER table are removed and the query executed. Every row of this result set *will* be reported. Then, the results are joined with the OUTER table, using all the references to the OUTER table in the original query, to retrieve data from the OUTER table. In your case, the queries would look something like 1. select p.a_id, p.p_id from person p, person d where p.a_id = d.a_id and p.ptype = "P" and d.ptype = "D" and d.code[1,2] = "lp" into temp t1; 2. select t1.*, f.percent from t1, OUTER finance f where t1.p_id = f.p_id and f.percent = NULL; HTH Rudy
Rudy Fernandes wrote: > Daryl Craig-Elliot wrote: > > > A simple query:- > > > > select > > p.a_id, p.p_id, f.percent > > from > > person p, person d, OUTER finance f > > where > > p.a_id = d.a_id > > and p.p_id = f.p_id > > and p.ptype = "P" > > and d.ptype = "D" > > and d.code[1,2] = "lp" > > > > ...returns... > > > > a_id p_id percent > > > > 4 7 100.000 > > 15 28 100.000 > > 65 118 > > > > So far, so good. The third row is a result of the OUTER. > > > > Now I add an extra condition to the query... > > > > and f.percent is NULL > > Conceptually (and, possibly, physically), a query that includes an OUTER is > executed as follows > > First, all references to the OUTER table are removed and the query > executed. Every row of this result set *will* be reported. > Then, the results are joined with the OUTER table, using all the > references to the OUTER table in the original query, to retrieve data from > the OUTER table. Thank you. I think I get it now! 8O) > > > In your case, the queries would look something like > > 1. select > p.a_id, p.p_id > from > person p, person d > where > p.a_id = d.a_id > and p.ptype = "P" > and d.ptype = "D" > and d.code[1,2] = "lp" > into temp t1; > > 2. select t1.*, f.percent > from t1, OUTER finance f > where > t1.p_id = f.p_id > and f.percent = NULL; > > HTH > Rudy
You are using the older Informix specific syntax (the newer syntax is not supported until 7.31+) so you cannot filter on the contents of the OUTER table successfully because the filter is applied BEFORE the OUTER JOIN. You have to select to a temp table then select from the temp table WHERE percent IS NULL. In the new SQL syntax you can use an ON clause to specify the OUTER JOIN conditions then the filter in the WHERE clause is specifically applied AFTER the join. But you'll have to upgrade for that. Art S. Kagel Daryl Craig-Elliot wrote: > > I'll be jiggered if I can!! > > This little problem is easy to work round. I was hoping that someone > might be able to tell me what's going on so that I can avoid it in the > future. 8O) > > IDS Linux Edition 7.30.UC7-1 & SQL 7.30.UC1. > > A simple query:- > > select > p.a_id, p.p_id, f.percent > from > person p, person d, OUTER finance f > where > p.a_id = d.a_id > and p.p_id = f.p_id > and p.ptype = "P" > and d.ptype = "D" > and d.code[1,2] = "lp" > > ...returns... > > a_id p_id percent > > 4 7 100.000 > 15 28 100.000 > 65 118 > > So far, so good. The third row is a result of the OUTER. > > Now I add an extra condition to the query... > > and f.percent is NULL > > The query now returns... > > a_id p_id percent > > 4 7 > 15 28 > 65 118 > > Where did my values go? Why haven't the first two rows been excluded? > > What is it doing?? 8O( > > As I said, it's simple to work round. I simply put the results of the > first query into a temporary table then apply the 'is NULL' condition to > the table and... lo and behold, I get the result I expected. i.e. just > the third row. > > Just curious, really. Can anyone enlighten me. > > Thanks in advance.