Basic SQL pagination returning incorrect results

[Courtney Robinson]

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 = ?

Then a >= ? - clearly shows there are values with a = ?

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)
}

[Courtney Robinson]

FYI - EXPLAIN shows that it uses the primary key index as expected, see below.

[Ilya Kazakov]

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 <[hidden email]>:

FYI - EXPLAIN shows that it uses the primary key index as expected, see below.

[Courtney Robinson]

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 <[hidden email]> 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 <[hidden email]>:

FYI - EXPLAIN shows that it uses the primary key index as expected, see below.

[ilya.kasnacheev]

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,

чт, 18 февр. 2021 г. в 11:44, Courtney Robinson <[hidden email]>:

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 <[hidden email]> 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 <[hidden email]>:

FYI - EXPLAIN shows that it uses the primary key index as expected, see below.