bad plan joining view including union [CORE4049]

[firebird-automations]

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]

Commented by: Attila Molnár (e_pluribus_unum)

create view missed

[firebird-automations]

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]

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]

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]

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]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

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]

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]

Modified by: Sean Leyne (seanleyne)

Link: This issue relate to CORE4976 [ CORE4976 ]

[EPluribusUnum]

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