Dremio BUG after alter Iceberg table

1

Hello friends, another Day reporting other bug, steps to reproduce:

  1. create iceberg table using CTA for example
CREATE TABLE lake.tab1 as select col1, col2 from space.tab1_vds
  1. add column
ALTER TABLE lake.tab1 ADD COLUMN (COL3 VARCHAR) BEFORE col2

ERROR 1: BEFORE KEYWORD doesn’t work

  1. insert new data
INSERT INTO lake.tab1 (col1, col3, col2)  as select col1, col3, col2 from space.tab1_vds

ERROR 2: by some reason dremio reorganizate columns definition so table order columns not match select columns, even I put in order in sql sentence, Dremio changes and fails

please @ben or @steven with this post are 6 bugs reported and confirmed, thank you by your attention to this post and past 5 post,
We continue working to build this amazing tool.
I start to consider myself part of the QA team :smile:

2

https://docs.dremio.com/software/sql-reference/sql-commands/tables/#alter-table
Documentation does not mention BEFORE so I’d expect ALTER TABLE query to fail. Does it not?

I don’t quite follow the Error 2 description…
What does DESCRIBE TABLE show?

3

hi, thank you by your response: in documentation are before in iceberg section
https://docs.dremio.com/software/sql-reference/sql-commands/apache-iceberg-tables/apache-iceberg-alter/

error 2 are strange, if you add new column to existing table, then try insert with select statement fails if you see pic Dremio change column order

imagen
imagen1322×422 20.7 KB

4

For clarify in the example “reclasificada” field are changed order by dremio

imagen
imagen1322×422 33.4 KB

I submit in same order in insert columns enumeration and in the select (see 1 and 2 numbers in pic) but dremio reorder columns in the insert part (3 and 4) which contradicts than docuemntation saids:

  • If column names are specified, data is inserted in the given column order, and columns not listed are populated as NULL.
    instead are doing
  • If column names are not specified, data is inserted in the column order of the table.

that isn’t correct because columns are specified

5

Thanks for the doc pointer - I always forget to check both Software and Cloud. I’ll file a ticket to get documentation corrected and will talk to the team about ordering.

Thank you for reporting this.

6

Thank you, sorry for the abuse but if you can please check my another pots, specially with Powerbi error than is critical.

  1. PowerBi error on load any VDS
  2. Dremio fails to cast
  3. Dremio fails pushdown - #7 by dacopan
  4. Dremio fails on promote partition folder
7

Could you paste the output of DESCRIBE TABLE lake.credito.hist.dr_ca_operacion_hist query, please?

8
COLUMN_NAME DATA_TYPE IS_NULLABLE NUMERIC_PRECISION NUMERIC_SCALE EXTENDED_PROPERTIES MASKING_POLICY
NUM_PRESTAMO DECIMAL YES 10 0
secuencial INTEGER YES 32 0
SECUENCIAL_ANT INTEGER YES 32 0
NUM_PRESTAMO_SEC CHARACTER VARYING YES
FEC_INICIAL DATE YES
PLAZO_ACTUAL INTEGER YES 32 0
cuotas_pagadas INTEGER YES 32 0
cuotas_x_pagar INTEGER YES 32 0
COD_ORIGINADOR_SCD2 BIGINT YES 64 0
NUM_CLASCRED_SCD2 BIGINT YES 64 0
ID_DUENIO_SCD2 BIGINT YES 64 0
SALDO_K_ESTRUC DECIMAL YES 17 2
DIAS_VENCIDO INTEGER YES 32 0
CALIFICACION CHARACTER VARYING YES
PROVISION_CONSTITUIDA DECIMAL YES 17 2
saldo_vigente_actual DECIMAL YES 15 2
saldo_ndi_actual DECIMAL YES 15 2
saldo_vencido_actual DECIMAL YES 15 2
riesgo_actual DECIMAL YES 17 2
saldo_adeudado_actual DECIMAL YES 38 2
dias_ven_actual INTEGER YES 32 0
CALIFICACION_ACTUAL CHARACTER VARYING YES
PROVISION_CONSTITUIDA_ACTUAL DECIMAL YES 17 2
ESTATUS_CALIF_ACT CHARACTER VARYING YES
CONS_PROV_ACT DECIMAL YES 18 2
DIAS_VENC_ACEL INTEGER YES 32 0
SUB_CALIFICACION_ACEL CHARACTER VARYING YES
PROVI_CONSTITUIDA_ACEL DECIMAL YES 16 2
ESTATUS_CALIF_ACEL CHARACTER VARYING YES
CONS_PROV_ACEL DECIMAL YES 18 2
oficial_cuenta INTEGER YES 32 0
ID_AGENCIA INTEGER YES 32 0
fecha_carga DATE YES
id_fecha_dim INTEGER YES 32 0
reclasificada DECIMAL YES 15 2

as you can see in table “reclasificada” field is the last column if you try make insert enumarate columns in distinct order for example “reclasificada” before “fecha_carga” Dremio fails because it reorder columns and fail

9

Confirming we have an issue with INSERT w/ specified column order. We deemed it severe enough to schedule for one of the upcoming releases.

1 Like
10

Hello @dch and @balaji.ramaswamy this issue persist in 24.1.0 when insert into … select …

11

Unexpected.
Can you reproduced it from scratch using a smaller table? Or is there something special about your setup?

12

yes, sure occure with any table, let me try and I’ll sent you sql

13

Until, prepare small error scenario attached current query profile
d6fe3ae6-d4a9-4dc3-ac27-5cf0ce0e6178.zip (24,6 KB)