phoenix-core/src/it/java/org/apache/phoenix/end2end/InstrFunctionIT.java

/*
 * 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.
 */
package org.apache.phoenix.end2end;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Test;

public class InstrFunctionIT extends ParallelStatsDisabledIT {
    private void initTable(Connection conn, String tableName, String sortOrder, String s, String subStr) throws Exception {
        String ddl = "CREATE TABLE " + tableName + " (name VARCHAR NOT NULL PRIMARY KEY " + sortOrder + ", substr VARCHAR)";
        conn.createStatement().execute(ddl);
        String dml = "UPSERT INTO " + tableName + " VALUES(?,?)";
        PreparedStatement stmt = conn.prepareStatement(dml);
        stmt.setString(1, s);
        stmt.setString(2, subStr);
        stmt.execute();
        conn.commit();        
    }
    
     private void testInstr(Connection conn, String queryToExecute, Integer expValue) throws Exception {        
        ResultSet rs;
        rs = conn.createStatement().executeQuery(queryToExecute);
        assertTrue(rs.next());
        assertEquals(expValue.intValue(), rs.getInt(1));
        assertFalse(rs.next());
        
    }
    
      private void testInstrFilter(Connection conn, String queryToExecute, String expected) throws Exception {        
        ResultSet rs;
        PreparedStatement stmt = conn.prepareStatement(queryToExecute);
        rs = stmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(expected, rs.getString(1));
        
    }

    @Test
    public void testSingleByteInstrAscending() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String tableName = generateUniqueName();
        initTable(conn, tableName, "ASC", "abcdefghijkl","fgh");
        String queryToExecute = "SELECT INSTR(name, 'fgh') FROM " + tableName;
        testInstr(conn, queryToExecute, 6);
    }
    
    @Test
    public void testSingleByteInstrDescending() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String tableName = generateUniqueName();
        initTable(conn, tableName, "DESC", "abcdefghijkl","fgh");
        String queryToExecute = "SELECT INSTR(name, 'fgh') FROM " + tableName;
        testInstr(conn, queryToExecute, 6);
    }
    
    @Test
    public void testSingleByteInstrAscendingNoString() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String tableName = generateUniqueName();
        initTable(conn, tableName, "ASC", "abcde fghijkl","lmn");
        String queryToExecute = "SELECT INSTR(name, 'lmn') FROM " + tableName;
        testInstr(conn, queryToExecute, 0);
    }
    
    @Test
    public void testSingleByteInstrDescendingNoString() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String tableName = generateUniqueName();
        initTable(conn, tableName, "DESC", "abcde fghijkl","lmn");
        String queryToExecute = "SELECT INSTR(name, 'lmn') FROM " + tableName;
        testInstr(conn, queryToExecute, 0);
    }

    @Test
    public void testMultiByteInstrAscending() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String tableName = generateUniqueName();
        initTable(conn, tableName, "ASC", "AɚɦFGH","ɚɦ");
        String queryToExecute = "SELECT INSTR(name, 'ɚɦ') FROM " + tableName;
        testInstr(conn, queryToExecute, 2);
    }
    
    @Test
    public void testMultiByteInstrDecending() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String tableName = generateUniqueName();
        initTable(conn, tableName, "DESC", "AɚɦFGH","ɚɦ");
        String queryToExecute = "SELECT INSTR(name, 'ɚɦ') FROM " + tableName;
        testInstr(conn, queryToExecute, 2);
    } 

    @Test
    public void testByteInstrAscendingFilter() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String tableName = generateUniqueName();
        initTable(conn, tableName, "ASC", "abcdefghijkl","fgh");
        String queryToExecute = "select NAME from " + tableName + " where instr(name, 'fgh') > 0";
        testInstrFilter(conn, queryToExecute,"abcdefghijkl");
    }
    
    
    @Test
    public void testByteInstrDecendingFilter() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String tableName = generateUniqueName();
        initTable(conn, tableName, "DESC", "abcdefghijkl","fgh");
        String queryToExecute = "select NAME from " + tableName + " where instr(name, 'fgh') > 0";
        testInstrFilter(conn, queryToExecute,"abcdefghijkl");
    }

    @Test
    public void testNonLiteralExpression() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String tableName = generateUniqueName();
        initTable(conn, tableName, "ASC", "asdf", "sdf");
        // Should be able to use INSTR with a non-literal expression as the 2nd argument
        String query = "SELECT INSTR(name, substr) FROM " + tableName;
        testInstr(conn, query, 2);
    }

    @Test
    public void testNonLiteralSourceExpression() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String tableName = generateUniqueName();
        initTable(conn, tableName, "ASC", "asdf", "sdf");
        // Using the function inside the SELECT will test client-side.
        String query = "SELECT INSTR('asdf', 'sdf') FROM " + tableName;
        testInstr(conn, query, 2);
        query = "SELECT INSTR('asdf', substr) FROM " + tableName;
        testInstr(conn, query, 2);
        query = "SELECT INSTR('qwerty', 'sdf') FROM " + tableName;
        testInstr(conn, query, 0);
        query = "SELECT INSTR('qwerty', substr) FROM " + tableName;
        testInstr(conn, query, 0);
        // Test the built-in function in a where clause to make sure
        // it works server-side (and not just client-side).
        query = "SELECT name FROM " + tableName + " WHERE INSTR(name, substr) = 2";
        testInstrFilter(conn, query, "asdf");
        query = "SELECT name FROM " + tableName + " WHERE INSTR(name, 'sdf') = 2";
        testInstrFilter(conn, query, "asdf");
        query = "SELECT name FROM " + tableName + " WHERE INSTR('asdf', substr) = 2";
        testInstrFilter(conn, query, "asdf");
        query = "SELECT name FROM " + tableName + " WHERE INSTR('asdf', 'sdf') = 2";
        testInstrFilter(conn, query, "asdf");
    }
}