Hi Panos,
NiFi includes Junit test: /nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/util/TestJdbcHugeStream.java This test also use aliases: *final* ResultSet resultSet = st.executeQuery("select " + " PER.ID as PersonId, PER.NAME as PersonName, PER.CODE as PersonCode" + ", PRD.ID as ProductId,PRD.NAME as ProductName,PRD.CODE as ProductCode" + ", REL.ID as RelId, REL.NAME as RelName, REL.CODE as RelCode" + ", ROW_NUMBER() OVER () as rownr " + " from persons PER, products PRD, relationships REL"); When Derby database is used test is successful. Please can you run this test using MariaDB? Maybe MariaDB JDBC driver have some unexpected behavior? Unfortunately I don't have running MariaDB at the moment. I won't exclude also bug in NiFi. Thanks Toivo 2016-04-25 12:13 GMT+03:00 Panos Geo <panospanos1...@outlook.com>: > Hello Toivo, all, > > I am not sure what else would you require from me to test this. In my > view, this appears to be a bug as no matter what I've tried any SQL > statement where fields from different tables have the same name, would > force the SQL execute processor to throw an > "org.apache.avro.AvroRuntimeException: Duplicate field name in record". > > For example, this statement : > > "SELECT *plant.name <http://plant.name>*, area.area_id, *area.name > <http://area.name>* > FROM plant, area > WHERE plant.plant_id=area.plant_id;" > > Aliases in the SQL statement are ignored by the SQL execute processor. > Direct (i.e. no through NiFi) SQL statement execution in MariaDB works > correctly. > > I would appreciate any help with this. > > Many thanks in advance, > Panos > > > > > ------------------------------ > Date: Thu, 21 Apr 2016 19:36:22 +0300 > > Subject: Re: AvroRuntimeException : Duplicate field name > From: toivo.ad...@gmail.com > To: users@nifi.apache.org > > It seems ExecuteSQL Junit test case must be created. > Then we can investigate problem. > > Unfortunately I don't have other ideas (at least at the moment). > > thanks > toivo > > 2016-04-21 19:26 GMT+03:00 Panos Geo <panospanos1...@outlook.com>: > > No worries, I appreciate your help anyhow. > > I am using MariaDB, but I get the below as warning when I start NiFi even > before triggering the processor to execute the SQL statement. When I do > trigger the processor to execute the SQL query, then I see the > AvroRuntimeException as full error... > > Thanks, > Panos > > ------------------------------ > Date: Thu, 21 Apr 2016 19:16:46 +0300 > > Subject: Re: AvroRuntimeException : Duplicate field name > From: toivo.ad...@gmail.com > To: users@nifi.apache.org > > Sorry, I didn't read your email carefully enough. > > Alias should work. > Which database you are using? > > thanks > toivo > > 2016-04-21 19:05 GMT+03:00 Panos Geo <panospanos1...@outlook.com>: > > Hello Toivo, > > Many thanks for your reply! As I have indicated in my initial email, using > aliases doesn't make any difference. It appears as if they are ignored and > I am getting the same error. > > Any other thoughts? > > Many thanks, > Panos > > > ------------------------------ > Date: Thu, 21 Apr 2016 18:59:33 +0300 > Subject: Re: AvroRuntimeException : Duplicate field name > From: toivo.ad...@gmail.com > To: users@nifi.apache.org > > > Hi, > > Field names should be unique. > Currently after executing query both 'plant.name' and 'area.name' will be > just same 'name' > > You can use alias to have unique name, like: > SELECT plant.name as pname, area.area_id, area.name as aname > > thanks > toivo > > 2016-04-21 18:51 GMT+03:00 Panos Geo <panospanos1...@outlook.com>: > > Hello all, > > I would appreciate your help in the Avro error that I am seeing. I am > executing the following very simple SQL select statement using the > ExecuteSQL processor : > > SELECT plant.name, area.area_id, area.name > FROM plant, area > WHERE plant.plant_id=area.plant_id; > > …and I get the following error message > “org.apache.avro.AvroRuntimeException: Duplicate field name in record > any.data.plant: name type:UNION pos:2 and name type:UNION pos:0.” > essentially telling me that I have two fields in the select statement with > the same name (plant.name and area.name) even though they are in > different tables (full trace below). > > I have tried to rename the field in the result (e.g. SELECT plant.name as > differentname) or try a JOIN equivalent statement but I still get the same > error. Is this a bug or am I doing something wrong? > > Many thanks for your help in advance, > Panos > > Stack trace : > 2016-04-21 16:52:41,888 ERROR [Timer-Driven Process Thread-10] > o.a.nifi.processors.standard.ExecuteSQL > ExecuteSQL[id=e8fbc2aa-8594-4011-9a9e-bd93d0c2e7f9] > ExecuteSQL[id=e8fbc2aa-8594-4011-9a9e-bd93d0c2e7f9] failed to process > session due to org.apache.avro.AvroRuntimeException: Duplicate field name > in record any.data.plant: name type:UNION pos:2 and name type:UNION pos:0.: > org.apache.avro.AvroRuntimeException: Duplicate field name in record > any.data.plant: name type:UNION pos:2 and name type:UNION pos:0. > 2016-04-21 16:52:41,889 ERROR [Timer-Driven Process Thread-10] > o.a.nifi.processors.standard.ExecuteSQL > org.apache.avro.AvroRuntimeException: Duplicate field name in record > any.data.plant: name type:UNION pos:2 and name type:UNION pos:0. > at org.apache.avro.Schema$RecordSchema.setFields(Schema.java:590) ~[na:na] > at > org.apache.avro.SchemaBuilder$FieldAssembler.endRecord(SchemaBuilder.java:2013) > ~[na:na] > at > org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(JdbcCommon.java:245) > ~[na:na] > at > org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:82) > ~[na:na] > at > org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:72) > ~[na:na] > at > org.apache.nifi.processors.standard.ExecuteSQL$1.process(ExecuteSQL.java:158) > ~[na:na] > at > org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:1954) > ~[nifi-framework-core-0.6.0-SNAPSHOT.jar:0.6.0-SNAPSHOT] > at > org.apache.nifi.processors.standard.ExecuteSQL.onTrigger(ExecuteSQL.java:152) > ~[na:na] > at > org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) > ~[nifi-api-0.6.0-SNAPSHOT.jar:0.6.0-SNAPSHOT] > at > org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1057) > ~[nifi-framework-core-0.6.0-SNAPSHOT.jar:0.6.0-SNAPSHOT] > at > org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) > [nifi-framework-core-0.6.0-SNAPSHOT.jar:0.6.0-SNAPSHOT] > at > org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) > [nifi-framework-core-0.6.0-SNAPSHOT.jar:0.6.0-SNAPSHOT] > at > org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:123) > [nifi-framework-core-0.6.0-SNAPSHOT.jar:0.6.0-SNAPSHOT] > at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) > [na:1.8.0_77] > at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) > [na:1.8.0_77] > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) > [na:1.8.0_77] > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) > [na:1.8.0_77] > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) > [na:1.8.0_77] > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) > [na:1.8.0_77] > at java.lang.Thread.run(Thread.java:745) [na:1.8.0_77] > > > > >