Issue Details (XML | Word | Printable)

Key: CORE-3176
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel VakeŇ°
Votes: 1
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

View with "subselect" column join table and not use index

Created: 13/Oct/10 08:27 AM   Updated: 04/Jun/13 08:39 AM
Component/s: Engine
Affects Version/s: 2.5.0
Fix Version/s: 2.5.1

Time Tracking:
Not Specified

Environment: Linux 2.6.18-194.17.1.el5 #1 SMP x86_64 GNU/Linux
Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
CREATE TABLE TMP
(
  ID Integer NOT NULL,
  CONSTRAINT PK_TMP_1 PRIMARY KEY (ID)
);

CREATE VIEW TMP_VIEW (ID1, ID2)
AS
SELECT 1,(SELECT 1 FROM RDB$DATABASE) FROM RDB$DATABASE;


/*** With view + subselect column ***/

SELECT * FROM tmp_view TV LEFT JOIN tmp T ON T.id=TV.id2

> PLAN JOIN (TV RDB$DATABASE NATURAL, T NATURAL)

- PK_TMP_1 is not used.


/*** With view + normal column ***/

SELECT * FROM tmp_view TV LEFT JOIN tmp T ON T.id=TV.id1

> PLAN JOIN (TV RDB$DATABASE NATURAL, T INDEX (PK_TMP_1))

- PK_TMP_1 is used.


/*** Without view + subselect column ***/

SELECT * FROM RDB$DATABASE TV LEFT JOIN tmp T ON T.id=(SELECT 1 FROM RDB$DATABASE)

> PLAN JOIN (TV NATURAL, T INDEX (PK_TMP_1))

- PK_TMP_1 is used.



In FB 2.1.3 and FB 2.5 RC3 is PK_TMP_1 used.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 13/Oct/10 09:10 AM
This regression is caused by the fix for CORE-3090.