phoenix-core/src/it/java/org/apache/phoenix/end2end/LikeExpressionIT.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.apache.phoenix.util.TestUtil.closeStmtAndConn;
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 java.sql.SQLException;
import java.sql.Statement;

import org.junit.Before;
import org.junit.Test;

public class LikeExpressionIT extends ParallelStatsDisabledIT {

    private String tableName;

    @Before
    public void initTable() throws Exception {
        tableName = generateUniqueName();
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = DriverManager.getConnection(getUrl());
            String ddl;
            ddl = "CREATE TABLE " + tableName + " (k VARCHAR NOT NULL PRIMARY KEY, i INTEGER)";
            conn.createStatement().execute(ddl);
            conn.commit();
        } finally {
            closeStmtAndConn(stmt, conn);
        }
        insertRow(conn, "123n7-app-2-", 1);
        insertRow(conn, "132n7-App-2-", 2);
        insertRow(conn, "213n7-app-2-", 4);
        insertRow(conn, "231n7-App-2-", 8);
        insertRow(conn, "312n7-app-2-", 16);
        insertRow(conn, "321n7-App-2-", 32);
    }

    private void insertRow(Connection conn, String k, int i) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement(
            "UPSERT INTO " + tableName + " VALUES (?, ?)");
        stmt.setString(1, k);
        stmt.setInt(2, i);
        stmt.executeUpdate();
        conn.commit();
    }

    private void testLikeExpression(Connection conn, String likeStr, int numResult, int expectedSum)
            throws Exception {
        String cmd = "select k, i from " + tableName + " where k like '" + likeStr + "'";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(cmd);
        int sum = 0;
        for (int i = 0; i < numResult; ++i) {
            assertTrue(rs.next());
            sum += rs.getInt("i");
        }
        assertFalse(rs.next());
        assertEquals(sum, expectedSum);
    }

    @Test
    public void testLikeExpression() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        // wildcard
        testLikeExpression(conn, "%1%3%7%2%", 3, 7);
        // CaseSensitive
        testLikeExpression(conn, "%A%", 3, 42);
        conn.close();
    }

    @Test
    public void testLikeEverythingExpression() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String table = generateUniqueName();
        String ddl = "CREATE TABLE " + table
                + " (k1 VARCHAR, k2 VARCHAR, CONSTRAINT pk PRIMARY KEY (k1,k2))";
        conn.createStatement().execute(ddl);
        conn.createStatement().execute("UPSERT INTO " + table + " VALUES('aa','bb')");
        conn.createStatement().execute("UPSERT INTO " + table + " VALUES('ab','bc')");
        conn.createStatement().execute("UPSERT INTO " + table + " VALUES(null,'cc')");
        conn.createStatement().execute("UPSERT INTO " + table + " VALUES('dd',null)");
        conn.commit();

        ResultSet rs = conn.createStatement().executeQuery(
                "SELECT * FROM " + table + " WHERE k1 LIKE '%'");
        assertTrue(rs.next());
        assertEquals("aa", rs.getString(1));
        assertEquals("bb", rs.getString(2));
        assertTrue(rs.next());
        assertEquals("ab", rs.getString(1));
        assertEquals("bc", rs.getString(2));
        assertTrue(rs.next());
        assertEquals("dd", rs.getString(1));
        assertEquals(null, rs.getString(2));
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE k2 LIKE '%'");
        assertTrue(rs.next());
        assertEquals(null, rs.getString(1));
        assertEquals("cc", rs.getString(2));
        assertTrue(rs.next());
        assertEquals("aa", rs.getString(1));
        assertEquals("bb", rs.getString(2));
        assertTrue(rs.next());
        assertEquals("ab", rs.getString(1));
        assertEquals("bc", rs.getString(2));
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE k2 LIKE '%%'");
        assertTrue(rs.next());
        assertEquals(null, rs.getString(1));
        assertEquals("cc", rs.getString(2));
        assertTrue(rs.next());
        assertEquals("aa", rs.getString(1));
        assertEquals("bb", rs.getString(2));
        assertTrue(rs.next());
        assertEquals("ab", rs.getString(1));
        assertEquals("bc", rs.getString(2));
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE k2 NOT LIKE '%'");
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE k2 NOT LIKE '%%'");
        assertFalse(rs.next());

        conn.close();
    }
    
    @Test
    public void testLikeWithEscapenLParen() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String t = generateUniqueName();
        String ddl = "CREATE TABLE " + t + " (k VARCHAR, v VARCHAR, CONSTRAINT pk PRIMARY KEY (k))";
        conn.createStatement().execute(ddl);
        conn.createStatement().execute("UPSERT INTO " + t + " VALUES('aa','bb')");
        conn.createStatement().execute("UPSERT INTO " + t + " VALUES('a\\(d','xx')");
        conn.createStatement().execute("UPSERT INTO " + t + " VALUES('dd',null)");
        conn.commit();
        
        ResultSet rs = conn.createStatement().executeQuery(
            "SELECT * FROM " + t + " WHERE k not like '%\\(%'");
        assertTrue(rs.next());
        assertEquals("aa", rs.getString(1));
        assertEquals("bb", rs.getString(2));
        assertTrue(rs.next());
        assertEquals("dd", rs.getString(1));
        assertEquals(null, rs.getString(2));
        assertFalse(rs.next());
    }

    @Test
    public void testNewLine() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String t = generateUniqueName();
        String ddl = "CREATE TABLE " + t + " (k VARCHAR NOT NULL PRIMARY KEY)";
        conn.createStatement().execute(ddl);
        conn.createStatement().execute("UPSERT INTO " + t + " VALUES('AA\nA')");
        conn.commit();

        ResultSet rs = conn.createStatement().executeQuery(
                "SELECT * FROM " + t + " WHERE k like 'AA%'");
        assertTrue(rs.next());
        assertEquals("AA\nA", rs.getString(1));

        rs = conn.createStatement().executeQuery(
                "SELECT * FROM " + t + " WHERE k like 'AA_A'");
        assertTrue(rs.next());
        assertEquals("AA\nA", rs.getString(1));

        rs = conn.createStatement().executeQuery(
                "SELECT * FROM " + t + " WHERE k like 'AA%A'");
        assertTrue(rs.next());
        assertEquals("AA\nA", rs.getString(1));

        rs = conn.createStatement().executeQuery(
                "SELECT * FROM " + t + " WHERE k like 'AA_'");
        assertFalse(rs.next());
    }

    @Test
    public void testOneChar() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String t = generateUniqueName();
        String ddl = "CREATE TABLE " + t + " (k VARCHAR NOT NULL PRIMARY KEY)";
        conn.createStatement().execute(ddl);
        conn.createStatement().execute("UPSERT INTO " + t + " VALUES('A')");
        conn.createStatement().execute("UPSERT INTO " + t + " VALUES('AA')");
        conn.commit();

        ResultSet rs = conn.createStatement().executeQuery(
                "SELECT * FROM " + t + " WHERE k like '_'");
        assertTrue(rs.next());
        assertEquals("A", rs.getString(1));
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery(
                "SELECT * FROM " + t + " WHERE k like '_A'");
        assertTrue(rs.next());
        assertEquals("AA", rs.getString(1));
        assertFalse(rs.next());
    }

    @Test
    public void testNull() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String table = generateUniqueName();
        String ddl = "CREATE TABLE " + table
                + " (pk INTEGER PRIMARY KEY, str VARCHAR)";
        conn.createStatement().execute(ddl);
        conn.createStatement().execute("UPSERT INTO " + table + " VALUES(0,'aa')");
        conn.createStatement().execute("UPSERT INTO " + table + " VALUES(1, null)");
        conn.commit();

        ResultSet rs = conn.createStatement().executeQuery(
                "SELECT str LIKE '%' FROM " + table);
        assertTrue(rs.next());
        assertEquals(true, rs.getBoolean(1));
        assertFalse(rs.wasNull());
        assertTrue(rs.next());
        assertEquals(false, rs.getBoolean(1));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery(
                "SELECT str LIKE '%%' FROM " + table);
        assertTrue(rs.next());
        assertEquals(true, rs.getBoolean(1));
        assertFalse(rs.wasNull());
        assertTrue(rs.next());
        assertEquals(false, rs.getBoolean(1));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery(
                "SELECT str NOT LIKE '%' FROM " + table);
        assertTrue(rs.next());
        assertEquals(false, rs.getBoolean(1));
        assertFalse(rs.wasNull());
        assertTrue(rs.next());
        assertEquals(false, rs.getBoolean(1));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery(
                "SELECT str NOT LIKE '%%' FROM " + table);
        assertTrue(rs.next());
        assertEquals(false, rs.getBoolean(1));
        assertFalse(rs.wasNull());
        assertTrue(rs.next());
        assertEquals(false, rs.getBoolean(1));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery(
                "SELECT NOT (str LIKE '%') FROM " + table);
        assertTrue(rs.next());
        assertEquals(false, rs.getBoolean(1));
        assertFalse(rs.wasNull());
        assertTrue(rs.next());
        assertEquals(false, rs.getBoolean(1));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery(
                "SELECT NOT(str LIKE '%%') FROM " + table);
        assertTrue(rs.next());
        assertEquals(false, rs.getBoolean(1));
        assertFalse(rs.wasNull());
        assertTrue(rs.next());
        assertEquals(false, rs.getBoolean(1));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());
    }

    @Test
    public void testLikeExpressionWithLimitOffset() throws Exception {
        String tableName = generateUniqueName();
        String ddl =
                "create table " + tableName
                        + " (id integer not null primary key, cf.col1 varchar, cf.col2 varchar, cf2.col3 varchar, cf2.col4 varchar)";
        String upsert = "UPSERT INTO " + tableName + " VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = DriverManager.getConnection(getUrl())) {
            conn.createStatement().execute(ddl);
            PreparedStatement stmt = conn.prepareStatement(upsert);
            for (int i = 1; i <= 10; i++) {
                stmt.setInt(1, i);
                stmt.setString(2, i + "col1");
                stmt.setString(3, i + "col2");
                stmt.setString(4, i + "col3");
                stmt.setString(5, i + "col4");
                stmt.executeUpdate();
            }
            conn.commit();

            String query =
                    "select cf.* from " + tableName
                            + " where cf.col1 like '%col1%' limit 10 offset 2";
            ResultSet rs = conn.createStatement().executeQuery(query);
            int expectedCount = 8;
            int i = 0;
            while (rs.next()) {
                i++;
                assertTrue(rs.getString("COL1").contains("col1"));
                assertTrue(rs.getString("COL2").contains("col2"));
            }
            assertEquals(expectedCount, i);

            query =
                    "select cf.*, cf2.* from " + tableName
                            + " where cf.col1 like '%col1%' limit 10 offset 2";
            rs = conn.createStatement().executeQuery(query);
            i = 0;
            while (rs.next()) {
                i++;
                assertTrue(rs.getString("COL1").contains("col1"));
                assertTrue(rs.getString("COL2").contains("col2"));
                assertTrue(rs.getString("COL3").contains("col3"));
                assertTrue(rs.getString("COL4").contains("col4"));
            }
            assertEquals(expectedCount, i);
            query = "select * from " + tableName + " where cf.col1 like '%col1%' limit 10 offset 2";
            rs = conn.createStatement().executeQuery(query);
            i = 0;
            while (rs.next()) {
                i++;
                assertTrue(rs.getString("COL1").contains("col1"));
                assertTrue(rs.getString("COL2").contains("col2"));
                assertTrue(rs.getString("COL3").contains("col3"));
                assertTrue(rs.getString("COL4").contains("col4"));
            }
            assertEquals(expectedCount, i);
        }
    }
}