Informix - Problem description
Problem IT10384 | Status: Closed |
WITH AN LBAC PROTECTED TABLE SPL WHERE CLAUSE USING VARIABLE PARAMETER WILL TAKE SEQUENTIAL SCAN | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
In SPL, a query like this, on an LBAC protected table, will yield an index access as expected: select col2 into ret from my_tab where col1=10000; 1) informix.lbac_tab: INDEX PATH Filters: LBACEnforceRow(informix.lbac_tab.lbl ) (1) Index Name: andreasl.idx_lbac_tab_col1 Index Keys: col1 (Serial, fragments: ALL) Lower Index Filter: informix.lbac_tab.col1 = 10000 whereas the same query using a variable parameter instead of literal would take a sequential scan path: let l_id = 10000; select col2 into ret from my_tab where col1=l_id; 1) informix.lbac_tab: SEQUENTIAL SCAN (Serial, fragments: ALL) Filters: (LBACEnforceRow(informix.lbac_tab.lbl ) AND informix.lbac_tab.col1 = '<VAR>' ) Even when forcing index access, this index would be read sequentially: let l_id = 10000; select {+INDEX(lbac_tab idx_lbac_tab_id)} col2 into ret from my_tab where col1=l_id; 1) informix.lbac_tab: INDEX PATH Filters: (LBACEnforceRow(informix.lbac_tab.lbl ) AND informix.lbac_tab.col1 = '<VAR>' ) (1) Index Name: andreasl.idx_lbac_tab_col1 Index Keys: col1 (Serial, fragments: ALL) Explicitely updating statistics, or re-creating the index, would even make the second SPL (parametrized statement) fail with: 768: Internal error in routine lbac_chk_idx_fltrs: unsafe filter in index scan. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * All users * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Update to IBM Informix Server 12.10.xC6 * **************************************************************** | |
Local Fix: | |
Solution | |
Problem Fixed In IBM Informix Server 12.10.xC6 | |
Workaround | |
not known / see Local fix | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 28.07.2015 30.12.2015 30.12.2015 |
Problem solved at the following versions (IBM BugInfos) | |
12.10.xC6 | |
Problem solved according to the fixlist(s) of the following version(s) | |
12.10.xC6 |