dbt run: Trips when invoked twice

[amotl]

Problem

When invoking dbt run twice on the delete+insert materialization example project added per crate/cratedb-examples#784, it errors out.

Error output

23:14:04  1 of 1 START sql incremental model doc.test .................................... [RUN]
23:14:04  1 of 1 ERROR creating sql incremental model doc.test ........................... [ERROR in 0.42s]
23:14:05
23:14:05  Finished running 1 incremental model in 0 hours 0 minutes and 0.56 seconds (0.56s).
23:14:05
23:14:05  Completed with 1 error, 0 partial successes, and 0 warnings:
23:14:05
23:14:05    Database Error in model test (models/example/test.sql)
  Column classification['type'] unknown
  CONTEXT:  io.crate.metadata.doc.DocTableInfo.getDynamic(DocTableInfo.java:684)
  io.crate.metadata.doc.DocTableInfo.resolveColumn(DocTableInfo.java:710)
  io.crate.analyze.InsertAnalyzer.lambda$analyze$0(InsertAnalyzer.java:125)
  java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:215)
  java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1709)
  java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:570)
  java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:560)
  java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:636)
  java.base/java.util.stream.AbstractPipeline.evaluateToArrayNode(AbstractPipeline.java:291)
  java.base/java.util.stream.ReferencePipeline.toArray(ReferencePipeline.java:656)
  java.base/java.util.stream.ReferencePipeline.toArray(ReferencePipeline.java:662)
  java.base/java.util.stream.ReferencePipeline.toList(ReferencePipeline.java:667)
  io.crate.analyze.InsertAnalyzer.analyze(InsertAnalyzer.java:126)
  io.crate.analyze.Analyzer$AnalyzerDispatcher.visitInsert(Analyzer.java:543)
  io.crate.analyze.Analyzer$AnalyzerDispatcher.visitInsert(Analyzer.java:261)
  io.crate.sql.tree.Insert.accept(Insert.java:102)
  io.crate.analyze.Analyzer.analyze(Analyzer.java:243)
  io.crate.session.Session.analyze(Session.java:362)
  io.crate.protocols.postgres.PostgresWireProtocol.handleSingleQuery(PostgresWireProtocol.java:823)
  io.crate.protocols.postgres.PostgresWireProtocol.lambda$handleSimpleQuery$6(PostgresWireProtocol.java:809)
  compiled code at target/run/testdrive_materialized/models/example/test.sql
23:14:05
23:14:05  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

[hlcianfagna]

The relevant statement that is sent to CrateDB is:

insert into "crate"."doc"."test" ("classification", "classification['type']", "classification['labels']", "ended", "error", "id", "node", "node['id']", "node['name']", "started", "stmt", "username")
    (
        select "classification", "classification['type']", "classification['labels']", "ended", "error", "id", "node", "node['id']", "node['name']", "started", "stmt", "username"
        from "test__dbt_tmp112145669990"
    )

The way to solve this would be to skip in both lists of columns all fields with square brackets as the parent fields will always also be listed.

[amotl]

Thanks for confirming and for suggesting a solution. So that solution would be a special logic tied to CrateDB, enabled unconditionally?

Should it be applied in all cases, and just work "locally on the spot", based on looking at the presence of square brackets within the query statement at hand ¹, or should it consider using or retrieving any table schema information, when applicable?

Footnotes

1. I don't even know if the statement is available as a whole at this spot at all. It depends on where it is being rendered. I will need to check. ↩

[amotl]

Thanks for submitting that patch. 💯

• Improve dbt materialization example cratedb-examples#863

Feel free to close this issue if that was the solution already.

[hlcianfagna]

Feel free to close this issue if that was the solution already.

That was just a workaround to unblock the example, but we still need to address the issue

[amotl]

The way to solve this would be to skip in both lists of columns all fields with square brackets as the parent fields will always also be listed.

Would it be around this spot that would need corresponding adjustments?

[amotl]

Gentle ping to self ;].