On Mon, Jul 11, 2022 at 1:54 AM ashwin k <[email protected]> wrote:
> The extra query is getting triggered only when offset is greater than zero. > > I didn't notice this query when offset is zero. > When offset is zero, the query to get the columns is not necessary, so Sequel doesn't issue it. Thanks, Jeremy > 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 > <https://groups.google.com/d/msgid/sequel-talk/CAPd3BLaesLrqOPCruK_M6SNx127pN6CZpGJ21PYh8xr5j3-yTw%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/CADGZSSf4LovejckXk%2BOw__OWMpnw4MHbs6xR5tx1iNXPHRLfhg%40mail.gmail.com.
