[2.1.214] NulllPointerException in org.h2.command.query.Select.queryDistinct #3664

nioertel · 2022-10-28T14:02:26Z

Hi,

we see the following NPE during simple DISTINCT selects:

Caused by: org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.NullPointerException"; SQL statement:
SELECT DISTINCT CATEGORY FROM PRODUCT [50000-214]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:554)
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
        at org.h2.message.DbException.get(DbException.java:212)
        at org.h2.message.DbException.convert(DbException.java:395)
        at org.h2.command.Command.executeQuery(Command.java:211)
        at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:128)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
        at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:722)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
        ... 12 more
Caused by: java.lang.NullPointerException
        at org.h2.command.query.Select.queryDistinct(Select.java:703)
        at org.h2.command.query.Select.queryWithoutCache(Select.java:831)
        at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
        at org.h2.command.query.Query.query(Query.java:494)
        at org.h2.command.query.Query.query(Query.java:475)
        at org.h2.command.CommandContainer.query(CommandContainer.java:251)
        at org.h2.command.Command.executeQuery(Command.java:190)
        ... 17 more

The query is straight forward as you see above.
The issue only happens if I put LAZY_QUERY_EXECUTION=1; into the connection string.
I think the issue is located in org.h2.command.query.Select#queryWithoutCache, where, if the input target is null, and we have a DISTINCT query and lazy is set to true, result may stay stay null and also to will become null and that's exactly what causes the NPE in queryDistinct.

However we have multiple databases with the same schema and the issue does not occur on all of them. So I believe on top it has something to do with statistics or the actual data other.

The text was updated successfully, but these errors were encountered:

andreitokar · 2022-10-29T14:17:00Z

@nioertel
Hi Niels,
Unless you are planning to create PR with fix yourself, would it be possible to provide a minimal standalone (schema, data, connection) test case. From your description it seems to be not quite 100% reproducible, so you are in the best position to distil it into a small standalone repeatable test case..
Even if you going to create a PR yourself, some test case is due to go along with it.

nioertel · 2022-10-30T16:36:40Z

I have a database here with which I can reproduce the issue reliably. However I can't share this database as it contains confidential data.
I will debug through the call hierarchy until Select#queryDistinct and compare method parameters with a database that doesn't cause the NPE.
Hopefully that helps to create a simple reproduction case. I would then also prepare a PR with the fix.

If you have any other ideas/ hints, let me know.

nioertel · 2022-10-31T09:44:57Z

@andreitokar, after some debugging, I found out the cause but I'm not sure about the fix, as this seems to need some broader understanding of the Select internals. So here is my analysis. If you can point me towards what you'd change, I'm happy to prepare the fix.

When generating the query plan (Select#preparePlan) - even if the query is a SELECT DISTINCT ... - the internal attribute isDistinctQuery is only set to true under certain conditions (e.g. if the selectivity on the column in question in >= 20, this attribute will always be false!). I couldn't fully understand though what the meaning of this attribute is, especially compared to distinct:

h2database/h2/src/main/org/h2/command/query/Select.java

Lines 1203 to 1224 in 1ba3590

    
           if (distinct && session.getDatabase().getSettings().optimizeDistinct && 
        
                   !isGroupQuery && filters.size() == 1 && 
        
                   expressions.size() == 1 && condition == null) { 
        
               Expression expr = expressions.get(0); 
        
               expr = expr.getNonAliasExpression(); 
        
               if (expr instanceof ExpressionColumn) { 
        
                   Column column = ((ExpressionColumn) expr).getColumn(); 
        
                   int selectivity = column.getSelectivity(); 
        
                   Index columnIndex = topTableFilter.getTable(). 
        
                           getIndexForColumn(column, false, true); 
        
                   if (columnIndex != null && 
        
                           selectivity != Constants.SELECTIVITY_DEFAULT && 
        
                           selectivity < 20) { 
        
                       Index current = topTableFilter.getIndex(); 
        
                       // if another index is faster 
        
                       if (current == null || current.getIndexType().isScan() || columnIndex == current) { 
        
                           topTableFilter.setIndex(columnIndex); 
        
                           isDistinctQuery = true; 
        
                       } 
        
                   } 
        
               } 
        
           }

Now, when Select#queryWithoutCache is called and we have set LAZY_QUERY_EXECUTION=1;, the method parameter target will be null (as expected) and
- if isDistinctQuery is true, we're not matching any of the following conditions, so in line 810 the ResultTarget to is null
- if isDistinctQuery is false, we're matching the condition in line 791 and in line 810 the ResultTarget to is not null

h2database/h2/src/main/org/h2/command/query/Select.java

Lines 776 to 810 in 1ba3590

    
           LocalResult result = null; 
        
           if (!lazy && (target == null || 
        
                   !session.getDatabase().getSettings().optimizeInsertFromSelect)) { 
        
               result = createLocalResult(result); 
        
           } 
        
           // Do not add rows before OFFSET to result if possible 
        
           boolean quickOffset = !fetchPercent; 
        
           if (sort != null && (!sortUsingIndex || isAnyDistinct())) { 
        
               result = createLocalResult(result); 
        
               result.setSortOrder(sort); 
        
               if (!sortUsingIndex) { 
        
                   quickOffset = false; 
        
               } 
        
           } 
        
           if (distinct) { 
        
               if (!isDistinctQuery) { 
        
                   quickOffset = false; 
        
                   result = createLocalResult(result); 
        
                   result.setDistinct(); 
        
               } 
        
           } else if (distinctExpressions != null) { 
        
               quickOffset = false; 
        
               result = createLocalResult(result); 
        
               result.setDistinct(distinctIndexes); 
        
           } 
        
           if (isWindowQuery || isGroupQuery && !isGroupSortedQuery) { 
        
               result = createLocalResult(result); 
        
           } 
        
           if (!lazy && (fetch >= 0 || offset > 0)) { 
        
               result = createLocalResult(result); 
        
           } 
        
           topTableFilter.startQuery(session); 
        
           topTableFilter.reset(); 
        
           topTableFilter.lock(session); 
        
           ResultTarget to = result != null ? result : target;

Now, coming to the actual query execution, there are a couple of cases for which no LazyResults can be produced - one of them is the case with true == isDistinctQuery:

h2database/h2/src/main/org/h2/command/query/Select.java

Lines 812 to 838 in 1ba3590

    
           LazyResult lazyResult = null; 
        
           if (fetch != 0) { 
        
               // Cannot apply limit now if percent is specified 
        
               long limit = fetchPercent ? -1 : fetch; 
        
               if (isQuickAggregateQuery) { 
        
                   queryQuick(columnCount, to, quickOffset && offset > 0); 
        
               } else if (isWindowQuery) { 
        
                   if (isGroupQuery) { 
        
                       queryGroupWindow(columnCount, result, offset, quickOffset); 
        
                   } else { 
        
                       queryWindow(columnCount, result, offset, quickOffset); 
        
                   } 
        
               } else if (isGroupQuery) { 
        
                   if (isGroupSortedQuery) { 
        
                       lazyResult = queryGroupSorted(columnCount, to, offset, quickOffset); 
        
                   } else { 
        
                       queryGroup(columnCount, result, offset, quickOffset); 
        
                   } 
        
               } else if (isDistinctQuery) { 
        
                   queryDistinct(to, offset, limit, withTies, quickOffset); 
        
               } else { 
        
                   lazyResult = queryFlat(columnCount, to, offset, limit, withTies, quickOffset); 
        
               } 
        
               if (quickOffset) { 
        
                   offset = 0; 
        
               } 
        
           }

Coming back to 2.:

if isDistinctQuery is false, we're fine and queryFlat is called form line 833 (it's not really producing a LazyResult, though, because a ResultTarget is passed in)
however if isDistinctQuery is true, no ResultTarget is created and queryDistinct is called from line 831. This then causes the NPE.

nioertel · 2022-11-02T10:45:42Z

@andreitokar This would be the reproduction case:

diff --git a/h2/src/test/org/h2/test/db/TestOptimizations.java b/h2/src/test/org/h2/test/db/TestOptimizations.java
index 239582436..8c4592f93 100644
--- a/h2/src/test/org/h2/test/db/TestOptimizations.java
+++ b/h2/src/test/org/h2/test/db/TestOptimizations.java
@@ -746,6 +746,11 @@ private void testDistinctOptimization() throws SQLException {
             assertEquals(i, rs.getInt(1));
         }
         assertFalse(rs.next());
+        rs = stat.executeQuery("SELECT DISTINCT TYPE FROM TEST");
+        for (int i = 0; rs.next(); i++) {
+            assertEquals(i, rs.getInt(1));
+        }
+        assertFalse(rs.next());
         stat.execute("ANALYZE");
         rs = stat.executeQuery("SELECT DISTINCT TYPE FROM TEST " +
                 "ORDER BY TYPE");

When running ./mvnw package, you can see this error:

11:41:54 00:00.559 org.h2.test.db.TestOptimizations FAIL org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.NullPointerException"; SQL statement:
SELECT DISTINCT TYPE FROM TEST [50000-219]
ERROR: FAIL (lazy memory ) org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.NullPointerException"; SQL statement:
SELECT DISTINCT TYPE FROM TEST [50000-219] org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.NullPointerException"; SQL statement:
SELECT DISTINCT TYPE FROM TEST [50000-219] ------------------------------
org.h2.jdbc.JdbcSQLNonTransientException: General error: "java.lang.NullPointerException"; SQL statement:
SELECT DISTINCT TYPE FROM TEST [50000-219]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:554)
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
        at org.h2.message.DbException.get(DbException.java:212)
        at org.h2.message.DbException.convert(DbException.java:395)
        at org.h2.command.Command.executeQuery(Command.java:212)
        at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:99)
        at org.h2.test.db.TestOptimizations.testDistinctOptimization(TestOptimizations.java:751)
        at org.h2.test.db.TestOptimizations.test(TestOptimizations.java:76)
        at org.h2.test.TestBase.runTest(TestBase.java:147)
        at org.h2.test.TestAll.addTest(TestAll.java:990)
        at org.h2.test.TestAll.test(TestAll.java:845)
        at org.h2.test.TestAll.runTests(TestAll.java:619)
        at org.h2.test.TestAll.testAll(TestAll.java:549)
        at org.h2.test.TestAll.run(TestAll.java:490)
        at org.h2.test.TestAll.main(TestAll.java:434)
        at org.h2.test.TestAllJunit.testCI(TestAllJunit.java:21)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:686)
        at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
        at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
        at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
        at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
        at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
        at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
        at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
        at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
        at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
        at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
        at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
        at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
        at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
        at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:212)
        at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:208)
        at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:137)
        at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:71)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
        at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
        at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
        at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
        at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
        at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
        at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
        at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
        at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
        at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
        at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
        at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
        at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
        at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
        at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
        at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
        at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
        at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
        at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:220)
        at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:188)
        at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:202)
        at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:181)
        at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:128)
        at org.apache.maven.surefire.junitplatform.JUnitPlatformProvider.invokeAllTests(JUnitPlatformProvider.java:150)
        at org.apache.maven.surefire.junitplatform.JUnitPlatformProvider.invoke(JUnitPlatformProvider.java:124)
        at org.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:384)
        at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:345)
        at org.apache.maven.surefire.booter.ForkedBooter.execute(ForkedBooter.java:126)
        at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:418)
Caused by: java.lang.NullPointerException
        at org.h2.command.query.Select.queryDistinct(Select.java:703)
        at org.h2.command.query.Select.queryWithoutCache(Select.java:831)
        at org.h2.command.query.Query.queryWithoutCacheLazyCheck(Query.java:197)
        at org.h2.command.query.Query.query(Query.java:494)
        at org.h2.command.query.Query.query(Query.java:475)
        at org.h2.command.CommandContainer.query(CommandContainer.java:253)
        at org.h2.command.Command.executeQuery(Command.java:191)
        ... 76 more

katzyn · 2022-11-02T11:15:02Z

Thank you for a test case! I can reproduce it now.

nioertel · 2022-11-02T12:01:19Z

Perfect. Do you already have a plan on when version 2.2.219 will be released?

katzyn · 2022-11-02T12:10:16Z

Sorry, currently there are no plans for a new release.

From my point of view we're not yet ready for a new patch release in 2.1 series due to noticeable not yet resolved regressions

katzyn mentioned this issue Nov 2, 2022

Fix NPE in Select.queryDistinct() with lazy query execution enabled #3669

Merged

katzyn closed this as completed in #3669 Nov 2, 2022

katzyn mentioned this issue Jun 2, 2023

Intermittent null pointer exception when running SELECT DISTINCT col FROM table query in 2.1.214 #3810

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[2.1.214] NulllPointerException in org.h2.command.query.Select.queryDistinct #3664

[2.1.214] NulllPointerException in org.h2.command.query.Select.queryDistinct #3664

nioertel commented Oct 28, 2022

andreitokar commented Oct 29, 2022

nioertel commented Oct 30, 2022

nioertel commented Oct 31, 2022

nioertel commented Nov 2, 2022

katzyn commented Nov 2, 2022

nioertel commented Nov 2, 2022

katzyn commented Nov 2, 2022

[2.1.214] NulllPointerException in org.h2.command.query.Select.queryDistinct #3664

[2.1.214] NulllPointerException in org.h2.command.query.Select.queryDistinct #3664

Comments

nioertel commented Oct 28, 2022

andreitokar commented Oct 29, 2022

nioertel commented Oct 30, 2022

nioertel commented Oct 31, 2022

nioertel commented Nov 2, 2022

katzyn commented Nov 2, 2022

nioertel commented Nov 2, 2022

katzyn commented Nov 2, 2022