[MCOL-334] Bad NULL match in view subquery Created: 2016-09-26  Updated: 2022-07-25  Resolved: 2016-11-08

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.0.3
Fix Version/s: 1.0.5

Type: Bug Priority: Minor
Reporter: Andrew Hutchings (Inactive) Assignee: David Hall (Inactive)
Resolution: Fixed Votes: 0
Labels: relnote

Issue Links:
PartOf
is part of MCOL-25 001 Working Folder UM Join Test Fails Closed
is part of MCOL-344 Set test001 working_tpch1 to Tr mode Closed
Sprint: 2016-19, 2016-21, 2016-22

 Description   

working_tpch1/view/view.sql

The subquery view test matches a NULL where it shouldn't.

Query set from the test run to reproduce this:

create or replace view v1 as (select sub1.c1 s1c1, sub1.c2 s1c2, sub2.c1 s2c1, sub2.c2 s2c2 from sub1 left join sub2 on sub1.c1 = sub2.c1);
 
create or replace view v2 as (select sub3.c1 s3c1, sub4.c1 s4c1, sub3.c2 s3c2, sub4.c2 s4c2 from sub3 right join sub4 on sub3.c1 = sub4.c1);
 
select * from v2 where s3c1 in (select s1c1 from v1) order by 1,2,3,4;

All the fields are INTs, when a NULL is returned by the IN subquery it matches NULL rows in v2.s3c1. This shouldn't happen and only happens with vtable mode enabled.

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2016-10-06 ]

Spent some time trying some different things. If vtable mode is disabled this problem doesn't occur. So we should probably be looking at vtable handling.

Comment by Andrew Hutchings (Inactive) [ 2016-10-07 ]

With Vtable:

ha_calpont::extra("$vtable_4", 45: UNKNOWN ENUM!)
 
<<< Parse vtable: drop table if exists infinidb_vtable.$vtable_4 restrict
 
ha_calpont_impl_close
 
ha_calpont_impl_delete_table: infinidb_vtable/tmp/#sql7371_4_1
 
ha_calpont::extra("sub4", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub4", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub4", 43: UNKNOWN ENUM!)
 
ha_calpont::extra("sub3", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub3", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub3", 43: UNKNOWN ENUM!)
 
ha_calpont::extra("sub1", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub1", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub1", 43: UNKNOWN ENUM!)
 
ha_calpont::extra("sub2", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub2", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub2", 43: UNKNOWN ENUM!)
 
<<< Parse vtable: create temporary table infinidb_vtable.$vtable_4 engine = aria as select * from v2 where s3c1 in (select s1c1 from v1)
 
store_lock
 
store_lock
 
store_lock
 
store_lock
 
external_lock for sub4
 
external_lock for sub3
 
external_lock for sub1
 
external_lock for sub2
 
info
 
info
 
info
 
info
 
rnd_init for table sub2
 
setting returnAll on tpch1.sub1(sub1/v1) engineType=InfiniDB
 
setting returnAll on tpch1.sub1(sub1/v1) engineType=InfiniDB
 
setting returnAll on tpch1.sub4(sub4/v2) engineType=InfiniDB
 
setting returnAll on tpch1.sub4(sub4/v2) engineType=InfiniDB
 
---------------- EXECUTION PLAN ----------------
 
>SELECT limit: 0 - 18446744073709551615
 
MAIN
 
>>Returned Columns
 
SimpleColumn tpch1.sub3.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub3/c1/v2/32635/int/sub3/s3c1/0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
SimpleColumn tpch1.sub4.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub4/c1/v2/32643/int/sub4/s4c1/0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
SimpleColumn tpch1.sub3.c2
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub3/c2/v2/32636/int/sub3/s3c2/0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
SimpleColumn tpch1.sub4.c2
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub4/c2/v2/32644/int/sub4/s4c2/0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
>>From Tables
 
tpch1.sub4(sub4/v2) engineType=InfiniDB
 
tpch1.sub3(sub3/v2) engineType=InfiniDB
 
>>Filters
 
ExistsFilter correlated=1 notExists=0
 
>SELECT limit: 0 - 18446744073709551615
 
WHERE
 
>>Returned Columns
 
SimpleColumn tpch1.sub1.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub1/c1/v1/32619/int/sub1//0/0/0/0/8/InfiniDB/-1
 
 
 
 
 
SimpleColumn tpch1.sub1.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub1/c1/v1/32619/int/sub1/s1c1/0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
>>From Tables
 
tpch1.sub1(sub1/v1) engineType=InfiniDB
 
tpch1.sub2(sub2/v1) engineType=InfiniDB
 
>>Filters
 
SimpleFilter(indexflag=0 joinFlag= 0 card= 0)
 
  SimpleColumn tpch1.sub3.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub3/c1/v2/32635/int/sub3/s3c1/0/-1/0/34/0/InfiniDB/-1
 
 
 
  Operator: = fOp=4 opType=9  SimpleColumn tpch1.sub1.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub1/c1/v1/32619/int/sub1//0/0/0/0/0/InfiniDB/-1
 
 
 
 
 
OuterJoinOnFilter
 
SimpleFilter(indexflag=0 joinFlag= 0 card= 0)
 
  SimpleColumn tpch1.sub1.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub1/c1/v1/32619/int/sub1//1/-1/0/0/0/InfiniDB/-1
 
 
 
  Operator: = fOp=4 opType=9  SimpleColumn tpch1.sub2.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub2/c1/v1/32627/int/sub2//0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
End OuterJoinOnFilter
 
 
 
OuterJoinOnFilter
 
SimpleFilter(indexflag=0 joinFlag= 0 card= 0)
 
  SimpleColumn tpch1.sub1.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub1/c1/v1/32619/int/sub1//1/-1/0/0/0/InfiniDB/-1
 
 
 
  Operator: = fOp=4 opType=9  SimpleColumn tpch1.sub2.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub2/c1/v1/32627/int/sub2//0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
End OuterJoinOnFilter
 
 
 
Operator: and fOp=12 opType=5
 
Operator: and fOp=12 opType=5
 
SessionID: 4
 
TxnID: -1
 
VerID:   SCN: 0
 
  Txns: 
TraceFlags: 0
 
StatementID: 0
 
DistUnionNum: 0
 
Limit: 0 - 18446744073709551615
 
String table threshold: 20
 
--- Column Map ---
 
c1 : 0x7f9514256bf0
 
c1 : 0x7f9514256f50
 
c1 : 0x7f95142572b0
 
c1 : 0x7f9514257610
 
UUID: 89b94546-dff2-4665-a4d7-4f0f5bc7b4b9
 
QueryType: SELECT
 
 
 
OuterJoinOnFilter
 
SimpleFilter(indexflag=0 joinFlag= 0 card= 0)
 
  SimpleColumn tpch1.sub3.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub3/c1/v2/32635/int/sub3//0/-1/0/0/0/InfiniDB/-1
 
 
 
  Operator: = fOp=4 opType=9  SimpleColumn tpch1.sub4.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub4/c1/v2/32643/int/sub4//1/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
End OuterJoinOnFilter
 
 
 
OuterJoinOnFilter
 
SimpleFilter(indexflag=0 joinFlag= 0 card= 0)
 
  SimpleColumn tpch1.sub3.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub3/c1/v2/32635/int/sub3//0/-1/0/0/0/InfiniDB/-1
 
 
 
  Operator: = fOp=4 opType=9  SimpleColumn tpch1.sub4.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub4/c1/v2/32643/int/sub4//1/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
End OuterJoinOnFilter
 
 
 
Operator: and fOp=12 opType=5
 
Operator: and fOp=12 opType=5
 
SessionID: 4
 
TxnID: 0
 
VerID:   SCN: 30461
 
  Txns: 
TraceFlags: 0
 
StatementID: 0
 
DistUnionNum: 0
 
Limit: 0 - 18446744073709551615
 
String table threshold: 20
 
--- Column Map ---
 
c1 : 0x7f951425a1e0
 
c1 : 0x7f951425a540
 
c1 : 0x7f951425a8a0
 
c1 : 0x7f951425ac00
 
c2 : 0x7f951425af60
 
c2 : 0x7f951425b2c0
 
UUID: 500274e8-6b3b-4dfa-b5a0-c0c767dce516
 
QueryType: SELECT
 
 
 
-------------- EXECUTION PLAN END --------------
 
 
 
ha_calpont::extra("sub4", 4: HA_EXTRA_NO_CACHE)
 
ha_calpont::extra("sub3", 4: HA_EXTRA_NO_CACHE)
 
ha_calpont::extra("sub1", 4: HA_EXTRA_NO_CACHE)
 
ha_calpont::extra("sub2", 4: HA_EXTRA_NO_CACHE)
 
ha_calpont::extra("sub2", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub1", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub3", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub4", 46: UNKNOWN ENUM!)
 
external_lock for sub4
 
external_lock for sub3
 
external_lock for sub1
 
external_lock for sub2
 
ha_calpont::extra("sub2", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub1", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub3", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub4", 46: UNKNOWN ENUM!)
 
<<< Parse vtable: alter table infinidb_vtable.$vtable_4 engine=infinidb comment='SCHEMA SYNC ONLY'
 
ha_calpont_impl_create_: /tmp/#sql7371_4_3
 
ha_calpont_impl_open: /tmp/#sql7371_4_3, 2, 18
 
ha_calpont::extra("#sql-7371_4", 5: HA_EXTRA_NO_READCHECK)
 
ha_calpont::extra("#sql-7371_4", 45: UNKNOWN ENUM!)
 
external_lock for #sql-7371_4
 
ha_calpont::extra("#sql-7371_4", 26: HA_EXTRA_NO_IGNORE_DUP_KEY)
 
external_lock for #sql-7371_4
 
ha_calpont::extra("#sql-7371_4", 42: HA_EXTRA_PREPARE_FOR_RENAME)
 
ha_calpont::extra("$vtable_4", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("$vtable_4", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("$vtable_4", 43: UNKNOWN ENUM!)
 
<<< V-TABLE insert select *  from infinidb_vtable.$vtable_4
 
store_lock
 
external_lock for $vtable_4
 
info
 
rnd_init for table $vtable_4
 
ha_calpont::extra("$vtable_4", 3: HA_EXTRA_CACHE)
 
fetchNextRow done for table $vtable_4 rows = 31
 
rnd_end for table $vtable_4
 
ha_calpont::extra("$vtable_4", 4: HA_EXTRA_NO_CACHE)
 
external_lock for $vtable_4
 
ha_calpont::extra("$vtable_4", 46: UNKNOWN ENUM!)

Without vtable:

ha_calpont::extra("sub4", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub4", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub4", 43: UNKNOWN ENUM!)
 
ha_calpont::extra("sub3", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub3", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub3", 43: UNKNOWN ENUM!)
 
ha_calpont::extra("sub1", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub1", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub1", 43: UNKNOWN ENUM!)
 
ha_calpont::extra("sub2", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub2", 45: UNKNOWN ENUM!)
 
ha_calpont::extra("sub2", 43: UNKNOWN ENUM!)
 
store_lock
 
store_lock
 
store_lock
 
store_lock
 
external_lock for sub4
 
external_lock for sub3
 
external_lock for sub1
 
external_lock for sub2
 
info
 
info
 
info
 
info
 
rnd_init for table sub4
 
sub4 send plan:
 
>SELECT limit: 0 - 18446744073709551615
 
MAIN
 
>>Returned Columns
 
SimpleColumn tpch1.sub4.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub4/c1//32643/int/sub4//0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
SimpleColumn tpch1.sub4.c2
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub4/c2//32644/int/sub4//0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
>>From Tables
 
tpch1.sub4(sub4/) engineType=InfiniDB
 
>>Filters
 
empty filter tree
 
SessionID: 4
 
TxnID: 0
 
VerID:   SCN: 30461
 
  Txns: 
TraceFlags: 32768
 
StatementID: 0
 
DistUnionNum: 0
 
Limit: 0 - 18446744073709551615
 
String table threshold: 20
 
--- Column Map ---
 
c1 : 0x7f951423da10
 
c2 : 0x7f951420e650
 
UUID: 13c1b5fa-5274-4159-aed5-f7cf084e33a8
 
QueryType: SELECT
 
 
 
ha_calpont::extra("sub4", 3: HA_EXTRA_CACHE)
 
fetchNextRow done for table sub4 rows = 11
 
rnd_init for table sub3
 
sub3 send plan:
 
>SELECT limit: 0 - 18446744073709551615
 
MAIN
 
>>Returned Columns
 
SimpleColumn tpch1.sub3.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub3/c1//32635/int/sub3//0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
SimpleColumn tpch1.sub3.c2
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub3/c2//32636/int/sub3//0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
>>From Tables
 
tpch1.sub3(sub3/) engineType=InfiniDB
 
>>Filters
 
empty filter tree
 
SessionID: 4
 
TxnID: 0
 
VerID:   SCN: 30461
 
  Txns: 
TraceFlags: 32768
 
StatementID: 0
 
DistUnionNum: 0
 
Limit: 0 - 18446744073709551615
 
String table threshold: 20
 
--- Column Map ---
 
c1 : 0x7f951422bbd0
 
c2 : 0x7f951422c000
 
UUID: 1816df48-4c90-4911-9003-f27a19866689
 
QueryType: SELECT
 
 
 
ha_calpont::extra("sub3", 3: HA_EXTRA_CACHE)
 
ha_calpont_impl_cond_push: sub3
 
rnd_init for table sub1
 
sub1 send plan:
 
>SELECT limit: 0 - 18446744073709551615
 
MAIN
 
>>Returned Columns
 
SimpleColumn tpch1.sub1.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub1/c1//32619/int/sub1//0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
>>From Tables
 
tpch1.sub1(sub1/) engineType=InfiniDB
 
>>Filters
 
empty filter tree
 
SessionID: 4
 
TxnID: 0
 
VerID:   SCN: 30461
 
  Txns: 
TraceFlags: 32768
 
StatementID: 0
 
DistUnionNum: 0
 
Limit: 0 - 18446744073709551615
 
String table threshold: 20
 
--- Column Map ---
 
c1 : 0x7f951422f660
 
UUID: 2a206a67-799e-4aff-8cdc-d83458688c1f
 
QueryType: SELECT
 
 
 
ha_calpont::extra("sub1", 3: HA_EXTRA_CACHE)
 
fetchNextRow done for table sub1 rows = 6
 
rnd_init for table sub2
 
sub2 send plan:
 
>SELECT limit: 0 - 18446744073709551615
 
MAIN
 
>>Returned Columns
 
SimpleColumn tpch1.sub2.c1
 
  s/t/c/v/o/ct/TA/CA/RA/#/card/join/source/engine/colPos: tpch1/sub2/c1//32627/int/sub2//0/-1/0/0/0/InfiniDB/-1
 
 
 
 
 
>>From Tables
 
tpch1.sub2(sub2/) engineType=InfiniDB
 
>>Filters
 
empty filter tree
 
SessionID: 4
 
TxnID: 0
 
VerID:   SCN: 30461
 
  Txns: 
TraceFlags: 32768
 
StatementID: 0
 
DistUnionNum: 0
 
Limit: 0 - 18446744073709551615
 
String table threshold: 20
 
--- Column Map ---
 
c1 : 0x7f9514233900
 
UUID: a3641456-c9c6-4203-bb22-5fb46de1aea4
 
QueryType: SELECT
 
 
 
ha_calpont::extra("sub2", 3: HA_EXTRA_CACHE)
 
ha_calpont_impl_cond_push: sub2
 
fetchNextRow done for table sub2 rows = 7
 
fetchNextRow done for table sub3 rows = 6
 
rnd_end for table sub4
 
ha_calpont::extra("sub4", 4: HA_EXTRA_NO_CACHE)
 
rnd_end for table sub3
 
ha_calpont::extra("sub3", 4: HA_EXTRA_NO_CACHE)
 
rnd_end for table sub1
 
ha_calpont::extra("sub1", 4: HA_EXTRA_NO_CACHE)
 
rnd_end for table sub2
 
ha_calpont::extra("sub2", 4: HA_EXTRA_NO_CACHE)
 
external_lock for sub4
 
external_lock for sub3
 
external_lock for sub1
 
external_lock for sub2
 
ha_calpont::extra("sub2", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub1", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub3", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub4", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub2", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub1", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub3", 46: UNKNOWN ENUM!)
 
ha_calpont::extra("sub4", 46: UNKNOWN ENUM!)

Comment by Andrew Hutchings (Inactive) [ 2016-10-08 ]

NOTE: test has been commented out in view.sql. When fixed this should be re-enabled.

Comment by Andrew Hutchings (Inactive) [ 2016-10-08 ]

Downgraded this as there is a workaround and it should not block Beta. It should be documented

Comment by Andrew Hutchings (Inactive) [ 2016-10-10 ]

To be clear the workaround is set infinidb_vtable_mode=0; before executing IN queries on views.

Comment by Andrew Hutchings (Inactive) [ 2016-10-21 ]

With original query:

getSelectPlan()
 
------------------ WHERE -----------------------
 
REF FIELD_ITEM: tpch1.sub3.c1
 
SUBSELECT Item: IN
 
UNKNOWN REF ITEM type 19
 
FIELD_ITEM: tpch1.sub1.c1
 
FUNC_ITEM: = (1)
 
Finish subselect item traversing
 
FUNC_ITEM: <in_optimizer> (0)
 
------------------------------------------------
 
 
 
getSelectPlan()
 
------------------ WHERE -----------------------
 
UNKNOWN REF ITEM type 19
 
FIELD_ITEM: tpch1.sub1.c1
 
FUNC_ITEM: = (1)
 
------------------------------------------------
 
 
 
FIELD_ITEM: tpch1.sub1.c1
 
FIELD_ITEM: tpch1.sub2.c1
 
FUNC_ITEM: = (1)
 
sub2 outer table expression: 
FIELD_ITEM: tpch1.sub1.c1
 
FIELD_ITEM: tpch1.sub2.c1
 
FUNC_ITEM: = (1)
 
------------------- SELECT --------------------
 
REF FIELD_ITEM: tpch1.sub1.c1
 
-----------------------------------------------
 
 
 
FIELD_ITEM: tpch1.sub3.c1
 
FIELD_ITEM: tpch1.sub4.c1
 
FUNC_ITEM: = (1)
 
sub3 outer table expression: 
FIELD_ITEM: tpch1.sub3.c1
 
FIELD_ITEM: tpch1.sub4.c1
 
FUNC_ITEM: = (1)
 
------------------- SELECT --------------------
 
REF FIELD_ITEM: tpch1.sub3.c1
 
REF FIELD_ITEM: tpch1.sub4.c1
 
REF FIELD_ITEM: tpch1.sub3.c2
 
REF FIELD_ITEM: tpch1.sub4.c2
 
-----------------------------------------------

Views removed:

select sub3.c1 s3c1, sub4.c1 s4c1, sub3.c2 s3c2, sub4.c2 s4c2 from sub3 right join sub4 on sub3.c1 = sub4.c1 where sub3.c1 in (select sub1.c1 s1c1 from sub1 left join sub2 on sub1.c1 = sub2.c1) order by 1,2,3,4;


getSelectPlan()
 
tpch1.sub3(sub3/) engineType=InfiniDB
 
tpch1.sub4(sub4/) engineType=InfiniDB
 
------------------ WHERE -----------------------
 
FIELD_ITEM: tpch1.sub3.c1
 
SUBSELECT Item: IN
 
CACHED FIELD_ITEM: tpch1.sub3.c1
 
FIELD_ITEM: tpch1.sub1.c1
 
FUNC_ITEM: = (1)
 
Finish subselect item traversing
 
FUNC_ITEM: <in_optimizer> (0)
 
------------------------------------------------
 
 
 
getSelectPlan()
 
tpch1.sub1(sub1/) engineType=InfiniDB
 
tpch1.sub2(sub2/) engineType=InfiniDB
 
------------------ WHERE -----------------------
 
CACHED FIELD_ITEM: tpch1.sub3.c1
 
FIELD_ITEM: tpch1.sub1.c1
 
FUNC_ITEM: = (1)
 
------------------------------------------------
 
 
 
sub2 outer table expression: 
FIELD_ITEM: tpch1.sub1.c1
 
FIELD_ITEM: tpch1.sub2.c1
 
FUNC_ITEM: = (1)
 
------------------- SELECT --------------------
 
FIELD_ITEM: tpch1.sub1.c1
 
-----------------------------------------------
 
 
 
sub3 outer table expression: 
FIELD_ITEM: tpch1.sub3.c1
 
FIELD_ITEM: tpch1.sub4.c1
 
FUNC_ITEM: = (1)
 
------------------- SELECT --------------------
 
FIELD_ITEM: tpch1.sub3.c1
 
FIELD_ITEM: tpch1.sub4.c1
 
FIELD_ITEM: tpch1.sub3.c2
 
FIELD_ITEM: tpch1.sub4.c2
 
-----------------------------------------------

Comment by Andrew Hutchings (Inactive) [ 2016-10-21 ]

ExeMgr:

OID-3559 EstimatedRowCount-2048
 
OID-3567 EstimatedRowCount-2048
 
jointype[0] = 0x2
 
OID-3583 EstimatedRowCount-2048
 
OID-3575 EstimatedRowCount-2048
 
jointype[0] = 0x9
 
jointype[0] = 0x2
 
PM join (0)
 
PMJoinerCount = 1
 
serializing joiner into 211 bytes
 
serializing joiner into 24 bytes
 
PM join (0)
 
PMJoinerCount = 1
 
serializing joiner into 156 bytes
 
serializing joiner into 24 bytes
 
PM join (0)
 
PMJoinerCount = 1
 
serializing joiner into 186 bytes
 
serializing joiner into 24 bytes

Views removed:

OID-3559 EstimatedRowCount-2048
 
OID-3567 EstimatedRowCount-2048
 
jointype[0] = 0x2
 
OID-3575 EstimatedRowCount-2048
 
OID-3583 EstimatedRowCount-2048
 
jointype[0] = 0x4
 
jointype[1] = 0x9
 
PM join (0)
 
PMJoinerCount = 1
 
serializing joiner into 120 bytes
 
serializing joiner into 24 bytes
 
PM join (0)
 
PM join (1)
 
PMJoinerCount = 2
 
serializing joiner into 168 bytes
 
serializing joiner into 156 bytes
 
serializing joiner into 24 bytes

Comment by Andrew Hutchings (Inactive) [ 2016-10-27 ]

This definitely is not related to MCOL-352

Comment by Andrew Hutchings (Inactive) [ 2016-11-02 ]

After much debugging I've found the cause to be the join order. The join for the 'IN' predicate is being executed before the join inside the view for the left join. This filters sub3.c1 before it is joined with sub4.c1 and the NULLs are the resulting missing rows from the left join filtering.

To fix this I need to make the IN join happen after the left join.

Comment by Andrew Hutchings (Inactive) [ 2016-11-03 ]

Patch makes sure joins inside views are processed before SEMI/ANTI joins on the view results.

Comment by Andrew Hutchings (Inactive) [ 2016-11-03 ]

This should be merged in the regression suite to re-enable the test after merge of the patch:
https://github.com/mariadb-corporation/mariadb-columnstore-regression-test/pull/7

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