Getting the error ODBC: ERROR [HY000] [Dremio][Connector] (1040) while applying query after expansion of a merged table

Hi,

I get this while applying changes after I have merged two tables.

Steps to replicate

  1. I had imported two indexes from ElasticSearch to PowerBI using the Dremio connector.
  2. I did a merge of the two tables in Power BI.
  3. After the merge, the merged column needs to be expanded to include the columns that I want to use.
  4. After this, I need to apply and close the query editor in order for the changes to be applied.
  5. While executing this I get the below error. As a result, I cannot proceed ahead with saving the newly merged table.

OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [HY000] [Dremio][Connector] (1040) Dremio failed to execute the query: select “OTBL”.“order_amount_total”,
“OTBL”.“line_create_date”,
“OTBL”.“order_invoice_name”,
“OTBL”.“line_price_subtotal”,
“OTBL”.“order_invoice_id”,
“OTBL”.“order_store_name”,
“OTBL”.“line_original_line_id”,
“OTBL”.“line_company_id”,
“OTBL”.“order_partner_name”,
“OTBL”.“line_id”,
“OTBL”.“line_order_id”,
“OTBL”.“line_product_id”,
“OTBL”.“order_company_name”,
“OTBL”.“order_company_id”,
“OTBL”.“line_create_uid”,
“OTBL”.“line_notice”,
“OTBL”.“line_line_qty_returned”,
“OTBL”.“line_name”,
“OTBL”.“line_order_name”,
“OTBL”.“order_pos_reference”,
“OTBL”.“line_create_uname”,
“OTBL”.“line_product_name”,
“OTBL”.“order_store_id”,
“OTBL”.“line_write_date”,
“OTBL”.“order_date_order”,
“OTBL”.“line_discount”,
“OTBL”.“line_tax_ids”,
“OTBL”.“line_company_name”,
“OTBL”.“order_partner_id”,
“OTBL”.“line_price_unit”,
“OTBL”.“line_tax_ids_after_fiscal_position”,
“OTBL”.“line___last_update”,
“OTBL”.“line_price_subtotal_incl”,
“OTBL”.“line_write_uname”,
“OTBL”.“line_write_uid”,
“OTBL”.“line_qty”,
“OTBL”.“line_display_name”,
“OTBL”.“order_id”,
“OTBL”.“order_state”,
“OTBL”."_index",
“OTBL”."_type",
“OTBL”."_uid",
“OTBL”.“line_pack_lot_ids”,
“ITBL”.“product_description_sale”,
“ITBL”.“product_display_name”,
“ITBL”.“product_att_material”,
“ITBL”.“variant_discount”,
“ITBL”.“variant_id”,
“ITBL”.“product_att_fashionability”,
“ITBL”.“product_id”,
“ITBL”.“product_color_name”,
“ITBL”.“product_category_type”,
“ITBL”.“product_style_no”,
“ITBL”.“product_barcode”,
“ITBL”.“product_name”,
“ITBL”.“product_att_fabric_type”,
“ITBL”.“variant_lst_price”,
“ITBL”.“product_age_group”,
“ITBL”.“variant_sale_price”,
“ITBL”.“variant_size_name”,
“ITBL”.“variant_standard_price”
from “KSS Prod.orders_ia2oj”."_doc" as “OTBL”
left outer join “KSS Prod.variants_pivn1”."_doc" as “ITBL” on ((cast(“OTBL”.“order_amount_total” as DOUBLE) = cast(“ITBL”.“variant_id” as DOUBLE) and “OTBL”.“order_amount_total” is not null) and “ITBL”.“variant_id” is not null or “OTBL”.“order_amount_total” is null and “ITBL”.“variant_id” is null)
[30038]Query execution error. Details:[
UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join

SQL Query select “OTBL”.“order_amount_total”,
“OTBL”.“line_create_date”,
“OTBL”.“order_invoice_name”,
“OTBL”.“line_price_subtotal”,
“OTBL”.“order_invoice_id”,
“OTBL”.“order_store_name”,
“OTBL”.“line_original_line_id”,
“OTBL”.“line_company_id”,
“OTBL”.“order_partner_name”,
“OTBL”.“line_id”,
“OTBL”.“line_order_id”,
“OTBL”.“line_product_id”,
“OTBL”.“order_company_name”,
“OTBL”.“order_company_id”,
“OTBL”.“line_create_uid”,
“OTBL”.“line_notice”,
“OTBL”.“line_line_qty_returned”,
“OTBL”.“line_name”,
“OTBL”.“line_order_name”,
“OTBL”.“order_pos_reference”,
“OTBL”.“line_create_uname”,
“OTBL”.“line_product_name”,
“OTBL”.“order_store_id”,
“OTBL”.“line_write_date”,
“OTBL”.“order_date_order”,
“OTBL”.“line_discount”,
“OTBL”.“line_tax_ids”,
“OTBL”.“line_company_name”,
“OTBL”.“order_partner_id”,
“OTBL”.“line_price_unit”,
“OTBL”.“line_tax_ids_after_fiscal_position”,
“OTBL”.“line___last_update”,
“OTBL”.“line_price_subtotal_incl”,
“OTBL”.“line_write_uname”,
“OTBL”.“line_write_uid”,
“OTBL”.“line_qty”,
“OTBL”.“line_display_name”,
“OTBL”.“order_id”,
“OTBL”.“order_state”,
“OTBL”."_index",
“OTBL”."_type",
“OTBL”."_uid",
“OTBL”.“line_pack_lot_ids”,
“ITBL”.“product_description_sale”,
“ITBL”.“product_display_name”,
“ITBL”.“product_att_material”,
“ITBL”.“variant_discount”,
“ITBL”.“variant_id”,
“ITBL”.“product_att_fashionability”,
“ITBL”.“product_id”,
“ITBL”.“product_color_name”,
“ITBL”.“product_category_type”,
“ITBL”.“product_style_no”,
“ITBL”.“product_barcode”,
“ITBL”.“product_name”,
“ITBL”.“product_att_fabric_type”,
“ITBL”.“variant_lst_price”,
“ITBL”.“product_age_group”,
“ITBL”.“variant_sale_price”,
“ITBL”.“variant_size_name”,
“ITBL”.“variant_standard_price”
from “KSS Prod.orders_ia2oj”."_doc" as “OTBL”
left outer join “KSS Prod.variants_pivn1”."_doc" as “ITBL” on ((cast(“OTBL”.“order_amount_total” as DOUBLE) = cast(“ITBL”.“variant_id” as DOUBLE) and “OTBL”.“order_amount_total” is not null) and “ITBL”.“variant_id” is not null or “OTBL”.“order_amount_total” is null and “ITBL”.“variant_id” is null)

[Error Id: d3089974-875d-49a8-b6ae-19f0a8f5f8b6 on KnightKiller:31010]

(com.dremio.exec.work.foreman.UnsupportedRelOperatorException) This query cannot be planned possibly due to either a cartesian join or an inequality join
com.dremio.exec.planner.sql.handlers.PrelTransformer.convertToPrel():510
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():49
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():69
com.dremio.exec.work.foreman.AttemptManager.run():288
java.util.concurrent.ThreadPoolExecutor.runWorker():-1
java.util.concurrent.ThreadPoolExecutor$Worker.run():-1
java.lang.Thread.run():-1

].

I tried doing the same by downloading the files as csv and merging them just to confirm that the fields I am using as a reference to merge are not causing an issue. Doing this works and there are no issues generated.

I am also using the latest Dremio ODBC connector v1.3.22.1055.

Thanks

Hi @Georgio,

The error:

UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join

… implies that the query that Power BI sends to Dremio for execution has one of these types of joins, with Dremio does not currently support. In Dremio you can take a look at the queries that Power BI is submitting by filter for “External Tools”.
13%20AM
Can you find this query and attach it here?

How big are the tables you are merging (how many rows each)?

Hi Ben, thanks for the quick response. I have uploaded the profile. Do let me know if this is what you were looking for.

With respect to the table rows, the main table has around 20,000 rows while the other has 10,000. I am trying to merge the smaller to the larger based on a common attribute.

7981012e-c58c-45a8-9070-42ea642b2213.zip (11.3 KB)