From 4d3b42bcc4823361163e773807073db3bdee3e3c Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Wed, 6 Dec 2023 22:33:09 +0900 Subject: [PATCH v28 6/6] ECPG: add SQL/JSON query function tests and parser LA fix for NESTED --- src/interfaces/ecpg/preproc/parse.pl | 1 + src/interfaces/ecpg/preproc/parser.c | 11 ++ src/interfaces/ecpg/test/ecpg_schedule | 1 + .../test/expected/sql-sqljson_queryfuncs.c | 171 ++++++++++++++++++ .../expected/sql-sqljson_queryfuncs.stderr | 46 +++++ .../expected/sql-sqljson_queryfuncs.stdout | 3 + src/interfaces/ecpg/test/sql/Makefile | 1 + src/interfaces/ecpg/test/sql/meson.build | 1 + .../ecpg/test/sql/sqljson_queryfuncs.pgc | 45 +++++ 9 files changed, 280 insertions(+) create mode 100644 src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.c create mode 100644 src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.stderr create mode 100644 src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.stdout create mode 100644 src/interfaces/ecpg/test/sql/sqljson_queryfuncs.pgc diff --git a/src/interfaces/ecpg/preproc/parse.pl b/src/interfaces/ecpg/preproc/parse.pl index 7574fc3110..d27fc7c87d 100644 --- a/src/interfaces/ecpg/preproc/parse.pl +++ b/src/interfaces/ecpg/preproc/parse.pl @@ -56,6 +56,7 @@ my %replace_token = ( # or in the block my %replace_string = ( 'FORMAT_LA' => 'format', + 'NESTED_LA' => 'nested', 'NOT_LA' => 'not', 'NULLS_LA' => 'nulls', 'WITH_LA' => 'with', diff --git a/src/interfaces/ecpg/preproc/parser.c b/src/interfaces/ecpg/preproc/parser.c index 38e7acb680..47172fb780 100644 --- a/src/interfaces/ecpg/preproc/parser.c +++ b/src/interfaces/ecpg/preproc/parser.c @@ -79,6 +79,7 @@ filtered_base_yylex(void) switch (cur_token) { case FORMAT: + case NESTED: case NOT: case NULLS_P: case WITH: @@ -122,6 +123,16 @@ filtered_base_yylex(void) } break; + case NESTED: + /* Replace NESTED by NESTED_LA if it's followed by PATH */ + switch (next_token) + { + case PATH: + cur_token = NESTED_LA; + break; + } + break; + case NOT: /* Replace NOT by NOT_LA if it's followed by BETWEEN, IN, etc */ switch (next_token) diff --git a/src/interfaces/ecpg/test/ecpg_schedule b/src/interfaces/ecpg/test/ecpg_schedule index 39814a39c1..770a1411f3 100644 --- a/src/interfaces/ecpg/test/ecpg_schedule +++ b/src/interfaces/ecpg/test/ecpg_schedule @@ -51,6 +51,7 @@ test: sql/oldexec test: sql/quote test: sql/show test: sql/sqljson +test: sql/sqljson_queryfuncs test: sql/insupd test: sql/parser test: sql/prepareas diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.c b/src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.c new file mode 100644 index 0000000000..59ed7fb699 --- /dev/null +++ b/src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.c @@ -0,0 +1,171 @@ +/* Processed by ecpg (regression mode) */ +/* These include files are added by the preprocessor */ +#include +#include +#include +/* End of automatic include section */ +#define ECPGdebug(X,Y) ECPGdebug((X)+100,(Y)) + +#line 1 "sqljson_queryfuncs.pgc" +#include + + +#line 1 "sqlca.h" +#ifndef POSTGRES_SQLCA_H +#define POSTGRES_SQLCA_H + +#ifndef PGDLLIMPORT +#if defined(WIN32) || defined(__CYGWIN__) +#define PGDLLIMPORT __declspec (dllimport) +#else +#define PGDLLIMPORT +#endif /* __CYGWIN__ */ +#endif /* PGDLLIMPORT */ + +#define SQLERRMC_LEN 150 + +#ifdef __cplusplus +extern "C" +{ +#endif + +struct sqlca_t +{ + char sqlcaid[8]; + long sqlabc; + long sqlcode; + struct + { + int sqlerrml; + char sqlerrmc[SQLERRMC_LEN]; + } sqlerrm; + char sqlerrp[8]; + long sqlerrd[6]; + /* Element 0: empty */ + /* 1: OID of processed tuple if applicable */ + /* 2: number of rows processed */ + /* after an INSERT, UPDATE or */ + /* DELETE statement */ + /* 3: empty */ + /* 4: empty */ + /* 5: empty */ + char sqlwarn[8]; + /* Element 0: set to 'W' if at least one other is 'W' */ + /* 1: if 'W' at least one character string */ + /* value was truncated when it was */ + /* stored into a host variable. */ + + /* + * 2: if 'W' a (hopefully) non-fatal notice occurred + */ /* 3: empty */ + /* 4: empty */ + /* 5: empty */ + /* 6: empty */ + /* 7: empty */ + + char sqlstate[5]; +}; + +struct sqlca_t *ECPGget_sqlca(void); + +#ifndef POSTGRES_ECPG_INTERNAL +#define sqlca (*ECPGget_sqlca()) +#endif + +#ifdef __cplusplus +} +#endif + +#endif + +#line 3 "sqljson_queryfuncs.pgc" + + +#line 1 "regression.h" + + + + + + +#line 4 "sqljson_queryfuncs.pgc" + + +/* exec sql whenever sqlerror sqlprint ; */ +#line 6 "sqljson_queryfuncs.pgc" + + +int +main () +{ +/* exec sql begin declare section */ + + +#line 12 "sqljson_queryfuncs.pgc" + char json [ 1024 ] ; +/* exec sql end declare section */ +#line 13 "sqljson_queryfuncs.pgc" + + + ECPGdebug (1, stderr); + + { ECPGconnect(__LINE__, 0, "ecpg1_regression" , NULL, NULL , NULL, 0); +#line 17 "sqljson_queryfuncs.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 17 "sqljson_queryfuncs.pgc" + + { ECPGsetcommit(__LINE__, "on", NULL); +#line 18 "sqljson_queryfuncs.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 18 "sqljson_queryfuncs.pgc" + + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_exists ( jsonb '[]' , '$' )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 20 "sqljson_queryfuncs.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 20 "sqljson_queryfuncs.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_value ( jsonb '{\"a\": 123}' , '$' || '.' || 'b' default 'foo' on empty )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 23 "sqljson_queryfuncs.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 23 "sqljson_queryfuncs.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select json_query ( jsonb '{\"a\": 123}' , '$' || '.' || 'a' with wrapper )", ECPGt_EOIT, + ECPGt_char,(json),(long)1024,(long)1,(1024)*sizeof(char), + ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); +#line 26 "sqljson_queryfuncs.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 26 "sqljson_queryfuncs.pgc" + + printf("Found json=%s\n", json); + + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "select * from json_table ( jsonb 'null' , '$[*]' as p0 columns ( nested '$' as p1 columns ( nested path '$' as p11 columns ( foo int ) , nested path '$' as p12 columns ( bar int ) ) , nested path '$' as p2 columns ( nested path '$' as p21 columns ( baz int ) ) ) plan ( p1 ) ) jt", ECPGt_EOIT, ECPGt_EORT); +#line 39 "sqljson_queryfuncs.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 39 "sqljson_queryfuncs.pgc" + + // error + + { ECPGdisconnect(__LINE__, "CURRENT"); +#line 42 "sqljson_queryfuncs.pgc" + +if (sqlca.sqlcode < 0) sqlprint();} +#line 42 "sqljson_queryfuncs.pgc" + + + return 0; +} diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.stderr b/src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.stderr new file mode 100644 index 0000000000..05998a8195 --- /dev/null +++ b/src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.stderr @@ -0,0 +1,46 @@ +[NO_PID]: ECPGdebug: set to 1 +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ECPGconnect: opening database ecpg1_regression on port +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ECPGsetcommit on line 18: action "on"; connection "ecpg1_regression" +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 20: query: select json_exists ( jsonb '[]' , '$' ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 20: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 20: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 20: RESULT: t offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 23: query: select json_value ( jsonb '{"a": 123}' , '$' || '.' || 'b' default 'foo' on empty ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 23: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 23: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 23: RESULT: foo offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 26: query: select json_query ( jsonb '{"a": 123}' , '$' || '.' || 'a' with wrapper ); with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 26: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 26: correctly got 1 tuples with 1 fields +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_is_type_an_array on line 26: type (3802); C (1); array (no) +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_get_data on line 26: RESULT: [123] offset: -1; array: no +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 29: query: select * from json_table ( jsonb 'null' , '$[*]' as p0 columns ( nested '$' as p1 columns ( nested path '$' as p11 columns ( foo int ) , nested path '$' as p12 columns ( bar int ) ) , nested path '$' as p2 columns ( nested path '$' as p21 columns ( baz int ) ) ) plan ( p1 ) ) jt; with 0 parameter(s) on connection ecpg1_regression +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 29: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_check_PQresult on line 29: bad response - ERROR: invalid JSON_TABLE plan +LINE 1: ...ted path '$' as p21 columns ( baz int ) ) ) plan ( p1 ) ) jt + ^ +DETAIL: Path name mismatch: expected p0 but p1 is given. +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: raising sqlstate 42601 (sqlcode -400): invalid JSON_TABLE plan on line 29 +[NO_PID]: sqlca: code: -400, state: 42601 +SQL error: invalid JSON_TABLE plan on line 29 +[NO_PID]: ecpg_finish: connection ecpg1_regression closed +[NO_PID]: sqlca: code: 0, state: 00000 diff --git a/src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.stdout b/src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.stdout new file mode 100644 index 0000000000..e5e0613748 --- /dev/null +++ b/src/interfaces/ecpg/test/expected/sql-sqljson_queryfuncs.stdout @@ -0,0 +1,3 @@ +Found json=t +Found json=foo +Found json=[123] diff --git a/src/interfaces/ecpg/test/sql/Makefile b/src/interfaces/ecpg/test/sql/Makefile index d8213b25ce..96a0646877 100644 --- a/src/interfaces/ecpg/test/sql/Makefile +++ b/src/interfaces/ecpg/test/sql/Makefile @@ -24,6 +24,7 @@ TESTS = array array.c \ quote quote.c \ show show.c \ sqljson sqljson.c \ + sqljson_queryfuncs sqljson_queryfuncs.c \ insupd insupd.c \ twophase twophase.c \ insupd insupd.c \ diff --git a/src/interfaces/ecpg/test/sql/meson.build b/src/interfaces/ecpg/test/sql/meson.build index f4c9418abb..7229fa75c7 100644 --- a/src/interfaces/ecpg/test/sql/meson.build +++ b/src/interfaces/ecpg/test/sql/meson.build @@ -26,6 +26,7 @@ pgc_files = [ 'show', 'sqlda', 'sqljson', + 'sqljson_queryfuncs', 'twophase', ] diff --git a/src/interfaces/ecpg/test/sql/sqljson_queryfuncs.pgc b/src/interfaces/ecpg/test/sql/sqljson_queryfuncs.pgc new file mode 100644 index 0000000000..8a2b7be1d1 --- /dev/null +++ b/src/interfaces/ecpg/test/sql/sqljson_queryfuncs.pgc @@ -0,0 +1,45 @@ +#include + +EXEC SQL INCLUDE sqlca; +exec sql include ../regression; + +EXEC SQL WHENEVER SQLERROR sqlprint; + +int +main () +{ +EXEC SQL BEGIN DECLARE SECTION; + char json[1024]; +EXEC SQL END DECLARE SECTION; + + ECPGdebug (1, stderr); + + EXEC SQL CONNECT TO REGRESSDB1; + EXEC SQL SET AUTOCOMMIT = ON; + + EXEC SQL SELECT JSON_EXISTS(jsonb '[]', '$') INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER) INTO :json; + printf("Found json=%s\n", json); + + EXEC SQL SELECT * FROM JSON_TABLE(jsonb 'null', '$[*]' AS p0 + COLUMNS ( + NESTED '$' AS p1 COLUMNS ( + NESTED PATH '$' AS p11 COLUMNS ( foo int ), + NESTED PATH '$' AS p12 COLUMNS ( bar int ) + ), + NESTED PATH '$' AS p2 COLUMNS ( + NESTED PATH '$' AS p21 COLUMNS ( baz int ) + ) + ) + PLAN (p1)) jt; + // error + + EXEC SQL DISCONNECT; + + return 0; +} -- 2.35.3