Doris2.1.4创建物化视图报错缺失字段

Viewed 31

Doris版本:2.1.4
只运行select可以出结果,创建视图会报错。
建表sql

-- 创建目标表
drop table testdb.at_sysdictionary;
create table testdb.at_sysdictionary
(
  atr_key              BIGINT,
  site_num             INT,
  atr_name             STRING,
  purge_status         INT,
  creation_time        DATETIME,
  creation_time_u      DATETIME,
  creation_time_z      STRING,
  last_modified_time   DATETIME,
  last_modified_time_u DATETIME,
  last_modified_time_z STRING,
  xfr_insert_pid       INT,
  xfr_update_pid       INT,
  trx_id               STRING,
  parent_key           BIGINT,
  category_s           STRING,
  description_s        STRING,
  name_s               STRING,
  readonly_y           INT
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

drop table testdb.at_sysdictionarydata;
create table testdb.at_sysdictionarydata
(
  atr_key              bigint,
  site_num             int,
  atr_name             string,
  purge_status         int,
  creation_time        datetime,
  creation_time_u      datetime,
  creation_time_z      string,
  last_modified_time   datetime,
  last_modified_time_u datetime,
  last_modified_time_z string,
  xfr_insert_pid       int,
  xfr_update_pid       int,
  trx_id               string,
  parent_key           bigint,
  description_s        string,
  key_s                string,
  sort_i               bigint,
  value_s              string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

drop table testdb.at_tmmodulesplinfor;
create table testdb.at_tmmodulesplinfor
(
  atr_key               bigint,
  site_num              int,
  atr_name              string,
  purge_status          int,
  creation_time         datetime,
  creation_time_u       datetime,
  creation_time_z       string,
  last_modified_time    datetime,
  last_modified_time_u  datetime,
  last_modified_time_z  string,
  xfr_insert_pid        int,
  xfr_update_pid        int,
  trx_id                string,
  parent_key            bigint,
  ivtest_time_t         datetime,
  ivtest_time_u         datetime,
  ivtest_time_z         string,
  iv_equipment_s        string,
  iv_key_i              bigint,
  pmax_f                float,
  cell_area_f           float,
  cell_manufacturer_s   string,
  cell_partnum_s        string,
  cell_piece_f          float,
  cell_ratios_f         float,
  cell_technology_s     string,
  current_grade_s       string,
  eva_manufacturer_s    string,
  eva_partnum_s         string,
  eva_spec_s            string,
  glass_manufacturer_s  string,
  glass_partnum_s       string,
  glass_spec_s          string,
  hited_y               int,
  is_calculate_y        int,
  j_box_length_s        string,
  j_box_manufacturer_s  string,
  j_box_partnum_s       string,
  join_manufacturer_s   string,
  join_partnum_s        string,
  module_id_s_s         string,
  pl_calculate_f        float,
  power_grade_i         bigint,
  power_level5_i        bigint,
  power_level6_i        bigint,
  product_type_s        string,
  sale_no_s             string,
  strip_manufacturer_s  string,
  strip_partnum_s       string,
  tergum_manufacturer_s string,
  tergum_partnum_s      string,
  granding_key_i        bigint,
  poe_manufacture_s     string,
  poe_partnum_s         string,
  poe_spec_s            string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

drop table testdb.at_pmmoduletask;
create table testdb.at_pmmoduletask
(
  atr_key              bigint,
  site_num             int,
  atr_name             string,
  purge_status         int,
  creation_time        datetime,
  creation_time_u      datetime,
  creation_time_z      string,
  last_modified_time   datetime,
  last_modified_time_u datetime,
  last_modified_time_z string,
  xfr_insert_pid       int,
  xfr_update_pid       int,
  trx_id               string,
  parent_key           bigint,
  column_1_s           string,
  complement_person_s  string,
  create_by_s          string,
  founday_remark_s     string,
  i_level_s            string,
  i_value_f            float,
  is_check_s           string,
  is_lock_s            string,
  is_return_to_line_s  string,
  is_scrap_s           string,
  is_ng_s              string,
  is_pack_s            string,
  last_update_by_s     string,
  lock_id_s            string,
  module_id_s          string,
  module_level_s       string,
  order_id_old_s       string,
  order_id_s           string,
  power_level_s        string,
  power_value_f        float,
  print_no_i           bigint,
  print_time_t         datetime,
  print_time_u         datetime,
  print_time_z         string,
  print_qty_i          bigint,
  printer_s            string,
  product_code_s       string,
  production_line_s    string,
  reissue_date_t       datetime,
  reissue_date_u       datetime,
  reissue_date_z       string,
  reissue_person_s     string,
  rework_order_id_s    string,
  shift_task_id_s      string,
  start_online_time_t  datetime,
  start_online_time_u  datetime,
  start_online_time_z  string,
  status_s             string,
  work_center_s        string,
  switch_rule_id_s     string,
  mark_print_time_t    datetime,
  mark_print_time_u    datetime,
  mark_print_time_z    string,
  mark_power_level_s   string,
  mark_current_level_s string,
  iv_key_i             bigint,
  is_instorage_s       string,
  isrelease_s          string,
  releaseby_s          string,
  release_time_t       datetime,
  release_time_u       datetime,
  release_time_z       string,
  recycle_time_t       datetime,
  recycle_time_u       datetime,
  recycle_time_z       string,
  area_s               string,
  qrcode_id_s          string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

drop table testdb.at_pmordertask;
create table testdb.at_pmordertask
(
  atr_key               bigint,
  site_num              int,
  atr_name              string,
  purge_status          int,
  creation_time         datetime,
  creation_time_u       datetime,
  creation_time_z       string,
  last_modified_time    datetime,
  last_modified_time_u  datetime,
  last_modified_time_z  string,
  xfr_insert_pid        int,
  xfr_update_pid        int,
  trx_id                string,
  parent_key            bigint,
  actual_end_time_t     datetime,
  actual_end_time_u     datetime,
  actual_end_time_z     string,
  actual_start_time_t   datetime,
  actual_start_time_u   datetime,
  actual_start_time_z   string,
  area_s                string,
  attr1_s               string,
  attr2_s               string,
  attr3_s               string,
  attr4_s               string,
  attr5_s               string,
  bom_revision_s        string,
  cell_color_s          string,
  cell_power_s          string,
  complete_qty_f        float,
  craft_confirm_time_t  datetime,
  craft_confirm_time_u  datetime,
  craft_confirm_time_z  string,
  craft_confirmer_s     string,
  created_by_s          string,
  customer_order_id_s   string,
  dispatched_qty_f      float,
  last_update_by_s      string,
  module_print_qty_f    float,
  module_print_status_i bigint,
  oem_i                 bigint,
  order_id_s            string,
  order_name_s          string,
  order_status_s        string,
  order_type_s          string,
  order_type_desc_s     string,
  org_code_s            string,
  original_order_id_s   string,
  package_qty_i         bigint,
  plan_end_time_t       datetime,
  plan_end_time_u       datetime,
  plan_end_time_z       string,
  plan_start_time_t     datetime,
  plan_start_time_u     datetime,
  plan_start_time_z     string,
  power_max_s           string,
  power_min_s           string,
  product_code_s        string,
  product_name_s        string,
  production_line_s     string,
  quantity_f            float,
  released_qty_f        float,
  remain_qty_f          float,
  route_s               string,
  route_rev_s           string,
  sale_no_s             string,
  schedule_group_name_s string,
  scrap_qty_f           float,
  single_box_no_s       string,
  status_i              bigint,
  switch_rule_id_s      string,
  target_power_s        string,
  third_party_monitor_s string,
  uom_s                 string,
  wip_qty_f             float,
  inst_id_s             string,
  islocked_s            string,
  ci_code_s             string,
  rfid_order_no_s       string,
  is_check_s            string,
  poe_s                 string,
  glass_type_s          string,
  specifiedmaterial_s   string,
  confirmby_s           string,
  close_time_t          datetime,
  close_time_u          datetime,
  close_time_z          string,
  hit_s                 string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

drop table testdb.area;
create table testdb.area
(
    area_key             bigint,
    site_num             int,
    area_name            string,
    description          string,
    category             string,
    creator_key          bigint,
    creation_time        datetime,
    creation_time_u      datetime,
    creation_time_z      string,
    last_modifier_key    bigint,
    last_modified_time   datetime,
    last_modified_time_u datetime,
    last_modified_time_z string,
    xfr_insert_pid       int,
    xfr_update_pid       int,
    trx_id               string,
    update_privilege_key bigint,
    delete_privilege_key bigint,
    location_key         bigint,
    inst_list_key        bigint,
    work_schedule_key    bigint,
    time_zone            string
)unique key(area_key)
distributed by hash(area_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

drop table testdb.at_immaterialsmarks;
create table testdb.at_immaterialsmarks
(
  atr_key              BIGINT,
  site_num             INT,
  atr_name             STRING,
  purge_status         INT,
  creation_time        DATETIME,
  creation_time_u      DATETIME,
  creation_time_z      STRING,
  last_modified_time   DATETIME,
  last_modified_time_u DATETIME,
  last_modified_time_z STRING,
  xfr_insert_pid       INT,
  xfr_update_pid       INT,
  trx_id               CHAR(38),
  parent_key           BIGINT,
  part_number_s        STRING,
  create_by_s          STRING,
  en_marks_desc_s      STRING,
  modify_by_s          STRING,
  reserves_s           STRING,
  zh_marks_desc_s      STRING
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

drop table testdb.at_pmmodulepasswcconsume;
create table testdb.at_pmmodulepasswcconsume
(
  atr_key                bigint,
  site_num               int,
  atr_name               string,
  purge_status           int,
  creation_time          datetime,
  creation_time_u        datetime,
  creation_time_z        string,
  last_modified_time     datetime,
  last_modified_time_u   datetime,
  last_modified_time_z   string,
  xfr_insert_pid         int,
  xfr_update_pid         int,
  trx_id                 string,
  parent_key             bigint,
  area_s                 string,
  batch_code_big_s       string,
  batch_code_small_s     string,
  cousume_time_t         datetime,
  cousume_time_u         datetime,
  cousume_time_z         string,
  created_by_s           string,
  last_update_by_s       string,
  mat_consume_no_f       float,
  material_code_s        string,
  material_group_s       string,
  module_id_s            string,
  module_pass_id_s       string,
  operation_code_s       string,
  order_id_s             string,
  parent_material_code_s string,
  product_code_s         string,
  production_line_s      string,
  remark_s               string,
  shift_s                string,
  shift_task_id_s        string,
  status_s               string,
  work_center_code_s     string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

drop table testdb.at_pmivtestrecord;
create table testdb.at_pmivtestrecord
(
  atr_key              bigint,
  site_num             int,
  atr_name             string,
  purge_status         int,
  creation_time        datetime,
  creation_time_u      datetime,
  creation_time_z      string,
  last_modified_time   datetime,
  last_modified_time_u datetime,
  last_modified_time_z string,
  xfr_insert_pid       int,
  xfr_update_pid       int,
  trx_id               char(38),
  parent_key           bigint,
  area_s               string,
  cirr_f               float,
  column_1_i           bigint,
  created_by_s         string,
  eff_f                float,
  equipment_id_s       string,
  ff_f                 float,
  gavg_f               float,
  ipm_f                float,
  irr_f                float,
  isc_f                float,
  lti_f                float,
  last_update_by_s     string,
  module_id_s          string,
  order_id_s           string,
  pmax_f               float,
  production_line_s    string,
  rs_f                 float,
  rsh_f                float,
  shift_task_id_s      string,
  status_s             string,
  tmod_f               float,
  tempdut_f            float,
  tempmc_f             float,
  test_time_t          datetime,
  test_time_u          datetime,
  test_time_z          string,
  voc_f                float,
  vpm_f                float,
  sequence_id_s        string,
  sequence_num_i       bigint
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

drop table testdb.at_erpvendorinfo;
create table testdb.at_erpvendorinfo
(
  atr_key              bigint,
  site_num             int,
  atr_name             string,
  purge_status         int,
  creation_time        datetime,
  creation_time_u      datetime,
  creation_time_z      string,
  last_modified_time   datetime,
  last_modified_time_u datetime,
  last_modified_time_z string,
  xfr_insert_pid       int,
  xfr_update_pid       int,
  trx_id               string,
  parent_key           bigint,
  vendoraddress_s      string,
  vendoraltname_s      string,
  vendorclass_s        string,
  vendorcode_s         string,
  vendorname_s         string,
  srmcode_s            string,
  supplierinitials_s   string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

drop table testdb.at_pmpackagedetail;
create table testdb.at_pmpackagedetail
(
  atr_key              bigint,
  site_num             int,
  atr_name             string,
  purge_status         int,
  creation_time        datetime,
  creation_time_u      datetime,
  creation_time_z      string,
  last_modified_time   datetime,
  last_modified_time_u datetime,
  last_modified_time_z string,
  xfr_insert_pid       int,
  xfr_update_pid       int,
  trx_id               string,
  parent_key           bigint,
  actual_current_f     float,
  actual_power_f       float,
  box_id_s             string,
  check_result_s       string,
  create_by_s          string,
  current_grade_s      string,
  gread_record_key_s   string,
  group_id_s           string,
  is_sample_s          string,
  last_update_by_s     string,
  module_id_s          string,
  module_sequence_i    bigint,
  order_id_s           string,
  package_time_t       datetime,
  package_time_u       datetime,
  package_time_z       string,
  part_number_s        string,
  power_grade_s        string,
  product_grade_s      string,
  production_line_s    string,
  sample_result_s      string,
  status_s             string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

create table testdb.at_sales_order
(
    atr_key              bigint,
    site_num             int,
    atr_name             string,
    purge_status         int,
    creation_time        datetime,
    creation_time_u      datetime,
    creation_time_z      string,
    last_modified_time   datetime,
    last_modified_time_u datetime,
    last_modified_time_z string,
    xfr_insert_pid       int,
    xfr_update_pid       int,
    trx_id               string,
    parent_key           bigint,
    barcode_starting_s   string,
    barcode_template_s   string,
    battery_rail_s       string,
    battery_type_s       string,
    border_template_s    string,
    created_by_s         string,
    executive_standard_s string,
    last_update_by_s     string,
    mark_template_s      string,
    module_type_s        string,
    nameplate_template_s string,
    packing_number_i     bigint,
    packing_size_s       string,
    production_type_s    string,
    remarks_s            string,
    reserve_s            string,
    sale_order_desc_s    string,
    sale_order_no_s      string,
    sop_document_s       string,
    status_s             string,
    unit_weight_f        float,
    sales_type_s         string,
    custom_rule_module_s string,
    custom_rule_box_s    string,
    ci_code_s            string,
    rfid_order_no_s      string,
    nameplate_type_s     string,
    nameplate_ver_s      string,
    devanning_report_s   string,
    ischeck_s            string,
    sh_by_s              string,
    sh_time_s            string,
    additional_temple_s  string,
    wirelen_s            string,
    predicted_yield_s    string,
    predicted_pct_s      string,
    shipmark_remark_s    string,
    pl_remark_s          string,
    hide_status_s        string,
    power_pct_s          string,
    calculatepl_s        string,
    is_prodution_s       string
)
unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
  "replication_allocation" = "tag.location.default: 3",
  "enable_unique_key_merge_on_write"="true"
);

创建物化视图sql:

-- 创建物化视图
create materialized view testdb.mes_PLDetailQuery_v
BUILD IMMEDIATE -- 立即刷新
REFRESH ON SCHEDULE EVERY 1 HOUR STARTS "2024-07-29 11:00:00" -- 自动定时刷新
DISTRIBUTED BY RANDOM BUCKETS 3
    as
select tak.order_id_s AS gongdan,
       plf.module_id_s_s AS tiaoma,
       nc.Batch_Code_Small_s AS pici,
       plf.product_type_s AS xinghao,
       nc.zh_marks_desc_s AS leixing,
       plf.pmax_f AS gonglv,
       plf.power_grade_i AS mingpai,
       iv.voc_f AS voc,
       iv.isc_f AS isc,
       iv.vpm_f AS vpmax,
       iv.ipm_f AS ipmax,
       iv.ff_f AS ff,
       iv.tempdut_f AS tdut,
       plf.iv_equipment_s AS shebei,
       plf.creation_time AS riqi,
       nvl(plf.cell_ratios_f, 0) AS xiaolv,
       b.sale_order_desc_s AS kehu,
       case
         when vc.srmcode_s is null then
          vc.vendorname_s
         else
            concat_ws(sdt.value_s , vc.srmcode_s)
       end AS cell,
        case
         when vg.srmcode_s is null then
          vg.vendorname_s
         else
          concat_ws(sdt.value_s , vg.srmcode_s)
       end AS glass,
       det.box_id_s,
       a.area_name
  from testdb.at_tmmodulesplinfor plf, testdb.at_sysdictionary sd, testdb.at_sysdictionarydata sdt
 inner join testdb.at_pmmoduletask tak
    on tak.Module_Id_s = plf.module_id_s_s
 INNER JOIN testdb.at_pmordertask ord
    ON ord.order_id_s = tak.order_id_s
 INNER JOIN testdb.area a
    ON ord.area_s = a.area_name
 inner join (select mp.module_id_s,
                    mp.material_code_s,
                    mim.zh_marks_desc_s,
                    mp.Batch_Code_Small_s
               from testdb.at_immaterialsmarks mim
               left join testdb.at_pmmodulepasswcconsume mp
                 on mim.part_number_s = mp.material_code_s
              where material_code_s like '71%') nc
    on nc.module_id_s = plf.module_id_s_s
 inner join testdb.at_pmivtestrecord iv
    on iv.atr_key = plf.iv_key_i
  LEFT OUTER JOIN testdb.at_erpvendorinfo vc
    ON vc.vendorcode_s = plf.cell_manufacturer_s
  LEFT OUTER JOIN testdb.at_erpvendorinfo vg
    ON vg.vendorcode_s = plf.glass_manufacturer_s
  LEFT OUTER JOIN testdb.at_pmpackagedetail det
    ON det.module_id_s = plf.module_id_s_s
left join testdb.at_sales_order b
    on ord.customer_order_id_s = b.sale_order_no_s
where sd.atr_key = sdt.parent_key
and sd.name_s = 'MaterialFirstStr';

报错:

org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1105] [HY000]: Unexpected exception: errCode = 2, detailMessage = Unknown column 'module_id_s_s' in 'plf' in JOIN clause
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:582)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:491)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:498)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:924)
	at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3805)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5073)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: Unexpected exception: errCode = 2, detailMessage = Unknown column 'module_id_s_s' in 'plf' in JOIN clause
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
	at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:96)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
	... 12 more
1 Answers

我本地复现了下,这个应该是和新优化器有关系,这个query 自动回退到了旧优化器,我们先看下的。
image.png