Examples with PhoenixStatement org.apache.phoenix.jdbc.PhoenixStatement used on opensource projects

Example 41 with PhoenixStatement

use of org.apache.phoenix.jdbc.PhoenixStatement in project phoenix by apache.

the class QueryOptimizerTest method testChooseIndexWithLongestRowKey.

@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());
}

Example 42 with PhoenixStatement

use of org.apache.phoenix.jdbc.PhoenixStatement in project phoenix by apache.

the class QueryOptimizerTest method testCharArrayLength.

@Test
public void testCharArrayLength() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    conn.createStatement().execute("CREATE TABLE TEST.TEST (testInt INTEGER, testCharArray CHAR(3)[], testByteArray BINARY(7)[], " + "CONSTRAINT test_pk PRIMARY KEY(testInt)) DEFAULT_COLUMN_FAMILY='T'");
    conn.createStatement().execute("CREATE INDEX TEST_INDEX ON TEST.TEST (testInt) INCLUDE (testCharArray, testByteArray)");
    PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class);
    QueryPlan plan = stmt.optimizeQuery("SELECT /*+ INDEX(TEST.TEST TEST_INDEX)*/ testCharArray,testByteArray FROM TEST.TEST");
    List<PColumn> columns = plan.getTableRef().getTable().getColumns();
    assertEquals(3, columns.size());
    assertEquals(3, columns.get(1).getMaxLength().intValue());
    assertEquals(7, columns.get(2).getMaxLength().intValue());
}

Example 43 with PhoenixStatement

use of org.apache.phoenix.jdbc.PhoenixStatement in project phoenix by apache.

the class QueryOptimizerTest method testChooseSmallerTable.

@Test
public void testChooseSmallerTable() 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 count(*) FROM t");
    assertEquals("IDX", plan.getTableRef().getTable().getTableName().getString());
}

Example 44 with PhoenixStatement

use of org.apache.phoenix.jdbc.PhoenixStatement in project phoenix by apache.

the class QueryOptimizerTest method testChoosePointLookupOverOrderByDesc.

@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());
}

Example 45 with PhoenixStatement

use of org.apache.phoenix.jdbc.PhoenixStatement in project phoenix by apache.

the class QueryOptimizerTest method testViewUsedWithQueryMore.

private void testViewUsedWithQueryMore(Integer saltBuckets) throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    int offset = saltBuckets == null ? 0 : 1;
    conn.createStatement().execute("CREATE TABLE MY_TABLES.MY_TABLE " + "(ORGANIZATION_ID CHAR(15) NOT NULL, " + "PKCOL1 CHAR(15) NOT NULL," + "PKCOL2 CHAR(15) NOT NULL," + "PKCOL3 CHAR(15) NOT NULL," + "PKCOL4 CHAR(15) NOT NULL,COL1 " + "CHAR(15)," + "COL2 CHAR(15)" + "CONSTRAINT PK PRIMARY KEY (ORGANIZATION_ID,PKCOL1,PKCOL2,PKCOL3,PKCOL4)) MULTI_TENANT=true" + (saltBuckets == null ? "" : (",SALT_BUCKETS=" + saltBuckets)));
    conn.createStatement().execute("CREATE INDEX MY_TABLE_INDEX \n" + "ON MY_TABLES.MY_TABLE (PKCOL1, PKCOL3, PKCOL2, PKCOL4)\n" + "INCLUDE (COL1, COL2)");
    Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, "000000000000000");
    Connection tsconn = DriverManager.getConnection(getUrl(), props);
    tsconn.createStatement().execute("CREATE VIEW MY_TABLE_MT_VIEW AS SELECT * FROM MY_TABLES.MY_TABLE");
    PhoenixStatement stmt = tsconn.createStatement().unwrap(PhoenixStatement.class);
    QueryPlan plan = stmt.optimizeQuery("select * from my_table_mt_view where (pkcol1, pkcol2, pkcol3, pkcol4) > ('0', '0', '0', '0')");
    assertEquals("MY_TABLE_MT_VIEW", plan.getTableRef().getTable().getTableName().getString());
    plan = stmt.compileQuery("select * from my_table_mt_view where (pkcol1, pkcol2) > ('0', '0') and pkcol3 = '000000000000000' and pkcol4 = '000000000000000'");
    assertEquals(3 + offset, plan.getContext().getScanRanges().getBoundPkColumnCount());
    plan = stmt.compileQuery("select * from my_table_mt_view where (pkcol3, pkcol4) > ('0', '0') and pkcol1 = '000000000000000'");
    assertEquals(2 + offset, plan.getContext().getScanRanges().getBoundPkColumnCount());
    plan = stmt.compileQuery("select * from my_table_mt_view where (pkcol1, pkcol2, pkcol3) < ('0', '0', '0')");
    assertEquals(4 + offset, plan.getContext().getScanRanges().getBoundPkColumnCount());
    plan = stmt.compileQuery("select * from my_table_mt_view where (pkcol1, pkcol2, pkcol3) < ('9', '9', '9') and (pkcol1, pkcol2) > ('0', '0')");
    assertEquals(4 + offset, plan.getContext().getScanRanges().getBoundPkColumnCount());
    plan = stmt.compileQuery("select * from my_table_mt_view where pkcol1 = 'a' and pkcol2 = 'b' and pkcol3 = 'c' and (pkcol1, pkcol2) < ('z', 'z')");
    assertEquals(4 + offset, plan.getContext().getScanRanges().getBoundPkColumnCount());
    // TODO: in theory pkcol2 and pkcol3 could be bound, but we don't have the logic for that yet
    plan = stmt.compileQuery("select * from my_table_mt_view where (pkcol2, pkcol3) > ('0', '0') and pkcol1 = '000000000000000'");
    assertEquals(2 + offset, plan.getContext().getScanRanges().getBoundPkColumnCount());
}