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 <[email protected]> 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 <[email protected]> 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 > > > > > > > > > > >
