[CI] JdbcCsvSpecIT filter.testInWithCompatibleDateTypes failing

[Tim-Brooks]

CI Link

https://gradle-enterprise.elastic.co/s/2ilvmmhzgmk6q

Repro line

./gradlew ':x-pack:plugin:sql:qa:server:single-node:javaRestTest' --tests "org.elasticsearch.xpack.sql.qa.single_node.JdbcCsvSpecIT" -Dtests.method="test {filter.testInWithCompatibleDateTypes}" -Dtests.seed=8F84D9B640938385 -Dtests.locale=hi-IN -Dtests.timezone=Europe/Ljubljana -Druntime.java=20

Does it reproduce?

Yes

Applicable branches

main

Failure history

No response

Failure excerpt

1> [2023-06-13T18:09:01,578][INFO ][o.e.x.s.q.s.JdbcCsvSpecIT] [test] [filter.testInWithCompatibleDateTypes] after test
2> REPRODUCE WITH: ./gradlew ':x-pack:plugin:sql:qa:server:single-node:javaRestTest' --tests "org.elasticsearch.xpack.sql.qa.single_node.JdbcCsvSpecIT" -Dtests.method="test {filter.testInWithCompatibleDateTypes}" -Dtests.seed=8F84D9B640938385 -Dtests.locale=hi-IN -Dtests.timezone=Europe/Ljubljana -Druntime.java=20
2> java.lang.AssertionError: Expected more data but no more entries found after [12]
at __randomizedtesting.SeedInfo.seed([8F84D9B640938385:7D0E66CEE6FEE7D]:0)
at org.junit.Assert.fail(Assert.java:88)
at org.junit.Assert.assertTrue(Assert.java:41)
at org.elasticsearch.xpack.sql.qa.jdbc.JdbcAssert.doAssertResultSetData(JdbcAssert.java:244)
at org.elasticsearch.xpack.sql.qa.jdbc.JdbcAssert.assertResultSetData(JdbcAssert.java:227)
at org.elasticsearch.xpack.sql.qa.jdbc.JdbcAssert.assertResultSets(JdbcAssert.java:104)
at org.elasticsearch.xpack.sql.qa.jdbc.CsvSpecTestCase.assertResults(CsvSpecTestCase.java:60)
at org.elasticsearch.xpack.sql.qa.jdbc.CsvSpecTestCase.executeAndAssert(CsvSpecTestCase.java:67)
at org.elasticsearch.xpack.sql.qa.jdbc.CsvSpecTestCase.doTest(CsvSpecTestCase.java:49)
at org.elasticsearch.xpack.sql.qa.jdbc.SpecBaseIntegrationTestCase.test(SpecBaseIntegrationTestCase.java:92)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
at java.base/java.lang.reflect.Method.invoke(Method.java:578)
at com.carrotsearch.randomizedtesting.RandomizedRunner.invoke(RandomizedRunner.java:1758)
at com.carrotsearch.randomizedtesting.RandomizedRunner$8.evaluate(RandomizedRunner.java:946)
at com.carrotsearch.randomizedtesting.RandomizedRunner$9.evaluate(RandomizedRunner.java:982)
at com.carrotsearch.randomizedtesting.RandomizedRunner$10.evaluate(RandomizedRunner.java:996)
at com.carrotsearch.randomizedtesting.rules.StatementAdapter.evaluate(StatementAdapter.java:36)
at org.apache.lucene.tests.util.TestRuleSetupTeardownChained$1.evaluate(TestRuleSetupTeardownChained.java:48)
at org.apache.lucene.tests.util.AbstractBeforeAfterRule$1.evaluate(AbstractBeforeAfterRule.java:43)
at org.apache.lucene.tests.util.TestRuleThreadAndTestName$1.evaluate(TestRuleThreadAndTestName.java:45)
at org.apache.lucene.tests.util.TestRuleIgnoreAfterMaxFailures$1.evaluate(TestRuleIgnoreAfterMaxFailures.java:60)
at org.apache.lucene.tests.util.TestRuleMarkFailure$1.evaluate(TestRuleMarkFailure.java:44)
at com.carrotsearch.randomizedtesting.rules.StatementAdapter.evaluate(StatementAdapter.java:36)
at com.carrotsearch.randomizedtesting.ThreadLeakControl$StatementRunner.run(ThreadLeakControl.java:390)
at com.carrotsearch.randomizedtesting.ThreadLeakControl.forkTimeoutingTask(ThreadLeakControl.java:843)
at com.carrotsearch.randomizedtesting.ThreadLeakControl$3.evaluate(ThreadLeakControl.java:490)
at com.carrotsearch.randomizedtesting.RandomizedRunner.runSingleTest(RandomizedRunner.java:955)
at com.carrotsearch.randomizedtesting.RandomizedRunner$5.evaluate(RandomizedRunner.java:840)
at com.carrotsearch.randomizedtesting.RandomizedRunner$6.evaluate(RandomizedRunner.java:891)
at com.carrotsearch.randomizedtesting.RandomizedRunner$7.evaluate(RandomizedRunner.java:902)
at org.apache.lucene.tests.util.AbstractBeforeAfterRule$1.evaluate(AbstractBeforeAfterRule.java:43)
at com.carrotsearch.randomizedtesting.rules.StatementAdapter.evaluate(StatementAdapter.java:36)
at org.apache.lucene.tests.util.TestRuleStoreClassName$1.evaluate(TestRuleStoreClassName.java:38)
at com.carrotsearch.randomizedtesting.rules.NoShadowingOrOverridesOnMethodsRule$1.evaluate(NoShadowingOrOverridesOnMethodsRule.java:40)
at com.carrotsearch.randomizedtesting.rules.NoShadowingOrOverridesOnMethodsRule$1.evaluate(NoShadowingOrOverridesOnMethodsRule.java:40)
at com.carrotsearch.randomizedtesting.rules.StatementAdapter.evaluate(StatementAdapter.java:36)
at com.carrotsearch.randomizedtesting.rules.StatementAdapter.evaluate(StatementAdapter.java:36)
at org.apache.lucene.tests.util.TestRuleAssertionsRequired$1.evaluate(TestRuleAssertionsRequired.java:53)
at org.apache.lucene.tests.util.AbstractBeforeAfterRule$1.evaluate(AbstractBeforeAfterRule.java:43)
at org.apache.lucene.tests.util.TestRuleMarkFailure$1.evaluate(TestRuleMarkFailure.java:44)
at org.apache.lucene.tests.util.TestRuleIgnoreAfterMaxFailures$1.evaluate(TestRuleIgnoreAfterMaxFailures.java:60)
at org.apache.lucene.tests.util.TestRuleIgnoreTestSuites$1.evaluate(TestRuleIgnoreTestSuites.java:47)
at com.carrotsearch.randomizedtesting.rules.StatementAdapter.evaluate(StatementAdapter.java:36)
at com.carrotsearch.randomizedtesting.ThreadLeakControl$StatementRunner.run(ThreadLeakControl.java:390)
at com.carrotsearch.randomizedtesting.ThreadLeakControl.lambda$forkTimeoutingTask$0(ThreadLeakControl.java:850)
at java.base/java.lang.Thread.run(Thread.java:1623)

[elasticsearchmachine]

Pinging @elastic/es-ql (Team:QL)

[kingherc]

Another one here

[nik9000]

https://gradle-enterprise.elastic.co/s/c2zvfhvqfdbk4/tests/task/:x-pack:plugin:sql:qa:server:security:without-ssl:javaRestTest/details/org.elasticsearch.xpack.sql.qa.security.JdbcCsvSpecIT/test%20%7Bfilter.testInWithCompatibleDateTypes1%7D?top-execution=1

[ywangd]

Failed on my PR https://gradle-enterprise.elastic.co/s/6bmj7phfappdy/tests/task/:x-pack:plugin:sql:qa:server:single-node:javaRestTest/details/org.elasticsearch.xpack.sql.qa.single_node.JdbcCsvSpecIT/test%20%7Bfilter.testInWithCompatibleDateTypes1%7D?top-execution=1

[ldematte]

Failed on my PR too https://gradle-enterprise.elastic.co/s/tcegetnlbomrk/tests/task/:x-pack:plugin:sql:qa:server:single-node:javaRestTest/details/org.elasticsearch.xpack.sql.qa.single_node.JdbcCsvSpecIT/test%20%7Bfilter.testInWithCompatibleDateTypes1%7D?expanded-stacktrace=WyIwIl0&top-execution=1

[astefan]

Muted this test again with #100192. I think it was unmuted by mistake.

The problem happens for a fetch_size less than or equal to 9: the first set of documents comes ok, then using the cursor we ask for the next set and this is where problems start. No matter how many <=9 fetch_size documents we ask initially, the second (using the cursor id) always returns 4 documents or less. For the record, the test expects (correctly) 14 documents. Whatever is (14 - (fetch_size) > 4) == true will fail the test.

[astefan]

I've tested outside of SQL, opening a PIT and paginating with it like SQL would do it. And, if I am not mistaken, SQL does the right thing and ES itself stops returning all the documents it has to.

[astefan]

I have created #100260 using the same query ES SQL is using and a narrowed down set of test data.

[elasticsearchmachine]

Pinging @elastic/es-analytics-geo (Team:Analytics)

[elasticsearchmachine]

This issue has been closed because it has been open for too long with no activity.

Any muted tests that were associated with this issue have been unmuted.

If the tests begin failing again, a new issue will be opened, and they may be muted again.