Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
AnatolyLoy opened this issue Nov 1, 2024 · 4 comments

Comments

@AnatolyLoy
Copy link

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
Copy link
Member

hvlad commented Nov 1, 2024

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

@dyemanov
Copy link
Member

dyemanov commented Nov 2, 2024

Seems like a duplicate for #7841

@AnatolyLoy
Copy link
Author

AnatolyLoy commented Nov 2, 2024

@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_TEST_NAME_ID)) 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,
    blr_relation2, 8, 'T','B','L','','T','E','S','T',
    1, 'T', 0,
    blr_indices, 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
Copy link
Author

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants