Обсуждение: BUG #8893: Precompiler ECPG doesn't check Var-List in Select corrrect.

The following bug has been logged on the website:

Bug reference:      8893
Logged by:          Herbert Grohmann
Email address:      herbert.grohmann@vallourec.com
PostgreSQL version: 9.0.15
Operating system:   Open Suse Linux 11.4
Description:

        EXEC SQL SELECT
            TO_CHAR(beginn, 'yyyy-mm-dd hh24:mi:ss'),
            TO_CHAR(ende,   'yyyy-mm-dd hh24:mi:ss'),
            tot_logisch, tot_15,
            stiche, laenge,
            anz_stangenwechsel, stoerzeit_stangenwechsel,
            stoerzeit_schicht, gueltig,
            stoerzeit_a1, stoerzeit_a2, stoerzeit_a3, stoerzeit_a4, stoerzeit_a5,
            stoerzeit_a6, stoerzeit_a7, stoerzeit_a8, stoerzeit_a9, stoerzeit_a10,
            schichtnr, meister,
            theor_planlaufzeit,
            TO_CHAR(time_1_rohr_schicht, 'yyyy-mm-dd hh24:mi:ss'),
            taktzeitverlust, merk_sammel_laeuft, zeit_sammelstoerung,
            Merker_Stangenwechsel_laeuft,
            Merker_Stoerung_laeuft,
            Merker_Handbetrieb_laeuft,
            Merker_Tavl_Auto_laeuft,
            TO_CHAR(d_stw, 'yyyy-mm-dd hh24:mi:ss'),
            TO_CHAR(d_sto, 'yyyy-mm-dd hh24:mi:ss'),
            TO_CHAR(d_han, 'yyyy-mm-dd hh24:mi:ss'),
            TO_CHAR(d_tav, 'yyyy-mm-dd hh24:mi:ss'),
            gewinn_plantakt,
            bem1, bem2, bem3, summe_plantakt, Id,
            sugru_1,sugru_2,sugru_3,sugru_4,sugru_5,sugru_6,sugru_7,sugru_8,sugru_9

stoerzeit_a11,stoerzeit_a12,stoerzeit_a13,stoerzeit_a14,stoerzeit_a15,stoerzeit_a16
            INTO :s_schichtsatz
            FROM ww_stoerung_schichten
            WHERE ende = to_timestamp(:s_schichtsatz.end, 'yyyy.mm.dd hh24:mi:ss')
        ;

Missing Komma after sugru_9 does not produce a Compiler Error! Bad Program
goes in Production.
Results ::

PgSQL-Error from<stoerd_get_schichtsatz>/<Error Read Schichtsatz>
 Err=<-201>/07002 -- /<too many arguments on line 317>
test_komma: Key=  2014-01-20 06:00:00| 2014-01-20 13:59:59
PgSQL-Error from<test_komm:: _get_schichtsatz>/<Error Read Schichtsatz>
 Err=<-201>/07002 -- /<too many arguments on line 317>
test_komma: Key=  2014-01-20 06:00:00| 2014-01-20 13:59:59
test_komma: Signal received , Stop !!!

This is a very old Error ad i hope, you can correct it.

An Update to newer Version of PostgreSQL is very problematic.

Beast regards
Herbert Grohmann

>         EXEC SQL SELECT
> ...

Could you please send an example that is compilable? At the very
least the variable definitions would be needed.

Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
Jabber: michael.meskes at gmail dot com
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL

herbert.grohmann@vallourec.com writes:
>         EXEC SQL SELECT
> ...
>             sugru_1,sugru_2,sugru_3,sugru_4,sugru_5,sugru_6,sugru_7,sugru_8,sugru_9
> stoerzeit_a11,stoerzeit_a12,stoerzeit_a13,stoerzeit_a14,stoerzeit_a15,stoerzeit_a16
> ...

> Missing Komma after sugru_9 does not produce a Compiler Error!

Unfortunately, that's legal SQL syntax: stoerzeit_a11 is taken as a column
alias for sugru_9, even without AS in front of it.

This might not be the single worst syntax choice the SQL standards
committee ever made, but it's certainly in the running for that
distinction.  Unfortunately, we're pretty much stuck with it :-( --- even
if we didn't want to follow the standard as best we can, there is a whole
lot of SQL code out there that depends on being able to omit AS.

            regards, tom lane

2014-01-20 16:36 keltezéssel, Tom Lane írta:
> herbert.grohmann@vallourec.com writes:
>>         EXEC SQL SELECT
>> ...
>>             sugru_1,sugru_2,sugru_3,sugru_4,sugru_5,sugru_6,sugru_7,sugru_8,sugru_9
>> stoerzeit_a11,stoerzeit_a12,stoerzeit_a13,stoerzeit_a14,stoerzeit_a15,stoerzeit_a16
>> ...
>> Missing Komma after sugru_9 does not produce a Compiler Error!
> Unfortunately, that's legal SQL syntax: stoerzeit_a11 is taken as a column
> alias for sugru_9, even without AS in front of it.
>
> This might not be the single worst syntax choice the SQL standards
> committee ever made, but it's certainly in the running for that
> distinction.  Unfortunately, we're pretty much stuck with it :-( --- even
> if we didn't want to follow the standard as best we can, there is a whole
> lot of SQL code out there that depends on being able to omit AS.

Still, this particular problem may be caught by the preprocessor.
The SELECT targetlist is not "*", so the number of members for both
the targetlist and the user structure (INTO :s_schichtsatz) are known.

Best regards,
Zoltán Böszörményi

Boszormenyi Zoltan <zboszor@pr.hu> writes:
> 2014-01-20 16:36 keltezéssel, Tom Lane írta:
>> Unfortunately, that's legal SQL syntax: stoerzeit_a11 is taken as a column
>> alias for sugru_9, even without AS in front of it.

> Still, this particular problem may be caught by the preprocessor.
> The SELECT targetlist is not "*", so the number of members for both
> the targetlist and the user structure (INTO :s_schichtsatz) are known.

Yeah, good point.  There was a comparable complaint a couple days ago
about plpgsql not checking its INTO target count carefully enough.
In both cases we could improve the odds of catching an error, even if
it wouldn't be perfect.

            regards, tom lane

On Mon, Jan 20, 2014 at 11:20:22PM +0100, Boszormenyi Zoltan wrote:
> Still, this particular problem may be caught by the preprocessor.
> The SELECT targetlist is not "*", so the number of members for both

Probably better looking for "*" as part of the targetlist, but yeah, that should be doable.

> the targetlist and the user structure (INTO :s_schichtsatz) are known.

However, from the top of my head I'd say it require an amount of code changes
that I would hesitate to backport. After all it's a new feature and not a
bugfix. And since Herbert indicated problems with updating his PostgreSQL
version, it won't help him much I'm afraid.

Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org
Jabber: michael.meskes at gmail dot com
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL