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:
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.
pgsql-odbc by date
|Next:||From: Tom Lane||Date: 2006-11-10 16:21:55|
|Subject: Re: Curious Join issue causing an E_FAIL error (with odbc version 220.127.116.11) |
|Previous:||From: Hiroshi Inoue||Date: 2006-11-10 14:53:12|
|Subject: Re: Serial columns and ADO|