JDBC Query will add "limit" at the end of "select * from tablename"

[titawork]

Why JDBC Query add "limit" at the end of "select * from tablename" ?
I think for tables with large of records, select without limit will spend long time, but add "LIMIT" for all query execute is not a good way.
But when I query with "select * from tablename offset 10 rows fetch next 10 rows only", it will run into error with the added "limit".

[cdessanti]

Yes is a limit clause is undetected a limit 150000 will be added. As a workaround you can add a limit with a big nunber will fix this. Also using a limit with offset would be fix you error. To maximize the performance on this kind of query i suggest you to use rowid pseudo column (if you haven't filters or group y on your query) Ottieni Outlook per Android<https://aka.ms/ghei36>

…

________________________________ From: titawork <[email protected]> Sent: Monday, July 1, 2019 10:12:59 AM To: omnisci/omniscidb Cc: Subscribed Subject: [omnisci/omniscidb] JDBC Query will add "limit" at the end of "select * from tablename" (#360) Why JDBC Query add "limit" at the end of "select * from tablename" ? I think for tables with large of records, select without limit will spend long time, but add "LIMIT" for all query execute is not a good way. But when I query with "select * from tablename offset 10 rows fetch next 10 rows only", it will run into error with the added "limit". — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub<https://github.com/omnisci/omniscidb/issues/360?email_source=notifications&email_token=AHLFBF6JTSAA7XOETSWNKWTP5G4AXA5CNFSM4H4P23X2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4G4SKV6Q>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AHLFBFZFFHX7RF35T5D2LNDP5G4AXANCNFSM4H4P23XQ>.

[titawork]

Thanks.
It is a good idea with using rowid pseudo column.
But I think a limit added is not good. It is not flexible.
Hope you can consider about delete this feature.

[cdessanti]

We could add a connection parameter to make users able to disable the limit at their own risk; the limit is added to the statement at parse level, so we haven't any idea about the size of the table (assuming the query consists of just one table).
We could also enhance the rewrite of the statement to manage the offset clause.

Without the limit, the driver could allocate a considerable chunk of memory to fit the entire dataset client side.

About using the rowid, is more efficient than limit/offset clause, but you can use just on simple projection queries like select * from tab.

p.s. @randyzwitch this issue would be moved to jdbc driver repository

[mlazatinph]

May we know the reason why the JDBC driver is overriding user input, i.e. when the user did not set any limit, why is the JDBC driver setting a limit?