Skip site navigation (1) Skip section navigation (2)

Curious Join issue causing an E_FAIL error (with odbc version 8.2.2.0)

From: Stijn Vanroye <s(dot)vanroye(at)easytowork(dot)nl>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Curious Join issue causing an E_FAIL error (with odbc version 8.2.2.0)
Date: 2006-11-10 16:08:27
Message-ID: ej28g0$1fuc$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-odbc
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

Responses

pgsql-odbc by date

Next:From: Tom LaneDate: 2006-11-10 16:21:55
Subject: Re: Curious Join issue causing an E_FAIL error (with odbc version 8.2.2.0)
Previous:From: Hiroshi InoueDate: 2006-11-10 14:53:12
Subject: Re: Serial columns and ADO

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group