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

bad plan joining view including union [CORE4049] #1476

Open
firebird-automations opened this issue Feb 14, 2013 · 10 comments
Open

bad plan joining view including union [CORE4049] #1476

firebird-automations opened this issue Feb 14, 2013 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Attila Molnár (e_pluribus_unum)

Relate to CORE4976

Votes: 3

Hi!

CREATE TABLE a (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE b (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE c (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));

CREATE VIEW v (
id,
code,
name)
AS
SELECT
id, code, name
FROM a
UNION
SELECT
id, code, name
FROM b

SELECT *
FROM c
JOIN v ON v.code = c.code
WHERE c.id=0

This has the following plan now :
PLAN JOIN ((V A NATURAL)
PLAN (V B NATURAL), C INDEX (RDB$8))

This is wrong. It shold be (same as the select with left join) :
PLAN JOIN (C INDEX (RDB$PRIMARY7)(V A INDEX (RDB$2))
PLAN (V B INDEX (RDB$4)))

Index statistics ha no effect at all, the plan is always wrong.

Thank You!

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

create view missed

@firebird-automations
Copy link
Collaborator Author

Modified by: Attila Molnár (e_pluribus_unum)

description: Hi!

CREATE TABLE a (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE b (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE c (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));

SELECT *
FROM c
JOIN v ON v.code = c.code
WHERE http://c.name = 'x'

This has the following plan now :
PLAN JOIN ((V A NATURAL)
PLAN (V B NATURAL), C INDEX (RDB$8))

This is wrong. It shold be (same as the select with left join) :
PLAN JOIN (C INDEX (CI1)(V A INDEX (RDB$2))
PLAN (V B INDEX (RDB$4)))

Index statistics ha no effect at all, the plan is always wrong.

Thank You!

=>

Hi!

CREATE TABLE a (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE b (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE c (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));

CREATE VIEW v (
id,
code,
name)
AS
SELECT
id, code, name
FROM a
UNION
SELECT
id, code, name
FROM b

SELECT *
FROM c
JOIN v ON v.code = c.code
WHERE http://c.name = 'x'

This has the following plan now :
PLAN JOIN ((V A NATURAL)
PLAN (V B NATURAL), C INDEX (RDB$8))

This is wrong. It shold be (same as the select with left join) :
PLAN JOIN (C INDEX (CI1)(V A INDEX (RDB$2))
PLAN (V B INDEX (RDB$4)))

Index statistics ha no effect at all, the plan is always wrong.

Thank You!

@firebird-automations
Copy link
Collaborator Author

Modified by: Attila Molnár (e_pluribus_unum)

description: Hi!

CREATE TABLE a (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE b (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE c (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));

CREATE VIEW v (
id,
code,
name)
AS
SELECT
id, code, name
FROM a
UNION
SELECT
id, code, name
FROM b

SELECT *
FROM c
JOIN v ON v.code = c.code
WHERE http://c.name = 'x'

This has the following plan now :
PLAN JOIN ((V A NATURAL)
PLAN (V B NATURAL), C INDEX (RDB$8))

This is wrong. It shold be (same as the select with left join) :
PLAN JOIN (C INDEX (CI1)(V A INDEX (RDB$2))
PLAN (V B INDEX (RDB$4)))

Index statistics ha no effect at all, the plan is always wrong.

Thank You!

=>

Hi!

CREATE TABLE a (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE b (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE c (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));

CREATE VIEW v (
id,
code,
name)
AS
SELECT
id, code, name
FROM a
UNION
SELECT
id, code, name
FROM b

SELECT *
FROM c
JOIN v ON v.code = c.code
WHERE c.id=0

This has the following plan now :
PLAN JOIN ((V A NATURAL)
PLAN (V B NATURAL), C INDEX (RDB$8))

This is wrong. It shold be (same as the select with left join) :
PLAN JOIN (C INDEX (CI1)(V A INDEX (RDB$2))
PLAN (V B INDEX (RDB$4)))

Index statistics ha no effect at all, the plan is always wrong.

Thank You!

@firebird-automations
Copy link
Collaborator Author

Modified by: Attila Molnár (e_pluribus_unum)

description: Hi!

CREATE TABLE a (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE b (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE c (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));

CREATE VIEW v (
id,
code,
name)
AS
SELECT
id, code, name
FROM a
UNION
SELECT
id, code, name
FROM b

SELECT *
FROM c
JOIN v ON v.code = c.code
WHERE c.id=0

This has the following plan now :
PLAN JOIN ((V A NATURAL)
PLAN (V B NATURAL), C INDEX (RDB$8))

This is wrong. It shold be (same as the select with left join) :
PLAN JOIN (C INDEX (CI1)(V A INDEX (RDB$2))
PLAN (V B INDEX (RDB$4)))

Index statistics ha no effect at all, the plan is always wrong.

Thank You!

=>

Hi!

CREATE TABLE a (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE b (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));
CREATE TABLE c (id INTEGER PRIMARY KEY, code VARCHAR (10) UNIQUE, name VARCHAR (10));

CREATE VIEW v (
id,
code,
name)
AS
SELECT
id, code, name
FROM a
UNION
SELECT
id, code, name
FROM b

SELECT *
FROM c
JOIN v ON v.code = c.code
WHERE c.id=0

This has the following plan now :
PLAN JOIN ((V A NATURAL)
PLAN (V B NATURAL), C INDEX (RDB$8))

This is wrong. It shold be (same as the select with left join) :
PLAN JOIN (C INDEX (RDB$PRIMARY7)(V A INDEX (RDB$2))
PLAN (V B INDEX (RDB$4)))

Index statistics ha no effect at all, the plan is always wrong.

Thank You!

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

FWIW, unions are always optimized as the leading stream, i.e the first one in the join order (unless it's an outer join). It affects what indices could be used for the underlying tables. Unfortunately, it cannot be fixed easily. Maybe in FB3, but no guarantees yet. For the time being, use a left join as a workaround.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.1.5 [ 10420 ]

Version: 2.5.1 [ 10333 ]

Version: 2.1.4 [ 10361 ]

Version: 2.5.0 [ 10221 ]

Version: 2.0.6 [ 10303 ]

Version: 3.0 Initial [ 10301 ]

Version: 2.1.3 [ 10302 ]

Version: 2.1.2 [ 10270 ]

Version: 2.0.5 [ 10222 ]

Version: 2.1.1 [ 10223 ]

Version: 2.1.0 [ 10041 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Valdir Stiebe Junior (ogecrom)

I've got the same results in a inner join with a view on a TABLE inner join VIEW case (without union inside the view). The inner join plan use NATURAL while the left join plan use the corrects indexes.

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue relate to CORE4976 [ CORE4976 ]

@EPluribusUnum
Copy link

This is still an issue (Tested with FB30 and FB40)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment