[MCOL-662] Unexpected results in cross engine join Created: 2017-04-12  Updated: 2017-10-25  Resolved: 2017-10-25

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

Type: Bug Priority: Minor
Reporter: Geoff Cleaves Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 7

Attachments: File prepare_test.sql    
Sprint: 2017-9, 2017-20, 2017-21

 Description   

The following query produces unexpected results. The predicates are acting as an OR instead of AND.

select
 
 fk
 
 ,tags
 
 ,sum(clicks) 
from
 
 cs_test cs
 
inner join
 
 dim_test d on cs.fk=d.id
 
where
 
 locate('cpa',tags) > 0
 
 AND locate('1clickflow',tags) > 0
 
group by 1,2

Results:

fk tags clicks
1 cpa 60
3 cpa|1clickflow 1200
2 cpa|1clickflow 6

Expected results:

fk tags clicks
3 cpa|1clickflow 1200
2 cpa|1clickflow 6

The query is produced by Tableau and cannot be tweaked. mysqldump file attached in order to create example tables.

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

The problem appears to be that the second function in the where condition doesn't appear to be executed at all. I shall look into this further as soon as possible.

Comment by David Thompson (Inactive) [ 2017-04-13 ]

Also to confirm this is isolated to the cross engine capability so a possible workaround is to make dim_test a columnstore table. Thanks for filing!

Comment by Andrew Hutchings (Inactive) [ 2017-10-23 ]

Filters were overwriting each other in CrossEngine step. Fix was to store the filters in a vector.

For QA: description has a pretty good test case (attachment has the tables and data)

Comment by Daniel Lee (Inactive) [ 2017-10-24 ]

Build verified: Github source for 1.1.1-1

/root/columnstore/mariadb-columnstore-server
commit f6cd94ea167789970db7b5b501569a6549495d10
Merge: 3d846d3 91b2553
Author: David.Hall <david.hall@mariadb.com>
Date: Tue Oct 24 09:15:58 2017 -0500

Merge pull request #72 from mariadb-corporation/MCOL-982

MCOL-982 Merge MariaDB 10.2.9

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 751f9fbd2f26026983915a0677d6d600be273073
Author: david hill <david.hill@mariadb.com>
Date: Tue Oct 24 14:05:48 2017 -0500

removed duplicaue entries

Comment by Daniel Lee (Inactive) [ 2017-10-25 ]

Build verified: Github source for 1.0.12-1

/root/columnstore/mariadb-columnstore-server
commit a42eb6d1e74e44c9e8fd9bb8290e6ce7dbf909f5
Merge: 2965fc8 6a14ced
Author: David.Hall <david.hall@mariadb.com>
Date: Tue Oct 3 10:12:33 2017 -0500

Merge pull request #69 from mariadb-corporation/MCOL-940

MCOL-940

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 22f5c04f854d8571fa81ac33a26edc256d3acd48
Merge: 2f19dc2 c132f1b
Author: David.Hall <david.hall@mariadb.com>
Date: Tue Oct 24 17:39:45 2017 -0500

Merge pull request #300 from mariadb-corporation/MCOL-973-1.0

MCOL-973 remove reference to TEXT in the 1.0 code

Verified with same tests

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