Hi all,
I have this problem:

This sample query create with Access

SELECT figure.*, utenti.ragione_sociale
FROM figure INNER JOIN utenti ON figure.codice_figura = utenti.azienda;

via-psqlODBC reports this

ERROR:  The field being ordered by must appear in the target list'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'

Postgres executes this sql query (I find this in Psqlodbc.log file) :

SELECT figure.oid,utenti.azienda
FROM utenti,figure WHERE (figure.codice_figura = utenti.azienda ) ORDER BY figure.codice_figura

Why is there ORDER BY CLAUSE ?
Why is there only "figure.oid" instead of "figure.*" in SELECT
arguments ??????

Best regards,
 Marco Pollachini
 Sferacarta                          mailto:sferac@bo.nettuno.it



Fri, 3 Jul 1998 12:02:13 +0200
Sferacarta Software <sferac@bo.nettuno.it>
Access & Postgres
This question may be better posted on the interfaces list.

See:  http://www.insightdist.com/psqlodbc
Visit the FAQ.   It is one of the last items listed.

Sferacarta Software wrote:

> Hi all,
> I have this problem:
>
> This sample query create with Access
>
> SELECT figure.*, utenti.ragione_sociale
> FROM figure INNER JOIN utenti ON figure.codice_figura = utenti.azienda;
>
> via-psqlODBC reports this
>
> ERROR:  The field being ordered by must appear in the target list'
> STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'
>
> Postgres executes this sql query (I find this in Psqlodbc.log file) :
>
> SELECT figure.oid,utenti.azienda
> FROM utenti,figure WHERE (figure.codice_figura = utenti.azienda ) ORDER BY figure.codice_figura
>
> Why is there ORDER BY CLAUSE ?
> Why is there only "figure.oid" instead of "figure.*" in SELECT
> arguments ??????
>
> Best regards,
>  Marco Pollachini
>  Sferacarta                          mailto:sferac@bo.nettuno.it




Mon, 06 Jul 1998 16:30:19 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] Access & Postgres
The backend patch is downloadable text file, linked to our FAQ.   I think the direct URL is
http://www.insightdist.com/psqlodbc/junkfilter_patch.txt

I know the code works.   Let me know, though,  if you have trouble retrieving or applying it.   The
patch was originally 2 patches.  I just cat'ed them together.   It should apply properly.

PS:  This is the first time I have distributed the patch this way.  So could I get an affirmation from
you even if it goes smoothy.

Sferacarta Software wrote:

> Hello David,
>
> Do you remember our problem with ORDER BY not in target list ?
> Well, seems that now we can't go on without your fix.
> Please may you send me it ?  Thanks
>
> lunedï¿½, 6 luglio 98, you wrote:
>
> DH> This question may be better posted on the interfaces list.
> DH> See:  http://www.insightdist.com/psqlodbc
> DH> Visit the FAQ.   It is one of the last items listed.
> H> Sferacarta Software wrote:
>
> >> Hi all,
> >> I have this problem:
> >>
> >> This sample query create with Access
> >>
> >> SELECT figure.*, utenti.ragione_sociale
> >> FROM figure INNER JOIN utenti ON figure.codice_figura = utenti.azienda;
> >>
> >> via-psqlODBC reports this
> >>
> >> ERROR:  The field being ordered by must appear in the target list'
> >> STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'
> >>
> >> Postgres executes this sql query (I find this in Psqlodbc.log file) :
> >>
> >> SELECT figure.oid,utenti.azienda
> >> FROM utenti,figure WHERE (figure.codice_figura = utenti.azienda ) ORDER BY figure.codice_figura
> >>
> >> Why is there ORDER BY CLAUSE ?
> >> Why is there only "figure.oid" instead of "figure.*" in SELECT
> >> arguments ??????
> >>
> >> Best regards,
> >>  Marco Pollachini
> >>  Sferacarta                          mailto:sferac@bo.nettuno.it
>
> Best regards,
>  Jose'                                  mailto:sferac@bo.nettuno.it




Wed, 08 Jul 1998 12:01:01 -0400
David Hartwig <daveh@insightdist.com>
Re: [HACKERS] Access & Postgres
> http://www.insightdist.com/psqlodbc/junkfilter_patch.txt
> I know the code works.

Hi David. Do you have access to the latest cvs development tree for
Postgres? I'm seeing backend crashes from the "junkfilter" regression
test, and have been for some time. I (just this morning) applied some
patches to the tree to fix up type coersion for inserts from other
tables or columns, and this changed code in the "junkfilter" area.
However, the regression test behavior is still bad.

If you have access to the latest tree, and had time to look at this it
would be great. Otherwise, I'll start poking at it. Talk to you soon...

                   - Tom

Thu, 09 Jul 1998 04:49:06 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [INTERFACES] Re: [HACKERS] Access & Postgres
> I'm seeing backend crashes from the "junkfilter" regression test

I think I have a patch for the problem:

postgres=> select c, count(*) from test_missing_target group by 3;
ERROR:  ORDER/GROUP BY position 3 is not in target list

Previously this query provoked a core dump. Will do some regression
testing and then commit to the source tree...

                       - Tom

Thu, 09 Jul 1998 13:30:43 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [INTERFACES] Re: [HACKERS] Access & Postgres
Tom,

Yes, I do have access to the cvs tree from home.   And, I will check it out
when I get home.

I am curious though.   Did you apply the patch, mentioned below, to your
latest 6.4 tree?    This patch should have been already part of the latest
6.4.   And, to my knowledge, it was already applied completely and
successfully.

Anyway,  the intent of the patch on our web site was to allow 6.3 users to
be able to use MS Access cleanly.   Now that I think of it, though, this
version of the patch does not include the new "output" and "sql"  files
necessary for the regression test.   In any case, I should clean that up.


Thomas G. Lockhart wrote:

> > http://www.insightdist.com/psqlodbc/junkfilter_patch.txt
> > I know the code works.
>
> Hi David. Do you have access to the latest cvs development tree for
> Postgres? I'm seeing backend crashes from the "junkfilter" regression
> test, and have been for some time. I (just this morning) applied some
> patches to the tree to fix up type coersion for inserts from other
> tables or columns, and this changed code in the "junkfilter" area.
> However, the regression test behavior is still bad.
>
> If you have access to the latest tree, and had time to look at this it
> would be great. Otherwise, I'll start poking at it. Talk to you soon...
>
>                    - Tom




Thu, 09 Jul 1998 09:42:38 -0400
David Hartwig <daveh@insightdist.com>
Re: [INTERFACES] Re: [HACKERS] Access & Postgres
> I am curious though.   Did you apply the patch, mentioned below, to 
> your latest 6.4 tree?    This patch should have been already part of 
> the latest 6.4.   And, to my knowledge, it was already applied 
> completely and successfully.

I did my final tests using a fresh cvs tree two days ago just before
committing my big "patch wad". In the last two or three weeks I did have
a "merge problem" with my patches in the resjunk area, which might have
been the patches you are talking about.

> Now that I think of it, though, this version of the patch does not 
> include the new "output" and "sql"  files necessary for the regression 
> test.   In any case, I should clean that up.

I am in the process of renaming and moving the "resjunk" test to
"select_implicit". Will try to commit the changes this morning so it is
available to you.

At the moment, all regression tests (except "random") pass on my Linux
box :)

                        - Tom

Thu, 09 Jul 1998 14:25:30 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [INTERFACES] Re: [HACKERS] Access & Postgres
> > http://www.insightdist.com/psqlodbc/junkfilter_patch.txt
> > I know the code works.
> 
> Hi David. Do you have access to the latest cvs development tree for
> Postgres? I'm seeing backend crashes from the "junkfilter" regression
> test, and have been for some time. I (just this morning) applied some
> patches to the tree to fix up type coersion for inserts from other
> tables or columns, and this changed code in the "junkfilter" area.
> However, the regression test behavior is still bad.
> 
> If you have access to the latest tree, and had time to look at this it
> would be great. Otherwise, I'll start poking at it. Talk to you soon...

I have now made the junk filter only run when needed.  Perhaps this will
fix the problem.

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

Sun, 19 Jul 1998 00:23:05 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [INTERFACES] Re: [HACKERS] Access & Postgres