11.7 errors for prep stmt which work in 11.5
Hi,
I have an error scenario which occurs on IDS 11.7 with a nested join statement
using a parameter in the query.
This is the query which fails on 11.7 with -254 but works on 11.5:
select first 1 1
from a
inner join b
inner join c on c.cf = b.bf
on b.bf = a.af and 1 = ?
I have included:
* A simple database schema with the 3 tables needed to reproduce the problem.
* The output of the test program when run against 11.5 and 11.7
* The source code of the program which reproduces the problem (25 lines).
Any help, or a reference to this problem already being encountered by someone
would be helpful. Our product has many queries like this sample one, which
have only started failing since an upgrade to 11.7.
Brgds,
Zoran
This is the database schema for a simple database named bug_ids117_db:
create table a(af int);
create table b(bf int);
create table c(cf int);
This is the output of the test when run against an 11.5 and 11.7 databases:
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
$ esql -VIBM Informix CSDK Version 3.70, IBM Informix-ESQL Version 3.70.UC4DE
Software Serial Number AAA#B000000
$ esql -o bug_ids117 bug_ids117.ec
$ echo "IDS 11.5 TEST"; INFORMIXSERVER=zids01 ./bug_ids117IDS 11.5 TEST
bug rows: 0
test rows: 0
$ echo "IDS 11.7 TEST"; INFORMIXSERVER=zids02 ./bug_ids117IDS 11.7 TEST
bug select error -254 [line: 14]
test rows: 0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This is the esql/c program, 25 lines total:
#include <stdio.h>
#include <sqlca.h>
int main(int argc, char **argv)
{
$long iVal = 1, oVal = 0;
$connect to 'bug_ids117_db';
if (0 != sqlca.sqlcode) printf("connect error %d [line: %d]\\
", sqlca.sqlcode,
__LINE__);
$select first 1 1 into :oVal from a inner join b inner join c on c.cf = b.bf
on b.bf = a.af and 1 = :iVal;
if (0 != sqlca.sqlcode && 100 != sqlca.sqlcode)
printf("bug select error %d [line: %d]\\
", sqlca.sqlcode, __LINE__);
else
printf("bug rows: %d\\
", oVal);
$select count(*) into :oVal from a;
if (0 != sqlca.sqlcode && 100 != sqlca.sqlcode)
printf("test select error %d\\
", sqlca.sqlcode);
else
printf("test rows: %d\\
", oVal);
return 0;
}
It works for me in 11.70:
Database selected.
> select dbinfo('version','full') from sysmaster:sysdual;
(constant)
IBM Informix Dynamic Server Version 11.70.FC3
1 row(s) retrieved.
> create table a(one int);
Table created.
> insert into a values (1);
1 row(s) inserted.
> insert into a values (2);
1 row(s) inserted.
> create table b(one int, two int );
Table created.
> insert into b values (1,1);
1 row(s) inserted.
> insert into b values(1,2);
1 row(s) inserted.
> insert into a values (3);
1 row(s) inserted.
> insert into b values (3,1);
1 row(s) inserted.
> create table c(one int, two int);
Table created.
> insert into c values (1,1);
1 row(s) inserted.
> insert into c values (2,1);
1 row(s) inserted.
> select * from a left outer join b left outer join c on b.one=c.one ona.one=b.one;
one one two one two
1 1 1 1 1
1 1 2 1 1
2
3 3 1
4 row(s) retrieved.
> select first 1 1 from a left outer join b left outer join c onb.one=c.one on a.one=b.one;
(constant)
1
1 row(s) retrieved.
>
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Fri, Mar 9, 2012 at 10:27 AM, ZORAN NASKOV <zoran@rrtechnologies.net>wrote:
> Hi,
> I have an error scenario which occurs on IDS 11.7 with a nested join
> statement
> using a parameter in the query.
>
> This is the query which fails on 11.7 with -254 but works on 11.5:
> select first 1 1
> from a>
> inner join b
>
> inner join c on c.cf = b.bf
>
> on b.bf = a.af and 1 = ?
>
> I have included:
> * A simple database schema with the 3 tables needed to reproduce the
> problem.
> * The output of the test program when run against 11.5 and 11.7
> * The source code of the program which reproduces the problem (25 lines).
>
> Any help, or a reference to this problem already being encountered by
> someone
> would be helpful. Our product has many queries like this sample one, which
> have only started failing since an upgrade to 11.7.
>
> Brgds,
> Zoran
>
> This is the database schema for a simple database named bug_ids117_db:
> create table a(af int);
> create table b(bf int);
> create table c(cf int);>
> This is the output of the test when run against an 11.5 and 11.7 databases:
> vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
> $ esql -V> IBM Informix CSDK Version 3.70, IBM Informix-ESQL Version 3.70.UC4DE
> Software Serial Number AAA#B000000
>
> $ esql -o bug_ids117 bug_ids117.ec>
> $ echo "IDS 11.5 TEST"; INFORMIXSERVER=zids01 ./bug_ids117> IDS 11.5 TEST
> bug rows: 0
> test rows: 0
>
> $ echo "IDS 11.7 TEST"; INFORMIXSERVER=zids02 ./bug_ids117> IDS 11.7 TEST
> bug select error -254 [line: 14]
> test rows: 0
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> This is the esql/c program, 25 lines total:
>
> #include <stdio.h>
> #include <sqlca.h>
>
> int main(int argc, char **argv)
> {
> $long iVal = 1, oVal = 0;
> $connect to 'bug_ids117_db';
>
> if (0 != sqlca.sqlcode) printf("connect error %d [line: %d]\\
",
> sqlca.sqlcode,
> __LINE__);
>
> $select first 1 1 into :oVal from a inner join b inner join c on c.cf =
> b.bf
> on b.bf = a.af and 1 = :iVal;
> if (0 != sqlca.sqlcode && 100 != sqlca.sqlcode)
>
> printf("bug select error %d [line: %d]\\
", sqlca.sqlcode, __LINE__);
> else
>
> printf("bug rows: %d\\
", oVal);
>
> $select count(*) into :oVal from a;
> if (0 != sqlca.sqlcode && 100 != sqlca.sqlcode)
>
> printf("test select error %d\\
", sqlca.sqlcode);
> else
>
> printf("test rows: %d\\
", oVal);
>
> return 0;
> }
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e8fbeac6a9004bad1da8c
for posterity sake:
Hi Art,
That is strange isnt is, as when I use onstat g sql for the session, the
server has the statement with the 1 = ? in its memory. I also had it using
a.af = ? and it still fails the same way.
I had this test with a prepared statement from a string, and then using a
cursor against the statement. Failed the same way in 11.7, and the onstat g
sql still shows the 1 = ? in the statement.
The way it does work is if I put the x = ? section in a where
clause,
however that doesnt solve my problem product installations already compiled
with the sql form which fails under 11.7
In any case, definitely a bug, and thank you for confirming it.
Thx,
Zoran
--------------------------------------------------------------------------------
From: Art Kagel
Sent: Friday, March 09, 2012 12:10 PM
To: Zoran Naskov
Subject: Re: 11.7 errors for prep stmt which work in 11.5 [26503]
OK, I tested in dbaccess and it works as I typed it, but I didn't have the
weird 'and 1 = ?' filter. What is that supposed to be doing? Are you passing
in '1' for the replaceable when you want a record and '0' when you don't? It
makes little sense to me. Trying the ESQL/C anyway...
OK, I figured out what's happening. The SQL parser is eliminating the "and 1 =
?" (or "and 1 = :iVal") in your query. I tried the following code where I
created the query in a string and prepared it. If I execute the prepared
statement without the "USING :iVal" clause is succeeds but returns no rows. If
I include the USING clause it fails, as your version does, with -254. This is
a bug. Call IBM and open a case. I would send them my code for testing:
/* ---- Start test program tstbug.ec ---- */
#include <stdlib.h>
#include <stdio.h>
#include <unistd.h>
int main(int argc, char **argv)
{
$int iVal = 1, oVal = 0;
$string stmt[400];
if (argc != 2) { printf("Usage: %s 0|1\\
", argv[0]); exit(1);}
$connect to 'art';
if (0 != sqlca.sqlcode) printf("connect error %d [line: %d]\\
",
sqlca.sqlcode,
__LINE__);
sprintf( stmt, "select first 1 1 as one from a inner join b inner join c on
c.one = b.one on b.one = a.one and 1 = ?" );
// $select first 1 1 as one into :oVal from a inner join b inner join c on
c.one = b.one on b.one = a.one and 1 = :iVal;
$prepare st from :stmt;
if (sqlca.sqlcode < 0) { printf( "Prepare error: %d.\\
", sqlca.sqlcode );
exit(1);}
if (argv[1][0] == '1') {
$execute st INTO :oVal USING :iVal;
} else {
$execute st INTO :oVal;
}
if (0 != sqlca.sqlcode && 100 != sqlca.sqlcode)
printf("bug select error %d [line: %d]\\
", sqlca.sqlcode, __LINE__);
else
printf("bug rows: %d\\
", oVal);
$select count(*) into :oVal from a;
if (0 != sqlca.sqlcode && 100 != sqlca.sqlcode)
printf("test select error %d\\
", sqlca.sqlcode);
else
printf("test rows: %d\\
", oVal);
return 0;
}
/* ---- End test program ---- */
Compile:
esql -o tstbug tstbug.ec
Test table schema:
create database art;
CREATE TABLE a (
one INTEGER
);
CREATE TABLE b (
one INTEGER,
two INTEGER
);
CREATE TABLE c (
one INTEGER,
two INTEGER
) ;
insert into a values (1);
insert into a values (2);
insert into b values (1,1);
insert into b values(1,2);
insert into a values (3);
insert into b values (3,1);
insert into c values (1,1);
insert into c values (2,1);
Test runs:
tstbug 0
bug rows: 0
test rows: 3
tstbug 1
bug select error -254 [line: 31]
test rows: 3
dbaccess art - <<EOF
select first 1 1 as one from a inner join b inner join c on c.one = b.one onb.one = a.one and 1 = 1;
EOF
Database selected.
one
1
1 row(s) retrieved.
Database closed.
Art
On Fri, Mar 9, 2012 at 11:30 AM, Zoran Naskov:
Hi Art,
Thanks for looking at this...
The problem is with an ESQL/C client executing the query and using a
variable to pass in the value. SPs don't seem to have the problem, only
the ESQL/C clients.
Are you able to compile the test program I included in the article and see
if it fails on your instance?
Thx,
Zoran