pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedGroup/Order by not in target - Was [NEW ODBC DRIVER]
4+ messages / 4 participants
[nested] [flat]
* Group/Order by not in target - Was [NEW ODBC DRIVER]
@ 1998-05-11 19:20 David Hartwig <daveh@insightdist.com>
1998-05-15 13:34 ` Re: [INTERFACES] Group/Order by not in target - Was [NEW ODBC DRIVER] Jose' Soares Da Silva <sferac@bo.nettuno.it>
0 siblings, 1 reply; 4+ messages in thread
From: David Hartwig @ 1998-05-11 19:20 UTC (permalink / raw)
To: Chris Osborn <fozztexx@talia.hq.vxn.com>; pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-interfaces
I suspect that it is only going to be the MS Access 97 users that are
going to suffer from this weakness in the backend. I believe Access is
trying to optimize somehow by breaking a single multi-join statement into
multiple statements. To do this Access must be joining on the client
side based on a relative row position rather than the specified join
columns.
Until the problem is resolved in the backend, the workaround is to
explicitly include the missing attributes in the query. In my
experience, the missing attributes are usually from one or more sides of
any join clauses. But, because Access is not showing the actual
statements it is sending to the backend, you will have to guess the
attributes until the query succeeds. (You could also look at the log
file)
It is not very difficult to produce this problem in MS Access 97; I
expect my users to beat me up pretty good on this issue. Thus, I plan to
look into making the fix in the backend myself. Conceptually it does not
seem too difficult.
1. Add a hidden attribute to the target node structure.
2. Modify the parser/analyzer to add any attributes in the GROUP/ORDER BY
clause that are missing from the target list, to the target list with the
hidden attribute set.
3. Strip the hidden nodes from the target list projection of the query.
4. Add the feature to HAVING clause?
Any, hints, comments, or objections?
Chris Osborn wrote:
> So what can I do to sort in Access '97? I doubt the backend will
> be getting changed anytime soon.
>
> On May. 09 98, 17:12 PDT, "Byron Nikolaidis" <byronn@insightdist.com>
> wrote:
>
> > Access insists on using order by clauses like that, even though
> > the driver returns information saying it can't support it!
>
> > Postgres simply can't handle order by clauses without the fields
> > also being in the target.
>
> > The error you see is a legitimate error coming from the backend.
>
> > When the backend can handle those kinds of order by clauses, the
> > error will stop happening.
Attachments:
vcard.vcf (text/x-vcard, 201B) download
begin: vcard
fn: David Hartwig
n: Hartwig;David
email;internet: daveh@insightdist.com
x-mozilla-cpt: ;2
x-mozilla-html: TRUE
version: 2.1
end: vcard
* Re: [INTERFACES] Group/Order by not in target - Was [NEW ODBC DRIVER]
1998-05-11 19:20 Group/Order by not in target - Was [NEW ODBC DRIVER] David Hartwig <daveh@insightdist.com>
@ 1998-05-15 13:34 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-05-15 13:51 ` Re: [HACKERS] Re: [INTERFACES] Group/Order by not in target - Was [NEW ODBC DRIVER] Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Jose' Soares Da Silva @ 1998-05-15 13:34 UTC (permalink / raw)
To: David Hartwig <daveh@insightdist.com>; +Cc: Chris Osborn <fozztexx@talia.hq.vxn.com>; pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-interfaces
On Mon, 11 May 1998, David Hartwig wrote:
> It is not very difficult to produce this problem in MS Access 97; I
> expect my users to beat me up pretty good on this issue. Thus, I plan to
> look into making the fix in the backend myself. Conceptually it does not
> seem too difficult.
>
> 1. Add a hidden attribute to the target node structure.
>
> 2. Modify the parser/analyzer to add any attributes in the GROUP/ORDER BY
> clause that are missing from the target list, to the target list with the
> hidden attribute set.
This would be a great enhancement!
SQL92 specifies that columns in the ORDER BY must appear in the
SELECT clause, but this limitation has no sense, indeed many databases
already implement this enhancement.
Go for it!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [HACKERS] Re: [INTERFACES] Group/Order by not in target - Was [NEW ODBC DRIVER]
1998-05-11 19:20 Group/Order by not in target - Was [NEW ODBC DRIVER] David Hartwig <daveh@insightdist.com>
1998-05-15 13:34 ` Re: [INTERFACES] Group/Order by not in target - Was [NEW ODBC DRIVER] Jose' Soares Da Silva <sferac@bo.nettuno.it>
@ 1998-05-15 13:51 ` Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 1998-05-15 13:51 UTC (permalink / raw)
To: sferac@bo.nettuno.it; +Cc: hackers@postgreSQL.org
>
> On Mon, 11 May 1998, David Hartwig wrote:
>
>
> > It is not very difficult to produce this problem in MS Access 97; I
> > expect my users to beat me up pretty good on this issue. Thus, I plan to
> > look into making the fix in the backend myself. Conceptually it does not
> > seem too difficult.
> >
> > 1. Add a hidden attribute to the target node structure.
> >
> > 2. Modify the parser/analyzer to add any attributes in the GROUP/ORDER BY
> > clause that are missing from the target list, to the target list with the
> > hidden attribute set.
> This would be a great enhancement!
> SQL92 specifies that columns in the ORDER BY must appear in the
> SELECT clause, but this limitation has no sense, indeed many databases
> already implement this enhancement.
> Go for it!
There already is code in the backend for Junk fields to be removed. Not
sure what it does, though.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [INTERFACES] Group/Order by not in target - Was [NEW ODBC DRIVER]
@ 1998-05-12 08:01 Tony Cowderoy <tony@nthfen.demon.co.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Tony Cowderoy @ 1998-05-12 08:01 UTC (permalink / raw)
To: daveh@insightdist.com; fozztexx@talia.hq.vxn.com; pgsql-hackers@postgresql.org; pgsql-interfaces
I have hit the Access 97 problem of ORDER BY not in target list with a
simple, one-table query. The problem does not seem to have anything to do
with joins.
I have logged the SQL sent to the back end with the CommLog option in the
238 driver (very useful, thanks Byron). When using ORDER BY, Access 97
first sends a query to retrieve just the key(s) and then sends another to
get the required data. Hence, for the first query unless the fields used
in ORDER BY are key fields they are not in the target list. Nice one MS!
Nothing like making your software do the obvious thing, is there?
There is a work around. First create your query the usual way (point and
click if you like). Next, display the SQL. Finally, convert it into a
PASSTHROUGH query, so that the backend receives your SQL as written. This
worked for my very simple test, but I have not checked it out for more
complex queries.
BTW, Access 2.0 does not seem to have this problem, so why this strange
behaviour has been introduced into 97 I cannot imagine.
Tony Cowderoy
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1998-05-15 13:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-05-11 19:20 Group/Order by not in target - Was [NEW ODBC DRIVER] David Hartwig <daveh@insightdist.com>
1998-05-15 13:34 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-05-15 13:51 ` Bruce Momjian <maillist@candle.pha.pa.us>
1998-05-12 08:01 Re: [INTERFACES] Group/Order by not in target - Was [NEW ODBC DRIVER] Tony Cowderoy <tony@nthfen.demon.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox