@Test public void testProjection() throws SQLException { Connection conn = DriverManager.getConnection(getUrl()); try { conn.createStatement().execute("CREATE TABLE t(k INTEGER PRIMARY KEY, a.v1 VARCHAR, b.v2 VARCHAR, c.v3 VARCHAR)"); assertFamilies(projectQuery("SELECT k FROM t"), "A"); assertFamilies(projectQuery("SELECT k FROM t WHERE k = 5"), "A"); assertFamilies(projectQuery("SELECT v2 FROM t WHERE k = 5"), "A", "B"); assertFamilies(projectQuery("SELECT v2 FROM t WHERE v2 = 'a'"), "B"); assertFamilies(projectQuery("SELECT v3 FROM t WHERE v2 = 'a'"), "B", "C"); assertFamilies(projectQuery("SELECT v3 FROM t WHERE v2 = 'a' AND v3 is null"), "A", "B", "C"); } finally { conn.close(); } }
" FROM " + "\""+ SYSTEM_CATALOG_SCHEMA + "\".\"" + SYSTEM_STATS_TABLE + "\"" + " LIMIT 1"; List<Object> binds = Collections.emptyList(); QueryPlan plan = getQueryPlan(query, binds); RowProjector p = plan.getProjector(); assertLiteralEquals((long)Integer.MIN_VALUE, p, 0); assertLiteralEquals(Long.MIN_VALUE, p, 1); assertLiteralEquals((long)Integer.MIN_VALUE + 1, p, 2); assertLiteralEquals(Long.MIN_VALUE + 1, p, 3); assertLiteralEquals((long)Integer.MIN_VALUE - 1, p, 4); assertLiteralEquals(oneLessThanMinLong, p, 5); assertLiteralEquals(Integer.MAX_VALUE, p, 6); assertLiteralEquals(Long.MAX_VALUE, p, 7); assertLiteralEquals(Integer.MAX_VALUE - 1, p, 8); assertLiteralEquals(Long.MAX_VALUE - 1, p, 9); assertLiteralEquals((long)Integer.MAX_VALUE + 1, p, 10); assertLiteralEquals(oneMoreThanMaxLong, p, 11);
@Test public void testCastingWithLengthInWhere() throws Exception { String query = "SELECT b_string FROM aTable WHERE CAST (b_string AS VARCHAR(10)) = 'b'"; List<Object> binds = Collections.emptyList(); compileQuery(query, binds); }
@Test public void testAggregateOnColumnsNotInGroupByForImmutableEncodedTable() throws Exception { String tableName = generateUniqueName(); String ddl = "CREATE IMMUTABLE TABLE " + tableName + " (a_string varchar not null, col1 integer, col2 integer" + " CONSTRAINT pk PRIMARY KEY (a_string))"; String query = "SELECT col1, max(a_string) from " + tableName + " group by col2"; try (Connection conn = DriverManager.getConnection(getUrl())) { conn.createStatement().execute(ddl); try { PreparedStatement statement = conn.prepareStatement(query); statement.executeQuery(); fail(); } catch (SQLException e) { // expected assertEquals(SQLExceptionCode.AGGREGATE_WITH_NOT_GROUP_BY_COLUMN.getErrorCode(), e.getErrorCode()); } } }
@Test public void testMultiCFProjection() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); String ddl = "CREATE TABLE multiCF (k integer primary key, a.a varchar, b.b varchar)"; conn.createStatement().execute(ddl); String query = "SELECT COUNT(*) FROM multiCF"; QueryPlan plan = getQueryPlan(query,Collections.emptyList()); plan.iterator(); Scan scan = plan.getContext().getScan(); assertTrue(scan.getFilter() instanceof FirstKeyOnlyFilter); assertEquals(1, scan.getFamilyMap().size()); }
@Test public void testInvalidNextValueFor() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("CREATE SEQUENCE alpha.zeta"); String[] queries = { "SELECT * FROM aTable WHERE a_integer < next value for alpha.zeta", "SELECT * FROM aTable GROUP BY a_string,next value for alpha.zeta", "SELECT * FROM aTable GROUP BY 1 + next value for alpha.zeta", "SELECT * FROM aTable GROUP BY a_integer HAVING a_integer < next value for alpha.zeta", "SELECT * FROM aTable WHERE a_integer < 3 GROUP BY a_integer HAVING a_integer < next value for alpha.zeta", "SELECT * FROM aTable ORDER BY next value for alpha.zeta", "SELECT max(next value for alpha.zeta) FROM aTable", }; for (String query : queries) { List<Object> binds = Collections.emptyList(); try { compileQuery(query, binds); fail("Compilation should have failed since this is an invalid usage of NEXT VALUE FOR: " + query); } catch (SQLException e) { assertEquals(query, SQLExceptionCode.INVALID_USE_OF_NEXT_VALUE_FOR.getErrorCode(), e.getErrorCode()); } } }
@Test public void testIndexOnViewWithChildView() throws SQLException { try (Connection conn = DriverManager.getConnection(getUrl())) { conn.createStatement().execute("CREATE TABLE PLATFORM_ENTITY.GLOBAL_TABLE (\n" + " ORGANIZATION_ID CHAR(15) NOT NULL,\n" + String query = "SELECT DOUBLE1 FROM PLATFORM_ENTITY.GLOBAL_VIEW\n" + "WHERE ORGANIZATION_ID = '00Dxx0000002Col' AND TEXT1='Test' AND INT1=1"; QueryPlan plan = getOptimizedQueryPlan(query); assertEquals("PLATFORM_ENTITY.GLOBAL_VIEW", plan.getContext().getCurrentTable().getTable().getName() .getString()); query = "SELECT DOUBLE1 FROM PLATFORM_ENTITY.GLOBAL_VIEW\n" + "WHERE ORGANIZATION_ID = '00Dxx0000002Col' AND TEXT1='Test'"; plan = getOptimizedQueryPlan(query); assertEquals("PLATFORM_ENTITY.GLOBAL_INDEX", plan.getContext().getCurrentTable().getTable().getName().getString());
@Test public void testSmallScanForPointLookups() throws SQLException { Properties props = PropertiesUtil.deepCopy(new Properties()); createTestTable(getUrl(), "CREATE TABLE FOO(\n" + " a VARCHAR NOT NULL,\n" + " b VARCHAR NOT NULL,\n" + " c VARCHAR,\n" + " CONSTRAINT pk PRIMARY KEY (a, b DESC, c)\n" + " )"); props.put(QueryServices.SMALL_SCAN_THRESHOLD_ATTRIB, "2"); try (Connection conn = DriverManager.getConnection(getUrl(), props)) { String query = "select * from foo where a = 'a' and b = 'b' and c in ('x','y','z')"; PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.optimizeQuery(query); plan.iterator(); //Fail since we have 3 rows in pointLookup assertFalse(plan.getContext().getScan().isSmall()); query = "select * from foo where a = 'a' and b = 'b' and c = 'c'"; plan = stmt.compileQuery(query); plan.iterator(); //Should be small scan, query is for single row pointLookup assertTrue(plan.getContext().getScan().isSmall()); } }
private Scan compileQuery(String query, List<Object> binds) throws SQLException { QueryPlan plan = getQueryPlan(query, binds); return plan.getContext().getScan(); }
@Test public void testGroupByOrderMatchPkColumnOrder4690() throws Exception{ this.doTestGroupByOrderMatchPkColumnOrderBug4690(false, false); this.doTestGroupByOrderMatchPkColumnOrderBug4690(false, true); this.doTestGroupByOrderMatchPkColumnOrderBug4690(true, false); this.doTestGroupByOrderMatchPkColumnOrderBug4690(true, true); }
private QueryPlan getOptimizedQueryPlan(String query) throws SQLException { return getOptimizedQueryPlan(query, Collections.emptyList()); }
for(boolean salted: new boolean[]{true,false}) { boolean[] groupBys=new boolean[]{true,true,true,true,false,false,false,false}; doTestOrderByReverseOptimizationBug3491(salted,true,true,true, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationBug3491(salted,true,true,false, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationBug3491(salted,true,false,true, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationBug3491(salted,true,false,false, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationBug3491(salted,false,true,true, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationBug3491(salted,false,true,false, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationBug3491(salted,false,false,true, groupBys, new OrderBy[]{
doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,true,true, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,true,false, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,false,true, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,true,false,false, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,true,true, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,true,false, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,false,true, groupBys, new OrderBy[]{ doTestOrderByReverseOptimizationWithNUllsLastBug3491(salted,false,false,false,
@Test public void testOrderPreservingGroupBy() throws Exception { try (Connection conn= DriverManager.getConnection(getUrl())) { conn.createStatement().execute("CREATE TABLE test (\n" + " pk1 INTEGER NOT NULL,\n" + " pk2 INTEGER NOT NULL,\n" + " pk3 INTEGER NOT NULL,\n" + " pk4 INTEGER NOT NULL,\n" + " v1 INTEGER,\n" + " CONSTRAINT pk PRIMARY KEY (\n" + " pk1,\n" + " pk2,\n" + " pk3,\n" + " pk4\n" + " )\n" + " )"); String[] queries = new String[] { "SELECT pk3 FROM test WHERE pk2 = 1 GROUP BY pk2+1,pk3 ORDER BY pk3", "SELECT pk3 FROM test WHERE pk2 = 1 GROUP BY pk2,pk3 ORDER BY pk3", "SELECT pk3 FROM test WHERE pk1 = 1 and pk2 = 2 GROUP BY pk1+pk2,pk3 ORDER BY pk3", "SELECT pk3 FROM test WHERE pk1 = 1 and pk2 = 2 GROUP BY pk4,CASE WHEN pk1 > pk2 THEN pk1 ELSE pk2 END,pk3 ORDER BY pk4,pk3", }; int index = 0; for (String query : queries) { QueryPlan plan = getQueryPlan(conn, query); assertTrue((index + 1) + ") " + queries[index], plan.getOrderBy().getOrderByExpressions().isEmpty()); index++; } } }
private void doTestGroupByOrderMatchPkColumnOrderBug4690(boolean desc ,boolean salted) throws Exception { Connection conn = null; try { conn = DriverManager.getConnection(getUrl()); String tableName = generateUniqueName(); String sql = "create table " + tableName + "( "+ " pk1 integer not null , " +
private Scan projectQuery(String query) throws SQLException { QueryPlan plan = getQueryPlan(query, Collections.emptyList()); plan.iterator(); // Forces projection return plan.getContext().getScan(); }
@Test public void testNotOrderPreservingGroupBy() throws Exception { try (Connection conn= DriverManager.getConnection(getUrl())) { int index = 0; for (String query : queries) { QueryPlan plan = getQueryPlan(conn, query); assertFalse((index + 1) + ") " + queries[index], plan.getOrderBy().getOrderByExpressions().isEmpty()); index++;
@Test public void testCastingIntegerToDecimalInSelect() throws Exception { String query = "SELECT CAST (a_integer AS DECIMAL)/2 FROM aTable WHERE 5=a_integer"; List<Object> binds = Collections.emptyList(); compileQuery(query, binds); }
@Test public void testNotKeyOrderedGroupByOptimization() throws Exception { // Select columns in PK String[] queries = new String[] { "SELECT count(1) FROM atable GROUP BY entity_id", "SELECT count(1) FROM atable GROUP BY substr(organization_id,2,3)", "SELECT count(1) FROM atable GROUP BY substr(entity_id,1,3)", "SELECT count(1) FROM atable GROUP BY to_date(organization_id)", "SELECT count(1) FROM atable GROUP BY regexp_substr(organization_id, '.*foo.*'),entity_id", "SELECT count(1) FROM atable GROUP BY substr(organization_id,1),entity_id", }; List<Object> binds = Collections.emptyList(); for (String query : queries) { QueryPlan plan = getQueryPlan(query, binds); assertEquals(plan.getGroupBy().getScanAttribName(), BaseScannerRegionObserver.UNORDERED_GROUP_BY_EXPRESSIONS); } }
@Test public void testOrderPreservingGroupByForNotPkColumns() throws Exception { try (Connection conn= DriverManager.getConnection(getUrl())) { conn.createStatement().execute("CREATE TABLE test (\n" + " pk1 varchar, \n" + int index = 0; for (String query : queries) { QueryPlan plan = getQueryPlan(conn, query); assertTrue((index + 1) + ") " + queries[index], plan.getOrderBy().getOrderByExpressions().isEmpty()); index++;