* Licensed to the Apache Software Foundation (ASF) under one

* or more contributor license agreements.  See the NOTICE file

* distributed with this work for additional information

* regarding copyright ownership.  The ASF licenses this file

* to you under the Apache License, Version 2.0 (the

* "License"); you may not use this file except in compliance

* with the License.  You may obtain a copy of the License at

*

* http://www.apache.org/licenses/LICENSE-2.0

*

* Unless required by applicable law or agreed to in writing, software

* distributed under the License is distributed on an "AS IS" BASIS,

* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

* See the License for the specific language governing permissions and

* limitations under the License.

*/

*

* Basic tests for Phoenix JDBC implementation

*

*

* @since 0.1

*/

    private static final String tenantId = getOrganizationId();

    private static final String ATABLE_INDEX_NAME = "ATABLE_IDX";

   

    @BeforeClass

    public static void doSetup() throws Exception {

        int targetQueryConcurrency = 2;

        int maxQueryConcurrency = 3;

        Map<String,String> props = Maps.newHashMapWithExpectedSize(3);

        props.put(QueryServices.QUEUE_SIZE_ATTRIB, Integer.toString(100));

        props.put(QueryServices.MAX_QUERY_CONCURRENCY_ATTRIB, Integer.toString(maxQueryConcurrency));

        props.put(QueryServices.TARGET_QUERY_CONCURRENCY_ATTRIB, Integer.toString(targetQueryConcurrency));

        props.put(IndexWriterUtils.HTABLE_THREAD_KEY, Integer.toString(100));

       

        // Must update config before starting server

        startServer(getUrl(), new ReadOnlyProps(props.entrySet().iterator()));

    }

   

    private long ts;

    private Date date;

    private String indexDDL;

   

    public QueryTest(String indexDDL) {

        this.indexDDL = indexDDL;

    }

   

    @Before

    public void initTable() throws Exception {

         ts = nextTimestamp();

        initATableValues(tenantId, getDefaultSplits(tenantId), date=new Date(System.currentTimeMillis()), ts);

        if (indexDDL != null && indexDDL.length() > 0) {

            Properties props = new Properties(TEST_PROPERTIES);

            props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));

            Connection conn = DriverManager.getConnection(getUrl(), props);

            conn.createStatement().execute(indexDDL);

        }

    }

   

    @Parameters(name="{0}")

    public static Collection<Object> data() {

        List<Object> testCases = Lists.newArrayList();

        testCases.add(new String[] { "CREATE INDEX " + ATABLE_INDEX_NAME + " ON aTable (a_integer DESC) INCLUDE ("

                + "    A_STRING, " + "    B_STRING, " + "    A_DATE)" });

        testCases.add(new String[] { "CREATE INDEX " + ATABLE_INDEX_NAME + " ON aTable (a_integer, a_string) INCLUDE ("

                + "    B_STRING, " + "    A_DATE)" });

        testCases.add(new String[] { "CREATE INDEX " + ATABLE_INDEX_NAME + " ON aTable (a_integer) INCLUDE ("

                + "    A_STRING, " + "    B_STRING, " + "    A_DATE)" });

        testCases.add(new String[] { "" });

        return testCases;

    }

   

    private void assertValueEqualsResultSet(ResultSet rs, List<Object> expectedResults) throws SQLException {

        List<List<Object>> nestedExpectedResults = Lists.newArrayListWithExpectedSize(expectedResults.size());

        for (Object expectedResult : expectedResults) {

            nestedExpectedResults.add(Arrays.asList(expectedResult));

        }

        assertValuesEqualsResultSet(rs, nestedExpectedResults);

    }

   

    private void assertValuesEqualsResultSet(ResultSet rs, List<List<Object>> expectedResults) throws SQLException {

        Set<List<Object>> expectedResultsSet = Sets.newHashSet(expectedResults);

        int count = 0;

        while (rs.next()) {

            List<Object> results = Lists.newArrayList();

            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {

                results.add(rs.getObject(i+1));

            }

            assertTrue("Expected to find " + results + " in results: " + results, expectedResultsSet.contains(results));

            count++;

        }

        assertEquals(count, expectedResults.size());

    }

   

    private void assertOneOfValuesEqualsResultSet(ResultSet rs, List<List<Object>>... expectedResultsArray) throws SQLException {

        List<List<Object>> results = Lists.newArrayList();

        while (rs.next()) {

            List<Object> result = Lists.newArrayList();

            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {

                result.add(rs.getObject(i+1));

            }

            results.add(result);

        }

        for (int j = 0; j < expectedResultsArray.length; j++) {

                List<List<Object>> expectedResults = expectedResultsArray[j];

                Set<List<Object>> expectedResultsSet = Sets.newHashSet(expectedResults);

                int count = 0;

                boolean brokeEarly = false;

                for (List<Object> result : results) {

                    if (!expectedResultsSet.contains(result)) {

                        brokeEarly = true;

                        break;

                    }

                    count++;

                }

                if (!brokeEarly && count == expectedResults.size()) {

                    return;

                }

        }

        fail("Unable to find " + results + " in " + Arrays.asList(expectedResultsArray));

    }

   

    @Test

    public void testIntFilter() throws Exception {

        String updateStmt =

            "upsert into " +

            "ATABLE(" +

            "    ORGANIZATION_ID, " +

            "    ENTITY_ID, " +

            "    A_INTEGER) " +

            "VALUES (?, ?, ?)";

        // Override value that was set at creation time

        String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 1);

        Properties props = new Properties(TEST_PROPERTIES);

        Connection upsertConn = DriverManager.getConnection(url, props);

        upsertConn.setAutoCommit(true); // Test auto commit

        PreparedStatement stmt = upsertConn.prepareStatement(updateStmt);

        stmt.setString(1, tenantId);

        stmt.setString(2, ROW4);

        stmt.setInt(3, -10);

        stmt.execute();

        upsertConn.close();

        String query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_integer >= ?";

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        PreparedStatement statement = conn.prepareStatement(query);

        statement.setString(1, tenantId);

        statement.setInt(2, 7);

        ResultSet rs = statement.executeQuery();

        assertValueEqualsResultSet(rs, Arrays.<Object>asList(ROW7, ROW8, ROW9));

        query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_integer < 2";

        statement = conn.prepareStatement(query);

        statement.setString(1, tenantId);

        rs = statement.executeQuery();

        assertValueEqualsResultSet(rs, Arrays.<Object>asList(ROW1, ROW4));

        query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_integer <= 2";

        statement = conn.prepareStatement(query);

        statement.setString(1, tenantId);

        rs = statement.executeQuery();

        assertValueEqualsResultSet(rs, Arrays.<Object>asList(ROW1, ROW2, ROW4));

        query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_integer >=9";

        statement = conn.prepareStatement(query);

        statement.setString(1, tenantId);

        rs = statement.executeQuery();

        assertTrue (rs.next());

        assertEquals(rs.getString(1), ROW9);

        assertFalse(rs.next());

        conn.close();

    }

   

    @Test

    public void testEmptyStringValue() throws Exception {

        testNoStringValue("");

    }

    @Test

    public void testScan() throws Exception {

        String query = "SELECT a_string, /* comment ok? */ b_string FROM aTable WHERE ?=organization_id and 5=a_integer";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            ResultSet rs = statement.executeQuery();

            assertTrue (rs.next());

            assertEquals(rs.getString(1), B_VALUE);

            assertEquals(rs.getString("B_string"), C_VALUE);

            assertFalse(rs.next());

        } finally {

            conn.close();

        }

    }

   

    @Test

    public void testScanByByteValue() throws Exception {

        String query = "SELECT a_string, b_string, a_byte FROM aTable WHERE ?=organization_id and 1=a_byte";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            ResultSet rs = statement.executeQuery();

            assertTrue (rs.next());

            assertEquals(rs.getString(1), A_VALUE);

            assertEquals(rs.getString("B_string"), B_VALUE);

            assertEquals(rs.getByte(3), 1);

            assertFalse(rs.next());

        } finally {

            conn.close();

        }

    }

   

    @Test

    public void testScanByShortValue() throws Exception {

        String query = "SELECT a_string, b_string, a_short FROM aTable WHERE ?=organization_id and 128=a_short";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            ResultSet rs = statement.executeQuery();

            assertTrue (rs.next());

            assertEquals(rs.getString(1), A_VALUE);

            assertEquals(rs.getString("B_string"), B_VALUE);

            assertEquals(rs.getShort("a_short"), 128);

            assertFalse(rs.next());

        } finally {

            conn.close();

        }

    }

   

    @Test

    public void testScanByFloatValue() throws Exception {

        String query = "SELECT a_string, b_string, a_float FROM aTable WHERE ?=organization_id and ?=a_float";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            statement.setFloat(2, 0.01f);

            ResultSet rs = statement.executeQuery();

            assertTrue (rs.next());

            assertEquals(rs.getString(1), A_VALUE);

            assertEquals(rs.getString("B_string"), B_VALUE);

            assertTrue(Floats.compare(rs.getFloat(3), 0.01f) == 0);

            assertFalse(rs.next());

        } finally {

            conn.close();

        }

    }

   

    @Test

    public void testScanByUnsignedFloatValue() throws Exception {

        String query = "SELECT a_string, b_string, a_unsigned_float FROM aTable WHERE ?=organization_id and ?=a_unsigned_float";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            statement.setFloat(2, 0.01f);

            ResultSet rs = statement.executeQuery();

            assertTrue (rs.next());

            assertEquals(rs.getString(1), A_VALUE);

            assertEquals(rs.getString("B_string"), B_VALUE);

            assertTrue(Floats.compare(rs.getFloat(3), 0.01f) == 0);

            assertFalse(rs.next());

        } finally {

            conn.close();

        }

    }

   

    @Test

    public void testScanByDoubleValue() throws Exception {

        String query = "SELECT a_string, b_string, a_double FROM aTable WHERE ?=organization_id and ?=a_double";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            statement.setDouble(2, 0.0001);

            ResultSet rs = statement.executeQuery();

            assertTrue (rs.next());

            assertEquals(rs.getString(1), A_VALUE);

            assertEquals(rs.getString("B_string"), B_VALUE);

            assertTrue(Doubles.compare(rs.getDouble(3), 0.0001) == 0);

            assertFalse(rs.next());

        } finally {

            conn.close();

        }

    }

   

    @Test

    public void testScanByUnsigned_DoubleValue() throws Exception {

        String query = "SELECT a_string, b_string, a_unsigned_double FROM aTable WHERE ?=organization_id and ?=a_unsigned_double";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            statement.setDouble(2, 0.0001);

            ResultSet rs = statement.executeQuery();

            assertTrue (rs.next());

            assertEquals(rs.getString(1), A_VALUE);

            assertEquals(rs.getString("B_string"), B_VALUE);

            assertTrue(Doubles.compare(rs.getDouble(3), 0.0001) == 0);

            assertFalse(rs.next());

        } finally {

            conn.close();

        }

    }

   

    @Test

    public void testAllScan() throws Exception {

        String query = "SELECT ALL a_string, b_string FROM aTable WHERE ?=organization_id and 5=a_integer";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            ResultSet rs = statement.executeQuery();

            assertTrue (rs.next());

            assertEquals(rs.getString(1), B_VALUE);

            assertEquals(rs.getString("B_string"), C_VALUE);

            assertFalse(rs.next());

        } finally {

            conn.close();

        }

    }

   

    @Test

    public void testDistinctScan() throws Exception {

        String query = "SELECT DISTINCT a_string FROM aTable WHERE organization_id=?";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            ResultSet rs = statement.executeQuery();

            assertTrue (rs.next());

            assertEquals(rs.getString(1), A_VALUE);

            assertTrue (rs.next());

            assertEquals(rs.getString(1), B_VALUE);

            assertTrue (rs.next());

            assertEquals(rs.getString(1), C_VALUE);

            assertFalse(rs.next());

        } finally {

            conn.close();

        }

    }

    @Test

    public void testInListSkipScan() throws Exception {

        String query = "SELECT entity_id, b_string FROM aTable WHERE organization_id=? and entity_id IN (?,?)";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            statement.setString(2, ROW2);

            statement.setString(3, ROW4);

            ResultSet rs = statement.executeQuery();

            Set<String> expectedvals = new HashSet<String>();

            expectedvals.add(ROW2+"_"+C_VALUE);

            expectedvals.add(ROW4+"_"+B_VALUE);

            Set<String> vals = new HashSet<String>();

            assertTrue (rs.next());

            vals.add(rs.getString(1) + "_" + rs.getString(2));

            assertTrue (rs.next());

            vals.add(rs.getString(1) + "_" + rs.getString(2));

            assertFalse(rs.next());

            assertEquals(expectedvals, vals);

        } finally {

            conn.close();

        }

    }

    @Test

    public void testNotInList() throws Exception {

        String query = "SELECT entity_id FROM aTable WHERE organization_id=? and entity_id NOT IN (?,?,?,?,?,?)";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            statement.setString(2, ROW2);

            statement.setString(3, ROW4);

            statement.setString(4, ROW1);

            statement.setString(5, ROW5);

            statement.setString(6, ROW7);

            statement.setString(7, ROW8);

            ResultSet rs = statement.executeQuery();

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

        } finally {

            conn.close();

        }

    }

   

    @Test

    public void testNotInListOfFloat() throws Exception {

        String query = "SELECT a_float FROM aTable WHERE organization_id=? and a_float NOT IN (?,?,?,?,?,?)";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            statement.setFloat(2, 0.01f);

            statement.setFloat(3, 0.02f);

            statement.setFloat(4, 0.03f);

            statement.setFloat(5, 0.04f);

            statement.setFloat(6, 0.05f);

            statement.setFloat(7, 0.06f);

            ResultSet rs = statement.executeQuery();

            assertTrue (rs.next());

            assertTrue(Doubles.compare(rs.getDouble(1), 0.07f)==0);

            assertTrue (rs.next());

            assertTrue(Doubles.compare(rs.getDouble(1), 0.08f)==0);

            assertTrue (rs.next());

            assertTrue(Doubles.compare(rs.getDouble(1), 0.09f)==0);

            assertFalse(rs.next());

        } finally {

            conn.close();

        }

    }

   

    @Test

    public void testNotInListOfDouble() throws Exception {

        String query = "SELECT a_double FROM aTable WHERE organization_id=? and a_double NOT IN (?,?,?,?,?,?)";

        Properties props = new Properties(TEST_PROPERTIES);

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2

        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);

        try {

            PreparedStatement statement = conn.prepareStatement(query);

            statement.setString(1, tenantId);

            statement.setDouble(2, 0.0001);

            statement.setDouble(3, 0.0002);

            statement.setDouble(4, 0.0003);

            statement.setDouble(5, 0.0004);

            statement.setDouble(6, 0.0005);

            statement.setDouble(7, 0.0006);

            ResultSet rs = statement.executeQuery();