Обсуждение: [BUGS] BUG #14754: ecpg SQL parsing error
The following bug has been logged on the website: Bug reference: 14754 Logged by: Richard Zuber Email address: zuberre@gmail.com PostgreSQL version: 9.5.7 Operating system: CentOS 7.3.1611 Description: Hello, I’ve been using ecpg as part of my automated test framework to ensure my various SQL migrations have proper syntax. I believe I have run into an error where ECPG is reporting a syntax error that does not in fact exist. Assume the following table is defined (though this should not be relevant to the issue): CREATE TABLE foo ( id SERIAL NOT NULL, name text NOT NULL, PRIMARY KEY (id) ); There are two files to be fed into ecpg (version - ecpg (PostgreSQL 9.5.7) 4.11.0) : --doesnt_work.sql EXEC SQL WITH cte AS ( INSERT INTO foo(name) VALUES ('bar') RETURNING id ) SELECT * FROM foo; --EOF The output of “doesn’t_work.sql” is: $ ecpg doesnt_work.sql doesnt_work.sql:7: ERROR: syntax error at or near ")" $ echo $? 3 Since this is a valid statement from a syntax perspective (ignoring what the statement does), I would expect no error. The following appears to work as expected: --works.sql EXEC SQL WITH cte AS ( INSERT INTO foo(name) VALUES ('bar') ) SELECT * FROM foo; --EOF $ ecpg works.sql $ echo $? 0 The difference between the two is the use of the “RETURNING” statement in the CTE. I have reviewed section 33.5 of the manual. While I took note of 33.5.3 in the manual, this statement does not actually return a result set. That said, I may be misinterpreting the documentation in this matter. I have reviewed release notes from later versions of postgres as well as the open TODOs, but have not seen anything that appears to relate to this behavior. I appreciate your attention, Richard Zuber -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
On 21 July 2017 at 15:16, <zuberre@gmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 14754 > Logged by: Richard Zuber > Email address: zuberre@gmail.com > PostgreSQL version: 9.5.7 > Operating system: CentOS 7.3.1611 > Description: > > Hello, > > I’ve been using ecpg as part of my automated test framework to ensure my > various SQL migrations have proper syntax. I believe I have run into an > error where ECPG is reporting a syntax error that does not in fact exist. > > Assume the following table is defined (though this should not be relevant to > the issue): > > CREATE TABLE foo > ( > id SERIAL NOT NULL, > name text NOT NULL, > PRIMARY KEY (id) > ); > > There are two files to be fed into ecpg (version - ecpg (PostgreSQL 9.5.7) > 4.11.0) : > > --doesnt_work.sql > EXEC SQL WITH cte AS > ( > INSERT INTO foo(name) > VALUES > ('bar') > RETURNING id > ) > SELECT * FROM foo; > --EOF > > The output of “doesn’t_work.sql” is: > > $ ecpg doesnt_work.sql > doesnt_work.sql:7: ERROR: syntax error at or near ")" > $ echo $? > 3 > > Since this is a valid statement from a syntax perspective (ignoring what the > statement does), I would expect no error. > > The following appears to work as expected: > > --works.sql > EXEC SQL WITH cte AS > ( > INSERT INTO foo(name) > VALUES > ('bar') > ) > SELECT * FROM foo; > --EOF > > $ ecpg works.sql > $ echo $? > 0 > > The difference between the two is the use of the “RETURNING” statement in > the CTE. I have reviewed section 33.5 of the manual. While I took note of > 33.5.3 in the manual, this statement does not actually return a result set. > That said, I may be misinterpreting the documentation in this matter. > > I have reviewed release notes from later versions of postgres as well as the > open TODOs, but have not seen anything that appears to relate to this > behavior. > > I appreciate your attention, > Richard Zuber > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- greg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Thanks for the report Richard, sorry it took me a while to find time to debug. > I’ve been using ecpg as part of my automated test framework to ensure > my > various SQL migrations have proper syntax. I believe I have run into > an > error where ECPG is reporting a syntax error that does not in fact > exist. I guess I agree, after all ecpg should accept everything that the server accepts, right? :) The problem is that ecpg expects every non-empty returning clause to end with an "into <variable>" sectio to store the data in C. This obviously does not make sense. I have to do more tests to see if making the into clause optional breaks other things. If you have a large base of test cases you're welcome to try it out too, once the patch is done. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
> I’ve been using ecpg as part of my automated test framework to ensure my > various SQL migrations have proper syntax. I believe I have run into an > error where ECPG is reporting a syntax error that does not in fact exist. Fixed in master, if I don't hear of any problems I'll backport in a couple of days. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs