I found the reason. The subquery was not cached, even so it is not marked 
as lazy. I attached the patch which solves this particular problem.  


On Wednesday, April 11, 2018 at 6:36:42 PM UTC-4, Pavel wrote:
>
> The example below runs less than a second. If I change connection parameter 
> to LAZY_QUERY_EXECUTION=1 then it takes forever.
>
>
> I tested with both 1.4.196 and 1.4.197. The same result.
>
>
> @Test
> public void subquery() throws Exception {
>     Class.forName("org.h2.Driver");
>     try (Connection conn = 
> DriverManager.getConnection("jdbc:h2:mem:;LAZY_QUERY_EXECUTION=0")) {
>         try (Statement stat = conn.createStatement()) {
>             final int ROWS = 100000;
>             stat.execute("CREATE TABLE ONE(X INTEGER , Y INTEGER )");
>             try (PreparedStatement prep = conn.prepareStatement("insert into 
> one values(?,?)")) {
>                 for (int row = 0; row < ROWS; row++) {
>                     prep.setInt(1, row / 100);
>                     prep.setInt(2, row);
>                     prep.execute();
>                 }
>             }
>             String s1 = "SELECT COUNT (*) from one where x in ( select y from 
> one where y < 100)";
>             try (ResultSet rs = stat.executeQuery(s1)) {
>                 rs.next();
>                 Assert.assertEquals(rs.getInt(1), 10000);
>             }
>         }
>     }
> }
>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
Index: h2/src/main/org/h2/command/dml/Query.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- h2/src/main/org/h2/command/dml/Query.java	(revision 8311:7310cac7dbeb4abef922b9b5c8f35a0b98f1f9ff)
+++ h2/src/main/org/h2/command/dml/Query.java	(revision 8311+:7310cac7dbeb+)
@@ -68,6 +68,8 @@
     private boolean cacheableChecked;
     private boolean neverLazy;
 
+    protected boolean lazy;
+
     Query(Session session) {
         super(session);
     }
@@ -348,7 +350,7 @@
         }
         fireBeforeSelectTriggers();
         if (noCache || !session.getDatabase().getOptimizeReuseResults() ||
-                session.isLazyQueryExecution()) {
+                lazy) {
             return queryWithoutCacheLazyCheck(limit, target);
         }
         Value[] params = getParameterValues();
Index: h2/src/main/org/h2/command/dml/Select.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- h2/src/main/org/h2/command/dml/Select.java	(revision 8311:7310cac7dbeb4abef922b9b5c8f35a0b98f1f9ff)
+++ h2/src/main/org/h2/command/dml/Select.java	(revision 8311+:7310cac7dbeb+)
@@ -569,7 +569,7 @@
                 limitRows = Math.min(l, limitRows);
             }
         }
-        boolean lazy = session.isLazyQueryExecution() &&
+        lazy = session.isLazyQueryExecution() &&
                 target == null && !isForUpdate && !isQuickAggregateQuery &&
                 limitRows != 0 && offsetExpr == null && isReadOnly();
         int columnCount = expressions.size();

Reply via email to