Bug with explain plan

[Michal Kolodziejski]

I have found a bug, related to automatic substitution of variables for variables with trailing colons.

Consider a query:

SELECT /*+ FIRST_ROWS(1) */ hoa.id_hist_obiekt_adres
   FROM historia_obiektu ho
   LEFT OUTER JOIN hist_obiekt_adres hoa
     ON hoa.id_historii_obiektu = ho.id_historii_obiektu_2 AND
        hoa.domyslny = 0
   WHERE ho.id_obiektu = p_id_obiektu AND
         ho.data_zmiany < (p_data_zmiany + 1)
   ORDER BY ho.id_obiektu, ho.data_zmiany DESC

If you select that query, and execure F5 (explain plan) PL/SQL Developer complains about "ORA-00932 inconsistent datatype expected DATE got NUMBER". PL/SQL Developer have trouble with an '(p_data_zmiany + 1)' construct, that is allowed in PL/SQL language (but not in SQL).

version tag: Version 6.0.2.880 (MBCS)

 

[Marco Kalter]

I don't think we can easily solve this. The (p_data_zmiany + 1) expression would have to be replaced by a single bind variable, but it's quite difficult to detect this situation.