[MCOL-1366] subquery multiple column in statement Created: 2018-04-24  Updated: 2018-06-25  Resolved: 2018-06-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: None
Fix Version/s: 1.1.5

Type: New Feature Priority: Minor
Reporter: patrice Assignee: Ravi Prakash (Inactive)
Resolution: Cannot Reproduce Votes: 0
Labels: None

Sprint: 2018-09, 2018-10, 2018-11, 2018-12, 2018-13

 Description   

Hi,

The following is complaining that a and b are not joined. it is my understanding that this should work : https://mariadb.com/kb/en/library/subquery-limitations/

select * from a where (x,y) not in (select distinct x,y from b);

workaround is a little longer to write

 Comments   
Comment by David Thompson (Inactive) [ 2018-04-25 ]

Ravi, can you take a look at this. I wonder if the underlying query rewrite optimization is no longer happening?

Comment by Daniel Lee (Inactive) [ 2018-04-25 ]

I tried the following in 1.1.4-1 and it seemed to work.

MariaDB [mytest]> select * from feed;
----------+

a b

----------+

100 200
10 20
1000 2000
100 200
10 20
1000 2000

----------+
6 rows in set (0.07 sec)

MariaDB [mytest]> select * from toto;
----------+

a b

----------+

100 200
10 20
1000 2000

----------+
3 rows in set (0.05 sec)

MariaDB [mytest]> select * from toto where (a,b) not in (select distinct a,b from feed);
Empty set (0.04 sec)

MariaDB [mytest]> select * from toto where (a,b) in (select distinct a,b from feed);
----------+

a b

----------+

100 200
10 20
1000 2000

----------+
3 rows in set (0.04 sec)

MariaDB [mytest]> select * from toto where (a,b) in (select distinct a,b from feed where a=10);
----------+

a b

----------+

10 20

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

MariaDB [mytest]> select * from toto where (a,b) not in (select distinct a,b from feed where a=10);
----------+

a b

----------+

100 200
1000 2000

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

Comment by patrice [ 2018-04-25 ]

I can't seem to reproduce that one. can be closed, I'll comment on it , if it comes back.

Comment by Ravi Prakash (Inactive) [ 2018-04-26 ]

Did you verify with other data types such as datetime, timestamp, varchar.

Comment by Ravi Prakash (Inactive) [ 2018-06-25 ]

This bug cannot be reproduced. I tried the following SQL statements:
--------------
CREATE TABLE t1 ( c1 int not null, c2 float, c3 char(10)) engine=columnstore
--------------

--------------
insert into t1 values(1,1.0, 'one'), (2,2.0, 'two'), (3, 3.1, 'three')
--------------

--------------
CREATE TABLE t2 ( c1 int not null, c2 float, c3 char(10)) engine=columnstore
--------------

--------------
insert into t2 values(1,1.0, 'one'), (2,2.0, 'two')
--------------

--------------
select * from t1 where (c1, c2) in (select distinct c1, c2 from t2)
--------------

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

c1 c2 c3

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

1 1 one
2 2 two

------------
--------------
select * from t1 where (c1, c2) not in (select distinct c1, c2 from t2)
--------------

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

c1 c2 c3

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

3 3.1 three

-------------
--------------
select * from t1 where (c1, c2) = (select distinct c1, c2 from t2 limit 1)
--------------

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

c1 c2 c3

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

1 1 one

------------
--------------
select * from t1,t2 where (t1.c1, t1.c2) in (select distinct c1, c2 from t2)
--------------

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

MariaDB [test]> select * from t1,t2 where t1.c1 > t2.c1 ;
ERROR 1815 (HY000): Internal error: IDB-1000: 't1' and 't2' are not joined.

In my view, the error can happen only when two tables do not have a equi-join condition. There is another bug on supporting cross-join between two tables.

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