[MCOL-677] Columnstore executes join on incompatible types Created: 2017-04-23  Updated: 2017-09-11  Resolved: 2017-05-02

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.0.8, 1.1.0
Fix Version/s: 1.1.0

Type: Bug Priority: Major
Reporter: Mateusz Zakarczemny Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: relnote

Issue Links:
Relates
relates to MCOL-695 Implement joins between CHAR/VARCHAR ... Closed
Sprint: 2017-8, 2017-9

 Description    

create table join1  (x varchar(2), y varchar(10)) engine=columnstore;
 
create table join2  (x varchar(2), y date, z int) engine=columnstore;
 
 
 
insert into join1 values("11", "2017-01-01");
 
insert into join1 values("12", "2017-01-02");
 
insert into join2 values("11", "2017-01-01", 22);
 
insert into join2 values("12", "2017-02-01", 23);

Join on two columns

select a.*, b.*
 
from join1 a
 
left join join2 b
 
on a.x = b.x and a.y = b.y

Produce

x	y	x	y	z
 
11	2017-01-01	11	2017-01-01	22

But join on single column:

select a.*, b.*
 
from join1 a
 
left join join2 b
 
on a.y = b.y

Results in error:

Internal error: IDB-1002: 'b' and 'a' have incompatible column type specified for join condition.

In think in both cases we should have error. I also observed on larger table that even if in first case query was executes results are sometimes incorrect (even it it is left join it behave like ordinary join)

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-04-24 ]

Confirmed...

This gets marked as an incompatible join in jlf_execplantojoblist.cpp but we skip 1c in jlf_tuplejoblist.cpp because we have a valid join on the tables as well. This means that the incompatible join map doesn't get converted into an error. Suggested fix is to move the incompatible join map check from jlf_tuplejoblist.cpp further up the code.

Comment by Andrew Hutchings (Inactive) [ 2017-04-25 ]

Patch in develop and regression suite.

I have not yet backported this to 1.0 as it affects behaviour of joins in several other cases.

Comment by Daniel Lee (Inactive) [ 2017-05-02 ]

Build tested:

[root@localhost mariadb-columnstore-server]# git show
commit 5a090c64bced6532578dd8910f15530fd37fce2c
Merge: 9efb0a7 b062156
Author: david hill <david.hill@mariadb.com>
Date: Thu Apr 27 16:13:15 2017 -0500

Merge pull request #45 from mariadb-corporation/FixPackageName

Change the package name to match engines format

[root@localhost mariadb-columnstore-engine]# git show
commit 715a514f155338c616d8408e5a7e7edc6cadba2f
Merge: 6320a59 f6caa88
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Thu Apr 27 17:38:57 2017 +0100

Merge branch 'MCOL-597' into develop

I reproduced the issue in 1.0.8-1. Also in MariaDB 10.2.5, the same queries returned the results as the following:

MariaDB [mytest]> select a., b.
-> from join1 a
-> left join join2 b
-> on a.x = b.x and a.y = b.y
-> ;
----------------------------------

x y x y z

----------------------------------

11 2017-01-01 11 2017-01-01 22
12 2017-01-02 NULL NULL NULL

----------------------------------
2 rows in set (0.00 sec)

MariaDB [mytest]> select a., b.
-> from join1 a
-> left join join2 b
-> on a.y = b.y;
----------------------------------

x y x y z

----------------------------------

11 2017-01-01 11 2017-01-01 22
12 2017-01-02 NULL NULL NULL

----------------------------------
2 rows in set (0.00 sec)

ColumnStore returned the following:

MariaDB [mytest]> select a., b.
-> from join1 a
-> left join join2 b
-> on a.x = b.x and a.y = b.y;
ERROR 1815 (HY000): Internal error: IDB-1002: 'a' and 'b' have incompatible column type specified for join condition.
MariaDB [mytest]> select a., b.
-> from join1 a
-> left join join2 b
-> on a.y = b.y;
ERROR 1815 (HY000): Internal error: IDB-1002: 'a' and 'b' have incompatible column type specified for join condition.

As mentioned in the comment, incompatible data type check has moved higher up in the logic. But should we fixed this issue instead? I am not sure what is involved though.

Comment by Daniel Lee (Inactive) [ 2017-05-02 ]

Created MCOL-695 for tracking the result-not-matching-reference issue. Closing ticket for the intended fix.

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