Обсуждение: ODBC SELECT WHERE a IN ('frob') returns where a = '' too

==========================================================================
POSTGRESQL BUG REPORT TEMPLATE
==========================================================================

Your name               : Adam Haberlach
Your email address      : adam@be.com


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)  :Linux version 2.2.2

  PostgreSQL version (example: PostgreSQL-6.5.1):   PostgreSQL-7.0

  Compiler used (example:  gcc 2.8.0)           : gcc version 2.7.2.3


Please enter a FULL description of your problem:
------------------------------------------------

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.


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

Adam Haberlach <adam@newsnipple.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...

            regards, tom lane

On Tue, May 23, 2000 at 01:33:56AM -0400, Tom Lane wrote:
> Adam Haberlach <adam@newsnipple.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@newsnipple.com        |  entire 'Net is based on people with
http://www.newsnipple.com/ |  too much free time on their hands."

> -----Original Message-----
> From: pgsql-bugs-owner@hub.org [mailto:pgsql-bugs-owner@hub.org]On
> Behalf Of Adam Haberlach
>
> On Tue, May 23, 2000 at 01:33:56AM -0400, Tom Lane wrote:
> > Adam Haberlach <adam@newsnipple.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' ) )
>
.
.

This seems to be the behavior of DAO-Jet with declare/fetch option
of psqlodbc driver.
Is this really a result of Access's *pass-through* query ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp