Basic SQL pagination returning incorrect results

classic Classic list List threaded Threaded
5 messages Options
Courtney Robinson Courtney Robinson
Reply | Threaded
Open this post in threaded view
|

Basic SQL pagination returning incorrect results

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 = ?
Screenshot 2021-02-12 at 14.03.16.png
Then a >= ? - clearly shows there are values with a = ?
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

https://hypi.io
Courtney Robinson Courtney Robinson
Reply | Threaded
Open this post in threaded view
|

Re: Basic SQL pagination returning incorrect results

FYI - EXPLAIN shows that it uses the primary key index as expected, see below.
Screenshot 2021-02-12 at 14.29.06.pngScreenshot 2021-02-12 at 14.28.56.pngScreenshot 2021-02-12 at 14.28.20.pngScreenshot 2021-02-12 at 14.28.01.png
Ilya Kazakov Ilya Kazakov
Reply | Threaded
Open this post in threaded view
|

Re: Basic SQL pagination returning incorrect results

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.
Screenshot 2021-02-12 at 14.29.06.pngScreenshot 2021-02-12 at 14.28.56.pngScreenshot 2021-02-12 at 14.28.20.pngScreenshot 2021-02-12 at 14.28.01.png
Courtney Robinson Courtney Robinson
Reply | Threaded
Open this post in threaded view
|

Re: Basic SQL pagination returning incorrect results

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.
Screenshot 2021-02-12 at 14.29.06.pngScreenshot 2021-02-12 at 14.28.56.pngScreenshot 2021-02-12 at 14.28.20.pngScreenshot 2021-02-12 at 14.28.01.png
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Basic SQL pagination returning incorrect results

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.

Regards,
--
Ilya Kasnacheev


чт, 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.
Screenshot 2021-02-12 at 14.29.06.pngScreenshot 2021-02-12 at 14.28.56.pngScreenshot 2021-02-12 at 14.28.20.pngScreenshot 2021-02-12 at 14.28.01.png