Re: ODBC SELECT WHERE a IN ('frob') returns where a = '' too

On Tue, May 23, 2000 at 01:33:56AM -0400, Tom Lane wrote:
> Adam Haberlach <adam(at)newsnipple(dot)com> writes:
> > When I do a query of the form
> > SELECT a FROM tbl WHERE a IN ('frob');
> > where a is an indexed text field containing 'frob', 'dingus', '', and
> > NULL
> > I get a set that includes rows both where
> > a == 'frob'
> > a == ''
>
> > This happens when I use ODBC from Microsoft Access's pass-through, but
> > does not seem to happen from the psql
> > command-line. This seems to confuse my coworker who is more familiar
> > with non-Postgres databases then I.
>
> I'm confused too. Best theory I can think of is that Access is
> transforming the query into something different before sending it to the
> backend. That'd be incredibly braindead, but then this is M$ software
> we're talking about. Anyway, the first thing to do is confirm or deny
> that theory by looking at the exact query the backend is getting.
> If you can't get Access to log what it sends, restart the postmaster
> with "-d2" or higher, and see what gets logged...

Here is what actually seems to happen. Put your "Oh My GOD that is so
DUMB hats on now."

/*************/
query: declare SQL_CUR071E7D04 cursor for SELECT "rawbebugs"."bbcnum" FROM
"rawbebugs" WHERE ("bestatus" IN ('fixed' ) )

query: fetch 100 in SQL_CUR071E7D04
query: fetch 100 in SQL_CUR071E7D04

query: declare SQL_CUR07ACC5B8 cursor for SELECT "bbcnum","bestatus","pkey" FROM
"rawbebugs" WHERE "bbcnum" IS NULL OR "bbcnum" = 3665 OR "bbcnum" = 4009 OR
"bbcnum" IS NULL OR "bbcnum" IS NULL OR "bbcnum" IS NULL OR "bbcnum" = 9731 OR
"bbcnum" = 9953 OR "bbcnum" IS NULL OR "bbcnum" IS NULL

query: fetch 100 in SQL_CUR07ACC5B8
query: fetch 100 in SQL_CUR07ACC5B8
...many more times...

query: declare SQL_CUR07ACC5B8 cursor for SELECT "bbcnum","bestatus","pkey" FROM
"rawbebugs" WHERE "bbcnum" IS NULL OR "bbcnum" IS NULL OR "bbcnum" IS NULL OR
"bbcnum" IS NULL OR "bbcnum" IS NULL OR "bbcnum" IS NULL OR "bbcnum" IS NULL OR
"bbcnum" IS NULL OR "bbcnum" IS NULL OR "bbcnum" IS NULL

query: fetch 100 in SQL_CUR07ACC5B8
query: fetch 100 in SQL_CUR07ACC5B8
...many more times...

fetch 100 in SQL_CUR071E7D04
fetch 100 in SQL_CUR071E7D04

query: declare SQL_CUR07ACC5B8 cursor for SELECT "bbcnum","bestatus","pkey" FROM
"rawbebugs" WHERE "bbcnum" IS NULL OR "bbcnum" IS NULL OR "bbcnum" IS NULL OR
"bbcnum" IS NULL OR "bbcnum" IS NULL OR "bbcnum" IS NULL OR "bbcnum" = 13531 OR
"bbcnum" IS NULL OR "bbcnum" = 25464 OR "bbcnum" = 14255

query: fetch 100 in SQL_CUR07ACC5B8
query: fetch 100 in SQL_CUR07ACC5B8
...many more times...
/***************/

It looks as though MS Access is either not smart enough or believes it is too smart
to handle the joining on its own. It assumes that bbcnum (possibly since it is the
first field) is an index (a non-null and unique one, at that), and then attempts
to use it to manually join the row, doing a rather ugly set of lookups against itself.

Does this mean that Access is lamer then I thought, that I mis-configured the database,
or that we are telling ODBC clients that there is an index around here somewhere?

--
Adam Haberlach |"You have to understand that the
adam(at)newsnipple(dot)com | entire 'Net is based on people with
http://www.newsnipple.com/ | too much free time on their hands."