FALSE with OR condition exclude using index

[Dzyszla]

Table:

CREATE TABLE T_TEST (
    V_INT   INTEGER,
    V_CHAR  CHAR(1)
);

CREATE INDEX T_TEST_IDX1 ON T_TEST (V_INT);
CREATE INDEX T_TEST_IDX2 ON T_TEST (V_CHAR);

SELECT (statistics and data in table no matters):

SELECT *
FROM t_test
WHERE
  (1 = 0
  OR v_int BETWEEN 41020000 AND 41029999
  OR v_int BETWEEN 41030000 AND 41039999)
  AND v_char = 'T'

Use only: PLAN (T_TEST INDEX (T_TEST_IDX2))

But when remove from OR-list dummy condition:

SELECT *
FROM t_test
WHERE
  (v_int BETWEEN 41020000 AND 41029999
  OR v_int BETWEEN 41030000 AND 41039999)
  AND v_char = 'T'

Now use booth: PLAN (T_TEST INDEX (T_TEST_IDX1, T_TEST_IDX1, T_TEST_IDX2))

Affected version: 3.0.11

[dyemanov]

This is by design. OR condition can be mapped to an index only if all ORed parts can use an index, which is obviously impossible for 1 = 0. Maybe such constant conditions could be tracked somehow to improve the current rules, but I wouldn't assign it a high priority.

[Dzyszla]

I think that throwing out such constant conditions already at the query optimization stage would help a lot. Look at the issue #6941 - there simply skip (remove) dummy condition it's all you need and then rest works exactly as expect.