|
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) |
