Select query on ExecuteSQL processor throws out ERROR duplicate filed on joining two tables

murali2425 — Thu, 23 Apr 2020 10:19:28 GMT

I have a situation, where I have to get the combined data from two tables from Mysql database, ExecuteSql processor throwing out bellow error, where as the same query is working fine from Mysql Workbench.

2020-04-22 14:55:06,597 ERROR [Timer-Driven Process Thread-8] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=9d81c7ae-0171-1000-f638-2f1e60bfe48e] Unable to execute SQL select query select
t1.CODE_CLOCK_ID,
t1.COMPANY_CD,
t1.GROUP_SEGMENT_L1,
t1.GROUP_SEGMENT_CD,
t2.CODE_CLOCK_ID,
t2.COMPANY_CD,
t2.GROUP_SEGMENT_L1,
t2.GROUP_SEGMENT_CD
from
sheet26 t1
INNER JOIN sheet27 t2
on t1.CODE_CLOCK_ID = t2.CODE_CLOCK_ID and t1.COMPANY_CD = t2.COMPANY_CD LIMIT 0, 1000; due to org.apache.nifi.processor.exception.ProcessException: org.apache.avro.AvroRuntimeException: Duplicate field CODE_CLOCK_ID in record any.data.sheet26: CODE_CLOCK_ID type:UNION pos:4 and CODE_CLOCK_ID type:UNION pos:0.. No FlowFile to route to failure: org.apache.nifi.processor.exception.ProcessException: org.apache.avro.AvroRuntimeException: Duplicate field CODE_CLOCK_ID in record any.data.sheet26: CODE_CLOCK_ID type:UNION pos:4 and CODE_CLOCK_ID type:UNION pos:0.
org.apache.nifi.processor.exception.ProcessException: org.apache.avro.AvroRuntimeException: Duplicate field CODE_CLOCK_ID in record any.data.sheet26: CODE_CLOCK_ID type:UNION pos:4 and CODE_CLOCK_ID type:UNION pos:0.
at org.apache.nifi.processors.standard.AbstractExecuteSQL.lambda$onTrigger$1(AbstractExecuteSQL.java:301)
at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2746)
at org.apache.nifi.processors.standard.AbstractExecuteSQL.onTrigger(AbstractExecuteSQL.java:297)
at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1176)
at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213)
at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:830)
Caused by: org.apache.avro.AvroRuntimeException: Duplicate field CODE_CLOCK_ID in record any.data.sheet26: CODE_CLOCK_ID type:UNION pos:4 and CODE_CLOCK_ID type:UNION pos:0.
at org.apache.avro.Schema$RecordSchema.setFields(Schema.java:651)
at org.apache.avro.SchemaBuilder$FieldAssembler.endRecord(SchemaBuilder.java:2013)
at org.apache.nifi.util.db.JdbcCommon.createSchema(JdbcCommon.java:636)
at org.apache.nifi.util.db.JdbcCommon.convertToAvroStream(JdbcCommon.java:239)
at org.apache.nifi.processors.standard.sql.DefaultAvroSqlWriter.writeResultSet(DefaultAvroSqlWriter.java:49)
at org.apache.nifi.processors.standard.AbstractExecuteSQL.lambda$onTrigger$1(AbstractExecuteSQL.java:299)
... 13 common frames omitted

what could be issue and what I have to change in query to get the record set from database?

I am using Nifi 1.11.4.

This issue happens even excluding INNER or even replacing INNER JOIN with "where" in the select query.

Re: Select query on ExecuteSQL processor throws out EROOR duplicate filed on joining two tables

Faerballert — Thu, 23 Apr 2020 07:43:57 GMT

maybe you should set for all or just for t1/t2.. fields an Alias like

t1.CODE_CLOCK_ID as CODE_CLOCK_ID_s26, t1.COMPANY_CD as COMPANY_CD_s26, t1.GROUP_SEGMENT_L1 as GROUP_SEGMENT_L1_s26, t1.GROUP_SEGMENT_CD as GROUP_SEGMENT_CD_s26, t2.CODE_CLOCK_ID asCODE_CLOCK_ID_s27, t2.COMPANY_CD as COMPANY_CD_s27, t2.GROUP_SEGMENT_L1 as GROUP_SEGMENT_L1_s27, t2.GROUP_SEGMENT_CD as GROUP_SEGMENT_CD_s27

Greets

Re: Select query on ExecuteSQL processor throws out EROOR duplicate filed on joining two tables

murali2425 — Thu, 23 Apr 2020 19:20:13 GMT

Hi, Faerballert,

Really it did work, thank you very much.

Re: Select query on ExecuteSQL processor throws out EROOR duplicate filed on joining two tables

yamaga — Fri, 13 Jan 2023 15:39:22 GMT

I want to extract a CSV file from SQL query

one of the columns should be present in the CSV twice, diffirent position.

I had the same error

is there a workaround ?

Thank you

question Re: Select query on ExecuteSQL processor throws out EROOR duplicate filed on joining two tables in Support Questions

Select query on ExecuteSQL processor throws out ERROR duplicate filed on joining two tables

Re: Select query on ExecuteSQL processor throws out EROOR duplicate filed on joining two tables

Re: Select query on ExecuteSQL processor throws out EROOR duplicate filed on joining two tables

Re: Select query on ExecuteSQL processor throws out EROOR duplicate filed on joining two tables