Обсуждение: Group/Order by not in target - Was [NEW ODBC DRIVER]

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.

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!

>
> 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)