[MCOL-2165] Autoswitch broken in some cases in 1.2.2 Created: 2019-02-12  Updated: 2020-08-25  Resolved: 2019-03-12

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.2.2
Fix Version/s: 1.2.3

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

Issue Links:
Relates
relates to MCOL-1963 GROUP BY doesn't process IN + correla... Closed
relates to MCOL-1964 GROUP BY doesn't process NOT IN + cor... Closed
relates to MCOL-1672 ColumnStore in auto-switch operationa... Closed
relates to MCOL-2147 Semi-join with correllated subquery c... Closed
Sprint: 2019-02, 2019-03

 Description   

Testcase from MCOL-2147

create table cs1 (t varchar(2) , i int) engine = columnstore;
 
create table cs2(t varchar(2) , i int) engine = columnstore;


set infinidb_vtable_mode= 2;
 
select calSetTrace(1);
 
select count(1) from cs1 where exists (select 1 from cs2 where cs1.t = (case cs2.t when 0 then cs1.t else cs2.t end));
 
select calGetTrace();

runs with MCS 1.1.6, because it autoswitch to vtable-mode 0 .

Result of the calGetTrace() is

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
 
BPS PM cs1 3009 (t) 0 1 0 0.002 0

The same output like with vtable = 0 and the join was not executed by the columnstore engine.

With vtable = 1 an error occured, so it is likely, that columnstore engine throw an error and autoswitch to vtable= 0

With 1.2.2 the autoswitch was not happened, the error occured like in vtable=1 and no autoswitch was happened.

 Comments   
Comment by Richard Stracke [ 2019-02-14 ]

Simple Example: (With MCS 1.1.6)

 
 
create table cs1 (t varchar(2) , i int) engine = columnstore;
 
create table cs2(t varchar(2) , i int) engine = columnstore;
 



set infinidb_vtable_mode= 0;
 
select calSetTrace(1);
 
select * from cs1 inner join cs2 on cs1.t = cs2.t;
 
select calGetTrace();

Result:

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
 
BPS PM cs1 3009 (i,t) 0 1 0 0.002 0

The join was not executed in Columnstore.

Same with table_mode 1:

set infinidb_vtable_mode= 1;
 
select calSetTrace(1);
 
select * from cs1 inner join cs2 on cs1.t = cs2.t;
 
select calGetTrace();

Result:

 
 
Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
 
BPS PM cs2 3012 (i,t) 0 1 0 0.002 0
 
BPS PM cs1 3009 (i,t) 0 0 1 0.000 0
 
HJS PM cs1-cs2 3009 - - - - ----- -

The join was executed in Columnstore .

Same with table_mode 2:

set infinidb_vtable_mode= 2;
 
select calSetTrace(1);
 
select * from cs1 inner join cs2 on cs1.t = cs2.t;
 
select calGetTrace();


Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
 
BPS PM cs2 3012 (i,t) 0 1 0 0.002 0
 
BPS PM cs1 3009 (i,t) 0 0 1 0.000 0
 
HJS PM cs1-cs2 3009 - - - - ----- -

Query and join was executed in columnstore as expected.

Now with exists clause:

 
 
set infinidb_vtable_mode= 0;
 
select calSetTrace(1);
 
select count(1) from cs1 where exists (select 1 from cs2 where cs1.t = cs2.t and cs1.t = (case cs2.t when 0 then cs1.t else cs2.t end));
 
select calGetTrace();

Result:

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
 
BPS PM cs1 3009 (t) 0 1 0 0.002 0

As expected, the join was not executed from columnstore (mysqld instead)

With table mode 1:

set infinidb_vtable_mode= 1;
 
select calSetTrace(1);
 
select count(1) from cs1 where exists (select 1 from cs2 where cs1.t = cs2.t and cs1.t = (case cs2.t when 0 then cs1.t else cs2.t end));
 
select calGetTrace();

Result:

ERROR 1815 (HY000): Internal error: IDB-3034: Query is not supported. Unknown column '' in subquery.
 
| NULL |

the same error as in 1.2.2, so the bug already exists in 1.1.6

Now Table mode 2:

set infinidb_vtable_mode= 2;
 
select calSetTrace(1);
 
select count(1) from cs1 where exists (select 1 from cs2 where cs1.t = cs2.t and cs1.t = (case cs2.t when 0 then cs1.t else cs2.t end));
 
select calGetTrace();
 



Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
 
BPS PM cs1 3009 (t) 0 1 0 0.002 0

The join is not executed in columnstore, the same execution order as with vtable = 0

Comment by Roman [ 2019-03-04 ]

Please review the fix that allows CS to process the query in both disabled_vtable and autoswitch modes.

Comment by Daniel Lee (Inactive) [ 2019-03-12 ]

Build verified 1.2.3-1

server commit:
2243073
engine commit:
2509d83

vtable mode 1 returns the following error as expected.

ERROR 1815 (HY000): Internal error: IDB-3034: Query is not supported. Unknown column ‘’ in subquery.

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