04 June 2010

Check your Datatype, also in Check Constraints

A question came up at the Oracle discussion forum on SQL and PL/SQL regarding not being able to see any data even though the table did contain records. My lesson from this question was to pay close attention to the datatypes and implicit conversions taking place. What still puzzles me is a way to detect the mistake made apart from looking closely.
Anyway these queries didn't return any records:

SELECT * FROM problem WHERE solved = '0';
SELECT * FROM problem WHERE solved = 0;



As you can see in the query different predicates are used. One compares the "solved" column to a NUMBER and the other compares it to a CHAR.

Without seeing the table definition a lot of things could be the cause of not being able to see any data. A reproducible test case would be nice.

SQL> CREATE TABLE "PROBLEM"
2 ( "PROBLEM_ID" NUMBER NOT NULL ENABLE,
3 "PROBLEM_DESCRIPTION" VARCHAR2(4000),
4 "ACTION_TAKEN" VARCHAR2(4000),
5 "TRANSACTION_ID" NUMBER NOT NULL ENABLE,
6 "SOLVED" NUMBER NOT NULL ENABLE,
7 CONSTRAINT "PROBLEM_PK" PRIMARY KEY ("PROBLEM_ID") ENABLE,
8 CONSTRAINT "SOLVED_VALUE" CHECK ( "SOLVED" IN ('1', '0')) enable
9 )
10 /

Table created.

SQL>
SQL>
SQL> CREATE INDEX "PROBLEM_IDX1" ON "PROBLEM" ("TRANSACTION_ID")
2 /

Index created.

SQL>
SQL> CREATE SEQUENCE "PROBLEM_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER NOCYCLE
2 /

Sequence created.

SQL>
SQL> CREATE OR REPLACE TRIGGER "BI_PROBLEM"
2 before insert on "PROBLEM"
3 for each row
4 begin
5 select "PROBLEM_SEQ".nextval into :NEW.PROBLEM_ID from dual;
6 end;
7
8 /

Trigger created.

SQL> ALTER TRIGGER "BI_PROBLEM" ENABLE
2 /

Trigger altered.

SQL> insert into problem
2 (problem_id
3 ,problem_description
4 ,transaction_id
5 ,solved
6 )
7 values
8 (22
9 ,'the problem'
10 ,81
11 ,0
12 );

1 row created.

SQL>
SQL> insert into problem
2 (problem_id
3 ,problem_description
4 ,transaction_id
5 ,solved
6 )
7 values
8 (2
9 ,'There are'
10 ,81
11 ,0
12 );

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> SELECT *
2 FROM problem
3 WHERE solved = '0';

no rows selected

SQL> SELECT *
2 FROM problem
3 WHERE solved = 0;

no rows selected

And there you have it, even though we created two records each with a 0 (zero) for the "solved" column, no rows were selected by either of the SELECT statements issued.
How could this be possible?
Let's have a look at the Explain plan for one of the statements:

SQL> select *
2 from problem
3 where solved = 0
4 /

no rows selected

SQL> select *
2 from table (dbms_xplan.display_cursor)
3 /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID dz2r64h4m6f2z, child number 0
-------------------------------------
select * from problem where solved = 0

Plan hash value: 4173894327

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| PROBLEM | 2 | 8086 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NULL IS NOT NULL)
2 - filter("SOLVED"=0)

Note
-----
- dynamic sampling used for this statement

The filter with operation id 1 in the Explain Plan shows "NULL IS NOT NULL". As this predicate is always false no data is shown. According to the "Inside the Oracle Optimizer"-blog this effectively removes the table from the query.

The "NULL IS NOT NULL" filter is a false constant predicate, that will prevent the table scan from even taking place.


So because of the filter the data isn't shown, but why is the filter introduced? There must be something wrong here. In the past I've used similar queries on similar tables which all seemed to be working well. Why is this scenario different?
Let's have a closer look at the table definition

CREATE TABLE "PROBLEM"
( "PROBLEM_ID" NUMBER NOT NULL ENABLE,
"PROBLEM_DESCRIPTION" VARCHAR2(4000),
"ACTION_TAKEN" VARCHAR2(4000),
"TRANSACTION_ID" NUMBER NOT NULL ENABLE,
"SOLVED" NUMBER NOT NULL ENABLE,
CONSTRAINT "PROBLEM_PK" PRIMARY KEY ("PROBLEM_ID") ENABLE,
CONSTRAINT "SOLVED_VALUE" CHECK ( "SOLVED" IN ('1', '0')) enable
)
/

Notice anything unusual?
No?...
How about now

...
"SOLVED" NUMBER
...
"SOLVED_VALUE" CHECK ( "SOLVED" IN ('1', '0'))
...

Notice the quotes in the Check Constraint definition? The datatype of the column is NUMBER and the Check in the Check constraint uses CHAR.
Could this be the cause? Let's verify

SQL> CREATE TABLE "PROBLEM"
2 ( "PROBLEM_ID" NUMBER NOT NULL ENABLE,
3 "PROBLEM_DESCRIPTION" VARCHAR2(4000),
4 "ACTION_TAKEN" VARCHAR2(4000),
5 "TRANSACTION_ID" NUMBER NOT NULL ENABLE,
6 "SOLVED" NUMBER NOT NULL ENABLE,
7 CONSTRAINT "PROBLEM_PK" PRIMARY KEY ("PROBLEM_ID") ENABLE,
8 CONSTRAINT "SOLVED_VALUE" CHECK ( "SOLVED" IN (1, 0)) enable
9 )
10 /

Table created.

SQL> insert into problem
2 (problem_id
3 ,problem_description
4 ,transaction_id
5 ,solved
6 )
7 values
8 (22
9 ,'the problem'
10 ,81
11 ,0
12 );

1 row created.

SQL>
SQL> select problem_description
2 from problem
3 /

PROBLEM_DESCRIPTION
----------------------------------------------------------------------------
the problem

And the filter is eliminated from the Explain Plan

SQL> select *
2 from table (dbms_xplan.display_cursor)
3 /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID 18wkx6g72p35j, child number 0
-------------------------------------
select problem_description from problem

Plan hash value: 3149918165

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| PROBLEM | 1 | 2002 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Puzzling
Why does these queries give output, even with the original Check Constraint in place? The only difference is using bind variable instead of hard coded values...

SQL> var b number
SQL>
SQL> begin
2 :b := 0;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select problem_description
2 from problem
3 where solved = :b
4 /

PROBLEM_DESCRIPTION
----------------------------------------
the problem
There are

SQL> select problem_description
2 from problem
3 where solved = to_char (:b)
4 /

PROBLEM_DESCRIPTION
----------------------------------------
the problem
There are


Any suggestions?

Oracle 11g Release 2
As always, things change with different versions. Until now this code has been run on Oracle 10g:

SQL> conn alex/alex@orcl
Connected.
SQL> select *
2 from v$version
3 /

BANNER
-------------------------------------------------------------
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

With Oracle 11g Release 2 the original queries (and original table definition) do return records:

SQL> select *
2 from v$version
3 /

BANNER
--------------------------------------------------------------

Personal Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> SELECT problem_description
2 FROM problem
3 WHERE solved = '0';

PROBLEM_DESCRIPTION
--------------------------------------------------------------

the problem
There are

SQL> SELECT problem_description
2 FROM problem
3 WHERE solved = 0;

PROBLEM_DESCRIPTION
--------------------------------------------------------------

the problem
There are


Links
Read the whole thread here.
Inside the Oracle Optimizer

1 comment:

  1. Hi Alex,

    This is a great example of (where bugs can be introduced with) "semantic query optimization", which I will talk about next tuesday at the Amis ODTUG-preview mini conference.

    Toon

    ReplyDelete