@Test public void testCompilerOptimization() throws Exception { String sequenceName = generateSequenceNameWithSchema(); String tableName = generateTableNameWithSchema(); conn.createStatement().execute("CREATE SEQUENCE " + sequenceName + " START WITH 3 INCREMENT BY 2"); conn.createStatement().execute("CREATE TABLE " + tableName + " (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX " + generateUniqueName() + " ON " + tableName + "(v1) INCLUDE (v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); stmt.optimizeQuery("SELECT k, NEXT VALUE FOR " + sequenceName + " FROM " + tableName + " WHERE v1 = 'bar'"); }
@Test public void testOrderByOptimizedOut() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE foo (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) IMMUTABLE_ROWS=true"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY k"); assertEquals(OrderBy.FWD_ROW_KEY_ORDER_BY,plan.getOrderBy()); }
@Test public void testChooseIndexFromDoubleQuotedHint() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx1 ON t(v1) INCLUDE(v2)"); conn.createStatement().execute("CREATE INDEX idx2 ON t(v1,v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT /*+ INDEX(t \"IDX1\") INDEX(t idx3) */ k FROM t WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("IDX1", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexWithLongestRowKey() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx1 ON t(v1) INCLUDE(v2)"); conn.createStatement().execute("CREATE INDEX idx2 ON t(v1,v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("IDX2", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testOrderByNotDroppedCompositeKey() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE foo (j INTEGER NOT NULL, k BIGINT NOT NULL, v VARCHAR CONSTRAINT pk PRIMARY KEY (j,k)) IMMUTABLE_ROWS=true"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY k,j"); assertFalse(plan.getOrderBy().getOrderByExpressions().isEmpty()); }
@Test public void testIndexHintParsing() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx1 ON t(v1) INCLUDE(v2)"); conn.createStatement().execute("CREATE INDEX idx2 ON t(v1,v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT /*+ INDEX(t idx3 idx4 \"idx5\") INDEX(t idx6 idx1) */ k FROM t WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("IDX1", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testOrderByNotDropped() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE foo (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) IMMUTABLE_ROWS=true"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY v"); assertFalse(plan.getOrderBy().getOrderByExpressions().isEmpty()); }
@Test public void testChooseTableForSelectionStar() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM t WHERE v1 = 'bar'"); // Choose T because v2 is not in index assertEquals("T", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexEvenWithSelectionStar() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1) INCLUDE (v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM t WHERE v1 = 'bar'"); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexFromOrderBy() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE k > 30 ORDER BY v1 LIMIT 5"); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testRVCAllColsForTableWithSecondaryIndexBasic() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE T (k VARCHAR NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"); conn.createStatement().execute("CREATE INDEX IDX ON T(v1, v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); String query = "select * from t where (k, v1, v2) <= ('3', '1', '2')"; QueryPlan plan = stmt.optimizeQuery(query); assertEquals("T", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChoosePointLookupOverOrderByDesc() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY DESC, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1, k)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE k = 30 ORDER BY v1, k LIMIT 5"); assertEquals("T", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testRVCUsingPkColsReturnedByPlanShouldUseIndex() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE T (k VARCHAR NOT NULL PRIMARY KEY, v1 CHAR(15), v2 VARCHAR)"); conn.createStatement().execute("CREATE INDEX IDX ON T(v1, v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); String query = "select * from t where (v1, v2, k) > ('1', '2', '3')"; QueryPlan plan = stmt.optimizeQuery(query); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexFromOrderByDesc() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY DESC, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE k > 30 ORDER BY v1, k DESC LIMIT 5"); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseTableFromOrderByAsc() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY DESC, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT k FROM t WHERE k > 30 ORDER BY v1, k LIMIT 5"); assertEquals("T", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testTableUsedWithQueryMore() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k1 CHAR(3) NOT NULL, k2 CHAR(15) NOT NULL, k3 DATE NOT NULL, k4 CHAR(15) NOT NULL, CONSTRAINT pk PRIMARY KEY (k1,k2,k3,k4))"); conn.createStatement().execute("CREATE INDEX idx ON t(k1,k3,k2,k4)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT * FROM t WHERE (k1,k2,k3,k4) > ('001','001xx000003DHml',to_date('2015-10-21 09:50:55.0'),'017xx0000022FuI')"); assertEquals("T", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexFromHint() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX idx1 ON t(v1) INCLUDE(v2)"); conn.createStatement().execute("CREATE INDEX idx2 ON t(v1,v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT /*+ INDEX(t idx1) */ k FROM t WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("IDX1", plan.getTableRef().getTable().getTableName().getString()); plan = stmt.optimizeQuery("SELECT k FROM t WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("IDX2", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testChooseIndexFromCaseSensitiveHint2() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE \"t\" (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX \"idx1\" ON \"t\"(v1) INCLUDE(v2)"); conn.createStatement().execute("CREATE INDEX \"idx2\" ON \"t\"(v1,v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT /*+ INDEX(\"t\" \"idx1\") */ k FROM \"t\" WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("idx1", plan.getTableRef().getTable().getTableName().getString()); plan = stmt.optimizeQuery("SELECT k FROM \"t\" WHERE v1 = 'foo' AND v2 = 'bar'"); assertEquals("idx2", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testDistinctPrefixOnVarcharIndex() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR)"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT COUNT(DISTINCT v1) FROM t"); assertTrue(plan.getGroupBy().isOrderPreserving()); assertFalse(plan.getGroupBy().getKeyExpressions().isEmpty()); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }
@Test public void testDistinctPrefixOnIntIndex() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 INTEGER, v2 VARCHAR)"); conn.createStatement().execute("CREATE INDEX idx ON t(v1)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery("SELECT COUNT(DISTINCT v1) FROM t"); assertTrue(plan.getGroupBy().isOrderPreserving()); assertFalse(plan.getGroupBy().getKeyExpressions().isEmpty()); assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString()); }