Bug ? No rows returned from a view
Here is what we've done with this problem :
1) As Leonid Belov suggest we've try setenv OPTCOMPIND=0 and it did not
work.
2) We recreated the indexes, did not work either.
Talking indexes, David Ashby suggest that if an index is
disabled for some time, it will not represent the actual content of the
table when it is re-enable. Is it the case ???
3) We recreated all the database, no improvement.
4) We simplifyed some view by removing some join, so that result in a
less complex query plan, and then the result is correct.
It seems like we've fallen on bug # 57392 "Once a query gets to a
certain level of complexity, it starts to return the wrong data."
5) Since we have had many problem with outer join query's that used to
work properly before v7.23, we're planning to move to v7.30 and hopping
this release will correct the "complexity problem"
Thanks for all the answers
Martin
> -----Original Message-----
> From: Martin Brassard [SMTP:martin.brassard1@sympatico.ca]
> Posted At: Tuesday, September 08, 1998 9:12 PM
> Posted To: informix
> Conversation: Bug ? No rows returned from a view
> Subject: Bug ? No rows returned from a view
>
> In the folowing problem, it seems that Informix doesn't return the
> same
> data depending of the query plan it uses.
>
> We issue this query et don't get any data.
>
> SELECT a0604_bla_no,
> a0612_bla_ver_no
> FROM bla02 -- This is a view with three outer join
> and one inner
> join.
> WHERE (
> a0604_bla_no BETWEEN 551794 AND 551894
> )
> ORDER BY a0604_bla_no desc,
> a0612_bla_ver_no desc;>
> Then, if we issue that other query that is the same, but with a bigger
> interval, we receive data that should have been returned by the
> previous
> query.
>
> SELECT a0604_bla_no,
> a0612_bla_ver_no
> FROM bla02
> WHERE (
> a0604_bla_no BETWEEN 2 AND 551894
> )
> ORDER BY a0604_bla_no desc,
> a0612_bla_ver_no desc;>
> The query plan perform an index search on the master table for the
> first
> case, but a sequential scan in the second (this is ok because the
> interval covers almost all the table).
>
> The data that covers a0604_bla_no from 551794 to 551894 and that is
> returned by the second query should have been returned by the first
> one. It looks like this behavior is incorrect, and I wonder if
> anybody
> have face this problem before and is there workaround.
>
> We are currently running Invormix Online 7.23.FC1 on Digital Unix
> v4.0d
> Alpha 4100
>
>
> Help will be appreciate.
>
> Martin
> MBrassard@magrit.com