|
Problem IT03174
|
Status: Closed |
RUNNING A PROCEDURE WHICH CONTAINS MULTIPLE SUB-QUERIES ON VIEWS CAN RETURN ERROR -5849 OR -640 WITH NO_SUBQF SET |
| product: |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 |
| Problem description: |
Running a complex stored procedure with multiple sub queries
against views can fail with error -5849
-5849 The sub-query flattening settings for the query plan
and the current
value of NO_SUBQF environment variable do not match.
Setting NO_SUBQF in the environment does not help, as the same
query then fails with error -640
-640 QPlan sanity failure line-number.
This is observed after a fresh dbimport of the database. Running
update statistics does not correct the problem.
If you set a trap for error -5849 using 'onmode -I -5849', you
will observe the following stack in the AF file it generates:
(oninit) afstack
(oninit) afhandler
(oninit) affail_interface
(oninit) check_traperror
(oninit) sqerr
(oninit) sqerr1
(oninit) d_qplan
(oninit) qd_getqplan
(oninit) ip_evalsql
(oninit) runproc
(oninit) udrlm_spl_execute
(oninit) udrlm_exec_routine
(oninit) udr_execute
(oninit) exroutine
(oninit) execproc
(oninit) aud_execproc
(oninit) excommand
(oninit) sq_execute
(oninit) sqmain
(oninit) spawn_thread
(oninit) th_init_initgls
(oninit) startup |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users of 12.10.xC1 through 12.10.xC4. *
****************************************************************
* PROBLEM DESCRIPTION: *
* Execution of a stored procedure may return -5849 or -640 *
* errors under the following conditions: *
* - a SQL statement inside the stored procedure references a *
* view that is materialized into a temp table *
* - the view definition has a correlated subquery that is *
* internally transformed into a join (subquery does not *
* contain any complex operations such as group by, *
* aggregation, remote references, references to VTI/VII, etc) *
****************************************************************
* RECOMMENDATION: *
* Update to IDS-12.10.xC5 *
**************************************************************** |
| Local Fix: |
|
| Solution |
Problem Fixed In IDS-12.10.xC5 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 15.07.2014
16.10.2015
16.10.2015 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 12.10.xC5
|
 |
| 12.10.xC5.W1
|
|
