Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /DISK2/WWW/plsql.cz/www/wp-content/themes/suffusion/library/suffusion-walkers.php on line 39
Dec 022012
 

Jedna z věcí,  která mi trošku trápí a o které si bohužel myslím, že povede na regulerní výrazy, scripty a parsování predikátů exekučního plánu. Protože jinak nevím, jak takovouhle věc systémově podchytit – mimo outlines a podobných featur.

Jde mi o join na sloupci, který je jeden VARCHAR2 a druhý NUMBER (alternativně něco podobného), kde může správně zafungovat implicitní konverze (na NUMBER), pokud jsou ve VARCHAR2 sloupci jen čísla, která implicitní konverze zkousne – v takovém případně select doběhne. Pokud tam čísla nejsou a explicitně to nepřevedeme pomocí to_char, tak select vyhučí na ORA-01722. To zní fér, ne?

Bohužel do toho ještě může promluvit exekuční plán a pořadí joinování tabulek, což je opravdu nepěkná vlastnost – programátor si nevšimne, že joinuje NUMBER na VARCHAR2 a select mu dobíhá bez chyby. Pokud se testuje (mělo by) tak třeba i projde na produkci, kde funguje nějaký pátek a pak najednou změna exekučního plánu a průser.

Slov bylo dost, takže go, hébičky, go!

create table tbl_a as select rownum id_a,rownum join_column_a from dual connect by level <=100;

Jednoduchá tabulka se sloupci id_a a join_column_a ve kterých jsou čísla od 1 do 100, typ: NUMBER. Nyní ještě jedna, ta samá tabulka, ale přejmenovaná na surfix _c:

create table tbl_c as select rownum id_c,rownum join_column_c from dual connect by level <=100;

tbl_c je stejná jako tbl_a. Mnohem zajímavější bude tabulka tbl_b:

create table tbl_b as select rownum id_b,
case when rownum=23 then 'au' else to_char(rownum) end join_column_b from dual connect by level <=100;

v tbl_b je sloupec id_b typu NUMBER ve kterém jsou čísla 1-100 a také sloupec join_column_b typu VARCHAR2(40) ve kterém jsou čísla <1-22> a <24-100> a text “au”.

Tabulky jsou připraveny, takže jdeme joinovat:

select * from tbl_a, tbl_b where tbl_a.join_column_a=tbl_b.join_column_b;

Tak tbl_a.join_column_a je typu NUMBER, tbl_b.join_column_b je typu VARCHAR2 a obsahuje v jednom řádku text “au”. Takže implicitní konverze to vezme na NUMBER a select vyhučí na ORA-01722. Takže to_char, explicitní konverze:

select * from tbl_a, tbl_b where to_char(tbl_a.join_column_a)=tbl_b.join_column_b;

Ok, explicitně zkonvertováno na string a select normálně doběhne. O co mi jde, je ale tenhle select:

select * from tbl_a, tbl_b,tbl_c where
tbl_a.join_column_a=tbl_b.join_column_b and /* number vs varchar2 s textem v id=23 */
tbl_c.id_c=tbl_b.id_b and /* number vs number */
id_c >(select 24 from dual);

Vyhučí to na chybě nebo, ne? Na jednu stranu se tam joinuje zkrz implicitní koverzi VARCHAR2 s nečíselnou hodnotou na NUMBER na stranu druhou je tam ale filtr id_c>24, který by měl zrovna tuhle hodnotu vyfiltrovat a měli by v VARCHAR2 sloupci join_column_b zbýt jen hodnoty, které lze zkonvertovat na number a regulerně se najoinovat na tbl_a.join_column_a. Tedy? Tedy pohled do exekučního plánu:

Invalid number a exekuční plán

Exekuční plán řiká: Nejprve TBL_C, vyfitrovat TBL_C, pak join na ID_C=ID_B, který odfiltruje z tabulky tbl_b řádek s textovou hodnotou a teprve pak problémový join JOIN_COLUMN_A=JOIN_COLUMN_B. Ošem tam již je řádek 23 s hodnotou “au” odfiltrován, takže ANO, sql select funguje.

No dobře, a co jiné pořadí joinování a filtrování :

Invalid number a exekuční plán

Hintem jsem si vynutil přesně obrácené pořadí joinování tabulek a nejprve se vyjoinujou TBL_A a TBL_B na sloupci, kde je textová hodnota “au” na řádku 23, která by se vyfiltrovala až v dalším kroku, nicméně takhle to na padne na prvním joinu na ORA-01722.

Mimochodem to co vybral Oracle (join C-B-A) a to co jsem vybral já (A-B-C) má stejně odhadnutý cost, moc hezky je na tom vidět, jak je ten select nebezpečný – stačí málo, aby se rozhodl jinak.

Závěr? Prakticky žádný, jen že na tohle kurva do píči pozor, protože typicky nekontrolujeme datové typy sloupců (ve smyslu, když vidím ID řádku a vidím tam hodnoty 1,2,3,4.. nepředpokládam, že je tam VARCHAR2 a končí to hodnotou “haf” a programátor předemnou byl xindl), když denně píšeme denně hafo selectů a tohle může být, bohužel, jeden z kostilvců, který se může projevit až za delší dobu při změně exekučního plánu – v extrémním případě sundá po letech produkčního běhu select přepočet statitstik..

Tip na jednu hodně mocnou věcičku, jak tomu předejít: Vhodné nastavení PLSQL-Warnings a override warningu na error. To je imho nejlepší řešení, jak eliminovat tento problém.

 Posted by at 00:56

  2 Responses to “Nebezpečná chyba ORA-01722: invalid number a exekuční plán”

  1. Azore, co jsou to ty hébičky? :)

  2. Hébička je postavička z jedné internetové stránky, kde patřím mezi těch 30 uživatelů ročně, jež ji navštěvují – http://jspurny.sweb.cz/Heebicka/

    konkrétně můj nejoblíbenější komix je tento: http://jspurny.sweb.cz/Heebicka/heebicka-12.png
    a tento
    http://jspurny.sweb.cz/Heebicka/heeb3.png

    Příbehy pak zde:
    http://jspurny.sweb.cz/Heebicka/komixy.html

 Leave a Reply

(required)

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>