Dummy conditions vs optimizer

[Dzyszla]

I have two, similar queries. In one of case have additional dummy where conditions (1=1, 0=0, true):

SELECT t1.*
FROM table1 t1
JOIN table2 t2 ON t2.fk_t1 = t1.id
JOIN table3 t3 ON t3.id = t1.fk_t3
WHERE
    0 = 0 AND /* with this in 1st case, without this line in 2nd case */
    t3.field = 6
    AND EXISTS (SELECT 1 FROM table2 x WHERE x.fk2_t2 = t2.id)

All necessary fields are indexed.

For each case, Firebird works different, and statistics of reads see like this:

1st case (with 0=0):

Query Time
------------------------------------------------
Prepare       : 32,00 ms
Execute       : 1 046,00 ms
Avg fetch time: 61,53 ms

Operations
------------------------------------------------
Read   : 8 342
Writes : 1
Fetches: 1 316 042
Marks  : 0


Enhanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|TABLE2                         |         0 |      4804 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|TABLE1                         |         0 |         0 |       96884 |       0 |       0 |       0 |        0 |        0 |        0 |
|TABLE3                         |         0 |    387553 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

And in 2nd case (without dummy condition):

Query Time
------------------------------------------------
Prepare       : 16,00 ms
Execute       : 515,00 ms
Avg fetch time: 30,29 ms

Operations
------------------------------------------------
Read   : 7 570
Writes : 1
Fetches: 648 103
Marks  : 0


Enhanced Info:
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|          Table Name           |  Records  |  Indexed  | Non-Indexed | Updates | Deletes | Inserts | Backouts |  Purges  | Expunges |
|                               |   Total   |   reads   |    reads    |         |         |         |          |          |          |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+
|TABLE2                         |         0 |       506 |      152655 |       0 |       0 |       0 |        0 |        0 |        0 |
|TABLE1                         |         0 |       467 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
|TABLE3                         |         0 |      1885 |           0 |       0 |       0 |       0 |        0 |        0 |        0 |
+-------------------------------+-----------+-----------+-------------+---------+---------+---------+----------+----------+----------+

And plans (last line): PLAN JOIN (T2 NATURAL, T1 INDEX (T1_ID_IDX), T3 INDEX (T3_ID_IDX)) vs PLAN JOIN (T1 NATURAL, T3 INDEX (T3_ID_IDX1), T2 INDEX (T2_FK_T1_IDX)) Why different? Affected version 2.1 to 3.0.

[Dzyszla]

I attach the DB with example datas, where can see different work for both cases because this depends for some more reasons. fb-test.zip

[spiffytortoise]

This looks like a duplicate of #1708

[Dzyszla]

This looks like a duplicate of #1708

Not exactly. There is about not optimize with always-false condition. Here I writing about change rules of execution after add always-true condition (nothing to change).