informix-community

Open full view…

SQL Syntax error with NVL() and SQL parameter

sebflaesch
Fri, 07 Jun 2019 11:19:34 GMT

Hi all, We may have found a bug in the SQL parser when combining NVL() with a sub-select, and an SQL parameter. Depending on where the SQL parameter is used we get the syntax error. Maybe this is expected but we wonder. Tested with ESQL/C: $ esql -V IBM Informix CSDK Version 4.50, IBM Informix-ESQL Version 4.50.FC1 $ dbaccess -V DB-Access Version 14.10.FC1DE With this program: #include <stdlib.h> #include <stdio.h> #include <signal.h> #include <string.h> void check_sqlcode(const char *msg) { printf(">> [%s]: sqlcode=%d\n", msg, sqlca.sqlcode); if (sqlca.sqlcode != 0 && sqlca.sqlcode != 100) exit(1); } int main(int argc, char ** argv) { EXEC SQL BEGIN DECLARE SECTION; int pk, pv; EXEC SQL END DECLARE SECTION; EXEC SQL DATABASE test1; check_sqlcode("DATABASE test1"); EXEC SQL CREATE TEMP TABLE tt1 ( k INT ); check_sqlcode("CREATE TEMP TABLE tt1"); EXEC SQL INSERT INTO tt1 VALUES ( 101 ); check_sqlcode("INSERT INTO tt1"); EXEC SQL CREATE TEMP TABLE tt2 ( k INT, v INT ); check_sqlcode("CREATE TEMP TABLE tt2"); EXEC SQL INSERT INTO tt2 VALUES ( 201, 0 ); check_sqlcode("INSERT INTO tt2"); pk = 201; pv = 5; EXEC SQL UPDATE tt2 SET v = (NVL(( SELECT COUNT(*) FROM tt1 WHERE tt1.k = :pk) * 5, 0)) WHERE k = :pk; check_sqlcode("UPDATE tt2 (works)"); EXEC SQL UPDATE tt2 SET v = (NVL(( SELECT COUNT(*) FROM tt1 WHERE tt1.k = :pk), 0) * :pv) WHERE k = :pk; check_sqlcode("UPDATE tt2 (works)"); EXEC SQL UPDATE tt2 SET v = (NVL(( SELECT COUNT(*) FROM tt1 WHERE tt1.k = :pk) * :pv, 0)) WHERE k = :pk; check_sqlcode("UPDATE tt2 (fails)"); return 0; } We get following result: $ esql -o test003.bin test003.ec && ./test003.bin >> [DATABASE test1]: sqlcode=0 >> [CREATE TEMP TABLE tt1]: sqlcode=0 >> [INSERT INTO tt1]: sqlcode=0 >> [CREATE TEMP TABLE tt2]: sqlcode=0 >> [INSERT INTO tt2]: sqlcode=0 >> [UPDATE tt2 (works)]: sqlcode=0 >> [UPDATE tt2 (works)]: sqlcode=0 >> [UPDATE tt2 (fails)]: sqlcode=-201 Please can someone from the dev team confirm and provide a bug id so we can track this issue? Thanks Seb

andreasl
Fri, 07 Jun 2019 14:05:19 GMT

Hi Seb, reproduces nicely - entering a defect. Any particular urgency? Problem seems to be with first argument of NVL being a multiplication with one factor being a host variable. Simplified repro: --- $ int hv = 5; $ database sysmaster; $ create temp table t (c int); $ update t set c = nvl(c * 5, 0); /* works */ $ update t set c = nvl(c * :hv, 0); /* -201 */ --- Can I suggest going through regular support channel next time? BR, Andreas