0RA-00952 on inner join with Oracle 10g

问题:

This works :

SELECT
     PRODUCT_INFOS.FIELD_VALUE,
     PRODUCT_INFOS.COIL_ID_SYSTEM,
     PRODUCT_INFOS.TIME_STAMP,
     PRODUCT_INFOS.FIELD_NAME
FROM
     PRODUCT_INFOS

This works :

SELECT
     COIL_INFOS.TIME_STAMP,
     COIL_INFOS.IN_THICKNESS,
     COIL_INFOS.MEASURED_LENGTH,
     COIL_INFOS.MEASURED_WIDTH,
     COIL_INFOS.PARAM_SET_TOP_SIDE,
     COIL_INFOS.PARAM_SET_BOTTOM_SIDE,
     COIL_INFOS.TIME_STAMP,
     COIL_INFOS.COIL_ID_SYSTEM
FROM
     COIL_INFOS
WHERE
     (COIL_INFOS.COIL_ID_SYSTEM LIKE '14051800' OR COIL_INFOS.COIL_ID LIKE '14051800')

This works on Oracle 11g but fails with 0RA-00952 - Table or view does not exist on 10g (version 10.2.0) :

SELECT
     PRODUCT_INFOS.FIELD_VALUE,
     COIL_INFOS.TIME_STAMP,
     COIL_INFOS.IN_THICKNESS,
     COIL_INFOS.MEASURED_LENGTH,
     COIL_INFOS.MEASURED_WIDTH,
     COIL_INFOS.PARAM_SET_TOP_SIDE,
     COIL_INFOS.PARAM_SET_BOTTOM_SIDE
FROM
     COIL_INFOS
INNER JOIN
     PRODUCT_INFOS
     on PRODUCT_INFOS.COIL_ID_SYSTEM=COIL_INFOS.COIL_ID_SYSTEM
     and PRODUCT_INFOS.TIME_STAMP=COIL_INFOS.TIME_STAMP
     and PRODUCT_INFOS.FIELD_NAME='ID bobina'
WHERE
     (COIL_INFOS.COIL_ID_SYSTEM LIKE '14051800' OR COIL_INFOS.COIL_ID
LIKE '14051800')

I see no other difference than the version of Oracle (the bases are created using the same dump). As I suspect a bug in Oracle 10g, I'd accept as answer a bug report clearly related to the problem.

Of course a direct solution or a workaround would be accepted too.

回答1:

I won't accept my own answer fast. Feel free to submit a more precise one, especially if you found a public bug report from Oracle.

I found a workaround :

SELECT
     PRODUCT_INFOS.FIELD_VALUE,
     COIL_INFOS.TIME_STAMP,
     COIL_INFOS.IN_THICKNESS,
     COIL_INFOS.MEASURED_LENGTH,
     COIL_INFOS.MEASURED_WIDTH,
     COIL_INFOS.PARAM_SET_TOP_SIDE,
     COIL_INFOS.PARAM_SET_BOTTOM_SIDE
FROM
     COIL_INFOS, PRODUCT_INFOS
WHERE
     PRODUCT_INFOS.COIL_ID_SYSTEM=COIL_INFOS.COIL_ID_SYSTEM
     and PRODUCT_INFOS.TIME_STAMP=COIL_INFOS.TIME_STAMP
     and PRODUCT_INFOS.FIELD_NAME='ID bobina'
     and (COIL_INFOS.COIL_ID_SYSTEM LIKE '14051800' OR COIL_INFOS.COIL_ID
LIKE '14051800') 

This simplified syntax for the same query does work on the 10g server. I'm more on more on the opinion the used 10g version is buggy.