Informix - Problem description
Problem IT33136 | Status: Closed |
SELECT INTO EXTERNAL TABLE USING DERIVED TABLE AND ORDER BY SEEMS TO CORRUPT OUTPUT | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
Using the following example: CREATE EXTERNAL TABLE tstbill ( as_of_date date, remit_to_name char(50), remit_to_addr2 char(50), remit_to_addr3 char(50), remit_to_addr4 char(50), remit_to_city char(50), remit_to_state char(3), remit_to_zip char(12) ) USING (DATAFILES ( "DISK:/some/path/to/output/tstbill.txt" ) ); INSERT INTO tstbill select today , 'Associated Integrated Supply Chain Solutions', '7954 Solution Center', '0', '0', 'Chicago' , 'IL' , '60677-7009' From ( SELECT today , 'TEST' , '' , '' , '' , 'Chicago' , 'IL' , '60677-7009' FROM gtr gtr WHERE gtr.csc_id between '124360' AND '124360' AND gtr.gla_id in ('14000') order by 3,2 ) order by 1 -- if you comment out the above order by 1, the data in the external -- table no longer looks corrupted If you have the "order by 1" in the select statement, when you look at the data generated in the tstbill.txt, you will see output like the following: 06/09/2020|Associated Integrated Supply Chain Solutions7954 S|olution Center00Ch icagoIL60677-7009 06/09/2020|Associated Integrated Supply Chain Solutions7954 S|olution Center00Ch icagoIL60677-7009 06/09/2020|Associated Integrated Supply Chain Solutions7954 S|olution Center00Ch icagoIL60677-7009 06/09/2020|Associated Integrated Supply Chain Solutions7954 S|olution Center00Ch icagoIL60677-7009 06/09/2020|Associated Integrated Supply Chain Solutions7954 S|olution Center00Ch icagoIL60677-7009 06/09/2020|Associated Integrated Supply Chain Solutions7954 S|olution Center00Ch icagoIL60677-7009 ... If the "order by 1" is removed from the select (so the order by on the derived table) the output looks correct. Like the following: 06/09/2020|Associated Integrated Supply Chain Solutions|7954 Solution Center|0|0 |Chicago|IL|60677-7009| 06/09/2020|Associated Integrated Supply Chain Solutions|7954 Solution Center|0|0 |Chicago|IL|60677-7009| 06/09/2020|Associated Integrated Supply Chain Solutions|7954 Solution Center|0|0 |Chicago|IL|60677-7009| 06/09/2020|Associated Integrated Supply Chain Solutions|7954 Solution Center|0|0 |Chicago|IL|60677-7009| | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC15 and 14.10.xC5. * **************************************************************** * PROBLEM DESCRIPTION: * * Using the following example: * * * * CREATE EXTERNAL TABLE tstbill * * ( * * as_of_date date, * * remit_to_name char(50), * * remit_to_addr2 char(50), * * remit_to_addr3 char(50), * * remit_to_addr4 char(50), * * remit_to_city char(50), * * remit_to_state char(3), * * remit_to_zip char(12) * * ) * * * * USING * * (DATAFILES * * ( * * "DISK:/some/path/to/output/tstbill.txt" * * ) * * ); * * * * INSERT INTO tstbill * * * * select * * today , * * 'Associated Integrated Supply Chain Solutions', * * '7954 Solution Center', * * '0', * * '0', * * 'Chicago' , * * 'IL' , * * '60677-7009' * * * * From * * ( * * SELECT * * today , * * 'TEST' , * * '' , * * '' , * * '' , * * 'Chicago' , * * 'IL' , * * '60677-7009' * * FROM * * gtr gtr * * WHERE * * gtr.csc_id between '124360' AND '124360' AND * * gtr.gla_id in ('14000') * * order by 3,2 * * ) * * order by 1 * * -- if you comment out the above order by 1, the data in the * * external * * -- table no longer looks corrupted * * * * If you have the "order by 1" in the select statement, when * * you * * look at the data generated in the tstbill.txt, you will see * * output like the following: * * * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * ... * * * * If the "order by 1" is removed from the select (so the order * * by * * on the derived table) the output looks correct. Like the * * following: * * * * 06/09/2020|Associated Integrated Supply Chain Solutions|7954 * * Solution Center|0|0 * * |Chicago|IL|60677-7009| * * 06/09/2020|Associated Integrated Supply Chain Solutions|7954 * * Solution Center|0|0 * * |Chicago|IL|60677-7009| * * 06/09/2020|Associated Integrated Supply Chain Solutions|7954 * * Solution Center|0|0 * * |Chicago|IL|60677-7009| * * 06/09/2020|Associated Integrated Supply Chain Solutions|7954 * * Solution Center|0|0 * * |Chicago|IL|60677-7009| * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 12.10.xC15 (when available) or * * 14.10.xC5. * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC15 and 14.10.xC5. * **************************************************************** * PROBLEM DESCRIPTION: * * Using the following example: * * * * CREATE EXTERNAL TABLE tstbill * * ( * * as_of_date date, * * remit_to_name char(50), * * remit_to_addr2 char(50), * * remit_to_addr3 char(50), * * remit_to_addr4 char(50), * * remit_to_city char(50), * * remit_to_state char(3), * * remit_to_zip char(12) * * ) * * * * USING * * (DATAFILES * * ( * * "DISK:/some/path/to/output/tstbill.txt" * * ) * * ); * * * * INSERT INTO tstbill * * * * select * * today , * * 'Associated Integrated Supply Chain Solutions', * * '7954 Solution Center', * * '0', * * '0', * * 'Chicago' , * * 'IL' , * * '60677-7009' * * * * From * * ( * * SELECT * * today , * * 'TEST' , * * '' , * * '' , * * '' , * * 'Chicago' , * * 'IL' , * * '60677-7009' * * FROM * * gtr gtr * * WHERE * * gtr.csc_id between '124360' AND '124360' AND * * gtr.gla_id in ('14000') * * order by 3,2 * * ) * * order by 1 * * -- if you comment out the above order by 1, the data in the * * external * * -- table no longer looks corrupted * * * * If you have the "order by 1" in the select statement, when * * you * * look at the data generated in the tstbill.txt, you will see * * output like the following: * * * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * 06/09/2020|Associated Integrated Supply Chain Solutions7954 * * S|olution Center00Ch * * icagoIL60677-7009 * * ... * * * * If the "order by 1" is removed from the select (so the order * * by * * on the derived table) the output looks correct. Like the * * following: * * * * 06/09/2020|Associated Integrated Supply Chain Solutions|7954 * * Solution Center|0|0 * * |Chicago|IL|60677-7009| * * 06/09/2020|Associated Integrated Supply Chain Solutions|7954 * * Solution Center|0|0 * * |Chicago|IL|60677-7009| * * 06/09/2020|Associated Integrated Supply Chain Solutions|7954 * * Solution Center|0|0 * * |Chicago|IL|60677-7009| * * 06/09/2020|Associated Integrated Supply Chain Solutions|7954 * * Solution Center|0|0 * * |Chicago|IL|60677-7009| * **************************************************************** * 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 : | 09.06.2020 28.01.2021 28.01.2021 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |