Firebird 5.0 - GBAK: Wrong warning "index X cannot be used in the specified plan"

[AnatolyLoy]

Firebird: 5.0.1. The stored procedure has the optimiser hint to use one of two indices for the table During restoring the database from the backup, the backup log contains a warning message: ... gbak: restoring index IND_TBL_TEST_NAME_ID ... gbak: WARNING: index IND_TBL_TEST_NAME_ID cannot be used in the specified plan ... gbak: activating and creating deferred index IND_TBL_TEST_NAME_ID ...

As a result:

• database successfully restored,
  • the stored procedure's BLR references to index IND_TBL_TEST_NAME_ID and
  • the stored procedure successfully uses the optimiser hint and the specified index.

However, the warning message wrongly attracts the attention of the support team and automatization routines.

The gbak must suppress a warning when the deferred index is planned to be activated and created later.

See the attached archive with the test artefacts: TEST__GBAK_WARN_INDEX_OPTIMISER_HINT.zip

[hvlad]

Is the PLAN in SP equal to the PLAN generated by the optimizer ?

[dyemanov]

Seems like a duplicate for #7841

[AnatolyLoy]

@hvlad I don't understand how to check it: "Is the PLAN in SP equal to the PLAN generated by the optimizer ?"

The test was prepared to demonstrate behavior instead a more complex DDL.

1. The table with the two fields has two indices: CREATE INDEX IND_TBL_TEST_NAME ON TBL_TEST(NAME); CREATE INDEX IND_TBL_TEST_NAME_ID ON TBL_TEST(NAME, ID);

2. Direct SELECT without optimizer hint uses index # 1: SELECT T.NAME , T.ID FROM TBL_TEST T ORDER BY T.NAME PLAN (T ORDER IND_TBL_TEST_NAME)

3. Direct SELECT with optimizer hint uses the index # 2 from hint as expected: SELECT T.NAME , T.ID FROM TBL_TEST T PLAN(T INDEX(IND_TBL_TESTNAMEID)) ORDER BY T.NAME PLAN (T ORDER IND_TBL_TEST_NAME_ID)

4. BLR of restored SP contains a reference to index # 2 as expected blr_plan, blr_retrieve, blrrelation2, 8, 'T','B','L','','T','E','S','T', 1, 'T', 0, blrindices, 1, 20, 'I','N','D','','T','B','L','','T','E','S','T','','N','A','M','E','_','I','D', blr_end,

5. When I run SELECT * FROM PROC_TEST, I see only PLAN (PROC_TEST NATURAL)

6. After re-creating the stored procedure, I see the same for items (4) and (5) above.

[AnatolyLoy]

Seems like a duplicate for #7841

It seems like a duplicate for #7841 — another major Firebird version and another optimizer hint, but the case is the same: the validation ignores deferred indices and generates a malicious warning message.