[MCOL-3814] join on view fails with "not joined" error Created: 2020-02-20  Updated: 2020-09-21  Resolved: 2020-08-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: MDB Plugin
Affects Version/s: 1.2.5
Fix Version/s: 1.4.5, 5.4.1

Type: Bug Priority: Major
Reporter: David Hall (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MCOL-4184 Table join not working in 1.4, previo... Open
Sprint: 2020-7
Epic/Theme: 1.4_regressions

 Description   

Consider the following from queries/working_tpch1_compareLogOnly/partitionOptimization/vTpch02b.sql

drop view if exists vTemp;
create view vTemp as select * from v_part where p_size=15 and p_type like '%BRASS';

select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
from vTemp, supplier, partsupp pso, nation, region
where p_partkey = pso.ps_partkey
and s_suppkey = pso.ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
and pso.ps_supplycost = (select min(psi.ps_supplycost)
from partsupp psi, supplier, nation, region
where pso.ps_partkey = psi.ps_partkey
and s_suppkey = psi.ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE')
order by s_acctbal desc,
n_name,
s_name,
p_partkey
limit 100;

In 1.1, this produces the correct answer.
In 1.2.5 forward, this produces:
ERROR 1815 (HY000) at line 4: Internal error: IDB-1000: 'vtemp' and 'vTemp, nation, pso, region, sub-query, supplier' are not joined.



 Comments   
Comment by David Hall (Inactive) [ 2020-03-11 ]

This also appears to affect
SELECT DISTINCT t2.gid AS lgid,(SELECT t1.name FROM t1, t2 WHERE t1.lid = t2.lid AND t2.gid = t1.lid and t1.lid =1 ) as clid FROM t2 order by 1, 2;
found in working_tpch1_compareLogOnly/view/mts_view.sql.
This may or may not have the same root cause.

Comment by David Hall (Inactive) [ 2020-08-12 ]

This was broken way back in 1.1 at MCOL-1989 Fix view in view subquery outer join

Comment by Gagan Goel (Inactive) [ 2020-08-18 ]

David.Hall Can you reinstate all the relevant regression tests for this fix?

Comment by Daniel Lee (Inactive) [ 2020-08-25 ]

Builds verified: 1.5.4-1 (drone b462), 1.4.4-1 (drone b452)
Release number for the 1.4 branch has not yet been changed to 1.4.5-1

Generated at Thu Feb 08 02:45:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.