APAR status
Closed as program error.
Error description
If you run a simple query joining 2 tables and using count(*) and group by clauses and the DS_NONPDQ_QUERY_MEM parameter is set to either its default (128 KB in 11.70, 256 KB in 12.10) or some other low value (512, 1024 KB), the values returned by the count(*) function become incorrect after certain number of returned rows. The num of rows after which the returned value become corrupted depends on the DS_NONPDQ_QUERY_MEM value. An example of a query showing this problem: select t1.id2, count(*)::integer as cnt from tab1 t1, tab2 t2 where t1.id2 = t2.id2 and t1.state_upoz = 'a' group by 1; The query above returns 12632 rows. With the default setting of DS_NONPDQ_QUERY_MEM (128KB) the first 1472 rows have the 'cnt' values correct, starting with line 1473 they become '1' for the rest of the resultset. When you change DS_NONPDQ_QUERY_MEM to 1024, the boundary moves from line 1473 to 11777. With DS_NONPDQ_QUERY_MEM 2048 the problem disappear. The occurrence of this problem also seems to depend on the number of rows in the tables used in the join. Hence there is not any 'save' value of DS_NONPDQ_QUERY_MEM parameter which would prevent all possible occurrences of the issue.
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * Users of 11.70.xC3 through 11.70.xC8. * **************************************************************** * PROBLEM DESCRIPTION: * * A query with group by and count(*) operation may return * * incorrect count information under the following conditions: * * - query is simple with one or two tables * * - if two tables are involved, a nested-loop join is being * * performed with the group-by column(s) on the outer table * * only * * - there are enough groups generated that the group-by * * operation overflows to temp partition * **************************************************************** * RECOMMENDATION: * * Update to IDS-11.70.xC9 * ****************************************************************
Problem conclusion
Problem Fixed In IDS-11.70.xC9
Temporary fix
Comments
APAR Information
APAR number
IT01329
Reported component name
INFORMIX SERVER
Reported component ID
5725A3900
Reported release
B70
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2014-04-25
Closed date
2017-06-09
Last modified date
2024-09-24
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
INFORMIX SERVER
Fixed component ID
5725A3900
Applicable component levels
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B70","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
24 September 2024