[Bug Fix] ECPG: could not use some CREATE TABLE AS syntax

Started by Higuchi, Daisukealmost 7 years ago7 messages

higuchi.daisuke@jp.fujitsu.com

almost 7 years ago

1 attachment(s)

Hi,

I found some "CREATE TABLE ... AS ... " syntaxes could not be used in ECPG.

[PROBLEM]
First, ECPG command is failed when the source code (*.pgc) has "IF NOT EXISTS".
-------------------------------------------------------------------
EXEC SQL CREATE TABLE IF NOT EXISTS test_cta AS SELECT * FROM test;
-------------------------------------------------------------------

Second, ECPG command is succeeded when the source code (*.pgc) has following embedded SQL. However, created c program has no "WITH NO DATA".
------------------------------------------------------------------
EXEC SQL CREATE TABLE test_cta AS SELECT * FROM test WITH NO DATA;
------------------------------------------------------------------

[Investigation]
In my investigation, parse.pl ignore type CreateAsStmt of gram.y and CreateAsStmt is defined in ecpg.trailer. ECPG use ecpg.trailer's CreateAsStmt. However, ecpg.trailer lacks some syntaxes.
I feel ignoring type CreateAsStmt of gram.y is strange. Seeing ecpg.trailer, it seems that ECPG wanted to output the message "CREATE TABLE AS cannot specify INTO" but is this needed now? In view of the maintenance, ECPG should use not ecpg.trailer's definition but gram.y's one.

I attached the patch for this and I will register this for next CF.

Regards,
Daisuke Higuchi

Attachments:

FIX_ECPG_CREATE_TABLE_AS_v1.patchapplication/octet-stream; name=FIX_ECPG_CREATE_TABLE_AS_v1.patchDownload

diff --git a/src/interfaces/ecpg/preproc/ecpg.addons b/src/interfaces/ecpg/preproc/ecpg.addons
index 86be504..05ce67d 100644
--- a/src/interfaces/ecpg/preproc/ecpg.addons
+++ b/src/interfaces/ecpg/preproc/ecpg.addons
@@ -343,7 +343,6 @@ ECPG: opt_hold block
 	}
 ECPG: into_clauseINTOOptTempTableName block
 					{
-						FoundInto = 1;
 						$$= cat2_str(mm_strdup("into"), $2);
 					}
 	| ecpg_into { $$ = EMPTY; }
diff --git a/src/interfaces/ecpg/preproc/ecpg.header b/src/interfaces/ecpg/preproc/ecpg.header
index 00143a7..dbccc88 100644
--- a/src/interfaces/ecpg/preproc/ecpg.header
+++ b/src/interfaces/ecpg/preproc/ecpg.header
@@ -39,7 +39,6 @@ int ecpg_internal_var = 0;
 char	*connection = NULL;
 char	*input_filename = NULL;
 
-static int	FoundInto = 0;
 static int	initializer = 0;
 static int	pacounter = 1;
 static char	pacounter_buffer[sizeof(int) * CHAR_BIT * 10 / 3]; /* a rough guess at the size we need */
diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer b/src/interfaces/ecpg/preproc/ecpg.trailer
index 60e1f53..8d040c1 100644
--- a/src/interfaces/ecpg/preproc/ecpg.trailer
+++ b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -29,15 +29,6 @@ statement: ecpgstart at stmt ';' { connection = NULL; }
 		}
 		;
 
-CreateAsStmt: CREATE OptTemp TABLE create_as_target AS {FoundInto = 0;} SelectStmt opt_with_data
-		{
-			if (FoundInto == 1)
-				mmerror(PARSE_ERROR, ET_ERROR, "CREATE TABLE AS cannot specify INTO");
-
-			$$ = cat_str(6, mm_strdup("create"), $2, mm_strdup("table"), $4, mm_strdup("as"), $7);
-		}
-		;
-
 at: AT connection_object
 		{
 			connection = $2;
diff --git a/src/interfaces/ecpg/preproc/ecpg.type b/src/interfaces/ecpg/preproc/ecpg.type
index 9497b91..d0b0e5f 100644
--- a/src/interfaces/ecpg/preproc/ecpg.type
+++ b/src/interfaces/ecpg/preproc/ecpg.type
@@ -53,7 +53,6 @@
 %type <str> coutputvariable
 %type <str> cvariable
 %type <str> db_prefix
-%type <str> CreateAsStmt
 %type <str> DeallocateStmt
 %type <str> dis_name
 %type <str> ecpg_bconst
diff --git a/src/interfaces/ecpg/preproc/parse.pl b/src/interfaces/ecpg/preproc/parse.pl
index 6dee500..ce57605 100644
--- a/src/interfaces/ecpg/preproc/parse.pl
+++ b/src/interfaces/ecpg/preproc/parse.pl
@@ -62,7 +62,6 @@ my %replace_types = (
 	# "ignore" means: do not create type and rules for this non-term-id
 	'stmtblock'          => 'ignore',
 	'stmtmulti'          => 'ignore',
-	'CreateAsStmt'       => 'ignore',
 	'DeallocateStmt'     => 'ignore',
 	'ColId'              => 'ignore',
 	'type_function_name' => 'ignore',
diff --git a/src/interfaces/ecpg/test/ecpg_schedule b/src/interfaces/ecpg/test/ecpg_schedule
index 991b8cb..b20f4e7 100644
--- a/src/interfaces/ecpg/test/ecpg_schedule
+++ b/src/interfaces/ecpg/test/ecpg_schedule
@@ -34,6 +34,7 @@ test: sql/array
 test: sql/binary
 test: sql/code100
 test: sql/copystdout
+test: sql/createtableas
 test: sql/define
 test: sql/desc
 test: sql/sqlda
diff --git a/src/interfaces/ecpg/test/expected/sql-createtableas.c b/src/interfaces/ecpg/test/expected/sql-createtableas.c
new file mode 100644
index 0000000..973b463
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-createtableas.c
@@ -0,0 +1,164 @@
+/* Processed by ecpg (regression mode) */
+/* These include files are added by the preprocessor */
+#include <ecpglib.h>
+#include <ecpgerrno.h>
+#include <sqlca.h>
+/* End of automatic include section */
+#define ECPGdebug(X,Y) ECPGdebug((X)+100,(Y))
+
+#line 1 "createtableas.pgc"
+#include <stdlib.h>
+#include <string.h>
+#include <stdlib.h>
+#include <stdio.h>
+
+
+#line 1 "regression.h"
+
+
+
+
+
+
+#line 6 "createtableas.pgc"
+
+
+/* exec sql whenever sqlerror  sqlprint ; */
+#line 8 "createtableas.pgc"
+
+
+int
+main(void)
+{
+	/* exec sql begin declare section */
+		 
+	
+#line 14 "createtableas.pgc"
+ int id ;
+/* exec sql end declare section */
+#line 15 "createtableas.pgc"
+
+
+	ECPGdebug(1, stderr);
+	{ ECPGconnect(__LINE__, 0, "ecpg1_regression" , NULL, NULL , NULL, 0); 
+#line 18 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 18 "createtableas.pgc"
+
+
+	{ ECPGsetcommit(__LINE__, "on", NULL);
+#line 20 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 20 "createtableas.pgc"
+
+	/* exec sql whenever sql_warning  sqlprint ; */
+#line 21 "createtableas.pgc"
+
+	/* exec sql whenever sqlerror  sqlprint ; */
+#line 22 "createtableas.pgc"
+
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table cta_test ( id int )", ECPGt_EOIT, ECPGt_EORT);
+#line 24 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 24 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 24 "createtableas.pgc"
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into cta_test values ( 100 )", ECPGt_EOIT, ECPGt_EORT);
+#line 25 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 25 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 25 "createtableas.pgc"
+
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table if not exists cta_test1 as select * from cta_test", ECPGt_EOIT, ECPGt_EORT);
+#line 27 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 27 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 27 "createtableas.pgc"
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select id from cta_test1", ECPGt_EOIT, 
+	ECPGt_int,&(id),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 28 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 28 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 28 "createtableas.pgc"
+
+	printf("ID = %d\n", id);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table cta_test2 as select * from cta_test with no data", ECPGt_EOIT, ECPGt_EORT);
+#line 31 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 31 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 31 "createtableas.pgc"
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select count ( id ) from cta_test2", ECPGt_EOIT, 
+	ECPGt_int,&(id),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 32 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 32 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 32 "createtableas.pgc"
+
+	printf("ID = %d\n", id);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table cta_test", ECPGt_EOIT, ECPGt_EORT);
+#line 35 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 35 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 35 "createtableas.pgc"
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table cta_test1", ECPGt_EOIT, ECPGt_EORT);
+#line 36 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 36 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 36 "createtableas.pgc"
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table cta_test2", ECPGt_EOIT, ECPGt_EORT);
+#line 37 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 37 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 37 "createtableas.pgc"
+
+	{ ECPGdisconnect(__LINE__, "ALL");
+#line 38 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 38 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 38 "createtableas.pgc"
+
+
+	return 0;
+}
diff --git a/src/interfaces/ecpg/test/expected/sql-createtableas.stderr b/src/interfaces/ecpg/test/expected/sql-createtableas.stderr
new file mode 100644
index 0000000..adb6711
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-createtableas.stderr
@@ -0,0 +1,66 @@
+[NO_PID]: ECPGdebug: set to 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGconnect: opening database ecpg1_regression on <DEFAULT> port <DEFAULT>  
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGsetcommit on line 20: action "on"; connection "ecpg1_regression"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 24: query: create table cta_test ( id int ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 24: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 24: OK: CREATE TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 25: query: insert into cta_test values ( 100 ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 25: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 25: OK: INSERT 0 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 27: query: create table if not exists cta_test1 as select * from cta_test; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 27: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 27: OK: SELECT 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 28: query: select id from cta_test1; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 28: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 28: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 28: RESULT: 100 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 31: query: create table cta_test2 as select * from cta_test with no data; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 31: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 31: OK: CREATE TABLE AS
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: query: select count ( id ) from cta_test2; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 32: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 32: RESULT: 0 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 35: query: drop table cta_test; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 35: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 35: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 36: query: drop table cta_test1; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 36: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 36: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 37: query: drop table cta_test2; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 37: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 37: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_finish: connection ecpg1_regression closed
+[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-createtableas.stdout b/src/interfaces/ecpg/test/expected/sql-createtableas.stdout
new file mode 100644
index 0000000..e27a3e9
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-createtableas.stdout
@@ -0,0 +1,2 @@
+ID = 100
+ID = 0
diff --git a/src/interfaces/ecpg/test/sql/Makefile b/src/interfaces/ecpg/test/sql/Makefile
index b7bc034..d438b2e 100644
--- a/src/interfaces/ecpg/test/sql/Makefile
+++ b/src/interfaces/ecpg/test/sql/Makefile
@@ -7,6 +7,7 @@ TESTS = array array.c \
 	binary binary.c \
 	code100 code100.c \
         copystdout copystdout.c \
+	createtableas createtableas.c \
 	define define.c \
         desc desc.c \
         sqlda sqlda.c \
diff --git a/src/interfaces/ecpg/test/sql/createtableas.pgc b/src/interfaces/ecpg/test/sql/createtableas.pgc
new file mode 100644
index 0000000..72f7077
--- /dev/null
+++ b/src/interfaces/ecpg/test/sql/createtableas.pgc
@@ -0,0 +1,41 @@
+#include <stdlib.h>
+#include <string.h>
+#include <stdlib.h>
+#include <stdio.h>
+
+exec sql include ../regression;
+
+exec sql whenever sqlerror sqlprint;
+
+int
+main(void)
+{
+	EXEC SQL BEGIN DECLARE SECTION;
+		int id;
+	EXEC SQL END DECLARE SECTION;
+
+	ECPGdebug(1, stderr);
+	EXEC SQL connect to REGRESSDB1;
+
+	EXEC SQL SET AUTOCOMMIT TO ON;
+	EXEC SQL WHENEVER SQLWARNING SQLPRINT;
+	EXEC SQL WHENEVER SQLERROR SQLPRINT;
+
+	EXEC SQL CREATE TABLE cta_test (id int);
+	EXEC SQL INSERT INTO cta_test values (100);
+
+	EXEC SQL CREATE TABLE IF NOT EXISTS cta_test1 AS SELECT * FROM cta_test;
+	EXEC SQL SELECT id INTO :id FROM cta_test1;
+	printf("ID = %d\n", id);
+
+	EXEC SQL CREATE TABLE cta_test2 AS SELECT * FROM cta_test WITH NO DATA;
+	EXEC SQL SELECT count(id) INTO :id FROM cta_test2;
+	printf("ID = %d\n", id);
+
+	EXEC SQL DROP TABLE cta_test;
+	EXEC SQL DROP TABLE cta_test1;
+	EXEC SQL DROP TABLE cta_test2;
+	EXEC SQL DISCONNECT all;
+
+	return 0;
+}

Michael Meskes

meskes@postgresql.org

almost 7 years ago

In reply to: Higuchi, Daisuke (#1)

Re: [Bug Fix] ECPG: could not use some CREATE TABLE AS syntax

Higuchi-san,

I found some "CREATE TABLE ... AS ... " syntaxes could not be used in
ECPG.
...
[Investigation]
In my investigation, parse.pl ignore type CreateAsStmt of gram.y and
CreateAsStmt is defined in ecpg.trailer. ECPG use ecpg.trailer's
CreateAsStmt. However, ecpg.trailer lacks some syntaxes.

Correct, the syntax of create as statement was changed and those
changes have not been added to ecpg.

I feel ignoring type CreateAsStmt of gram.y is strange. Seeing
ecpg.trailer, it seems that ECPG wanted to output the message "CREATE
TABLE AS cannot specify INTO" but is this needed now? In view of the
maintenance, ECPG should use not ecpg.trailer's definition but
gram.y's one.

I beg to disagree, or I don't understand. Why would ecpg's changes to
the statement be wrong nowadays?

I attached the patch for this and I will register this for next CF.

I think the patch does not work correctly. The statement
CREATE TABLE a AS SELECT * INTO test FROM a;
is accepted with your patch, but it is not accepted in current ecpg nor
is it accepted by the backend when you execute it through ecpg. The
whole change of this rule has been made to make sure this syntax is not
accepted.

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

Higuchi, Daisuke

higuchi.daisuke@jp.fujitsu.com

almost 7 years ago

In reply to: Michael Meskes (#2)

RE: [Bug Fix] ECPG: could not use some CREATE TABLE AS syntax

Hi, Meskes-san,

Thanks for your response!

I beg to disagree, or I don't understand. Why would ecpg's changes to
the statement be wrong nowadays?

I might confuse you, but it does not mean that it is wrong to reject CREATE TABLE AS ... INTO ... syntax in ECPG.

My goal is to accept syntax which is currently rejected by ECPG. To realize that, I am considering following two ways:
(a) new syntax of create as statement should be added to ECPG
(b) make ECPG to use not ecpg.trailer but gram.y in the syntax of create as statement

In (a), we need to keep similar codes in both ecpg.trailer and gram.y. Also, if the syntax of create as statement will be changed in the future, there is a possibility that it will not be reflected in ECPG like this bug. Therefore, I thought that (b) was better and created a patch. And, in order to make it the simplest code, some SQL which is rejected in current ECPG is accepted in my patch's ECPG.

The statement CREATE TABLE a AS SELECT * INTO test FROM a; is accepted
with your patch, but it is not accepted in current ecpg nor is it accepted
by the backend when you execute it through ecpg.

Indeed, CREATE TABLE a AS SELECT * INTO test FROM a; is accepted in my patch's ECPG, but the backend always reject, but which SQL should be rejected in both ECPG and the backend? Following inappropriate SQL are accepted in ECPG but rejected by the backend (I am wondering why only CREATE TABLE .. AS .. INTO is rejected and other inappropriate SQL are accepted in current ECPG. ).
- EXEC SQL delete from test where c1 = (select into c2 from test2);

From the viewpoint of compatibility, if (b) is not good, I will consider (a) solution like following:

diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -34,7 +34,14 @@ CreateAsStmt: CREATE OptTemp TABLE create_as_target AS {FoundInto = 0;} SelectSt
                        if (FoundInto == 1)
                                mmerror(PARSE_ERROR, ET_ERROR, "CREATE TABLE AS cannot specify INTO");

-                   $$ = cat_str(6, mm_strdup("create"), $2, mm_strdup("table"), $4, mm_strdup("as"), $7);
+                 $$ = cat_str(7, mm_strdup("create"), $2, mm_strdup("table"), $4, mm_strdup("as"), $7, $8);
+         }
+                |  CREATE OptTemp TABLE IF_P NOT EXISTS create_as_target AS {FoundInto = 0;} SelectStmt opt_with_data
+         {
+                 if (FoundInto == 1)
+                         mmerror(PARSE_ERROR, ET_ERROR, "CREATE TABLE AS cannot specify INTO");
+
+                 $$ = cat_str(7, mm_strdup("create"), $2, mm_strdup("table if not exists"), $7, mm_strdup("as"), $10, $11);
                }
                ;

I also want to hear your opinion. I will change my opinion flexibly.

Regards,
Daisuke, Higuchi

Michael Meskes

meskes@postgresql.org

almost 7 years ago

In reply to: Higuchi, Daisuke (#3)

Re: [Bug Fix] ECPG: could not use some CREATE TABLE AS syntax

Hi Higuchi-san,

My goal is to accept syntax which is currently rejected by ECPG. To
realize that, I am considering following two ways:
(a) new syntax of create as statement should be added to ECPG

Correct.

(b) make ECPG to use not ecpg.trailer but gram.y in the syntax of
create as statement

I don't see how this would be possible to be honest.

In (a), we need to keep similar codes in both ecpg.trailer and
gram.y. Also, if the syntax of create as statement will be changed in
the future, there is a possibility that it will not be reflected in
ECPG like this bug. Therefore, I thought that (b) was better and
created a patch. And, in order to make it the simplest code, some SQL
which is rejected in current ECPG is accepted in my patch's ECPG.

Yes, I fully understand that. However, in doing so you accept
statements that the backend later on rejects. The sole reason for the
big ecpg grammar is to prevent those cases whenever possible.

Indeed, CREATE TABLE a AS SELECT * INTO test FROM a; is accepted in
my patch's ECPG, but the backend always reject, but which SQL should
be rejected in both ECPG and the backend? Following inappropriate SQL
are accepted in ECPG but rejected by the backend (I am wondering why
only CREATE TABLE .. AS .. INTO is rejected and other inappropriate
SQL are accepted in current ECPG. ).

That does sound like a bug to me. There may cases where it is not
possible to catch an invalid syntax for one reason or another. But I
would definitely go the extra mile to make the parsers as compatible as
possible.

From the viewpoint of compatibility, if (b) is not good, I will
consider (a) solution like following:

diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer
b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -34,7 +34,14 @@ CreateAsStmt: CREATE OptTemp TABLE
create_as_target AS {FoundInto = 0;} SelectSt
if (FoundInto == 1)
mmerror(PARSE_ERROR, ET_ERROR,
"CREATE TABLE AS cannot specify INTO");

-                   $$ = cat_str(6, mm_strdup("create"), $2,
mm_strdup("table"), $4, mm_strdup("as"), $7);
+                 $$ = cat_str(7, mm_strdup("create"), $2,
mm_strdup("table"), $4, mm_strdup("as"), $7, $8);
+         }
+                |  CREATE OptTemp TABLE IF_P NOT EXISTS
create_as_target AS {FoundInto = 0;} SelectStmt opt_with_data
+         {
+                 if (FoundInto == 1)
+                         mmerror(PARSE_ERROR, ET_ERROR, "CREATE
TABLE AS cannot specify INTO");
+
+                 $$ = cat_str(7, mm_strdup("create"), $2,
mm_strdup("table if not exists"), $7, mm_strdup("as"), $10, $11);
}
;

I also want to hear your opinion. I will change my opinion flexibly.

I agree that this the way to go.

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

Higuchi, Daisuke

higuchi.daisuke@jp.fujitsu.com

almost 7 years ago

In reply to: Michael Meskes (#4)

1 attachment(s)

RE: [Bug Fix] ECPG: could not use some CREATE TABLE AS syntax

Hi, Meskes-san

Yes, I fully understand that. However, in doing so you accept
statements that the backend later on rejects. The sole reason for
the big ecpg grammar is to prevent those cases whenever possible.

Ok, I agree with you.

I also want to hear your opinion. I will change my opinion flexibly.

I agree that this the way to go.

I updated and attached the patch. As I show in previous post, this version is that "IF NOT EXISTS" keyword and variable for "WITH NO DATA" are added to ecpg.trailer.

Regards,
Daisuke, Higuchi

Attachments:

FIX_ECPG_CREATE_TABLE_AS_v2.patchapplication/octet-stream; name=FIX_ECPG_CREATE_TABLE_AS_v2.patchDownload

diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer b/src/interfaces/ecpg/preproc/ecpg.trailer
index 6755d4a..7deb969 100644
--- a/src/interfaces/ecpg/preproc/ecpg.trailer
+++ b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -34,7 +34,14 @@ CreateAsStmt: CREATE OptTemp TABLE create_as_target AS {FoundInto = 0;} SelectSt
 			if (FoundInto == 1)
 				mmerror(PARSE_ERROR, ET_ERROR, "CREATE TABLE AS cannot specify INTO");
 
-			$$ = cat_str(6, mm_strdup("create"), $2, mm_strdup("table"), $4, mm_strdup("as"), $7);
+			$$ = cat_str(7, mm_strdup("create"), $2, mm_strdup("table"), $4, mm_strdup("as"), $7, $8);
+		}
+                |  CREATE OptTemp TABLE IF_P NOT EXISTS create_as_target AS {FoundInto = 0;} SelectStmt opt_with_data
+		{
+			if (FoundInto == 1)
+				mmerror(PARSE_ERROR, ET_ERROR, "CREATE TABLE AS cannot specify INTO");
+
+			$$ = cat_str(7, mm_strdup("create"), $2, mm_strdup("table if not exists"), $7, mm_strdup("as"), $10, $11);
 		}
 		;
 
diff --git a/src/interfaces/ecpg/test/ecpg_schedule b/src/interfaces/ecpg/test/ecpg_schedule
index 81afc5d..b1d67ea 100644
--- a/src/interfaces/ecpg/test/ecpg_schedule
+++ b/src/interfaces/ecpg/test/ecpg_schedule
@@ -34,6 +34,7 @@ test: sql/array
 test: sql/binary
 test: sql/code100
 test: sql/copystdout
+test: sql/createtableas
 test: sql/define
 test: sql/desc
 test: sql/sqlda
diff --git a/src/interfaces/ecpg/test/expected/sql-createtableas.c b/src/interfaces/ecpg/test/expected/sql-createtableas.c
new file mode 100644
index 0000000..973b463
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-createtableas.c
@@ -0,0 +1,164 @@
+/* Processed by ecpg (regression mode) */
+/* These include files are added by the preprocessor */
+#include <ecpglib.h>
+#include <ecpgerrno.h>
+#include <sqlca.h>
+/* End of automatic include section */
+#define ECPGdebug(X,Y) ECPGdebug((X)+100,(Y))
+
+#line 1 "createtableas.pgc"
+#include <stdlib.h>
+#include <string.h>
+#include <stdlib.h>
+#include <stdio.h>
+
+
+#line 1 "regression.h"
+
+
+
+
+
+
+#line 6 "createtableas.pgc"
+
+
+/* exec sql whenever sqlerror  sqlprint ; */
+#line 8 "createtableas.pgc"
+
+
+int
+main(void)
+{
+	/* exec sql begin declare section */
+		 
+	
+#line 14 "createtableas.pgc"
+ int id ;
+/* exec sql end declare section */
+#line 15 "createtableas.pgc"
+
+
+	ECPGdebug(1, stderr);
+	{ ECPGconnect(__LINE__, 0, "ecpg1_regression" , NULL, NULL , NULL, 0); 
+#line 18 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 18 "createtableas.pgc"
+
+
+	{ ECPGsetcommit(__LINE__, "on", NULL);
+#line 20 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 20 "createtableas.pgc"
+
+	/* exec sql whenever sql_warning  sqlprint ; */
+#line 21 "createtableas.pgc"
+
+	/* exec sql whenever sqlerror  sqlprint ; */
+#line 22 "createtableas.pgc"
+
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table cta_test ( id int )", ECPGt_EOIT, ECPGt_EORT);
+#line 24 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 24 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 24 "createtableas.pgc"
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into cta_test values ( 100 )", ECPGt_EOIT, ECPGt_EORT);
+#line 25 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 25 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 25 "createtableas.pgc"
+
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table if not exists cta_test1 as select * from cta_test", ECPGt_EOIT, ECPGt_EORT);
+#line 27 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 27 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 27 "createtableas.pgc"
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select id from cta_test1", ECPGt_EOIT, 
+	ECPGt_int,&(id),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 28 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 28 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 28 "createtableas.pgc"
+
+	printf("ID = %d\n", id);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table cta_test2 as select * from cta_test with no data", ECPGt_EOIT, ECPGt_EORT);
+#line 31 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 31 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 31 "createtableas.pgc"
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select count ( id ) from cta_test2", ECPGt_EOIT, 
+	ECPGt_int,&(id),(long)1,(long)1,sizeof(int), 
+	ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 32 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 32 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 32 "createtableas.pgc"
+
+	printf("ID = %d\n", id);
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table cta_test", ECPGt_EOIT, ECPGt_EORT);
+#line 35 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 35 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 35 "createtableas.pgc"
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table cta_test1", ECPGt_EOIT, ECPGt_EORT);
+#line 36 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 36 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 36 "createtableas.pgc"
+
+	{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "drop table cta_test2", ECPGt_EOIT, ECPGt_EORT);
+#line 37 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 37 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 37 "createtableas.pgc"
+
+	{ ECPGdisconnect(__LINE__, "ALL");
+#line 38 "createtableas.pgc"
+
+if (sqlca.sqlwarn[0] == 'W') sqlprint();
+#line 38 "createtableas.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint();}
+#line 38 "createtableas.pgc"
+
+
+	return 0;
+}
diff --git a/src/interfaces/ecpg/test/expected/sql-createtableas.stderr b/src/interfaces/ecpg/test/expected/sql-createtableas.stderr
new file mode 100644
index 0000000..adb6711
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-createtableas.stderr
@@ -0,0 +1,66 @@
+[NO_PID]: ECPGdebug: set to 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGconnect: opening database ecpg1_regression on <DEFAULT> port <DEFAULT>  
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ECPGsetcommit on line 20: action "on"; connection "ecpg1_regression"
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 24: query: create table cta_test ( id int ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 24: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 24: OK: CREATE TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 25: query: insert into cta_test values ( 100 ); with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 25: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 25: OK: INSERT 0 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 27: query: create table if not exists cta_test1 as select * from cta_test; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 27: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 27: OK: SELECT 1
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 28: query: select id from cta_test1; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 28: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 28: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 28: RESULT: 100 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 31: query: create table cta_test2 as select * from cta_test with no data; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 31: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 31: OK: CREATE TABLE AS
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: query: select count ( id ) from cta_test2; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 32: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 32: correctly got 1 tuples with 1 fields
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_get_data on line 32: RESULT: 0 offset: -1; array: no
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 35: query: drop table cta_test; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 35: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 35: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 36: query: drop table cta_test1; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 36: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 36: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 37: query: drop table cta_test2; with 0 parameter(s) on connection ecpg1_regression
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_execute on line 37: using PQexec
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_process_output on line 37: OK: DROP TABLE
+[NO_PID]: sqlca: code: 0, state: 00000
+[NO_PID]: ecpg_finish: connection ecpg1_regression closed
+[NO_PID]: sqlca: code: 0, state: 00000
diff --git a/src/interfaces/ecpg/test/expected/sql-createtableas.stdout b/src/interfaces/ecpg/test/expected/sql-createtableas.stdout
new file mode 100644
index 0000000..e27a3e9
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-createtableas.stdout
@@ -0,0 +1,2 @@
+ID = 100
+ID = 0
diff --git a/src/interfaces/ecpg/test/sql/Makefile b/src/interfaces/ecpg/test/sql/Makefile
index c3d86ae..e067227 100644
--- a/src/interfaces/ecpg/test/sql/Makefile
+++ b/src/interfaces/ecpg/test/sql/Makefile
@@ -7,6 +7,7 @@ TESTS = array array.c \
 	binary binary.c \
 	code100 code100.c \
         copystdout copystdout.c \
+	createtableas createtableas.c \
 	define define.c \
         desc desc.c \
         sqlda sqlda.c \
diff --git a/src/interfaces/ecpg/test/sql/createtableas.pgc b/src/interfaces/ecpg/test/sql/createtableas.pgc
new file mode 100644
index 0000000..72f7077
--- /dev/null
+++ b/src/interfaces/ecpg/test/sql/createtableas.pgc
@@ -0,0 +1,41 @@
+#include <stdlib.h>
+#include <string.h>
+#include <stdlib.h>
+#include <stdio.h>
+
+exec sql include ../regression;
+
+exec sql whenever sqlerror sqlprint;
+
+int
+main(void)
+{
+	EXEC SQL BEGIN DECLARE SECTION;
+		int id;
+	EXEC SQL END DECLARE SECTION;
+
+	ECPGdebug(1, stderr);
+	EXEC SQL connect to REGRESSDB1;
+
+	EXEC SQL SET AUTOCOMMIT TO ON;
+	EXEC SQL WHENEVER SQLWARNING SQLPRINT;
+	EXEC SQL WHENEVER SQLERROR SQLPRINT;
+
+	EXEC SQL CREATE TABLE cta_test (id int);
+	EXEC SQL INSERT INTO cta_test values (100);
+
+	EXEC SQL CREATE TABLE IF NOT EXISTS cta_test1 AS SELECT * FROM cta_test;
+	EXEC SQL SELECT id INTO :id FROM cta_test1;
+	printf("ID = %d\n", id);
+
+	EXEC SQL CREATE TABLE cta_test2 AS SELECT * FROM cta_test WITH NO DATA;
+	EXEC SQL SELECT count(id) INTO :id FROM cta_test2;
+	printf("ID = %d\n", id);
+
+	EXEC SQL DROP TABLE cta_test;
+	EXEC SQL DROP TABLE cta_test1;
+	EXEC SQL DROP TABLE cta_test2;
+	EXEC SQL DISCONNECT all;
+
+	return 0;
+}

Michael Meskes

meskes@postgresql.org

almost 7 years ago

In reply to: Higuchi, Daisuke (#5)

Re: [Bug Fix] ECPG: could not use some CREATE TABLE AS syntax

Hi Higuchi-san,

I updated and attached the patch. As I show in previous post, this
version is that "IF NOT EXISTS" keyword and variable for "WITH NO
DATA" are added to ecpg.trailer.

Thank you, committed.

Higuchi, Daisuke

higuchi.daisuke@jp.fujitsu.com

almost 7 years ago

In reply to: Michael Meskes (#6)

RE: [Bug Fix] ECPG: could not use some CREATE TABLE AS syntax

Hi, Meskes-san

Thank you, committed.

Thank you!
By the way, I have found another bugs which are related to ECPG , so I will post that later.

Regards,
Daisuke, Higuchi