The extra query is getting triggered only when offset is greater than zero.
I didn't notice this query when offset is zero. On Sun, 10 Jul, 2022, 10:39 pm Jeremy Evans, <[email protected]> wrote: > On Sun, Jul 10, 2022 at 1:51 AM ashwin k <[email protected]> wrote: > >> Not sure if first query can be ignored? >> > > Not sure what you mean by "can be ignored". It's necessary to get the > columns, and you need to know the columns to create the second query. > > Thanks, > Jeremy > > >> >> On Sat, 9 Jul, 2022, 11:52 am Jeremy Evans, <[email protected]> >> wrote: >> >>> On Fri, Jul 8, 2022 at 9:41 PM ashwin k <[email protected]> wrote: >>> >>>> Limit fires an extra query when the offset is more than 1. I created a >>>> temp table and executed the query >>>> DB[:temp].limit(3,3).all >>>> >>>> *First it executed this query* >>>> START SELECT * FROM (SELECT * FROM "TEMP") "T1" WHERE (ROWNUM <= 1) >>>> - END ( 0.0060s) SELECT * FROM (SELECT * FROM "TEMP") "T1" WHERE >>>> (ROWNUM <= 1) >>>> >>>> *and then this* >>>> >>>> - START SELECT "ID", "NAME" FROM (SELECT "T1".*, ROWNUM >>>> "X_SEQUEL_ROW_NUMBER_X" FROM (SELECT * FROM "TEMP") "T1") "T1" WHERE >>>> (("X_SEQUEL_ROW_NUMBER_X" > 3) AND ("X_SEQUEL_ROW_NUMBER_X" <= (3 + 3))) >>>> >>>> - END ( 0.0033s) SELECT "ID", "NAME" FROM (SELECT "T1".*, ROWNUM >>>> "X_SEQUEL_ROW_NUMBER_X" FROM (SELECT * FROM "TEMP") "T1") "T1" WHERE >>>> (("X_SEQUEL_ROW_NUMBER_X" > 3) AND ("X_SEQUEL_ROW_NUMBER_X" <= (3 + 3))) => >>>> [{:id=>0.4e1, :name=>"d"}, {:id=>0.5e1, :name=>"e"}] >>>> >>> >>> Not sure what database you are using, but I'm guessing an old version of >>> Oracle? In order to run the query with a limit and offset on an old >>> version of Oracle, the limit and offset need to be emulated using ROWNUM, >>> and that requires getting the columns. The first query is run to get the >>> columns, and the second query to get the data (you can see the second query >>> uses the columns returned by the first query). >>> >>> Thanks, >>> Jeremy >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "sequel-talk" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/sequel-talk/CADGZSScb43%2B3r9M3-coN1G60B%3DuRJV_QuaRfY1XYBSf2SjKLAg%40mail.gmail.com >>> <https://groups.google.com/d/msgid/sequel-talk/CADGZSScb43%2B3r9M3-coN1G60B%3DuRJV_QuaRfY1XYBSf2SjKLAg%40mail.gmail.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "sequel-talk" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sequel-talk/CAPd3BLZ%2BChuU6%2BBbL4FsxBnafjqd13Z4_CBpGbAep0cUSTe6Uw%40mail.gmail.com >> <https://groups.google.com/d/msgid/sequel-talk/CAPd3BLZ%2BChuU6%2BBbL4FsxBnafjqd13Z4_CBpGbAep0cUSTe6Uw%40mail.gmail.com?utm_medium=email&utm_source=footer> >> . >> > -- > You received this message because you are subscribed to the Google Groups > "sequel-talk" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sequel-talk/CADGZSSca%2BbZiwyU8UaYNg_FwmT%3DscUuVDj-Ju40vG%2B848kRvkQ%40mail.gmail.com > <https://groups.google.com/d/msgid/sequel-talk/CADGZSSca%2BbZiwyU8UaYNg_FwmT%3DscUuVDj-Ju40vG%2B848kRvkQ%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/CAPd3BLaesLrqOPCruK_M6SNx127pN6CZpGJ21PYh8xr5j3-yTw%40mail.gmail.com.
