Обсуждение: Curious Join issue causing an E_FAIL error (with odbc version 8.2.2.0)

Hi List,

I have experienced a strange issue today: when executing a query with a
3-level join in it I got an E_FAIL error in my client software. I did
manage to "fix" it but I still find it strange.

Suppose you have the following situation:

there are three tables A,B and C (quite dumb names I know)

and the following query:

select
<some fields>,
C.id
from
A
left join B on (A.fkB_id=B.id)
left join C on (b.fkC_id=C.id)

The above query produces the error.
With some trial and error I managed to avoid the error by ommitting the
C.id field from the select. Of course this is hardly a solution since I
need those values ;)

The next thing I tried was replace the C.id field with B.fkC_id (which
have the same value since they form the join condition). No more error...

Do note that with the specific data in the test case there where no
records in the C table matching the join condition (since this can
happen is the reason why a left join is used).

It seems that in this particular case the problem is solved, but it
still doesn't explain why the original query did run correctly with the
odbc driver version 8.1.x.

Browsing the logfiles I concluded that the ODBC did pull the data from
the back-end using the first query. I have tried with both the 8.1.4 and
8.1.5 version of postgres.

My conclusion is that there is some issue with the odbc 8.2.x tree that
doesn't allow to use the C.id field in the select. Maybe related with
the fact that there are no records matching the join in the C table.

Unfortunatly I don't have the luxury of time to pursue this issue any
further at the moment. Despite my conclusion beïng somewhat vague ;) I
post this in the hope that somebody has seen something similar or can
give a little less vague conclusion... To be honest, I am a bit afraid I
might be confronted with the same problem later on.

Sincerly,

Stijn Vanroye

www.xillion.nl
www.easytowork.nl

Stijn Vanroye <s.vanroye@easytowork.nl> writes:
> select
> <some fields>,
> C.id
> from
> A
> left join B on (A.fkB_id=B.id)
> left join C on (b.fkC_id=C.id)

> The above query produces the error.
> With some trial and error I managed to avoid the error by ommitting the
> C.id field from the select. Of course this is hardly a solution since I
> need those values ;)

> The next thing I tried was replace the C.id field with B.fkC_id (which
> have the same value since they form the join condition). No more error...

Um, well no they don't necessarily have the same value.  Since it's a
left join, C.id might read as null.

Perhaps your E_FAIL was a consequence of some bit of your code not being
prepared to cope with a NULL value?

            regards, tom lane

Tom Lane schreef:
> Stijn Vanroye <s.vanroye@easytowork.nl> writes:
>> select
>> <some fields>,
>> C.id
>> from
>> A
>> left join B on (A.fkB_id=B.id)
>> left join C on (b.fkC_id=C.id)
>
>> The above query produces the error.
>> With some trial and error I managed to avoid the error by ommitting the
>> C.id field from the select. Of course this is hardly a solution since I
>> need those values ;)
>
>> The next thing I tried was replace the C.id field with B.fkC_id (which
>> have the same value since they form the join condition). No more error...
>
> Um, well no they don't necessarily have the same value.  Since it's a
> left join, C.id might read as null.
You are right, I stand corrected (and ashamed). I think that in my
specific case and use of the data this is not a problem, but I will have
to check into that to make sure!

>
> Perhaps your E_FAIL was a consequence of some bit of your code not being
> prepared to cope with a NULL value?
I don't think that's the case. My code never came into play. I am using
ADO components in delphi, maybe there is a problem with coping with null
values. But, the code for these components is completely unchanged
troughout all my tests (actually I think it hasn't changed since the
dawn of man ;) ). In fact, all my software, as well as the data and
where conditions have been exactly the same with every test case. With
no changes to the query, code, data or where conditions the error
manifested itself with the driver version 8.2.x, while it didn't occured
when using the 8.1.x driver.


Regards,

Stijn Vanroye

www.xillion.nl
www.easytowork.nl

Stijn Vanroye <s.vanroye@easytowork.nl> writes:
> ... With no changes to the query, code, data or where conditions the error
> manifested itself with the driver version 8.2.x, while it didn't occured
> when using the 8.1.x driver.

Well, that could be a driver bug, but you'll need to put together a
self-contained test case if you want anyone to look very hard for it.

            regards, tom lane

Stijn Vanroye wrote:
> Hi List,
>
> I have experienced a strange issue today: when executing a query with a
> 3-level join in it I got an E_FAIL error in my client software. I did
> manage to "fix" it but I still find it strange.
>
> Suppose you have the following situation:
>
> there are three tables A,B and C (quite dumb names I know)
>
> and the following query:
>
> select
> <some fields>,
> C.id
> from
> A
> left join B on (A.fkB_id=B.id)
> left join C on (b.fkC_id=C.id)
>
> The above query produces the error.

Could you show me the whole code ?

regards,
Hiroshi Inoue

Tom Lane schreef:
 > Well, that could be a driver bug, but you'll need to put together a
 > self-contained test case if you want anyone to look very hard for it.
Tom, what is desirable for the people on the list in terms of a
self-containted test case? Any specific requirements conscerning the
set-up or output of the test case?

Hiroshi Inoue schreef:
> Could you show me the whole code ?

This is not a query I wrote, so I haven't looked into why everyting is
as it is. In the select are some commented lines. That's the interesting
region, as well as the related left joins. I can't post all the table
definitions, but if you have a question about a field type or such, just
ask.

select
   dbu_field.field_id,
   dbu_field.fieldname,
   dbu_field.length,
   dbu_field."precision",
   dbu_field.lookupclass_id,
   dbu_field.lookupkeyfield_id,
   dbu_field.lookupvaluefield_id,
   dbu_field.agtype_id,
   dbu_field.sql_filter,
   dbu_field.sql_code,
   lookupobject.classname as lookupobject,
   lookuptable.tablename as lookuptable,
   --original line
   --lookuptable.table_id as lookuptable_id,
   --new line
   lookupobject.table_id as lookuptable_id,
   lookupkeyfield.fieldname as lookupkeyfield,
   lookupvaluefield.fieldname as lookupvaluefield,
   agtype.description as agtype
from
   dbu_field
   left join dbu_class as lookupobject on
(dbu_field.lookupclass_id=lookupobject.class_id)
   left join dbu_table as lookuptable on
(lookupobject.table_id=lookuptable.table_id)
   left join dbu_field as lookupkeyfield on
(lookupkeyfield.field_id=dbu_field.lookupkeyfield_id)
   left join dbu_field as lookupvaluefield on
(lookupvaluefield.field_id=dbu_field.lookupvaluefield_id)
   left join dbu_agtype as agtype on (agtype.agtype_id=dbu_field.agtype_id)
where
   dbu_field.class_id=123
order by
   dbu_field.fieldorder;


Regards,

Stijn.

Stijn Vanroye wrote:
> Tom Lane schreef:
>  > Well, that could be a driver bug, but you'll need to put together a
>  > self-contained test case if you want anyone to look very hard for it.
> Tom, what is desirable for the people on the list in terms of a
> self-containted test case? Any specific requirements conscerning the
> set-up or output of the test case?
>
> Hiroshi Inoue schreef:
>> Could you show me the whole code ?
>
> This is not a query I wrote, so I haven't looked into why everyting is
> as it is. In the select are some commented lines. That's the interesting
> region, as well as the related left joins. I can't post all the table
> definitions, but if you have a question about a field type or such, just
> ask.

What I meant is the program not the query only.
Hmm could send me directly the Mylog output ?

regards,
Hiroshi Inoue

Hiroshi Inoue schreef:
> Stijn Vanroye wrote:
>> Tom Lane schreef:
>>  > Well, that could be a driver bug, but you'll need to put together a
>>  > self-contained test case if you want anyone to look very hard for it.
>> Tom, what is desirable for the people on the list in terms of a
>> self-containted test case? Any specific requirements conscerning the
>> set-up or output of the test case?
>>
>> Hiroshi Inoue schreef:
>>> Could you show me the whole code ?
>>
>> This is not a query I wrote, so I haven't looked into why everyting is
>> as it is. In the select are some commented lines. That's the
>> interesting region, as well as the related left joins. I can't post
>> all the table definitions, but if you have a question about a field
>> type or such, just ask.
>
> What I meant is the program not the query only.
> Hmm could send me directly the Mylog output ?
I would love to send you the MyLog output, only it's 23Mb. I can't
exactly say which part of it is important or not, due to lack of experience.
I will, however, upload it to one of our server and send you the link
off-list.

Kind regards,

Stijn Vanroye.

Since I have been communicating off-list with Hiroshi I am posting the
outcome of the problem here:

The cause for the behaviour had to do with a small bug in the driver.
This bug is however solved in the latest snapshot (8.2.0201).


Regards,

Stijn Vanroye.