Bug with CAST and parallelism?

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Wed, 1 Mar 2006 18:43:04 -0600
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E360182B502@CWYMSX04.Corp.Acxiom.net>

Database is Oracle 9.2.0.6, OS is Tru64 5.1, data warehouse environment.

It appears that when using the CAST function with a SUBSTR, against data from a table that has parallelism set, Oracle "pre-processes" the data using the CAST function. I can explain this best with an example (unfortunately based on a production job)

/* Start with a table holding a column with data longer than 6
positions. */
CREATE TABLE dherri_test1_tb AS SELECT '00000115855' field1 FROM dual;

Table created.

/* Create a second table off the above, but only pull the */
/* last 6 characters for one of the columns */
CREATE TABLE dherri_test2_tb AS
SELECT CAST(SUBSTR(field1,6,6) AS VARCHAR2(6)) cast_sub_field1, field1 FROM (SELECT * FROM dherri_test1_tb a);

Table created.

SELECT * FROM dherri_test2_tb;

CAST_S FIELD1
------ -----------
115855 00000115855

1 row selected.

/* Same try as previous CTAS, but this time pull the data in parallel */
CREATE TABLE dherri_test3_tb AS
SELECT CAST(SUBSTR(field1,6,6) AS VARCHAR2(6)) cast_sub_field1, field1 FROM (SELECT /*+ PARALLEL(a) */ * FROM dherri_test1_tb a);

Table created.

SELECT * FROM dherri_test3_tb;

CAST_S FIELD1
------ -----------
000001 000001

1 row selected.

Not only did the column with the CAST(SUBSR... functions on it get converted to VARCHAR2(6) BEFORE the functions were run, but so did the second column with no functions applied to it.

I'll file this as a bug with Oracle, but I'm curious if anyone has run into anything similar to this before or can reproduce this on different versions of Oracle.

The workaround is simple enough: remove parallelism or use NOPARALLEL(a) hint. Unfortunately, being a DW the tables are rather large (although correct data wins over fast data). Those who wrote refresh of various tables always recreate the tables using CTAS, so the CAST function is found in a lot of code. Sure would stink if this problem is happening elsewhere and we have yet to discover it!

Thanks.

Dave

Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri_at_acxiom.com>

"When I come home from work and see those little noses pressed against the windowpane, then I know I am a success" - Paul Faulkner

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 01 2006 - 18:43:04 CST