Thread
-
ODBC SELECT WHERE a IN ('frob') returns where a = '' too
Adam Haberlach <adam@newsnipple.com> — 2000-05-23T05:31:49Z
========================================================================== 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." -
Re: ODBC SELECT WHERE a IN ('frob') returns where a = '' too
Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-23T05:33:56Z
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 -
Re: ODBC SELECT WHERE a IN ('frob') returns where a = '' too
Adam Haberlach <adam@newsnipple.com> — 2000-05-23T06:50:22Z
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." -
RE: ODBC SELECT WHERE a IN ('frob') returns where a = '' too
Hiroshi Inoue <inoue@tpf.co.jp> — 2000-05-23T07:57:09Z
> -----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