[MCOL-1030] support non equality based joins and correlated subqueries Created: 2017-11-14  Updated: 2020-08-25  Resolved: 2017-11-27

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.0.11
Fix Version/s: Icebox

Type: New Feature Priority: Major
Reporter: Z Assignee: Andrew Hutchings (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Environment:

CentOS release 6.5 (Final)

Issue Links:
Duplicate
duplicates MCOL-131 Cartesian product should be supported Stalled
Epic Link: ColumnStore Compatibility Improvements

 Description   

CREATE TABLE `test1` (
`c1` int(10) unsigned DEFAULT NULL,
`c2` char(16) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8;

INSERT INTO `test1` VALUES ('1', 'test1');
INSERT INTO `test1` VALUES ('2', 'test2');

CREATE TABLE `test2` (
`c1` int(10) unsigned DEFAULT NULL,
`c2` char(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `test2` VALUES ('1', 'test1');

MariaDB [test]> select * from test1;
-----------+

c1 c2

-----------+

1 test1
2 test2

-----------+
2 rows in set (0.11 sec)

MariaDB [test]> select * from test2;
-----------+

c1 c2

-----------+

1 test1

-----------+
1 row in set (0.00 sec)

select * from test1 t1 where not exists (select c1 from test2 t2 where t1.c1>t2.c1);

ERROR 1815 (HY000): Internal error: IDB-1000: 't1' and 'sub-query' are not joined.

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-11-14 ]

Could you please let us know what the intended use case for this query is?

At the moment this appears to be a cartesian join inside the subquery which isn't supported.

Comment by David Thompson (Inactive) [ 2017-11-14 ]

this is a correlated subquery since you are joining on the subquery table t2 to the outer query table t1. If you change the query to:

select * from test1 t1 where not exists (select c1 from test2 t2 where t1.c1 = t2.c1);

it works correctly.

Also the following query has the same error:

select * from test1 t1 join test2 t2 on t1.c1 > t2.c1;

So this is more of an enhancement to support non equality based joins i think.

Comment by David Thompson (Inactive) [ 2017-11-14 ]

The original query does work with vtable mode 0 (https://mariadb.com/kb/en/library/columnstore-operating-mode/). but will be less performant on large data since the joins are not distributable.

MariaDB [test]> set infinidb_vtable_mode=0
 
    -> ;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> select * from test1 t1 where not exists (select c1 from test2 t2 where t1.c1 = t2.c1);
 
+------+-------+
 
| c1   | c2    |
 
+------+-------+
 
|    2 | test2 |
 
+------+-------+
 
1 row in set (0.01 sec)

Comment by Andrew Hutchings (Inactive) [ 2017-11-27 ]

Marking as duplicate of MCOL-131 since fixing that will also resolve this issue.

Comment by David Hill (Inactive) [ 2019-08-06 ]

Adding a customer issue number, than ran into the issue in there product.

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