Can cause hidden bug
Chuck Jolley, July 27, 2016 - 3:37 pm UTC
We had this affect our database once when we migrated to a newer version.
One of our sql statements had a non-obvious implicit conversion in it and the new optimizer evaluated it in the opposite direction causing an error we had a hard time figuring out.
Implicit conversions should be considered a bug even if they aren't causing an immediate problem.
August 04, 2016 - 7:19 am UTC
I have to admit - I'd love a setting in the database where implicit conversions are *not* performed....but I imagine a lot of people's code would break :-)
implicit conversion
Ravi B, July 27, 2016 - 5:42 pm UTC
Yes it is doing implicit conversion.
"TEST1"."COL1"=TO_NUMBER("TEST2"."COL2"))
Plan hash value: 1107625602
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 195 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 195 | 7 (15)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 1 | 130 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST2 | 1 | 65 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 65 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TEST3 | 1 | 65 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TEST1 | 1 | 65 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TEST1"."COL1"="TEST3"."COL1" AND
"TEST1"."COL1"=TO_NUMBER("TEST2"."COL2"))
Implicit Conversion
Chuck Jolley, July 27, 2016 - 11:59 pm UTC
Yes and written this way the bug becomes obvious:
select * from TEST1
INNER JOIN TEST2 ON TEST1.COL1=<b>to_number</b>(TEST2.COL2 )
INNER JOIN TEST3 ON TEST1.COL1=TEST3.COL1;
rewritten as
select * from TEST1
INNER JOIN TEST2 ON <b>to_char</b>(TEST1.COL1)=TEST2.COL2
INNER JOIN TEST3 ON TEST1.COL1=TEST3.COL1;
the bug cannot occure.
RE: setting in the database where implicit conversions are *not* performed.
Duke Ganote, August 04, 2016 - 3:43 pm UTC
As an ALTER SESSION option disallowing implicit conversions that would be VERY useful during development!
August 04, 2016 - 4:02 pm UTC
Well the PL/SQL warning PLW-07202 tells you when there's a bind and column type mismatch. You could change this to be a compilation error if you want!
It doesn't cover all cases. But it may get developers thinking about type conversions enough that they pick up others themselves...
Chris
PLW-07204: conversion away from column type...
Duke Ganote, August 04, 2016 - 4:38 pm UTC
I'd not messed with the PLSQL warnings before. Bummer it, you say, doesn't cover all types of conversions. And it seems require CREATE PROCEDURE privilege; I could see it being useful on some read-only production DB queries.
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
Session altered.
<code> create or replace procedure x AS
cnt NUMBER;
BEGIN
WITH sample_data
( col1, col2 ) AS ( SELECT
1,'abc' FROM DUAL UNION ALL SELECT
2, '2' FROM DUAL
)
SELECT count(*)
INTO cnt
FROM sample_data t1
JOIN sample_data t2
ON t1.col1 = t2.col2
WHERE t2.col2 <> 'abc'
; END;
SQL> /
SP2-0804: Procedure created with compilation warnings
SQL> show errors
Errors for PROCEDURE X:
LINE/COL ERROR
-------- --------------------------------------------------
1/1 PLW-05018: unit X omitted optional AUTHID clause; default value
DEFINER used
13/18 PLW-07204: conversion away from column type may result in
sub-optimal query plan
SQL> DECLARE cnt NUMBER;
BEGIN
WITH sample_data
( col1, col2 ) AS ( SELECT
1,'abc' FROM DUAL UNION ALL SELECT
2, '2' FROM DUAL
)
SELECT count(*)
INTO cnt
FROM sample_data t1
JOIN sample_data t2
ON t1.col1 = t2.col2
WHERE t2.col2 <> 'abc'
; DBMS_OUTPUT.PUT_LINE(cnt); END;
/
1
PL/SQL procedure successfully completed.