You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Mohit Jain <mo...@open-insights.com> on 2019/08/08 10:18:01 UTC

GenerateTable fetch throws error with MS SQL 2012+

Hi Team,

I'm facing the following issue while using MS SQL 2012+. in Nifi 1.6.0 -

*GenerateTableFetch[id=87d43fae-5c02-1f3f-a58f-9fafeac5a640]
GenerateTableFetch[id=87d43fae-5c02-1f3f-a58f-9fafeac5a640] failed to
process session due to Order by clause cannot be null or empty when using
row paging; Processor Administratively Yielded for 1 sec:
java.lang.IllegalArgumentException: Order by clause cannot be null or empty
when using row paging*

It seems like a bug to me, is it resolved in the latest versions of Nifi?

Thanks,
Mohit

Re: GenerateTable fetch throws error with MS SQL 2012+

Posted by Mohit Jain <mo...@open-insights.com>.
Hi Matt,
Does it means that all the other DBs(Oracle, MySQL, etc) might fetch
duplicates rows with GenerateTableFetch?

Thanks,
Mohit

On Thu, Aug 8, 2019 at 8:16 PM Matt Burgess <ma...@apache.org> wrote:

> Mohit,
>
> MSSQL seems to have the only parser (of the more popular DBs) that
> complains about an empty ORDER BY clause when doing paging (with
> OFFSET / FETCH NEXT), so the default behavior is to throw an
> exception. This happens when you don't supply a Max Value Column,
> meaning you want to fetch all rows but haven't specified how to order
> them, to ensure that each page gets unique rows and all rows are
> returned. Otherwise each fetch could grab duplicate rows and some rows
> may never be fetched, as the ordering is arbitrary.  For some reason
> other DBs (PostgreSQL, Oracle) lets you try it with the documented
> caveat that the ordering is arbitrary.
>
> So an ordering must be applied, and to that end we added a Custom
> ORDER BY Clause property [1] such that the user can provide their own
> clause if no Max Value Column is specified. This property will be
> available in the upcoming NiFi 1.10.0 release. The only workarounds I
> know of are to use QueryDatabaseTable instead of GenerateTableFetch,
> or to supply a Max Value Column property value in GenerateTableFetch.
>
> Regards,
> Matt
>
> [1] https://issues.apache.org/jira/browse/NIFI-6348
>
> On Thu, Aug 8, 2019 at 6:18 AM Mohit Jain <mo...@open-insights.com> wrote:
> >
> > Hi Team,
> >
> > I'm facing the following issue while using MS SQL 2012+. in Nifi 1.6.0 -
> >
> > GenerateTableFetch[id=87d43fae-5c02-1f3f-a58f-9fafeac5a640]
> GenerateTableFetch[id=87d43fae-5c02-1f3f-a58f-9fafeac5a640] failed to
> process session due to Order by clause cannot be null or empty when using
> row paging; Processor Administratively Yielded for 1 sec:
> java.lang.IllegalArgumentException: Order by clause cannot be null or empty
> when using row paging
> >
> > It seems like a bug to me, is it resolved in the latest versions of Nifi?
> >
> > Thanks,
> > Mohit
> >
> >
> >
> >
> >
>

Re: GenerateTable fetch throws error with MS SQL 2012+

Posted by Matt Burgess <ma...@apache.org>.
Mohit,

MSSQL seems to have the only parser (of the more popular DBs) that
complains about an empty ORDER BY clause when doing paging (with
OFFSET / FETCH NEXT), so the default behavior is to throw an
exception. This happens when you don't supply a Max Value Column,
meaning you want to fetch all rows but haven't specified how to order
them, to ensure that each page gets unique rows and all rows are
returned. Otherwise each fetch could grab duplicate rows and some rows
may never be fetched, as the ordering is arbitrary.  For some reason
other DBs (PostgreSQL, Oracle) lets you try it with the documented
caveat that the ordering is arbitrary.

So an ordering must be applied, and to that end we added a Custom
ORDER BY Clause property [1] such that the user can provide their own
clause if no Max Value Column is specified. This property will be
available in the upcoming NiFi 1.10.0 release. The only workarounds I
know of are to use QueryDatabaseTable instead of GenerateTableFetch,
or to supply a Max Value Column property value in GenerateTableFetch.

Regards,
Matt

[1] https://issues.apache.org/jira/browse/NIFI-6348

On Thu, Aug 8, 2019 at 6:18 AM Mohit Jain <mo...@open-insights.com> wrote:
>
> Hi Team,
>
> I'm facing the following issue while using MS SQL 2012+. in Nifi 1.6.0 -
>
> GenerateTableFetch[id=87d43fae-5c02-1f3f-a58f-9fafeac5a640] GenerateTableFetch[id=87d43fae-5c02-1f3f-a58f-9fafeac5a640] failed to process session due to Order by clause cannot be null or empty when using row paging; Processor Administratively Yielded for 1 sec: java.lang.IllegalArgumentException: Order by clause cannot be null or empty when using row paging
>
> It seems like a bug to me, is it resolved in the latest versions of Nifi?
>
> Thanks,
> Mohit
>
>
>
>
>