Informix - Problem description
Problem IT34215 | Status: Closed |
IWA: OFFLOADED QUERY WITH COUNT INCLUDING DISTINCT AND A CASE EXPRESSION IN THE PROJECTION LIST CAN LEAD TO ERROR -904,57011 | |
product: | |
INFORMIX WHSE A / 5725D1400 / C10 - IDS 12.10 | |
Problem description: | |
When you attempt to offload a query like this select count(distinct case when tab1.c2 = 'NEU' then tab2.c2 end) from tab1 inner join tab2 on tab1.c1 = tab2.c1; to the Informix Warehouse Accelerator (IWA) you might receive an error 26561: cannot map to Informix sqlcode: DRDA OPEN IWA error:-904,'57011',Task canceled:QueryExecutionTerminated The tables in this reproduction are setup like create table tab1(c1 int, c2 varchar(20)); create table tab2(c1 int, c2 varchar(12)); insert into tab1 values(1, 'NEU'); insert into tab1 values(2, NULL); insert into tab1 values(3, 'ABC'); insert into tab1 values(4, 'XYZ'); insert into tab1 values(4, 'NEU'); insert into tab2 values(1, NULL); insert into tab2 values(2, NULL); insert into tab2 values(3, NULL); insert into tab2 values(1, 'XYZ'); insert into tab2 values(4, 'XYZ'); The error does not necessarily occur every time the query is offloaded. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC15 and 14.10.xC5. * **************************************************************** * PROBLEM DESCRIPTION: * * When you attempt to offload a query like this * * * * select count(distinct case when tab1.c2 = 'NEU' then tab2.c2 * * end) * * from tab1 inner join tab2 on tab1.c1 = tab2.c1; * * * * to the Informix Warehouse Accelerator (IWA) you might * * receive an * * error * * * * 26561: cannot map to Informix sqlcode: DRDA OPEN IWA * * error:-904,'57011',Task canceled:QueryExecutionTerminated * * * * The tables in this reproduction are setup like * * * * create table tab1(c1 int, c2 varchar(20)); * * create table tab2(c1 int, c2 varchar(12)); * * insert into tab1 values(1, 'NEU'); * * insert into tab1 values(2, NULL); * * insert into tab1 values(3, 'ABC'); * * insert into tab1 values(4, 'XYZ'); * * insert into tab1 values(4, 'NEU'); * * * * insert into tab2 values(1, NULL); * * insert into tab2 values(2, NULL); * * insert into tab2 values(3, NULL); * * insert into tab2 values(1, 'XYZ'); * * insert into tab2 values(4, 'XYZ'); * * * * The error does not necessarily occur every time the query is * * offloaded. * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 12.10.xC15 (when available) or * * 14.10.xC5. * **************************************************************** | |
Local Fix: | |
When you use a CHAR data type instead of a VARCHAR you possibly can prevent the error. | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC15 and 14.10.xC5. * **************************************************************** * PROBLEM DESCRIPTION: * * When you attempt to offload a query like this * * * * select count(distinct case when tab1.c2 = 'NEU' then tab2.c2 * * end) * * from tab1 inner join tab2 on tab1.c1 = tab2.c1; * * * * to the Informix Warehouse Accelerator (IWA) you might * * receive an * * error * * * * 26561: cannot map to Informix sqlcode: DRDA OPEN IWA * * error:-904,'57011',Task canceled:QueryExecutionTerminated * * * * The tables in this reproduction are setup like * * * * create table tab1(c1 int, c2 varchar(20)); * * create table tab2(c1 int, c2 varchar(12)); * * insert into tab1 values(1, 'NEU'); * * insert into tab1 values(2, NULL); * * insert into tab1 values(3, 'ABC'); * * insert into tab1 values(4, 'XYZ'); * * insert into tab1 values(4, 'NEU'); * * * * insert into tab2 values(1, NULL); * * insert into tab2 values(2, NULL); * * insert into tab2 values(3, NULL); * * insert into tab2 values(1, 'XYZ'); * * insert into tab2 values(4, 'XYZ'); * * * * The error does not necessarily occur every time the query is * * offloaded. * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 12.10.xC15 (when available) or * * 14.10.xC5. * **************************************************************** | |
Comment | |
Fixed in Informix Server 12.10.xC15 and 14.10.xC5. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 15.09.2020 04.02.2021 04.02.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |