nifi-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mohit Jain <mo...@open-insights.com>
Subject Re: GenerateTable fetch throws error with MS SQL 2012+
Date Fri, 09 Aug 2019 06:14:56 GMT
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 <mattyb149@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 <mohit@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
> >
> >
> >
> >
> >
>

Mime
View raw message