[Bug]: 执行ssb查询时报错

xuanyiLee commented 4 months ago

Is there an existing issue for the same bug?

[X] I have checked the existing issues.

Branch Name

mo cloud

Commit ID

14c4496c6

Other Environment Information

- Hardware parameters:
- OS type:
- Others:
MO cloud收费版
版本：8.0.30-MatrixOne-v1.1.3
实例ID：018f99bd-0970-74ec-bcd8-8c4ee809bdbc
查询ID：018fbcd8-4cc9-7a58-8a9a-dd9a8d73b3aa
事务ID：018fbcd8-4cc9-7ec3-a8ea-085db59e99bf
会话ID：018fbcd2-d659-7af5-b3e8-ed0b46c7818a

Actual Behavior

执行如下语句，有时会报错column REVENUE does not exist，执行三次大概会报错2次

SELECT
  c_nation,
  s_nation,
  d_year,
  SUM(lo_revenue) AS REVENUE
FROM
  customer,
  lineorder,
  supplier,
  dates
WHERE
  lo_custkey = c_custkey
  AND lo_suppkey = s_suppkey
  AND lo_orderdate = d_datekey
  AND c_region = 'ASIA'
  AND s_region = 'ASIA'
  AND d_year >= 1992
  AND d_year <= 1997
GROUP BY
  c_nation,
  s_nation,
  d_year
ORDER BY
  d_year ASC,
  REVENUE DESC;

建表语句如下：

create database if not exists ssb;
use ssb;
drop table if exists lineorder;
drop table if exists part;
drop table if exists supplier;
drop table if exists customer;
drop table if exists date;
drop table if exists lineorder_flat;

create table lineorder (
        lo_orderkey bigint,
        lo_linenumber int,
        lo_custkey int,
        lo_partkey int,
        lo_suppkey int,
        lo_orderdate date,
        lo_orderpriority char (15),
        lo_shippriority tinyint,
        lo_quantity double,
        lo_extendedprice double,
        lo_ordtotalprice double,
        lo_discount double,
        lo_revenue double,
        lo_supplycost double,
        lo_tax double,
        lo_commitdate date,
        lo_shipmode char (10)
) ;

create table part (
        p_partkey int,
        p_name varchar (22),
        p_mfgr char (6),
        p_category char (7),
        p_brand char (9),
        p_color varchar (11),
        p_type varchar (25),
        p_size int,
        p_container char (10)
) ;

create table supplier (
        s_suppkey int,
        s_name char (25),
        s_address varchar (25),
        s_city char (10),
        s_nation char (15),
        s_region char (12),
        s_phone char (15)
) ;

create table customer (
        c_custkey int,
        c_name varchar (25),
        c_address varchar (25),
        c_city char (10),
        c_nation char (15),
        c_region char (12),
        c_phone char (15),
        c_mktsegment char (10)
) ;

create table date (
        d_datekey date,
        d_date char (18),
        d_dayofweek char (9),
        d_month char (9),
        d_year int,
        d_yearmonthnum int,
        d_yearmonth char (7),
        d_daynuminweek varchar(12),
        d_daynuminmonth int,
        d_daynuminyear int,
        d_monthnuminyear int,
        d_weeknuminyear int,
        d_sellingseason varchar (12),
        d_lastdayinweekfl varchar (1),
        d_lastdayinmonthfl varchar (1),
        d_holidayfl varchar (1),
        d_weekdayfl varchar (1)
) ;

Expected Behavior

No response

Steps to Reproduce

连续多次执行上面的SQL

Additional information

No response

arjunsk commented 4 months ago

Hi Ouyuanning, could you kindly have a look at this and assign this to the right person? Thank you!

ouyuanning commented 4 months ago

本地未复现语句用这个，提交者的语句中的 dates 这个表写错了，建表的时候是用的 date

SELECT
  c_nation,
  s_nation,
  d_year,
  SUM(lo_revenue) AS REVENUE
FROM
  customer,
  lineorder,
  supplier,
  date
WHERE
  lo_custkey = c_custkey
  AND lo_suppkey = s_suppkey
  AND lo_orderdate = d_datekey
  AND c_region = 'ASIA'
  AND s_region = 'ASIA'
  AND d_year >= 1992
  AND d_year <= 1997
GROUP BY
  c_nation,
  s_nation,
  d_year
ORDER BY
  d_year ASC,
  REVENUE DESC;

如果确实有报错的话，应该是build plan的时候的报错。猜测可能跟大小写有关。但是执行了很多次未复现。

aressu1985 commented 4 months ago

1.2-dev DAILY从来没有出现过该问题，怀疑是大小写原因，delay到1.3.0在分析解决