[Bug] "insert into TABLE select ..." failed when enable_vectorized_engine=true

[hello-stephen]

Search before asking

• I had searched in the issues and found no similar issues.

Version

branch: master
commit id: 953429e

What's Wrong?

I want to create ssb-flat table and instert data.
I use tools/ssb-tools , loaded ssb data, scale 100, then create table lineorder-flat with sql:

CREATE TABLE `lineorder_flat` (
  `lo_orderkey` bigint(20) NOT NULL COMMENT "",
  `lo_linenumber` bigint(20) NOT NULL COMMENT "",
  `lo_custkey` int(11) NOT NULL COMMENT "",
  `lo_partkey` int(11) NOT NULL COMMENT "",
  `lo_suppkey` int(11) NOT NULL COMMENT "",
  `lo_orderdate` int(11) NOT NULL COMMENT "",
  `lo_orderpriority` varchar(16) NOT NULL COMMENT "",
  `lo_shippriority` int(11) NOT NULL COMMENT "",
  `lo_quantity` bigint(20) NOT NULL COMMENT "",
  `lo_extendedprice` bigint(20) NOT NULL COMMENT "",
  `lo_ordtotalprice` bigint(20) NOT NULL COMMENT "",
  `lo_discount` bigint(20) NOT NULL COMMENT "",
  `lo_revenue` bigint(20) NOT NULL COMMENT "",
  `lo_supplycost` bigint(20) NOT NULL COMMENT "",
  `lo_tax` bigint(20) NOT NULL COMMENT "",
  `lo_commitdate` bigint(20) NOT NULL COMMENT "",
  `lo_shipmode` varchar(11) NOT NULL COMMENT "",
  `c_name` varchar(26) NOT NULL COMMENT "",
  `c_address` varchar(41) NOT NULL COMMENT "",
  `c_city` varchar(11) NOT NULL COMMENT "",
  `c_nation` varchar(16) NOT NULL COMMENT "",
  `c_region` varchar(13) NOT NULL COMMENT "",
  `c_phone` varchar(16) NOT NULL COMMENT "",
  `c_mktsegment` varchar(11) NOT NULL COMMENT "",
  `s_name` varchar(26) NOT NULL COMMENT "",
  `s_address` varchar(26) NOT NULL COMMENT "",
  `s_city` varchar(11) NOT NULL COMMENT "",
  `s_nation` varchar(16) NOT NULL COMMENT "",
  `s_region` varchar(13) NOT NULL COMMENT "",
  `s_phone` varchar(16) NOT NULL COMMENT "",
  `p_name` varchar(23) NOT NULL COMMENT "",
  `p_mfgr` varchar(7) NOT NULL COMMENT "",
  `p_category` varchar(8) NOT NULL COMMENT "",
  `p_brand` varchar(10) NOT NULL COMMENT "",
  `p_color` varchar(12) NOT NULL COMMENT "",
  `p_type` varchar(26) NOT NULL COMMENT "",
  `p_size` int(11) NOT NULL COMMENT "",
  `p_container` varchar(11) NOT NULL COMMENT ""
)
PARTITION BY RANGE(`lo_orderdate`)
(PARTITION p1992 VALUES [("-2147483648"), ("19930101")),
PARTITION p1993 VALUES [("19930101"), ("19940101")),
PARTITION p1994 VALUES [("19940101"), ("19950101")),
PARTITION p1995 VALUES [("19950101"), ("19960101")),
PARTITION p1996 VALUES [("19960101"), ("19970101")),
PARTITION p1997 VALUES [("19970101"), ("19980101")),
PARTITION p1998 VALUES [("19980101"), ("19990101")))
DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 48
PROPERTIES (
"replication_num" = "1"
);

set global query_timeout=7200;
set global parallel_fragment_exec_instance_num=1;

then use shell script to insert data;

for con in 'lo_orderdate<19930101' 'lo_orderdate>=19930101 and lo_orderdate<19940101' 'lo_orderdate>=19940101 and lo_orderdate<19950101' 'lo_orderdate>=19950101 and lo_orderdate<19960101' 'lo_orderdate>=19960101 and lo_orderdate<19970101' 'lo_orderdate>=19970101 and lo_orderdate<19980101' 'lo_orderdate>=19980101 and lo_orderdate<19990101';do
    echo $con
    mysql -uroot -P9030 -h:: -Dssb100 -e "insert into lineorder_flat select lo_orderkey , lo_linenumber , lo_custkey , lo_partkey , lo_suppkey , lo_orderdate , lo_orderpriority , lo_shippriority , lo_quantity , lo_extendedprice , lo_ordtotalprice , lo_discount , lo_revenue , lo_supplycost , lo_tax , lo_commitdate , lo_shipmode , c_name , c_address , c_city , c_nation , c_region , c_phone , c_mktsegment , s_name , s_address , s_city , s_nation , s_region , s_phone , p_name , p_mfgr , p_category , p_brand , p_color , p_type , p_size , p_container from (select lo_orderkey,lo_linenumber,lo_custkey,lo_partkey,lo_suppkey,lo_orderdate,lo_orderpriority,lo_shippriority,lo_quantity,lo_extendedprice,lo_ordtotalprice,lo_discount,lo_revenue,lo_supplycost,lo_tax,lo_commitdate,lo_shipmode from lineorder where ${con}) l  inner join customer c on (c.c_custkey = l.lo_custkey)  inner join supplier s on (s.s_suppkey = l.lo_suppkey)  inner join part p on (p.p_partkey = l.lo_partkey);"
done

Based on past experience， I split data by year in order to insert success.
But one of three BEs crashed after starting insert for a while.
Then I set enable_vectorized_engine=false and retry, it success.
be.conf below

flush_thread_num_per_store=5
streaming_load_max_mb = 160000
#disable_storage_page_cache=true
#chunk_reserved_bytes_limit=134217728
enable_storage_vectorization=true
enable_low_cardinality_optimize=true
track_new_delete=false
memory_verbose_track=false
#disable_auto_compaction=true
enable_vectorized_compaction=false

What You Expected?

insert success

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[starocean999]

I am investigating this issue.

[starocean999]

same root cause and already fixed by #9569

[yiguolei]

#9518 Fixed by this PR.