Bug in ODBC?

[Knox]

Hi,
I think there's a bug in an ODBC driver (Advantage ODBC 9.0.0.1). Here's
the SQL:

SELECT TRDATA.ACCT, repolog.Acct, TRDATA.STATUS
FROM TRDATA LEFT JOIN repolog ON TRDATA.ACCT = repolog.Acct
WHERE (((repolog.Acct) Is Null) AND ((TRDATA.STATUS)="C"));

Here's the answer it gives:

TRDATA.Acct repolog.Acct STATUS
528296 C
B96723 C
256697 P
H156137 C

There's no way a "P" status should show up. I've done things like rebuild
indexes on the tables, try multiple computers, etc.

If this were SQL server, I think there'd be execution plans where I could
study the underlying steps to try to further debug this, but what do you do
for Access queries? Or am I missing something else? Any suggestions
welcomed.

TIA,

Knox North

 

[Stefan Hoffmann]

hi Knox,

I think there's a bug in an ODBC driver (Advantage ODBC 9.0.0.1).

What does the vendor say about it?

mfG
--> stefan <--

 

[Knox]

I've posted in their support forums, but have not yet received a reply.
It's a free download, so it's not like I have a support contract. I'm
looking for any way to narrow the problem down. Right now, I'm just
guessing. For all I know, the ODBC driver returns all the data to Microsoft
Jet, and it's up to Jet to combine it and the bug is in Jet. Or there's
something else I don't understand.

Knox

 

[Knox]

I realized that normally these drivers are pretty reliable. However, this
is the first time that I've done a left join from a ODBC table to a regular
access table.

What I did to get around this issue was create a query that selected just
what I wanted from the ODBC table (TRDATA) and then use a second query to do
the left join.

Normally, the optimizer analyzes all the queries and you would still have
the bug. However, I made the query from the ODBC table to be a passthrough
query, so the optimizer couldn't work on it.

This made the bug go away, although probably made the performance worse.

Knox