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.

Sign up for GitHub

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

Jump to bottom

Derived table and normal table plan issue - some indexes can not by used [CORE2327] #2751

Closed
firebird-automations opened this issue Feb 17, 2009 · 13 comments
Closed

Derived table and normal table plan issue - some indexes can not by used [CORE2327] #2751

firebird-automations opened this issue Feb 17, 2009 · 13 comments
Labels
affect-version: 2.1.1 affect-version: 2.5 Alpha 1 component: engine priority: minor resolution: duplicate type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @livius2

Duplicates CORE1607

Some indexes can not be used i we select from derived table
but is posible to use it in plan if it is a normal table

//this is only sample then not look for logic in this query

normal table select:
SELECT
(SELECT
FIRST 1 http://G.ID
FROM
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<= '2009-02-17'
AND G.DATAP+G.CZASP<= '2009-02-17 12:00'
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
ADRESY_DB A

but when i use derived table this plan is not acceptable but should be

SELECT
(SELECT
FIRST 1 http://G.ID
FROM
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<= '2009-02-17'
AND G.DATAP+G.CZASP<= '2009-02-17 12:00'
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
(
SELECT 'X001' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X002' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X003' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X004' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X005' AS OBIEKT FROM RDB$DATABASE A
) AS A(OBIEKT)

in second query with derived table i get this error:
SQL Message : -284
index cannot be used in the specified plan

Engine Code : 335544642
Engine Message :
index IXA_GPRSTEST_DB__OBIEKT cannot be used in the specified plan
@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: Some indexes can not be used i we select from derived table
but is posible to use it in plan if it is a normal table

normal table select:
SELECT
*
from
(SELECT

(SELECT
FIRST 1 http://G.ID
from
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<='2009-02-17'
AND G.DATAP+G.CZASP<='2009-02-17 12:00'
AND (G.SZEROKOSC IS NOT NULL AND (G.SZEROKOSC<>'') AND G.DLUGOSC IS NOT NULL AND (G.DLUGOSC<>'') AND (G.RODZAJ='A'))
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
ADRESY_DB A
WHERE
A.ID_GRUPY=2
) AS GLAST(G_ID)

but when i use derived table this plan is not acceptable but should be

SELECT
*
from
(SELECT

(SELECT
FIRST 1 http://G.ID
from
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<='2009-02-17'
AND G.DATAP+G.CZASP<='2009-02-17 12:00'
AND (G.SZEROKOSC IS NOT NULL AND (G.SZEROKOSC<>'') AND G.DLUGOSC IS NOT NULL AND (G.DLUGOSC<>'') AND (G.RODZAJ='A'))
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
(
SELECT 'X001' AS OBIEKT, 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X002', 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X003', 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X004', 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X005', 1 AS ID_GRUPY FROM RDB$DATABASE A
) AS A(OBIEKT, ID_GRUPY)
WHERE
A.ID_GRUPY=2
) AS GLAST(G_ID)

in second query with derived table i get this error:
SQL Message : -284
index cannot be used in the specified plan

Engine Code : 335544642
Engine Message :
index IXA_GPRSTEST_DB__OBIEKT cannot be used in the specified plan

=>

Some indexes can not be used i we select from derived table
but is posible to use it in plan if it is a normal table

//this is only sample then not look for logic in this query

normal table select:
SELECT
*
from
(SELECT

(SELECT
FIRST 1 http://G.ID
from
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<='2009-02-17'
AND G.DATAP+G.CZASP<='2009-02-17 12:00'
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
ADRESY_DB A
WHERE
A.ID_GRUPY=2
) AS GLAST(G_ID)

but when i use derived table this plan is not acceptable but should be

SELECT
*
from
(SELECT

(SELECT
FIRST 1 http://G.ID
from
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<='2009-02-17'
AND G.DATAP+G.CZASP<='2009-02-17 12:00'
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
(
SELECT 'X001' AS OBIEKT, 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X002', 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X003', 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X004', 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X005', 1 AS ID_GRUPY FROM RDB$DATABASE A
) AS A(OBIEKT, ID_GRUPY)
WHERE
A.ID_GRUPY=2
) AS GLAST(G_ID)

in second query with derived table i get this error:
SQL Message : -284
index cannot be used in the specified plan

Engine Code : 335544642
Engine Message :
index IXA_GPRSTEST_DB__OBIEKT cannot be used in the specified plan

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Does this issue exist in v2.1.2 RC1 as well?

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: Some indexes can not be used i we select from derived table
but is posible to use it in plan if it is a normal table

//this is only sample then not look for logic in this query

normal table select:
SELECT
*
from
(SELECT

(SELECT
FIRST 1 http://G.ID
from
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<='2009-02-17'
AND G.DATAP+G.CZASP<='2009-02-17 12:00'
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
ADRESY_DB A
WHERE
A.ID_GRUPY=2
) AS GLAST(G_ID)

but when i use derived table this plan is not acceptable but should be

SELECT
*
from
(SELECT

(SELECT
FIRST 1 http://G.ID
from
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<='2009-02-17'
AND G.DATAP+G.CZASP<='2009-02-17 12:00'
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
(
SELECT 'X001' AS OBIEKT, 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X002', 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X003', 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X004', 1 AS ID_GRUPY FROM RDB$DATABASE A
union
SELECT 'X005', 1 AS ID_GRUPY FROM RDB$DATABASE A
) AS A(OBIEKT, ID_GRUPY)
WHERE
A.ID_GRUPY=2
) AS GLAST(G_ID)

in second query with derived table i get this error:
SQL Message : -284
index cannot be used in the specified plan

Engine Code : 335544642
Engine Message :
index IXA_GPRSTEST_DB__OBIEKT cannot be used in the specified plan

=>

Some indexes can not be used i we select from derived table
but is posible to use it in plan if it is a normal table

//this is only sample then not look for logic in this query

normal table select:
SELECT
(SELECT
FIRST 1 http://G.ID
FROM
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<= '2009-02-17'
AND G.DATAP+G.CZASP<= '2009-02-17 12:00'
AND (G.SZEROKOSC IS NOT NULL AND (G.SZEROKOSC<>'') AND G.DLUGOSC IS NOT NULL AND (G.DLUGOSC<>'') AND (G.RODZAJ='A'))
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
ADRESY_DB A

but when i use derived table this plan is not acceptable but should be

SELECT
(SELECT
FIRST 1 http://G.ID
FROM
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<= '2009-02-17'
AND G.DATAP+G.CZASP<= '2009-02-17 12:00'
AND (G.SZEROKOSC IS NOT NULL AND (G.SZEROKOSC<>'') AND G.DLUGOSC IS NOT NULL AND (G.DLUGOSC<>'') AND (G.RODZAJ='A'))
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
(
SELECT 'X001' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X002' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X003' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X004' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X005' AS OBIEKT FROM RDB$DATABASE A
) AS A(OBIEKT)

in second query with derived table i get this error:
SQL Message : -284
index cannot be used in the specified plan

Engine Code : 335544642
Engine Message :
index IXA_GPRSTEST_DB__OBIEKT cannot be used in the specified plan

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

I try this now in v2.1.2 RC1 and also the same problem

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

As far as I understand, this ticket duplicates CORE1607 which is fixed in v2.5 Beta and post-v2.1.2 builds.
You can validate it with a snapshot build: http://www.firebirdsql.org/download/snapshot_builds/win/

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

description: Some indexes can not be used i we select from derived table
but is posible to use it in plan if it is a normal table

//this is only sample then not look for logic in this query

normal table select:
SELECT
(SELECT
FIRST 1 http://G.ID
FROM
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<= '2009-02-17'
AND G.DATAP+G.CZASP<= '2009-02-17 12:00'
AND (G.SZEROKOSC IS NOT NULL AND (G.SZEROKOSC<>'') AND G.DLUGOSC IS NOT NULL AND (G.DLUGOSC<>'') AND (G.RODZAJ='A'))
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
ADRESY_DB A

but when i use derived table this plan is not acceptable but should be

SELECT
(SELECT
FIRST 1 http://G.ID
FROM
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<= '2009-02-17'
AND G.DATAP+G.CZASP<= '2009-02-17 12:00'
AND (G.SZEROKOSC IS NOT NULL AND (G.SZEROKOSC<>'') AND G.DLUGOSC IS NOT NULL AND (G.DLUGOSC<>'') AND (G.RODZAJ='A'))
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
(
SELECT 'X001' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X002' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X003' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X004' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X005' AS OBIEKT FROM RDB$DATABASE A
) AS A(OBIEKT)

in second query with derived table i get this error:
SQL Message : -284
index cannot be used in the specified plan

Engine Code : 335544642
Engine Message :
index IXA_GPRSTEST_DB__OBIEKT cannot be used in the specified plan

=>

Some indexes can not be used i we select from derived table
but is posible to use it in plan if it is a normal table

//this is only sample then not look for logic in this query

normal table select:
SELECT
(SELECT
FIRST 1 http://G.ID
FROM
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<= '2009-02-17'
AND G.DATAP+G.CZASP<= '2009-02-17 12:00'
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
ADRESY_DB A

but when i use derived table this plan is not acceptable but should be

SELECT
(SELECT
FIRST 1 http://G.ID
FROM
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<= '2009-02-17'
AND G.DATAP+G.CZASP<= '2009-02-17 12:00'
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
(
SELECT 'X001' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X002' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X003' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X004' AS OBIEKT FROM RDB$DATABASE A
union
SELECT 'X005' AS OBIEKT FROM RDB$DATABASE A
) AS A(OBIEKT)

in second query with derived table i get this error:
SQL Message : -284
index cannot be used in the specified plan

Engine Code : 335544642
Engine Message :
index IXA_GPRSTEST_DB__OBIEKT cannot be used in the specified plan

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

I see that this is becaouse of union
and this is true
if i do the "same" without union then it work

SELECT
(SELECT
FIRST 1 http://G.ID
FROM
GPRSTEST_DB G
WHERE
G.OBIEKT=A.OBIEKT
AND G.DATAP<= '2009-02-17'
AND G.DATAP+G.CZASP<= '2009-02-17 12:00'
PLAN (G ORDER IXD_GPRSTEST_DB__DATAP__CZASP INDEX (IXA_GPRSTEST_DB__OBIEKT))
ORDER BY G.DATAP DESC, G.CZASP DESC
)
FROM
(
SELECT 'X001' AS OBIEKT FROM RDB$DATABASE A
) AS A(OBIEKT)

i will test it on snapshot tomorrow

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

with FB2.5 Alpha 1 doesn't work

but as you say i test it
with snapshot 2.5.0.22983 Beta1 and work ok :))

@firebird-automations
Copy link
Collaborator Author

Modified by: @livius2

Version: 2.5 Alpha 1 [ 10224 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE1607 [ CORE1607 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 2.1.1 affect-version: 2.5 Alpha 1 component: engine priority: minor resolution: duplicate type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations