phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject phoenix git commit: PHOENIX-4212 Disallow DML operations on connections with CURRENT_SCN set - DerivedTableIT (Ethan Wang)
Date Fri, 15 Sep 2017 23:59:34 GMT
Repository: phoenix
Updated Branches:
  refs/heads/4.x-HBase-1.1 4348212a3 -> 2c53fa56f


PHOENIX-4212 Disallow DML operations on connections with CURRENT_SCN set - DerivedTableIT (Ethan Wang)


Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/2c53fa56
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/2c53fa56
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/2c53fa56

Branch: refs/heads/4.x-HBase-1.1
Commit: 2c53fa56fced927021f8ab04a703e0f0987627e0
Parents: 4348212
Author: James Taylor <jamestaylor@apache.org>
Authored: Fri Sep 15 16:30:03 2017 -0700
Committer: James Taylor <jamestaylor@apache.org>
Committed: Fri Sep 15 16:59:10 2017 -0700

----------------------------------------------------------------------
 .../apache/phoenix/end2end/DerivedTableIT.java  | 1166 +++++++++++++++---
 1 file changed, 975 insertions(+), 191 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/phoenix/blob/2c53fa56/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java
index 39fb747..265ad21 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java
@@ -18,7 +18,6 @@
 
 package org.apache.phoenix.end2end;
 
-import static org.apache.phoenix.util.TestUtil.ATABLE_NAME;
 import static org.apache.phoenix.util.TestUtil.A_VALUE;
 import static org.apache.phoenix.util.TestUtil.B_VALUE;
 import static org.apache.phoenix.util.TestUtil.C_VALUE;
@@ -42,100 +41,68 @@ import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
-import java.util.Collection;
-import java.util.List;
 import java.util.Properties;
 
-import org.apache.phoenix.util.PhoenixRuntime;
 import org.apache.phoenix.util.PropertiesUtil;
 import org.apache.phoenix.util.QueryUtil;
-import org.junit.Before;
 import org.junit.Test;
-import org.junit.runner.RunWith;
-import org.junit.runners.Parameterized;
-import org.junit.runners.Parameterized.Parameters;
-
-import com.google.common.collect.Lists;
-
-
-@RunWith(Parameterized.class)
-public class DerivedTableIT extends BaseClientManagedTimeIT {
-    private static final String tenantId = getOrganizationId();
-    
-    private long ts;
-    private String[] indexDDL;
-    private String[] plans;
-    
-    public DerivedTableIT(String[] indexDDL, String[] plans) {
-        this.indexDDL = indexDDL;
-        this.plans = plans;
-    }
-    
-    @Before
-    public void initTable() throws Exception {
-         ts = nextTimestamp();
-        initATableValues(ATABLE_NAME, tenantId, getDefaultSplits(tenantId), null, ts, getUrl(), null);
-        if (indexDDL != null && indexDDL.length > 0) {
-            Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-            props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
-            Connection conn = DriverManager.getConnection(getUrl(), props);
-            for (String ddl : indexDDL) {
-                conn.createStatement().execute(ddl);
-            }
-        }
-    }
-    
-    @Parameters(name="DerivedTableIT_{index}") // name is used by failsafe as file name in reports
-    public static Collection<Object> data() {
-        List<Object> testCases = Lists.newArrayList();
-        testCases.add(new String[][] {
-                { 
-                "CREATE INDEX ATABLE_DERIVED_IDX ON aTable (a_byte) INCLUDE (A_STRING, B_STRING)" 
-                }, {
-                "CLIENT PARALLEL 1-WAY FULL SCAN OVER ATABLE_DERIVED_IDX\n" +
+
+public class DerivedTableIT extends ParallelStatsDisabledIT {
+    private static String[] initTableWithIndex(String tableName) throws Exception {
+        String tenantId = getOrganizationId();
+        initATableValues(tableName, tenantId, getDefaultSplits(tenantId), null, null, getUrl(), null);
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        String ddl="CREATE INDEX "+tableName+"_DERIVED_IDX ON "+tableName+" (a_byte) INCLUDE (A_STRING, B_STRING)";
+        conn.createStatement().execute(ddl);
+        String[] plans= {"CLIENT PARALLEL 1-WAY FULL SCAN OVER "+tableName+"_DERIVED_IDX\n" +
                 "    SERVER AGGREGATE INTO DISTINCT ROWS BY [\"A_STRING\", \"B_STRING\"]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [\"B_STRING\"]\n" +
                 "CLIENT SORTED BY [A]\n" +
                 "CLIENT AGGREGATE INTO DISTINCT ROWS BY [A]\n" +
                 "CLIENT SORTED BY [A DESC]",
-                
-                "CLIENT PARALLEL 1-WAY FULL SCAN OVER ATABLE_DERIVED_IDX\n" +
-                "    SERVER AGGREGATE INTO DISTINCT ROWS BY [\"A_STRING\", \"B_STRING\"]\n" +
-                "CLIENT MERGE SORT\n" +
-                "CLIENT SORTED BY [A]\n" +
-                "CLIENT AGGREGATE INTO DISTINCT ROWS BY [A]\n" +
-                "CLIENT DISTINCT ON [COLLECTDISTINCT(B)]\n" + 
-                "CLIENT SORTED BY [A DESC]"}});
-        testCases.add(new String[][] {
-                {}, {
-                "CLIENT PARALLEL 4-WAY FULL SCAN OVER ATABLE\n" +
+
+                "CLIENT PARALLEL 1-WAY FULL SCAN OVER "+tableName+"_DERIVED_IDX\n" +
+                        "    SERVER AGGREGATE INTO DISTINCT ROWS BY [\"A_STRING\", \"B_STRING\"]\n" +
+                        "CLIENT MERGE SORT\n" +
+                        "CLIENT SORTED BY [A]\n" +
+                        "CLIENT AGGREGATE INTO DISTINCT ROWS BY [A]\n" +
+                        "CLIENT DISTINCT ON [COLLECTDISTINCT(B)]\n" +
+                        "CLIENT SORTED BY [A DESC]"};
+        return plans;
+    }
+
+    private static String[] initTableWithoutIndex(String tableName) throws Exception {
+        String tenantId = getOrganizationId();
+        initATableValues(tableName, tenantId, getDefaultSplits(tenantId), null, null, getUrl(), null);
+        String[] plans= {"CLIENT PARALLEL 4-WAY FULL SCAN OVER "+tableName+"\n" +
                 "    SERVER AGGREGATE INTO DISTINCT ROWS BY [A_STRING, B_STRING]\n" +
                 "CLIENT MERGE SORT\n" +
                 "CLIENT SORTED BY [B_STRING]\n" +
                 "CLIENT SORTED BY [A]\n" +
                 "CLIENT AGGREGATE INTO DISTINCT ROWS BY [A]\n" +
                 "CLIENT SORTED BY [A DESC]",
-                
-                "CLIENT PARALLEL 4-WAY FULL SCAN OVER ATABLE\n" +
-                "    SERVER AGGREGATE INTO DISTINCT ROWS BY [A_STRING, B_STRING]\n" +
-                "CLIENT MERGE SORT\n" +
-                "CLIENT SORTED BY [A]\n" +
-                "CLIENT AGGREGATE INTO DISTINCT ROWS BY [A]\n" +
-                "CLIENT DISTINCT ON [COLLECTDISTINCT(B)]\n" + 
-                "CLIENT SORTED BY [A DESC]"}});
-        return testCases;
+
+                "CLIENT PARALLEL 4-WAY FULL SCAN OVER "+tableName+"\n" +
+                        "    SERVER AGGREGATE INTO DISTINCT ROWS BY [A_STRING, B_STRING]\n" +
+                        "CLIENT MERGE SORT\n" +
+                        "CLIENT SORTED BY [A]\n" +
+                        "CLIENT AGGREGATE INTO DISTINCT ROWS BY [A]\n" +
+                        "CLIENT DISTINCT ON [COLLECTDISTINCT(B)]\n" +
+                        "CLIENT SORTED BY [A DESC]"};
+        return plans;
     }
 
     @Test
     public void testDerivedTableWithWhere() throws Exception {
-        long ts = nextTimestamp();
+        String tableName=generateUniqueName();
+        initTableWithIndex(tableName);
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
         Connection conn = DriverManager.getConnection(getUrl(), props);
         try {
             // (where)
-            String query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM aTable WHERE a_byte + 1 < 9) AS t";
+            String query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+" WHERE a_byte + 1 < 9) AS t";
             PreparedStatement statement = conn.prepareStatement(query);
             ResultSet rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -161,9 +128,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(17,rs.getInt(2));
 
             assertFalse(rs.next());
-            
+
             // () where
-            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM aTable) AS t WHERE t.b = '" + C_VALUE + "'";
+            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+") AS t WHERE t.b = '" + C_VALUE + "'";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -177,9 +144,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(18,rs.getInt(2));
 
             assertFalse(rs.next());
-            
+
             // (where) where
-            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM aTable WHERE a_byte + 1 < 9) AS t WHERE t.b = '" + C_VALUE + "'";
+            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+" WHERE a_byte + 1 < 9) AS t WHERE t.b = '" + C_VALUE + "'";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -192,7 +159,7 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertFalse(rs.next());
 
             // (groupby where) where
-            query = "SELECT t.a, t.c, t.m FROM (SELECT a_string a, count(*) c, max(a_byte) m FROM aTable WHERE a_byte != 8 GROUP BY a_string) AS t WHERE t.c > 1";
+            query = "SELECT t.a, t.c, t.m FROM (SELECT a_string a, count(*) c, max(a_byte) m FROM "+tableName+" WHERE a_byte != 8 GROUP BY a_string) AS t WHERE t.c > 1";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -205,9 +172,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(7,rs.getInt(3));
 
             assertFalse(rs.next());
-            
+
             // (groupby having where) where
-            query = "SELECT t.a, t.c, t.m FROM (SELECT a_string a, count(*) c, max(a_byte) m FROM aTable WHERE a_byte != 8 GROUP BY a_string HAVING count(*) >= 2) AS t WHERE t.a != '" + A_VALUE + "'";
+            query = "SELECT t.a, t.c, t.m FROM (SELECT a_string a, count(*) c, max(a_byte) m FROM "+tableName+" WHERE a_byte != 8 GROUP BY a_string HAVING count(*) >= 2) AS t WHERE t.a != '" + A_VALUE + "'";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -216,18 +183,18 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(7,rs.getInt(3));
 
             assertFalse(rs.next());
-            
+
             // (limit) where
-            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM aTable LIMIT 2) AS t WHERE t.b = '" + C_VALUE + "'";
+            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+" LIMIT 2) AS t WHERE t.b = '" + C_VALUE + "'";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
             assertEquals(ROW2,rs.getString(1));
 
             assertFalse(rs.next());
-            
+
             // ((where limit) where limit) limit
-            query = "SELECT u.eid FROM (SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM aTable WHERE a_string = '" + B_VALUE + "' LIMIT 5) AS t WHERE t.b = '" + C_VALUE + "' LIMIT 4) AS u WHERE u.eid >= '" + ROW1 + "' LIMIT 3";
+            query = "SELECT u.eid FROM (SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+" WHERE a_string = '" + B_VALUE + "' LIMIT 5) AS t WHERE t.b = '" + C_VALUE + "' LIMIT 4) AS u WHERE u.eid >= '" + ROW1 + "' LIMIT 3";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -238,7 +205,7 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertFalse(rs.next());
 
             // (count) where
-            query = "SELECT t.c FROM (SELECT count(*) c FROM aTable) AS t WHERE t.c > 0";
+            query = "SELECT t.c FROM (SELECT count(*) c FROM "+tableName+") AS t WHERE t.c > 0";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -247,14 +214,14 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertFalse(rs.next());
 
             // Inner limit < outer query offset
-            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM aTable LIMIT 1 OFFSET 1 ) AS t WHERE t.b = '"
+            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+" LIMIT 1 OFFSET 1 ) AS t WHERE t.b = '"
                     + C_VALUE + "' OFFSET 2";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertFalse(rs.next());
 
             // (where) offset
-            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM aTable WHERE a_byte + 1 < 9 ) AS t OFFSET 2";
+            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+" WHERE a_byte + 1 < 9 ) AS t OFFSET 2";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue(rs.next());
@@ -274,7 +241,7 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(17, rs.getInt(2));
 
             // (offset) where
-            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM aTable OFFSET 4) AS t WHERE t.b = '"
+            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+" OFFSET 4) AS t WHERE t.b = '"
                     + C_VALUE + "'";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
@@ -289,16 +256,16 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             conn.close();
         }
     }
-    
+
     @Test
     public void testDerivedTableWithGroupBy() throws Exception {
-        long ts = nextTimestamp();
+        String tableName=generateUniqueName();
+        String[] plans=initTableWithIndex(tableName);
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
         Connection conn = DriverManager.getConnection(getUrl(), props);
         try {
             // () groupby having
-            String query = "SELECT t.a, count(*), max(t.s) FROM (SELECT a_string a, a_byte s FROM aTable WHERE a_byte != 8) AS t GROUP BY t.a HAVING count(*) > 1";
+            String query = "SELECT t.a, count(*), max(t.s) FROM (SELECT a_string a, a_byte s FROM "+tableName+" WHERE a_byte != 8) AS t GROUP BY t.a HAVING count(*) > 1";
             PreparedStatement statement = conn.prepareStatement(query);
             ResultSet rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -311,9 +278,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(7,rs.getInt(3));
 
             assertFalse(rs.next());
-            
+
             // (groupby) groupby
-            query = "SELECT t.c, count(*) FROM (SELECT count(*) c FROM aTable GROUP BY a_string) AS t GROUP BY t.c";
+            query = "SELECT t.c, count(*) FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t GROUP BY t.c";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -324,9 +291,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(2,rs.getInt(2));
 
             assertFalse(rs.next());
-            
+
             // (groupby) groupby orderby
-            query = "SELECT t.c, count(*) FROM (SELECT count(*) c FROM aTable GROUP BY a_string) AS t GROUP BY t.c ORDER BY count(*) DESC";
+            query = "SELECT t.c, count(*) FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t GROUP BY t.c ORDER BY count(*) DESC";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -337,9 +304,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(1,rs.getInt(2));
 
             assertFalse(rs.next());
-            
+
             // (groupby a, b orderby b) groupby a orderby a
-            query = "SELECT t.a, COLLECTDISTINCT(t.b) FROM (SELECT b_string b, a_string a FROM aTable GROUP BY a_string, b_string ORDER BY b_string) AS t GROUP BY t.a ORDER BY t.a DESC";
+            query = "SELECT t.a, COLLECTDISTINCT(t.b) FROM (SELECT b_string b, a_string a FROM "+tableName+" GROUP BY a_string, b_string ORDER BY b_string) AS t GROUP BY t.a ORDER BY t.a DESC";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -361,12 +328,12 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(array,rs.getArray(2));
 
             assertFalse(rs.next());
-            
+
             rs = conn.createStatement().executeQuery("EXPLAIN " + query);
             assertEquals(plans[0], QueryUtil.getExplainPlan(rs));
-            
+
             // distinct b (groupby a, b) groupby a orderby a
-            query = "SELECT DISTINCT COLLECTDISTINCT(t.b) FROM (SELECT b_string b, a_string a FROM aTable GROUP BY a_string, b_string) AS t GROUP BY t.a ORDER BY t.a DESC";
+            query = "SELECT DISTINCT COLLECTDISTINCT(t.b) FROM (SELECT b_string b, a_string a FROM "+tableName+" GROUP BY a_string, b_string) AS t GROUP BY t.a ORDER BY t.a DESC";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -383,12 +350,12 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(array,rs.getArray(1));
 
             assertFalse(rs.next());
-            
+
             rs = conn.createStatement().executeQuery("EXPLAIN " + query);
             assertEquals(plans[1], QueryUtil.getExplainPlan(rs));
-            
+
             // (orderby) groupby
-            query = "SELECT t.a_string, count(*) FROM (SELECT * FROM aTable order by a_integer) AS t where a_byte != 8 group by t.a_string";
+            query = "SELECT t.a_string, count(*) FROM (SELECT * FROM "+tableName+" order by a_integer) AS t where a_byte != 8 group by t.a_string";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -404,7 +371,7 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertFalse(rs.next());
 
             // (groupby) groupby orderby offset
-            query = "SELECT t.c, count(*) FROM (SELECT count(*) c FROM aTable GROUP BY a_string) AS t GROUP BY t.c ORDER BY count(*) DESC OFFSET 1";
+            query = "SELECT t.c, count(*) FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t GROUP BY t.c ORDER BY count(*) DESC OFFSET 1";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue(rs.next());
@@ -417,16 +384,16 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             conn.close();
         }
     }
-    
+
     @Test
     public void testDerivedTableWithOrderBy() throws Exception {
-        long ts = nextTimestamp();
+        String tableName=generateUniqueName();
+        initTableWithIndex(tableName);
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
         Connection conn = DriverManager.getConnection(getUrl(), props);
         try {
             // (orderby)
-            String query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM aTable ORDER BY b, eid) AS t";
+            String query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+" ORDER BY b, eid) AS t";
             PreparedStatement statement = conn.prepareStatement(query);
             ResultSet rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -449,9 +416,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(ROW9,rs.getString(1));
 
             assertFalse(rs.next());
-            
+
             // () orderby
-            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM aTable) AS t ORDER BY t.b, t.eid";
+            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+") AS t ORDER BY t.b, t.eid";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -474,9 +441,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(ROW9,rs.getString(1));
 
             assertFalse(rs.next());
-            
+
             // (orderby) orderby
-            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM aTable ORDER BY b, eid) AS t ORDER BY t.b DESC, t.eid DESC";
+            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+" ORDER BY b, eid) AS t ORDER BY t.b DESC, t.eid DESC";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -499,9 +466,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(ROW1,rs.getString(1));
 
             assertFalse(rs.next());
-            
+
             // (limit) orderby
-            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM aTable LIMIT 2) AS t ORDER BY t.b DESC, t.eid";
+            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+" LIMIT 2) AS t ORDER BY t.b DESC, t.eid";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -514,16 +481,16 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             conn.close();
         }
     }
-    
+
     @Test
     public void testDerivedTableWithLimit() throws Exception {
-        long ts = nextTimestamp();
+        String tableName=generateUniqueName();
+        initTableWithIndex(tableName);
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
         Connection conn = DriverManager.getConnection(getUrl(), props);
         try {
             // (limit)
-            String query = "SELECT t.eid FROM (SELECT entity_id eid FROM aTable LIMIT 2) AS t";
+            String query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 2) AS t";
             PreparedStatement statement = conn.prepareStatement(query);
             ResultSet rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -532,9 +499,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(ROW2,rs.getString(1));
 
             assertFalse(rs.next());
-            
+
             // () limit
-            query = "SELECT t.eid FROM (SELECT entity_id eid FROM aTable) AS t LIMIT 2";
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+") AS t LIMIT 2";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -543,9 +510,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(ROW2,rs.getString(1));
 
             assertFalse(rs.next());
-            
+
             // (limit 2) limit 4
-            query = "SELECT t.eid FROM (SELECT entity_id eid FROM aTable LIMIT 2) AS t LIMIT 4";
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 2) AS t LIMIT 4";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -554,9 +521,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(ROW2,rs.getString(1));
 
             assertFalse(rs.next());
-            
+
             // (limit 4) limit 2
-            query = "SELECT t.eid FROM (SELECT entity_id eid FROM aTable LIMIT 4) AS t LIMIT 2";
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 4) AS t LIMIT 2";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -564,10 +531,10 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertTrue (rs.next());
             assertEquals(ROW2,rs.getString(1));
 
-            assertFalse(rs.next());                        
-            
-            // limit ? limit ?            
-            query = "SELECT t.eid FROM (SELECT entity_id eid FROM aTable LIMIT ?) AS t LIMIT ?";
+            assertFalse(rs.next());
+
+            // limit ? limit ?
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT ?) AS t LIMIT ?";
             statement = conn.prepareStatement(query);
             statement.setInt(1, 4);
             statement.setInt(2, 2);
@@ -578,9 +545,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(ROW2,rs.getString(1));
 
             assertFalse(rs.next());
-            
+
             // (groupby orderby) limit
-            query = "SELECT a, s FROM (SELECT a_string a, sum(a_byte) s FROM aTable GROUP BY a_string ORDER BY sum(a_byte)) LIMIT 2";
+            query = "SELECT a, s FROM (SELECT a_string a, sum(a_byte) s FROM "+tableName+" GROUP BY a_string ORDER BY sum(a_byte)) LIMIT 2";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -591,9 +558,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(10,rs.getInt(2));
 
             assertFalse(rs.next());
-            
+
             // (union) groupby limit
-            query = "SELECT a_string, count(*) FROM (SELECT a_string FROM aTable where a_byte < 4 union all SELECT a_string FROM aTable where a_byte > 8) group by a_string limit 2";
+            query = "SELECT a_string, count(*) FROM (SELECT a_string FROM "+tableName+" where a_byte < 4 union all SELECT a_string FROM "+tableName+" where a_byte > 8) group by a_string limit 2";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -603,7 +570,7 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(C_VALUE,rs.getString(1));
             assertEquals(1,rs.getInt(2));
 
-            assertFalse(rs.next());            
+            assertFalse(rs.next());
         } finally {
             conn.close();
         }
@@ -611,13 +578,13 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
 
     @Test
     public void testDerivedTableWithOffset() throws Exception {
-        long ts = nextTimestamp();
+        String tableName=generateUniqueName();
+        initTableWithIndex(tableName);
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
         Connection conn = DriverManager.getConnection(getUrl(), props);
         try {
             // (LIMIT OFFSET )
-            String query = "SELECT t.eid FROM (SELECT entity_id eid FROM aTable LIMIT 2 OFFSET 1) AS t";
+            String query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 2 OFFSET 1) AS t";
             PreparedStatement statement = conn.prepareStatement(query);
             ResultSet rs = statement.executeQuery();
             assertTrue(rs.next());
@@ -628,7 +595,7 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertFalse(rs.next());
 
             // (OFFSET) limit
-            query = "SELECT t.eid FROM (SELECT entity_id eid FROM aTable OFFSET 1) AS t LIMIT 2";
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" OFFSET 1) AS t LIMIT 2";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue(rs.next());
@@ -639,7 +606,7 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertFalse(rs.next());
 
             // (limit OFFSET) limit OFFSET
-            query = "SELECT t.eid FROM (SELECT entity_id eid FROM aTable LIMIT 2 OFFSET 1) AS t LIMIT 4 OFFSET 1";
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 2 OFFSET 1) AS t LIMIT 4 OFFSET 1";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue(rs.next());
@@ -647,7 +614,7 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertFalse(rs.next());
 
             // (limit OFFSET) limit 2
-            query = "SELECT t.eid FROM (SELECT entity_id eid FROM aTable LIMIT 4 OFFSET 1) AS t LIMIT 2";
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 4 OFFSET 1) AS t LIMIT 2";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue(rs.next());
@@ -658,7 +625,7 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertFalse(rs.next());
 
             // (limit ? OFFSET ?) limit ? OFFSET ?
-            query = "SELECT t.eid FROM (SELECT entity_id eid FROM aTable LIMIT ? OFFSET ?) AS t LIMIT ? OFFSET ?";
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT ? OFFSET ?) AS t LIMIT ? OFFSET ?";
             statement = conn.prepareStatement(query);
             statement.setInt(1, 4);
             statement.setInt(2, 2);
@@ -672,7 +639,7 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertFalse(rs.next());
 
             // (groupby orderby OFFSET)
-            query = "SELECT a, s FROM (SELECT a_string a, sum(a_byte) s FROM aTable GROUP BY a_string ORDER BY sum(a_byte) OFFSET 1)";
+            query = "SELECT a, s FROM (SELECT a_string a, sum(a_byte) s FROM "+tableName+" GROUP BY a_string ORDER BY sum(a_byte) OFFSET 1)";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue(rs.next());
@@ -684,8 +651,8 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
 
             assertFalse(rs.next());
 
-            // (union OFFSET) groupby 
-            query = "SELECT a_string, count(*) FROM (SELECT a_string FROM aTable where a_byte < 4 union all SELECT a_string FROM aTable where a_byte > 8 OFFSET 1) group by a_string";
+            // (union OFFSET) groupby
+            query = "SELECT a_string, count(*) FROM (SELECT a_string FROM "+tableName+" where a_byte < 4 union all SELECT a_string FROM "+tableName+" where a_byte > 8 OFFSET 1) group by a_string";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -702,13 +669,13 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
 
     @Test
     public void testDerivedTableWithDistinct() throws Exception {
-        long ts = nextTimestamp();
+        String tableName=generateUniqueName();
+        initTableWithIndex(tableName);
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
         Connection conn = DriverManager.getConnection(getUrl(), props);
         try {
             // (distinct)
-            String query = "SELECT * FROM (SELECT DISTINCT a_string, b_string FROM aTable) AS t WHERE t.b_string != '" + C_VALUE + "' ORDER BY t.b_string, t.a_string";
+            String query = "SELECT * FROM (SELECT DISTINCT a_string, b_string FROM "+tableName+") AS t WHERE t.b_string != '" + C_VALUE + "' ORDER BY t.b_string, t.a_string";
             PreparedStatement statement = conn.prepareStatement(query);
             ResultSet rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -728,9 +695,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(E_VALUE,rs.getString(2));
 
             assertFalse(rs.next());
-            
+
             // distinct ()
-            query = "SELECT DISTINCT t.a, t.b FROM (SELECT a_string a, b_string b FROM aTable) AS t WHERE t.b != '" + C_VALUE + "' ORDER BY t.b, t.a";
+            query = "SELECT DISTINCT t.a, t.b FROM (SELECT a_string a, b_string b FROM "+tableName+") AS t WHERE t.b != '" + C_VALUE + "' ORDER BY t.b, t.a";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -750,9 +717,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(E_VALUE,rs.getString(2));
 
             assertFalse(rs.next());
-            
+
             // distinct (distinct)
-            query = "SELECT DISTINCT t.a FROM (SELECT DISTINCT a_string a, b_string b FROM aTable) AS t";
+            query = "SELECT DISTINCT t.a FROM (SELECT DISTINCT a_string a, b_string b FROM "+tableName+") AS t";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -763,9 +730,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(C_VALUE,rs.getString(1));
 
             assertFalse(rs.next());
-            
+
             // distinct (groupby)
-            query = "SELECT distinct t.c FROM (SELECT count(*) c FROM aTable GROUP BY a_string) AS t";
+            query = "SELECT distinct t.c FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -774,9 +741,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(4,rs.getInt(1));
 
             assertFalse(rs.next());
-            
+
             // distinct (groupby) orderby
-            query = "SELECT distinct t.c FROM (SELECT count(*) c FROM aTable GROUP BY a_string) AS t ORDER BY t.c DESC";
+            query = "SELECT distinct t.c FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t ORDER BY t.c DESC";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -785,9 +752,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(1,rs.getInt(1));
 
             assertFalse(rs.next());
-            
+
             // distinct (limit)
-            query = "SELECT DISTINCT t.a, t.b FROM (SELECT a_string a, b_string b FROM aTable LIMIT 2) AS t";
+            query = "SELECT DISTINCT t.a, t.b FROM (SELECT a_string a, b_string b FROM "+tableName+" LIMIT 2) AS t";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -802,70 +769,70 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             conn.close();
         }
     }
-    
+
     @Test
     public void testDerivedTableWithAggregate() throws Exception {
-        long ts = nextTimestamp();
+        String tableName=generateUniqueName();
+        initTableWithIndex(tableName);
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
         Connection conn = DriverManager.getConnection(getUrl(), props);
         try {
             // (count)
-            String query = "SELECT * FROM (SELECT count(*) FROM aTable WHERE a_byte != 8) AS t";
+            String query = "SELECT * FROM (SELECT count(*) FROM "+tableName+" WHERE a_byte != 8) AS t";
             PreparedStatement statement = conn.prepareStatement(query);
             ResultSet rs = statement.executeQuery();
             assertTrue (rs.next());
             assertEquals(8,rs.getInt(1));
 
             assertFalse(rs.next());
-            
+
             // count ()
-            query = "SELECT count(*) FROM (SELECT a_byte FROM aTable) AS t WHERE t.a_byte != 8";
+            query = "SELECT count(*) FROM (SELECT a_byte FROM "+tableName+") AS t WHERE t.a_byte != 8";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
             assertEquals(8,rs.getInt(1));
 
             assertFalse(rs.next());
-            
+
             // count (distinct)
-            query = "SELECT count(*) FROM (SELECT DISTINCT a_string FROM aTable) AS t";
+            query = "SELECT count(*) FROM (SELECT DISTINCT a_string FROM "+tableName+") AS t";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
             assertEquals(3,rs.getInt(1));
 
             assertFalse(rs.next());
-            
+
             // count (groupby)
-            query = "SELECT count(*) FROM (SELECT count(*) c FROM aTable GROUP BY a_string) AS t";
+            query = "SELECT count(*) FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
             assertEquals(3,rs.getInt(1));
 
             assertFalse(rs.next());
-            
+
             // count (limit)
-            query = "SELECT count(*) FROM (SELECT entity_id FROM aTable LIMIT 2) AS t";
+            query = "SELECT count(*) FROM (SELECT entity_id FROM "+tableName+" LIMIT 2) AS t";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
             assertEquals(2,rs.getInt(1));
 
             assertFalse(rs.next());
-            
+
             // count (subquery)
-            query = "SELECT count(*) FROM (SELECT * FROM aTable WHERE (organization_id, entity_id) in (SELECT organization_id, entity_id FROM aTable WHERE a_byte != 8)) AS t";
+            query = "SELECT count(*) FROM (SELECT * FROM "+tableName+" WHERE (organization_id, entity_id) in (SELECT organization_id, entity_id FROM "+tableName+" WHERE a_byte != 8)) AS t";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
             assertEquals(8,rs.getInt(1));
 
             assertFalse(rs.next());
-            
+
             // count (orderby)
-            query = "SELECT count(a_byte) FROM (SELECT * FROM aTable order by a_integer) AS t where a_byte != 8";
+            query = "SELECT count(a_byte) FROM (SELECT * FROM "+tableName+" order by a_integer) AS t where a_byte != 8";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -876,18 +843,18 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             conn.close();
         }
     }
-    
+
     @Test
     public void testDerivedTableWithJoin() throws Exception {
-        long ts = nextTimestamp();
+        String tableName=generateUniqueName();
+        initTableWithIndex(tableName);
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
         Connection conn = DriverManager.getConnection(getUrl(), props);
         try {
             // groupby (join)
-            String query = "SELECT q.id1, count(q.id2) FROM (SELECT t1.entity_id id1, t2.entity_id id2, t2.a_byte b2" 
-                        + " FROM aTable t1 JOIN aTable t2 ON t1.a_string = t2.b_string" 
-                        + " WHERE t1.a_byte >= 8) AS q WHERE q.b2 != 5 GROUP BY q.id1";
+            String query = "SELECT q.id1, count(q.id2) FROM (SELECT t1.entity_id id1, t2.entity_id id2, t2.a_byte b2"
+                    + " FROM "+tableName+" t1 JOIN "+tableName+" t2 ON t1.a_string = t2.b_string"
+                    + " WHERE t1.a_byte >= 8) AS q WHERE q.b2 != 5 GROUP BY q.id1";
             PreparedStatement statement = conn.prepareStatement(query);
             ResultSet rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -898,11 +865,11 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(2,rs.getInt(2));
 
             assertFalse(rs.next());
-            
+
             // distinct (join)
-            query = "SELECT DISTINCT q.id1 FROM (SELECT t1.entity_id id1, t2.a_byte b2" 
-                        + " FROM aTable t1 JOIN aTable t2 ON t1.a_string = t2.b_string" 
-                        + " WHERE t1.a_byte >= 8) AS q WHERE q.b2 != 5";
+            query = "SELECT DISTINCT q.id1 FROM (SELECT t1.entity_id id1, t2.a_byte b2"
+                    + " FROM "+tableName+" t1 JOIN "+tableName+" t2 ON t1.a_string = t2.b_string"
+                    + " WHERE t1.a_byte >= 8) AS q WHERE q.b2 != 5";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -913,9 +880,9 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertFalse(rs.next());
 
             // count (join)
-            query = "SELECT COUNT(*) FROM (SELECT t2.a_byte b2" 
-                        + " FROM aTable t1 JOIN aTable t2 ON t1.a_string = t2.b_string" 
-                        + " WHERE t1.a_byte >= 8) AS q WHERE q.b2 != 5";
+            query = "SELECT COUNT(*) FROM (SELECT t2.a_byte b2"
+                    + " FROM "+tableName+" t1 JOIN "+tableName+" t2 ON t1.a_string = t2.b_string"
+                    + " WHERE t1.a_byte >= 8) AS q WHERE q.b2 != 5";
             statement = conn.prepareStatement(query);
             rs = statement.executeQuery();
             assertTrue (rs.next());
@@ -926,16 +893,16 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             conn.close();
         }
     }
-    
+
     @Test
     public void testNestedDerivedTable() throws Exception {
-        long ts = nextTimestamp();
+        String tableName=generateUniqueName();
+        initTableWithIndex(tableName);
         Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
-        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
         Connection conn = DriverManager.getConnection(getUrl(), props);
         try {
             // select(select(select))
-            String query = "SELECT q.id, q.x10 * 10 FROM (SELECT t.eid id, t.x + 9 x10, t.astr a, t.bstr b FROM (SELECT entity_id eid, a_string astr, b_string bstr, a_byte + 1 x FROM aTable WHERE a_byte + 1 < ?) AS t ORDER BY b, id) AS q WHERE q.a = ? OR q.b = ? OR q.b = ?";
+            String query = "SELECT q.id, q.x10 * 10 FROM (SELECT t.eid id, t.x + 9 x10, t.astr a, t.bstr b FROM (SELECT entity_id eid, a_string astr, b_string bstr, a_byte + 1 x FROM "+tableName+" WHERE a_byte + 1 < ?) AS t ORDER BY b, id) AS q WHERE q.a = ? OR q.b = ? OR q.b = ?";
             PreparedStatement statement = conn.prepareStatement(query);
             statement.setInt(1, 9);
             statement.setString(2, A_VALUE);
@@ -962,11 +929,828 @@ public class DerivedTableIT extends BaseClientManagedTimeIT {
             assertEquals(160,rs.getInt(2));
 
             assertFalse(rs.next());
-            
+
+            // select(select(select) join (select(select)))
+            query = "SELECT q1.id, q2.id FROM (SELECT t.eid id, t.astr a, t.bstr b FROM (SELECT entity_id eid, a_string astr, b_string bstr, a_byte abyte FROM "+tableName+") AS t WHERE t.abyte >= ?) AS q1"
+                    + " JOIN (SELECT t.eid id, t.astr a, t.bstr b, t.abyte x FROM (SELECT entity_id eid, a_string astr, b_string bstr, a_byte abyte FROM "+tableName+") AS t) AS q2 ON q1.a = q2.b"
+                    + " WHERE q2.x != ? ORDER BY q1.id, q2.id DESC";
+            statement = conn.prepareStatement(query);
+            statement.setInt(1, 8);
+            statement.setInt(2, 5);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW8,rs.getString(1));
+            assertEquals(ROW7,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(ROW8,rs.getString(1));
+            assertEquals(ROW4,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(ROW8,rs.getString(1));
+            assertEquals(ROW1,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(ROW9,rs.getString(1));
+            assertEquals(ROW8,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(ROW9,rs.getString(1));
+            assertEquals(ROW2,rs.getString(2));
+
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testDerivedTableWithWhereWithoutIndex() throws Exception {
+        String tableName=generateUniqueName();
+        initTableWithoutIndex(tableName);
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            // (where)
+            String query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+" WHERE a_byte + 1 < 9) AS t";
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+            assertEquals(11,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+            assertEquals(12,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW3,rs.getString(1));
+            assertEquals(13,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW4,rs.getString(1));
+            assertEquals(14,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW5,rs.getString(1));
+            assertEquals(15,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW6,rs.getString(1));
+            assertEquals(16,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW7,rs.getString(1));
+            assertEquals(17,rs.getInt(2));
+
+            assertFalse(rs.next());
+
+            // () where
+            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+") AS t WHERE t.b = '" + C_VALUE + "'";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+            assertEquals(12,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW5,rs.getString(1));
+            assertEquals(15,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW8,rs.getString(1));
+            assertEquals(18,rs.getInt(2));
+
+            assertFalse(rs.next());
+
+            // (where) where
+            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+" WHERE a_byte + 1 < 9) AS t WHERE t.b = '" + C_VALUE + "'";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+            assertEquals(12,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW5,rs.getString(1));
+            assertEquals(15,rs.getInt(2));
+
+            assertFalse(rs.next());
+
+            // (groupby where) where
+            query = "SELECT t.a, t.c, t.m FROM (SELECT a_string a, count(*) c, max(a_byte) m FROM "+tableName+" WHERE a_byte != 8 GROUP BY a_string) AS t WHERE t.c > 1";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(4,rs.getInt(2));
+            assertEquals(4,rs.getInt(3));
+            assertTrue (rs.next());
+            assertEquals(B_VALUE,rs.getString(1));
+            assertEquals(3,rs.getInt(2));
+            assertEquals(7,rs.getInt(3));
+
+            assertFalse(rs.next());
+
+            // (groupby having where) where
+            query = "SELECT t.a, t.c, t.m FROM (SELECT a_string a, count(*) c, max(a_byte) m FROM "+tableName+" WHERE a_byte != 8 GROUP BY a_string HAVING count(*) >= 2) AS t WHERE t.a != '" + A_VALUE + "'";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(B_VALUE,rs.getString(1));
+            assertEquals(3,rs.getInt(2));
+            assertEquals(7,rs.getInt(3));
+
+            assertFalse(rs.next());
+
+            // (limit) where
+            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+" LIMIT 2) AS t WHERE t.b = '" + C_VALUE + "'";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // ((where limit) where limit) limit
+            query = "SELECT u.eid FROM (SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+" WHERE a_string = '" + B_VALUE + "' LIMIT 5) AS t WHERE t.b = '" + C_VALUE + "' LIMIT 4) AS u WHERE u.eid >= '" + ROW1 + "' LIMIT 3";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW5,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW8,rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // (count) where
+            query = "SELECT t.c FROM (SELECT count(*) c FROM "+tableName+") AS t WHERE t.c > 0";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(9,rs.getInt(1));
+
+            assertFalse(rs.next());
+
+            // Inner limit < outer query offset
+            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+" LIMIT 1 OFFSET 1 ) AS t WHERE t.b = '"
+                    + C_VALUE + "' OFFSET 2";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertFalse(rs.next());
+
+            // (where) offset
+            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+" WHERE a_byte + 1 < 9 ) AS t OFFSET 2";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue(rs.next());
+            assertEquals(ROW3, rs.getString(1));
+            assertEquals(13, rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals(ROW4, rs.getString(1));
+            assertEquals(14, rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals(ROW5, rs.getString(1));
+            assertEquals(15, rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals(ROW6, rs.getString(1));
+            assertEquals(16, rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals(ROW7, rs.getString(1));
+            assertEquals(17, rs.getInt(2));
+
+            // (offset) where
+            query = "SELECT t.eid, t.x + 9 FROM (SELECT entity_id eid, b_string b, a_byte + 1 x FROM "+tableName+" OFFSET 4) AS t WHERE t.b = '"
+                    + C_VALUE + "'";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue(rs.next());
+            assertEquals(ROW5, rs.getString(1));
+            assertEquals(15, rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals(ROW8, rs.getString(1));
+            assertEquals(18, rs.getInt(2));
+
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testDerivedTableWithGroupByWithoutIndex() throws Exception {
+        String tableName=generateUniqueName();
+        String[] plans=initTableWithoutIndex(tableName);
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            // () groupby having
+            String query = "SELECT t.a, count(*), max(t.s) FROM (SELECT a_string a, a_byte s FROM "+tableName+" WHERE a_byte != 8) AS t GROUP BY t.a HAVING count(*) > 1";
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(4,rs.getInt(2));
+            assertEquals(4,rs.getInt(3));
+            assertTrue (rs.next());
+            assertEquals(B_VALUE,rs.getString(1));
+            assertEquals(3,rs.getInt(2));
+            assertEquals(7,rs.getInt(3));
+
+            assertFalse(rs.next());
+
+            // (groupby) groupby
+            query = "SELECT t.c, count(*) FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t GROUP BY t.c";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(1,rs.getInt(1));
+            assertEquals(1,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(4,rs.getInt(1));
+            assertEquals(2,rs.getInt(2));
+
+            assertFalse(rs.next());
+
+            // (groupby) groupby orderby
+            query = "SELECT t.c, count(*) FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t GROUP BY t.c ORDER BY count(*) DESC";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(4,rs.getInt(1));
+            assertEquals(2,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(1,rs.getInt(1));
+            assertEquals(1,rs.getInt(2));
+
+            assertFalse(rs.next());
+
+            // (groupby a, b orderby b) groupby a orderby a
+            query = "SELECT t.a, COLLECTDISTINCT(t.b) FROM (SELECT b_string b, a_string a FROM "+tableName+" GROUP BY a_string, b_string ORDER BY b_string) AS t GROUP BY t.a ORDER BY t.a DESC";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(C_VALUE,rs.getString(1));
+            String[] b = new String[1];
+            b[0] = E_VALUE;
+            Array array = conn.createArrayOf("VARCHAR", b);
+            assertEquals(array,rs.getArray(2));
+            assertTrue (rs.next());
+            assertEquals(B_VALUE,rs.getString(1));
+            b = new String[3];
+            b[0] = B_VALUE;
+            b[1] = C_VALUE;
+            b[2] = E_VALUE;
+            array = conn.createArrayOf("VARCHAR", b);
+            assertEquals(array,rs.getArray(2));
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(array,rs.getArray(2));
+
+            assertFalse(rs.next());
+
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            assertEquals(plans[0], QueryUtil.getExplainPlan(rs));
+
+            // distinct b (groupby a, b) groupby a orderby a
+            query = "SELECT DISTINCT COLLECTDISTINCT(t.b) FROM (SELECT b_string b, a_string a FROM "+tableName+" GROUP BY a_string, b_string) AS t GROUP BY t.a ORDER BY t.a DESC";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            b = new String[1];
+            b[0] = E_VALUE;
+            array = conn.createArrayOf("VARCHAR", b);
+            assertEquals(array,rs.getArray(1));
+            assertTrue (rs.next());
+            b = new String[3];
+            b[0] = B_VALUE;
+            b[1] = C_VALUE;
+            b[2] = E_VALUE;
+            array = conn.createArrayOf("VARCHAR", b);
+            assertEquals(array,rs.getArray(1));
+
+            assertFalse(rs.next());
+
+            rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+            assertEquals(plans[1], QueryUtil.getExplainPlan(rs));
+
+            // (orderby) groupby
+            query = "SELECT t.a_string, count(*) FROM (SELECT * FROM "+tableName+" order by a_integer) AS t where a_byte != 8 group by t.a_string";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(4,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(B_VALUE,rs.getString(1));
+            assertEquals(3,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(C_VALUE,rs.getString(1));
+            assertEquals(1,rs.getInt(2));
+
+            assertFalse(rs.next());
+
+            // (groupby) groupby orderby offset
+            query = "SELECT t.c, count(*) FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t GROUP BY t.c ORDER BY count(*) DESC OFFSET 1";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue(rs.next());
+            assertEquals(1, rs.getInt(1));
+            assertEquals(1, rs.getInt(2));
+
+            assertFalse(rs.next());
+
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testDerivedTableWithOrderByWithoutIndex() throws Exception {
+        String tableName=generateUniqueName();
+        initTableWithoutIndex(tableName);
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            // (orderby)
+            String query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+" ORDER BY b, eid) AS t";
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW4,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW7,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW5,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW8,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW3,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW6,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW9,rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // () orderby
+            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+") AS t ORDER BY t.b, t.eid";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW4,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW7,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW5,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW8,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW3,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW6,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW9,rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // (orderby) orderby
+            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+" ORDER BY b, eid) AS t ORDER BY t.b DESC, t.eid DESC";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW9,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW6,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW3,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW8,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW5,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW7,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW4,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // (limit) orderby
+            query = "SELECT t.eid FROM (SELECT entity_id eid, b_string b FROM "+tableName+" LIMIT 2) AS t ORDER BY t.b DESC, t.eid";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testDerivedTableWithLimitWithoutIndex() throws Exception {
+        String tableName=generateUniqueName();
+        initTableWithoutIndex(tableName);
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            // (limit)
+            String query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 2) AS t";
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // () limit
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+") AS t LIMIT 2";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // (limit 2) limit 4
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 2) AS t LIMIT 4";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // (limit 4) limit 2
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 4) AS t LIMIT 2";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // limit ? limit ?
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT ?) AS t LIMIT ?";
+            statement = conn.prepareStatement(query);
+            statement.setInt(1, 4);
+            statement.setInt(2, 2);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // (groupby orderby) limit
+            query = "SELECT a, s FROM (SELECT a_string a, sum(a_byte) s FROM "+tableName+" GROUP BY a_string ORDER BY sum(a_byte)) LIMIT 2";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(C_VALUE,rs.getString(1));
+            assertEquals(9,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(10,rs.getInt(2));
+
+            assertFalse(rs.next());
+
+            // (union) groupby limit
+            query = "SELECT a_string, count(*) FROM (SELECT a_string FROM "+tableName+" where a_byte < 4 union all SELECT a_string FROM "+tableName+" where a_byte > 8) group by a_string limit 2";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(3,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(C_VALUE,rs.getString(1));
+            assertEquals(1,rs.getInt(2));
+
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testDerivedTableWithOffsetWithoutIndex() throws Exception {
+        String tableName=generateUniqueName();
+        initTableWithoutIndex(tableName);
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            // (LIMIT OFFSET )
+            String query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 2 OFFSET 1) AS t";
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue(rs.next());
+            assertEquals(ROW2, rs.getString(1));
+            assertTrue(rs.next());
+            assertEquals(ROW3, rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // (OFFSET) limit
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" OFFSET 1) AS t LIMIT 2";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue(rs.next());
+            assertEquals(ROW2, rs.getString(1));
+            assertTrue(rs.next());
+            assertEquals(ROW3, rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // (limit OFFSET) limit OFFSET
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 2 OFFSET 1) AS t LIMIT 4 OFFSET 1";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue(rs.next());
+            assertEquals(ROW3, rs.getString(1));
+            assertFalse(rs.next());
+
+            // (limit OFFSET) limit 2
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT 4 OFFSET 1) AS t LIMIT 2";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue(rs.next());
+            assertEquals(ROW2, rs.getString(1));
+            assertTrue(rs.next());
+            assertEquals(ROW3, rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // (limit ? OFFSET ?) limit ? OFFSET ?
+            query = "SELECT t.eid FROM (SELECT entity_id eid FROM "+tableName+" LIMIT ? OFFSET ?) AS t LIMIT ? OFFSET ?";
+            statement = conn.prepareStatement(query);
+            statement.setInt(1, 4);
+            statement.setInt(2, 2);
+            statement.setInt(3, 2);
+            statement.setInt(4, 2);
+            rs = statement.executeQuery();
+            assertTrue(rs.next());
+            assertEquals(ROW5, rs.getString(1));
+            assertTrue(rs.next());
+            assertEquals(ROW6, rs.getString(1));
+            assertFalse(rs.next());
+
+            // (groupby orderby OFFSET)
+            query = "SELECT a, s FROM (SELECT a_string a, sum(a_byte) s FROM "+tableName+" GROUP BY a_string ORDER BY sum(a_byte) OFFSET 1)";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue(rs.next());
+            assertEquals(A_VALUE, rs.getString(1));
+            assertEquals(10, rs.getInt(2));
+            assertTrue(rs.next());
+            assertEquals(B_VALUE, rs.getString(1));
+            assertEquals(26, rs.getInt(2));
+
+            assertFalse(rs.next());
+
+            // (union OFFSET) groupby
+            query = "SELECT a_string, count(*) FROM (SELECT a_string FROM "+tableName+" where a_byte < 4 union all SELECT a_string FROM "+tableName+" where a_byte > 8 OFFSET 1) group by a_string";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(2,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(C_VALUE,rs.getString(1));
+            assertEquals(1,rs.getInt(2));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testDerivedTableWithDistinctWithoutIndex() throws Exception {
+        String tableName=generateUniqueName();
+        initTableWithoutIndex(tableName);
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            // (distinct)
+            String query = "SELECT * FROM (SELECT DISTINCT a_string, b_string FROM "+tableName+") AS t WHERE t.b_string != '" + C_VALUE + "' ORDER BY t.b_string, t.a_string";
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(B_VALUE,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(B_VALUE,rs.getString(1));
+            assertEquals(B_VALUE,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(E_VALUE,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(B_VALUE,rs.getString(1));
+            assertEquals(E_VALUE,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(C_VALUE,rs.getString(1));
+            assertEquals(E_VALUE,rs.getString(2));
+
+            assertFalse(rs.next());
+
+            // distinct ()
+            query = "SELECT DISTINCT t.a, t.b FROM (SELECT a_string a, b_string b FROM "+tableName+") AS t WHERE t.b != '" + C_VALUE + "' ORDER BY t.b, t.a";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(B_VALUE,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(B_VALUE,rs.getString(1));
+            assertEquals(B_VALUE,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(E_VALUE,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(B_VALUE,rs.getString(1));
+            assertEquals(E_VALUE,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(C_VALUE,rs.getString(1));
+            assertEquals(E_VALUE,rs.getString(2));
+
+            assertFalse(rs.next());
+
+            // distinct (distinct)
+            query = "SELECT DISTINCT t.a FROM (SELECT DISTINCT a_string a, b_string b FROM "+tableName+") AS t";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(B_VALUE,rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(C_VALUE,rs.getString(1));
+
+            assertFalse(rs.next());
+
+            // distinct (groupby)
+            query = "SELECT distinct t.c FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(1,rs.getInt(1));
+            assertTrue (rs.next());
+            assertEquals(4,rs.getInt(1));
+
+            assertFalse(rs.next());
+
+            // distinct (groupby) orderby
+            query = "SELECT distinct t.c FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t ORDER BY t.c DESC";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(4,rs.getInt(1));
+            assertTrue (rs.next());
+            assertEquals(1,rs.getInt(1));
+
+            assertFalse(rs.next());
+
+            // distinct (limit)
+            query = "SELECT DISTINCT t.a, t.b FROM (SELECT a_string a, b_string b FROM "+tableName+" LIMIT 2) AS t";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(B_VALUE,rs.getString(2));
+            assertTrue (rs.next());
+            assertEquals(A_VALUE,rs.getString(1));
+            assertEquals(C_VALUE,rs.getString(2));
+
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testDerivedTableWithAggregateWithoutIndex() throws Exception {
+        String tableName=generateUniqueName();
+        initTableWithoutIndex(tableName);
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            // (count)
+            String query = "SELECT * FROM (SELECT count(*) FROM "+tableName+" WHERE a_byte != 8) AS t";
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(8,rs.getInt(1));
+
+            assertFalse(rs.next());
+
+            // count ()
+            query = "SELECT count(*) FROM (SELECT a_byte FROM "+tableName+") AS t WHERE t.a_byte != 8";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(8,rs.getInt(1));
+
+            assertFalse(rs.next());
+
+            // count (distinct)
+            query = "SELECT count(*) FROM (SELECT DISTINCT a_string FROM "+tableName+") AS t";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(3,rs.getInt(1));
+
+            assertFalse(rs.next());
+
+            // count (groupby)
+            query = "SELECT count(*) FROM (SELECT count(*) c FROM "+tableName+" GROUP BY a_string) AS t";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(3,rs.getInt(1));
+
+            assertFalse(rs.next());
+
+            // count (limit)
+            query = "SELECT count(*) FROM (SELECT entity_id FROM "+tableName+" LIMIT 2) AS t";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(2,rs.getInt(1));
+
+            assertFalse(rs.next());
+
+            // count (subquery)
+            query = "SELECT count(*) FROM (SELECT * FROM "+tableName+" WHERE (organization_id, entity_id) in (SELECT organization_id, entity_id FROM "+tableName+" WHERE a_byte != 8)) AS t";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(8,rs.getInt(1));
+
+            assertFalse(rs.next());
+
+            // count (orderby)
+            query = "SELECT count(a_byte) FROM (SELECT * FROM "+tableName+" order by a_integer) AS t where a_byte != 8";
+            statement = conn.prepareStatement(query);
+            rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(8,rs.getInt(1));
+
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testNestedDerivedTableWithoutIndex() throws Exception {
+        String tableName=generateUniqueName();
+        initTableWithIndex(tableName);
+        initTableWithoutIndex(tableName);
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            // select(select(select))
+            String query = "SELECT q.id, q.x10 * 10 FROM (SELECT t.eid id, t.x + 9 x10, t.astr a, t.bstr b FROM (SELECT entity_id eid, a_string astr, b_string bstr, a_byte + 1 x FROM "+tableName+" WHERE a_byte + 1 < ?) AS t ORDER BY b, id) AS q WHERE q.a = ? OR q.b = ? OR q.b = ?";
+            PreparedStatement statement = conn.prepareStatement(query);
+            statement.setInt(1, 9);
+            statement.setString(2, A_VALUE);
+            statement.setString(3, C_VALUE);
+            statement.setString(4, E_VALUE);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW1,rs.getString(1));
+            assertEquals(110,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW4,rs.getString(1));
+            assertEquals(140,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW2,rs.getString(1));
+            assertEquals(120,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW5,rs.getString(1));
+            assertEquals(150,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW3,rs.getString(1));
+            assertEquals(130,rs.getInt(2));
+            assertTrue (rs.next());
+            assertEquals(ROW6,rs.getString(1));
+            assertEquals(160,rs.getInt(2));
+
+            assertFalse(rs.next());
+
             // select(select(select) join (select(select)))
-            query = "SELECT q1.id, q2.id FROM (SELECT t.eid id, t.astr a, t.bstr b FROM (SELECT entity_id eid, a_string astr, b_string bstr, a_byte abyte FROM aTable) AS t WHERE t.abyte >= ?) AS q1" 
-                        + " JOIN (SELECT t.eid id, t.astr a, t.bstr b, t.abyte x FROM (SELECT entity_id eid, a_string astr, b_string bstr, a_byte abyte FROM aTable) AS t) AS q2 ON q1.a = q2.b" 
-                        + " WHERE q2.x != ? ORDER BY q1.id, q2.id DESC";
+            query = "SELECT q1.id, q2.id FROM (SELECT t.eid id, t.astr a, t.bstr b FROM (SELECT entity_id eid, a_string astr, b_string bstr, a_byte abyte FROM "+tableName+") AS t WHERE t.abyte >= ?) AS q1"
+                    + " JOIN (SELECT t.eid id, t.astr a, t.bstr b, t.abyte x FROM (SELECT entity_id eid, a_string astr, b_string bstr, a_byte abyte FROM "+tableName+") AS t) AS q2 ON q1.a = q2.b"
+                    + " WHERE q2.x != ? ORDER BY q1.id, q2.id DESC";
             statement = conn.prepareStatement(query);
             statement.setInt(1, 8);
             statement.setInt(2, 5);


Mime
View raw message