Re: wrong query result
On Mar 17, 8:33 am, Jonathan Leffler <jleff...@earthlink.net> wrote:
> SaltTan wrote:
> > Update:
> > Actually with no bind variable query does not work too.
> > And the query without where clause in subquery works fine.
>
> >> 10.00xC5, 10.00xC6
> >> A query with left join and "in" subquery was prepared and executed
> >> with a bind variable. The query returns some rows.
> >> Then the same query was executed with another bind variable. At this
> >> time the query returns no rows.
> >> If I change "left join" to "outer", the query works.
> >> If I change "in" to "exists", the query works too.
>
> >> Test:
> >> SELECT b.tabid, b.tabname, c.grantee, e.username
> >> FROM systables b, (systabauth AS c LEFT JOIN sysusers AS e ON
> >> e.username = c.grantee)
> >> WHERE c.tabid = b.tabid
> >> AND b.tabid IN (SELECT a.tabid FROM systables a WHERE a.tabid < 10)>
> >> prepare;
>
> >> open;
> >> 9 rows returned
> >> close;
>
> >> open;
> >> 0 rows returned ---- !!!!!!!!!!!!!!
> >> close;
>
> >> unprepare;
> >> prepare;
>
> >> open;
> >> 9 rows returned
> >> close;
>
> Can we see the exact code, please? In particular, you are not showing
> any bind variables in the query. And I'd like to see the outer notation
> that you are using.
>
> Note that ANSI outer joins *do* have different semantics from Informix
> outer joins -- you cannot readily switch between the two and always get
> the same result, particularly if there is a filter condition on rows in
> the outer-join tables.
>
> You don't need to provide the data in systables, but it would be helpful
> to have the contents of systabauth and sysusers, at least as far as you
> think is relevant.
>
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleff...@earthlink.net, jleff...@us.ibm.com
> Guardian of DBD::Informix v2005.02 --http://dbi.perl.org/
Actually the Baan query with this problem have a bind variable. And I
was trying to reproduce that query. Later I have found that the same
query without bind variables does not work too. I've forgotten to
revise my message, sorry.
The contents of systabauth and sysusers is not relevant, this query
returns 9 rows - select for public on system tables.
This is the exact code from Borland Delphi:
procedure TForm1.bugClick(Sender: TObject);
begin
IfxSQL3.SQL.Text := 'SELECT b.tabid, b.tabname, c.grantee, e.username
'+
'FROM systables b, (systabauth AS c LEFT JOIN sysusers AS e ON
e.username = c.grantee) '+
'WHERE c.tabid = b.tabid '+
'AND b.tabid IN (SELECT a.tabid FROM systables a WHERE a.tabid <
10 )';
IfxSQL3.Prepare;
IfxSQL3.Open;
ShowMessage(booltostr(IfxSQL3.EOF)); ---- It shows 0 (EOF == False)
IfxSQL3.close;
IfxSQL3.Open;
ShowMessage(booltostr(IfxSQL3.EOF)); ---- It shows -1 (EOF == True)
end;
SaltTan wrote:
> On Mar 17, 8:33 am, Jonathan Leffler <jleff...@earthlink.net> wrote:
>> SaltTan wrote:
>>> Update:
>>> Actually with no bind variable query does not work too.
>>> And the query without where clause in subquery works fine.
>>>> 10.00xC5, 10.00xC6
>>>> A query with left join and "in" subquery was prepared and executed
>>>> with a bind variable. The query returns some rows.
>>>> Then the same query was executed with another bind variable. At this
>>>> time the query returns no rows.
>>>> If I change "left join" to "outer", the query works.
>>>> If I change "in" to "exists", the query works too.
>>>> Test:
>>>> SELECT b.tabid, b.tabname, c.grantee, e.username
>>>> FROM systables b, (systabauth AS c LEFT JOIN sysusers AS e ON
>>>> e.username = c.grantee)
>>>> WHERE c.tabid = b.tabid
>>>> AND b.tabid IN (SELECT a.tabid FROM systables a WHERE a.tabid < 10)>>>> prepare;
>>>> open;
>>>> 9 rows returned
>>>> close;
>>>> open;
>>>> 0 rows returned ---- !!!!!!!!!!!!!!
>>>> close;
>>>> unprepare;
>>>> prepare;
>>>> open;
>>>> 9 rows returned
>>>> close;
>> Can we see the exact code, please? In particular, you are not showing
>> any bind variables in the query. And I'd like to see the outer notation
>> that you are using.
>>
>> Note that ANSI outer joins *do* have different semantics from Informix
>> outer joins -- you cannot readily switch between the two and always get
>> the same result, particularly if there is a filter condition on rows in
>> the outer-join tables.
>>
>> You don't need to provide the data in systables, but it would be helpful
>> to have the contents of systabauth and sysusers, at least as far as you
>> think is relevant.
>
> Actually the Baan query with this problem have a bind variable. And I
> was trying to reproduce that query. Later I have found that the same
> query without bind variables does not work too. I've forgotten to
> revise my message, sorry.
>
> The contents of systabauth and sysusers is not relevant, this query
> returns 9 rows - select for public on system tables.
You have a regular join between systables and the result of an outer
join of systabauth and sysusers; how do you deduce that the contents of
systabauth and sysusers is irrelevant? Actually, given that it is the
system catalog, the contents of systabauth for tabid values less than 10
probably is fixed. However, there could be all sorts of stuff in the
sysusers table. For example, I don't know whether you've granted public
any access to the database, which makes it hard to know what the outer
join is going to be doing.
> This is the exact code from Borland Delphi:
>
> procedure TForm1.bugClick(Sender: TObject);
> begin
> IfxSQL3.SQL.Text := 'SELECT b.tabid, b.tabname, c.grantee, e.username
> '+
> 'FROM systables b, (systabauth AS c LEFT JOIN sysusers AS e ON
> e.username = c.grantee) '+
> 'WHERE c.tabid = b.tabid '+
> 'AND b.tabid IN (SELECT a.tabid FROM systables a WHERE a.tabid <
> 10 )';
Thank you - this is one of the two queries I asked about, and the less
interesting of the two since it was given in your question. I was
really interested in the Informix-notation outer join; I need to see why
you think that it should produce the same result as the ISO-notation
outer join.
> IfxSQL3.Prepare;
> IfxSQL3.Open;
> ShowMessage(booltostr(IfxSQL3.EOF)); ---- It shows 0 (EOF == False)
> IfxSQL3.close;
>
> IfxSQL3.Open;
> ShowMessage(booltostr(IfxSQL3.EOF)); ---- It shows -1 (EOF == True)
If my understanding of what I see is correct, you are saying that when a
statement is used the first time, it gives one answer, and when used a
second time, it gives a different answer?
Assuming nothing else has gone changing the database in the interim,
there would be a bug somewhere in the system if that is correct. If
anything changed, we need to understand what has changed.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
On Mar 20, 7:42 am, Jonathan Leffler <jleff...@earthlink.net> wrote:
> SaltTan wrote:
> > On Mar 17, 8:33 am, Jonathan Leffler <jleff...@earthlink.net> wrote:
> >> SaltTan wrote:
> >>> Update:
> >>> Actually with no bind variable query does not work too.
> >>> And the query without where clause in subquery works fine.
> >>>> 10.00xC5, 10.00xC6
> >>>> A query with left join and "in" subquery was prepared and executed
> >>>> with a bind variable. The query returns some rows.
> >>>> Then the same query was executed with another bind variable. At this
> >>>> time the query returns no rows.
> >>>> If I change "left join" to "outer", the query works.
> >>>> If I change "in" to "exists", the query works too.
> >>>> Test:
> >>>> SELECT b.tabid, b.tabname, c.grantee, e.username
> >>>> FROM systables b, (systabauth AS c LEFT JOIN sysusers AS e ON
> >>>> e.username = c.grantee)
> >>>> WHERE c.tabid = b.tabid
> >>>> AND b.tabid IN (SELECT a.tabid FROM systables a WHERE a.tabid < 10)> >>>> prepare;
> >>>> open;
> >>>> 9 rows returned
> >>>> close;
> >>>> open;
> >>>> 0 rows returned ---- !!!!!!!!!!!!!!
> >>>> close;
> >>>> unprepare;
> >>>> prepare;
> >>>> open;
> >>>> 9 rows returned
> >>>> close;
> >> Can we see the exact code, please? In particular, you are not showing
> >> any bind variables in the query. And I'd like to see the outer notation
> >> that you are using.
>
> >> Note that ANSI outer joins *do* have different semantics from Informix
> >> outer joins -- you cannot readily switch between the two and always get
> >> the same result, particularly if there is a filter condition on rows in
> >> the outer-join tables.
>
> >> You don't need to provide the data in systables, but it would be helpful
> >> to have the contents of systabauth and sysusers, at least as far as you
> >> think is relevant.
>
> > Actually the Baan query with this problem have a bind variable. And I
> > was trying to reproduce that query. Later I have found that the same
> > query without bind variables does not work too. I've forgotten to
> > revise my message, sorry.
>
> > The contents of systabauth and sysusers is not relevant, this query
> > returns 9 rows - select for public on system tables.
>
> You have a regular join between systables and the result of an outer
> join of systabauth and sysusers; how do you deduce that the contents of
> systabauth and sysusers is irrelevant? Actually, given that it is the
> system catalog, the contents of systabauth for tabid values less than 10
> probably is fixed. However, there could be all sorts of stuff in the
> sysusers table. For example, I don't know whether you've granted public
> any access to the database, which makes it hard to know what the outer
> join is going to be doing.
I think difference between "outer" and "left join" is how tables will
be filtered - post-join or pre-join. In my query I filter the dominant
table, it doesn't matter when the filter will be applied, both querys
return the same number of rows.
I have checked my query with public access granted and not granted,
there is the problem in both cases.
> > This is the exact code from Borland Delphi:
>
> > procedure TForm1.bugClick(Sender: TObject);
> > begin
> > IfxSQL3.SQL.Text := 'SELECT b.tabid, b.tabname, c.grantee, e.username
> > '+
> > 'FROM systables b, (systabauth AS c LEFT JOIN sysusers AS e ON
> > e.username = c.grantee) '+
> > 'WHERE c.tabid = b.tabid '+
> > 'AND b.tabid IN (SELECT a.tabid FROM systables a WHERE a.tabid <
> > 10 )';
>
> Thank you - this is one of the two queries I asked about, and the less
> interesting of the two since it was given in your question. I was
> really interested in the Informix-notation outer join; I need to see why
> you think that it should produce the same result as the ISO-notation
> outer join.
SELECT b.tabid, b.tabname, c.grantee, e.username
FROM systables b, (systabauth AS c LEFT JOIN sysusers AS e ON
e.username = c.grantee)
WHERE c.tabid = b.tabid
AND exists (SELECT a.tabid FROM systables a WHERE b.tabid = a.tabid
and a.tabid < 10 )
SELECT b.tabid, b.tabname, c.grantee, e.username
FROM systables b, (systabauth AS c LEFT JOIN sysusers AS e ON
e.username = c.grantee)
WHERE c.tabid = b.tabid
AND b.tabid IN (SELECT a.tabid FROM systables a )
SELECT b.tabid, b.tabname, c.grantee, e.username
FROM systables b, systabauth AS c, outer sysusers AS e
WHERE c.tabid = b.tabid and e.username = c.grantee
AND b.tabid IN (SELECT a.tabid FROM systables a WHERE a.tabid < 10 )
All these querys work correctly.
> > IfxSQL3.Prepare;
> > IfxSQL3.Open;
> > ShowMessage(booltostr(IfxSQL3.EOF)); ---- It shows 0 (EOF == False)
> > IfxSQL3.close;
>
> > IfxSQL3.Open;
> > ShowMessage(booltostr(IfxSQL3.EOF)); ---- It shows -1 (EOF == True)
>
> If my understanding of what I see is correct, you are saying that when a
> statement is used the first time, it gives one answer, and when used a
> second time, it gives a different answer?
Yes that's the problem
> Assuming nothing else has gone changing the database in the interim,
> there would be a bug somewhere in the system if that is correct. If
> anything changed, we need to understand what has changed.
Yes I think it's a bug. The support engineer has reproduced this
behavior.
> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleff...@earthlink.net, jleff...@us.ibm.com
> Guardian of DBD::Informix v2005.02 --http://dbi.perl.org/