Failed to update from joined table

Jun_Zhou · September 11, 2024, 8:23am

Hi Team, I’m using CrateDB 5.5.2 and tried to update a field in a table with values obtained from joining another table. However, I encountered the following error, but I don’t know the exact cause.

UPDATE Ebs.Rwmv_Xxmtl_Transactions
   SET Posted_By =
       (SELECT Ebs.Fnd_User.Description
          FROM Ebs.Mtl_Material_Transactions
          JOIN Ebs.Fnd_User
            ON Ebs.Fnd_User.User_Id = Ebs.Mtl_Material_Transactions.Created_By
         WHERE Ebs.Mtl_Material_Transactions.Transaction_Id = Ebs.Rwmv_Xxmtl_Transactions.Transaction_Id)
 WHERE Ebs.Rwmv_Xxmtl_Transactions.Posted_By IS NULL;

error message

ERROR: Relation 'ebs.rwmv_xxmtl_transactions' unknown
 at position ：io.crate.analyze.relations.FullQualifiedNameFieldProvider.resolveField(FullQualifiedNameFieldProvider.java:165)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitQualifiedNameReference(ExpressionAnalyzer.java:944)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitQualifiedNameReference(ExpressionAnalyzer.java:444)
io.crate.sql.tree.QualifiedNameReference.accept(QualifiedNameReference.java:38)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitComparisonExpression(ExpressionAnalyzer.java:777)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitComparisonExpression(ExpressionAnalyzer.java:444)
io.crate.sql.tree.ComparisonExpression.accept(ComparisonExpression.java:80)
io.crate.analyze.expressions.ExpressionAnalyzer.convert(ExpressionAnalyzer.java:245)
io.crate.analyze.expressions.ExpressionAnalyzer.lambda$generateQuerySymbol$1(ExpressionAnalyzer.java:254)
java.base/java.util.Optional.map(Optional.java:260)
io.crate.analyze.expressions.ExpressionAnalyzer.generateQuerySymbol(ExpressionAnalyzer.java:254)
io.crate.analyze.relations.RelationAnalyzer.visitQuerySpecification(RelationAnalyzer.java:423)
io.crate.analyze.relations.RelationAnalyzer.visitQuerySpecification(RelationAnalyzer.java:115)
io.crate.sql.tree.QuerySpecification.accept(QuerySpecification.java:101)
io.crate.analyze.relations.RelationAnalyzer.visitQuery(RelationAnalyzer.java:150)
io.crate.analyze.relations.RelationAnalyzer.visitQuery(RelationAnalyzer.java:115)
io.crate.sql.tree.Query.accept(Query.java:72)
io.crate.analyze.relations.RelationAnalyzer.analyze(RelationAnalyzer.java:133)
io.crate.analyze.expressions.SubqueryAnalyzer.analyze(SubqueryAnalyzer.java:41)
io.crate.analyze.expressions.ExpressionAnalyzer$InnerExpressionAnalyzer.visitSubqueryExpression(ExpressionAnalyzer.java:1120)

table definition

+--------------------------------------------------------------+
| SHOW CREATE TABLE ebs.rwmv_xxmtl_transactions                |
+--------------------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "ebs"."rwmv_xxmtl_transactions" ( |
|    "id" VARCHAR(48) NOT NULL,                                |
|    "transaction_id" VARCHAR(480),                            |
|    "transaction_date" VARCHAR(480),                          |
|    "transaction_type_name" VARCHAR(480),                     |
|    "subinventory_code" VARCHAR(480),                         |
|    "transfer_subinventory" VARCHAR(480),                     |
|    "creation_date" VARCHAR(480),                             |
|    "rcv_transaction_id" VARCHAR(480),                        |
|    "organization_id" VARCHAR(480),                           |
|    "project_id" VARCHAR(480),                                |
|    "transfer_project_id" VARCHAR(480),                       |
|    "organization_name" VARCHAR(480),                         |
|    "item_code" VARCHAR(480),                                 |
|    "lot_number" VARCHAR(480),                                |
|    "subinventory_name" VARCHAR(480),                         |
|    "transfer_subinventory_name" VARCHAR(480),                |
|    "project_number" VARCHAR(480),                            |
|    "transaction_quantity" INTEGER,                           |
|    "transaction_reference" VARCHAR(480),                     |
|    "transaction_source_name" VARCHAR(480),                   |
|    "posted_by" VARCHAR(480),                                 |
|    "item_desc" VARCHAR(480),                                 |
|    "product_model" VARCHAR(480),                             |
|    "pur_cate" VARCHAR(480),                                  |
|    "uom" VARCHAR(480),                                       |
|    "source_name" VARCHAR(480),                               |
|    "reference_number" VARCHAR(480),                          |
|    "reference_type" VARCHAR(480),                            |
|    "reference_crt_name" VARCHAR(480),                        |
|    "notice_header_remarks" VARCHAR(4000),                    |
|    "dlvy_header_remarks" VARCHAR(4000),                      |
|    PRIMARY KEY ("id")                                        |
| )                                                            |
| CLUSTERED BY ("id") INTO 4 SHARDS                            |
| WITH (                                                       |
|    "allocation.max_retries" = 5,                             |
|    "blocks.metadata" = false,                                |
|    "blocks.read" = false,                                    |
|    "blocks.read_only" = false,                               |
|    "blocks.read_only_allow_delete" = false,                  |
|    "blocks.write" = false,                                   |
|    codec = 'default',                                        |
|    column_policy = 'strict',                                 |
|    "mapping.total_fields.limit" = 1000,                      |
|    max_ngram_diff = 1,                                       |
|    max_shingle_diff = 3,                                     |
|    number_of_replicas = '0-1',                               |
|    refresh_interval = 1000,                                  |
|    "routing.allocation.enable" = 'all',                      |
|    "routing.allocation.total_shards_per_node" = -1,          |
|    "store.type" = 'fs',                                      |
|    "translog.durability" = 'REQUEST',                        |
|    "translog.flush_threshold_size" = 536870912,              |
|    "translog.sync_interval" = 5000,                          |
|    "unassigned.node_left.delayed_timeout" = 60000,           |
|    "write.wait_for_active_shards" = '1'                      |
| )                                                            |
+--------------------------------------------------------------+

cr> show create table Ebs.Fnd_User;
+-----------------------------------------------------+
| SHOW CREATE TABLE ebs.fnd_user                      |
+-----------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "ebs"."fnd_user" (       |
|    "user_id" BIGINT,                                |
|    "user_name" TEXT,                                |
|    "description" TEXT                               |
| )                                                   |
| CLUSTERED INTO 4 SHARDS                             |
| WITH (                                              |
|    "allocation.max_retries" = 5,                    |
|    "blocks.metadata" = false,                       |
|    "blocks.read" = false,                           |
|    "blocks.read_only" = false,                      |
|    "blocks.read_only_allow_delete" = false,         |
|    "blocks.write" = false,                          |
|    codec = 'default',                               |
|    column_policy = 'strict',                        |
|    "mapping.total_fields.limit" = 1000,             |
|    max_ngram_diff = 1,                              |
|    max_shingle_diff = 3,                            |
|    number_of_replicas = '0-1',                      |
|    refresh_interval = 1000,                         |
|    "routing.allocation.enable" = 'all',             |
|    "routing.allocation.total_shards_per_node" = -1, |
|    "store.type" = 'fs',                             |
|    "translog.durability" = 'REQUEST',               |
|    "translog.flush_threshold_size" = 536870912,     |
|    "translog.sync_interval" = 5000,                 |
|    "unassigned.node_left.delayed_timeout" = 60000,  |
|    "write.wait_for_active_shards" = '1'             |
| )                                                   |
+-----------------------------------------------------+
SHOW 1 row in set (0.002 sec)

cr> show create table Ebs.Mtl_Material_Transactions;
+----------------------------------------------------------------+
| SHOW CREATE TABLE ebs.mtl_material_transactions                |
+----------------------------------------------------------------+
| CREATE TABLE IF NOT EXISTS "ebs"."mtl_material_transactions" ( |
|    "transaction_id" TEXT,                                      |
|    "created_by" TEXT                                           |
| )                                                              |
| CLUSTERED INTO 4 SHARDS                                        |
| WITH (                                                         |
|    "allocation.max_retries" = 5,                               |
|    "blocks.metadata" = false,                                  |
|    "blocks.read" = false,                                      |
|    "blocks.read_only" = false,                                 |
|    "blocks.read_only_allow_delete" = false,                    |
|    "blocks.write" = false,                                     |
|    codec = 'default',                                          |
|    column_policy = 'strict',                                   |
|    "mapping.total_fields.limit" = 1000,                        |
|    max_ngram_diff = 1,                                         |
|    max_shingle_diff = 3,                                       |
|    number_of_replicas = '0-1',                                 |
|    refresh_interval = 1000,                                    |
|    "routing.allocation.enable" = 'all',                        |
|    "routing.allocation.total_shards_per_node" = -1,            |
|    "store.type" = 'fs',                                        |
|    "translog.durability" = 'REQUEST',                          |
|    "translog.flush_threshold_size" = 536870912,                |
|    "translog.sync_interval" = 5000,                            |
|    "unassigned.node_left.delayed_timeout" = 60000,             |
|    "write.wait_for_active_shards" = '1'                        |
| )                                                              |
+----------------------------------------------------------------+
SHOW 1 row in set (0.002 sec)

surister · September 11, 2024, 9:11am

If you run just the select

SELECT
  Ebs.Fnd_User.Description
FROM
  Ebs.Mtl_Material_Transactions
  JOIN Ebs.Fnd_User ON Ebs.Fnd_User.User_Id = Ebs.Mtl_Material_Transactions.Created_By
WHERE
  Ebs.Mtl_Material_Transactions.Transaction_Id = Ebs.Rwmv_Xxmtl_Transactions.Transaction_Id

You get the same error
[9/11/2024, 11:04:11 AM]: RelationUnknown[Relation 'ebs.rwmv_xxmtl_transactions' unknown]

ebs.fnd_user is being joined with mtl_material_transactions but on the conditional WHERE you are also introducing Rwmv_Xxmtl_Transactions which is not known by cratedb at that point since it’s not used in the join.

To simplify:

SELECT
  *
FROM
  a
  JOIN b ON b.foo = a.foo
WHERE
  a.foo = c.foo

 [9/11/2024, 11:11:01 AM]: RelationUnknown[Relation 'doc.c' unknown]

Is not valid

Topic		Replies	Views
Bug with "UPDATE" statement using subselect CrateDB	9	263	January 9, 2024
Cratedb update a column with 3 million records CrateDB	11	706	September 28, 2022
Bug \| JDBC not mapping properly in 4.5.0 CrateDB	2	648	April 28, 2021
Problem in `NOT ANY` Operator CrateDB	9	850	July 20, 2021
Queries against table with table.field_name != table.field_name failing CrateDB	0	602	October 16, 2018

Failed to update from joined table

Related topics