Thread: BUG #8893: Precompiler ECPG doesn't check Var-List in Select corrrect.
BUG #8893: Precompiler ECPG doesn't check Var-List in Select corrrect.
From
herbert.grohmann@vallourec.com
Date:
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
Re: BUG #8893: Precompiler ECPG doesn't check Var-List in Select corrrect.
From
Michael Meskes
Date:
> 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
Re: BUG #8893: Precompiler ECPG doesn't check Var-List in Select corrrect.
From
Boszormenyi Zoltan
Date:
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
Re: BUG #8893: Precompiler ECPG doesn't check Var-List in Select corrrect.
From
Michael Meskes
Date:
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