Julian, > Do you need to generate a different plan (i.e. a different tree of RelNodes) > for scrolling vs non-scrolling? The plan is same. I just need to construct a different ES query and batching Enumerator
On Thu, Oct 25, 2018 at 1:31 PM Julian Hyde <jh...@apache.org> wrote: > > Do you need to generate a different plan (i.e. a different tree of RelNodes) > for scrolling vs non-scrolling? If so, it’s certainly inconvenient that you > don’t know until execute time whether they want scrolling. A possible > solution would be to generate TWO plans - one scrolling, one non-scrolling > inside the prepared statement - and pick which one based on the runtime > context. > > Julian > > > On Oct 25, 2018, at 1:42 AM, Christian Beikov <christian.bei...@gmail.com> > > wrote: > > > > Hey Andrei, > > > > I don't have an answer for how you can access these settings from within > > the adapter nor how one could do that via RelNodes but the suggestion to > > use DataContext for that purpose sounds reasonable. Maybe someone else has > > an idea? > > > > Anyway, since these are settings that don't affect the general semantics of > > the query/statement and also usually require a special API to be used, I'd > > rather see these aspects not end up in the query string. > > > > Am 25.10.2018 um 02:15 schrieb Andrei Sereda: > >> Christian, > >> > >> I like TYPE_SCROLL_INSENSITIVE / fetchSize in PreparedStatement > >> generally but have some reservations (questions) : > >> > >> How to pass resultSetType / fetchSize from PreparedStatement to RelNodes ? > >> What if user doesn’t use JDBC (eg. RelBuilders) ? > >> On Wed, Oct 24, 2018 at 6:28 PM Christian Beikov > >> <christian.bei...@gmail.com> wrote: > >>> In JDBC one can configure a fetch size which would reflect the amount of > >>> rows to be fetched initially, but also subsequently. > >>> https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setFetchSize(int) > >>> > >>> According to what you are writing, ES behvior is what > >>> TYPE_SCROLL_INSENSITIVE would do i.e. provide a snapshot view that isn't > >>> affected by changes. > >>> > >>> IMO TYPE_SCROLL_SENSITIVE means that if you have rows R1, R2, R3, R4, > >>> ... and view R1, R2, then R3 is deleted and you fetch the next rows, you > >>> wouldn't see R3. > >>> > >>> According to the JDBC spec > >>> (https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20int,%20int) > >>> ) you don't have to support all modes. Usually, user code doesn't use > >>> scrolling that much, but at least forward scrolling makes sense. > >>> > >>> Am 24.10.2018 um 21:38 schrieb Andrei Sereda: > >>>> Hi Julian, > >>>> > >>>> Scrolling (in elastic) does not only mean “open a cursor” but also > >>>> iterate > >>>> over consistent snapshot. From docs: > >>>> > >>>> The results that are returned from a scroll request reflect the state of > >>>> the index at the time that the initial search request was made, like a > >>>> snapshot in time. Subsequent changes to documents (index, update or > >>>> delete) > >>>> will only affect later search requests. > >>>> > >>>> So pagination (fetch / offset) can’t exactly replicate this > >>>> functionality. > >>>> > >>>> The problem with scrolling (in elastic) is that it is expensive and can’t > >>>> (shouldn’t) be enabled it by default. > >>>> > >>>> There is one more “issue”. Currently select * from elastic returns at > >>>> most > >>>> 10 rows (in calcite). This is consistent with elastic behaviour which > >>>> limits result set to 10 documents (unless size is specified). When > >>>> returning a cursor (eg. using JDBC TYPE_SCROLL_SENSITIVE > >>>> <https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#TYPE_SCROLL_SENSITIVE> > >>>> or SQL hint) does it mean return whole elastic index ? I’m not at ease > >>>> with > >>>> returning different results based on hints or cursor settings. > >>>> > >>>> Andrei. > >>>> > >>>> On Wed, Oct 24, 2018 at 3:02 PM Julian Hyde <jhyde.apa...@gmail.com> > >>>> wrote: > >>>> > >>>>> It seems to me that Elasticsearch scroll means return a cursor - a > >>>>> collection of rows that you iterate over, and you may not read all of > >>>>> them. > >>>>> This is the default operation of JDBC. > >>>>> > >>>>> So, I guess we need to give the user a way to signal their intent to > >>>>> read > >>>>> all rows versus only the first few. Oracle’s FIRST_ROWS and ALL_ROWS > >>>>> hints[1] seem close to this. We would want the hints to be acted upon by > >>>>> both the optimizer and the JDBC transport. > >>>>> > >>>>> Related is pagination. SQL has FETCH and OFFSET, which allow you to > >>>>> retrieve different pieces of a large result set in separate statements > >>>>> or > >>>>> (using query parameters) executions. It would be useful if the server > >>>>> could > >>>>> be given a hint to cache a statement across page requests. > >>>>> > >>>>> Julian > >>>>> > >>>>> [1] > >>>>> https://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#4924 > >>>>> > >>>>>> On Oct 24, 2018, at 11:19 AM, Christian Beikov < > >>>>> christian.bei...@gmail.com> wrote: > >>>>>> Hey, > >>>>>> > >>>>>> not sure if this should be an SQL keyword. JDBC specifies various > >>>>> constants that can be used at statement creation time: > >>>>> https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html > >>>>>> Not sure though if or how these configurations are accessible for data > >>>>> stores or dialects, but IMO using these would be the proper way. > >>>>>> Regards > >>>>>> > >>>>>> Christian > >>>>>> > >>>>>>> Am 24.10.2018 um 18:44 schrieb Andrei Sereda: > >>>>>>> Hello, > >>>>>>> > >>>>>>> > >>>>>>> I was thinking about adding [scrolling functionality]( > >>>>>>> > >>>>> https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-scroll.html > >>>>> ) > >>>>>>> to elastic search adapter. Since scrolling has non-negligible effect > >>>>>>> on > >>>>> the > >>>>>>> cluster it should be selectively enabled on per query basis. So, > >>>>>>> likely, > >>>>>>> user has to explicitly set "scroll flag" somewhere. > >>>>>>> > >>>>>>> Most natural way seems in SQL. [Calcite sql grammar]( > >>>>>>> https://calcite.apache.org/docs/reference.html) has `SCROLL` keyword > >>>>>>> (unused to my knowledge). There were also discussions about adding > >>>>> hints to > >>>>>>> Calcite. > >>>>>>> > >>>>>>> ### Examples > >>>>>>> ```sql > >>>>>>> -- special sql keyword ? > >>>>>>> SCROLL select * from elastic; > >>>>>>> > >>>>>>> -- assuming hints are available in calcite > >>>>>>> /* HINT: scroll */ select * from elastic; > >>>>>>> ``` > >>>>>>> > >>>>>>> What people think about this use-case ? Are there better ideas ? > >>>>>>> > >>>>>>> Regards, > >>>>>>> Andrei. > >>>>>>> >
