Basic SQL pagination returning incorrect results

[Courtney Robinson <co...@hypi.io>]

Using Ignite 2.8.1 to create a table with 2 cols as PK.
Note this test is running on a single node in an integration test so
partition key shouldn't come into play here.

If I use a = ? AND b > ? then there are no results. If I use a = ? AND b >=
? then there are results.
If b > ? is used without a = ? then it works. There are definitely results
that match the filter.

Example reproducer with Groovy is available at the end. Here are
screenshots of the cases that fail/pass.
First if I do a = ?
[image: Screenshot 2021-02-12 at 14.03.16.png]
Then a >= ? - clearly shows there are values with a = ?
[image: Screenshot 2021-02-12 at 14.02.54.png]
The example that produced the data and I expect to pass is below.
What I'm trying to do is paginate over all the data in a table the
"traverse" function below pulls N rows at a time and keeps track of which a
and b was last seen as well as what should be = vs >.
If there's an alternative way to map over all data in a SQL defined table
then let me know or point out what (if anything) I'm doing wrong here.

def "Can paginate over all rows of a table with two primary keys"() {
  given:
  def rand = new Random()
  repo.query("create table page1(a varchar, b varchar, c varchar,
PRIMARY KEY (a, b))")
  def generated = []
  def partitions = ['a', 'b', 'c']
  when:
  for (i in 0..<100) {
    def row = [partitions[rand.nextInt(3)], ULIDUtil.newId(), ULIDUtil.newId()]
    generated.add(row)
    assert repo.query("INSERT INTO page1(a,b,c) VALUES(?,?,?)", row as
Object[])[0].UPDATED == 1
  }
  def traversed = []
  def total = repo.traverse("page1", ['a', 'b'] as String[], 10, {
rows -> traversed += rows })
  then:
  total == generated.size()
  traversed.containsAll(generated)
}


Regards,
Courtney Robinson
Founder and CEO, Hypi
Tel: ++44 208 123 2413 (GMT+0) <https://hypi.io>

<https://hypi.io>
https://hypi.io

Re: Basic SQL pagination returning incorrect results

[Ilya Kasnacheev <il...@gmail.com>]

Hello!

You should be able to find commit(s) in ignite-2.9.1 branch named
"IGNITE-13765 Yadda yadda ..." and cherry-pick them to ignite-2.8.1 branch.
https://github.com/apache/ignite

Regards,
-- 
Ilya Kasnacheev


чт, 18 февр. 2021 г. в 11:44, Courtney Robinson <co...@hypi.io>:

> Hey Ilya,
> Good find. I checked Jira but didn't find this.
> Is there a path that can be applied to 2.8.1 for this?
> We haven't upgraded to 2.9.1 yet because when we attempted to do so in our
> test env we got data corruption in Ignite persistence. As a result we were
> going to put this aside until we had more time to workout why it happened
>
> On Mon, Feb 15, 2021 at 1:12 AM Ilya Kazakov <ka...@gmail.com>
> wrote:
>
>> Hello, Courtney!
>>
>> It looks like a known issue:
>> https://issues.apache.org/jira/browse/IGNITE-13765
>> It was fixed in 2.9.1, Could you check your case in 2.9.1, please?
>>
>> ---------
>> Ilya
>>
>> пт, 12 февр. 2021 г. в 22:31, Courtney Robinson <
>> courtney.robinson@hypi.io>:
>>
>>> FYI - EXPLAIN shows that it uses the primary key index as expected, see
>>> below.
>>> [image: Screenshot 2021-02-12 at 14.29.06.png][image: Screenshot
>>> 2021-02-12 at 14.28.56.png][image: Screenshot 2021-02-12 at
>>> 14.28.20.png][image: Screenshot 2021-02-12 at 14.28.01.png]
>>>
>>>>

Re: Basic SQL pagination returning incorrect results

[Courtney Robinson <co...@hypi.io>]

Hey Ilya,
Good find. I checked Jira but didn't find this.
Is there a path that can be applied to 2.8.1 for this?
We haven't upgraded to 2.9.1 yet because when we attempted to do so in our
test env we got data corruption in Ignite persistence. As a result we were
going to put this aside until we had more time to workout why it happened

On Mon, Feb 15, 2021 at 1:12 AM Ilya Kazakov <ka...@gmail.com> wrote:

> Hello, Courtney!
>
> It looks like a known issue:
> https://issues.apache.org/jira/browse/IGNITE-13765
> It was fixed in 2.9.1, Could you check your case in 2.9.1, please?
>
> ---------
> Ilya
>
> пт, 12 февр. 2021 г. в 22:31, Courtney Robinson <courtney.robinson@hypi.io
> >:
>
>> FYI - EXPLAIN shows that it uses the primary key index as expected, see
>> below.
>> [image: Screenshot 2021-02-12 at 14.29.06.png][image: Screenshot
>> 2021-02-12 at 14.28.56.png][image: Screenshot 2021-02-12 at 14.28.20.png][image:
>> Screenshot 2021-02-12 at 14.28.01.png]
>>
>>>

Re: Basic SQL pagination returning incorrect results

[Ilya Kazakov <ka...@gmail.com>]

Hello, Courtney!

It looks like a known issue:
https://issues.apache.org/jira/browse/IGNITE-13765
It was fixed in 2.9.1, Could you check your case in 2.9.1, please?

---------
Ilya

пт, 12 февр. 2021 г. в 22:31, Courtney Robinson <co...@hypi.io>:

> FYI - EXPLAIN shows that it uses the primary key index as expected, see
> below.
> [image: Screenshot 2021-02-12 at 14.29.06.png][image: Screenshot
> 2021-02-12 at 14.28.56.png][image: Screenshot 2021-02-12 at 14.28.20.png][image:
> Screenshot 2021-02-12 at 14.28.01.png]
>
>>

Re: Basic SQL pagination returning incorrect results

[Courtney Robinson <co...@hypi.io>]

FYI - EXPLAIN shows that it uses the primary key index as expected, see
below.
[image: Screenshot 2021-02-12 at 14.29.06.png][image: Screenshot 2021-02-12
at 14.28.56.png][image: Screenshot 2021-02-12 at 14.28.20.png][image:
Screenshot 2021-02-12 at 14.28.01.png]

>