[Bug]查询报错信息存在误导

Viewed 127

v2.1.6

稳定复现

建立两张表
CREATE TABLE `table_test1` (
  `os` varchar(*) NULL,
  `ver` varchar(*) NULL,
  `metrics` int NULL,
  `date` int not null
) ENGINE=OLAP
DUPLICATE KEY(`os`, `ver`)
COMMENT 'OLAP'
PARTITION BY LIST(`date`) (
  PARTITION p20240912 VALUES IN ("20240912"),
  PARTITION p20240913 VALUES IN ("20240913")
)
DISTRIBUTED BY RANDOM BUCKETS 1;


CREATE TABLE `table_test2` (
  `os` varchar(*) NULL,
  `ver` varchar(*) NULL,
  `metrics` int NULL,
  `date` int not null
) ENGINE=OLAP
DUPLICATE KEY(`os`, `ver`)
COMMENT 'OLAP'
PARTITION BY LIST(`date`) (
  PARTITION p20240912 VALUES IN ("20240912"),
  PARTITION p20240913 VALUES IN ("20240913")
)
DISTRIBUTED BY RANDOM BUCKETS 1;


为第一张表建立mv
CREATE MATERIALIZED VIEW test_mv_1 AS
SELECT
  date,
  os,
  ver,
  sum(metrics) as metrics
from olap.table_test1
GROUP BY
  date,
  os,
  ver;
  

进行关联查询(查询第二张表某个不存在的字段) 返回了不符合预期的报错信息
mysql> SELECT
    ->   *
    -> FROM (
    ->   SELECT
    ->     `date`
    ->   FROM olap.table_test1 as table_test1
    ->   WHERE `date` BETWEEN 20240912 AND 20240913
    -> ) AS a0
    -> FULL JOIN
    -> (
    ->   SELECT
    ->     `date`,
    ->     `other_column`
    ->   FROM olap.table_test2 as table_test2
    ->   WHERE `date` BETWEEN 20240912 AND 20240913
    -> ) AS a1
    -> ON a0.`date` = a1.`date`;
ERROR 1105 (HY000): errCode = 2, detailMessage = Unexpected exception: org.apache.doris.common.AnalysisException: errCode = 2, detailMessage = Unknown column 'mv_date' in 'olap.table_test1'


对查询进行修改,把表as别名改成别的,返回正确的报错信息
mysql> SELECT
    ->   *
    -> FROM (
    ->   SELECT
    ->     `date`
    ->   FROM olap.table_test1 as t1
    ->   WHERE `date` BETWEEN 20240912 AND 20240913
    -> ) AS a0
    -> FULL JOIN
    -> (
    ->   SELECT
    ->     `date`,
    ->     `other_column`
    ->   FROM olap.table_test2 as t2
    ->   WHERE `date` BETWEEN 20240912 AND 20240913
    -> ) AS a1
    -> ON a0.`date` = a1.`date`;
ERROR 1054 (42S22): errCode = 2, detailMessage = Unknown column 'other_column' in 'table list'

印象中这个bug从2.0.x到现在一直都存在,导致每次排查查询报错需要花很多时间

2 Answers

旧优化器的报错,后续旧优化器将会废弃不维护了,新版本都默认使用新优化器
建议使用正确字段名,制定相关规范进行规避。

你好请问现在定位到原因了吗