Examples with FunctionNotFoundException org.apache.phoenix.schema.FunctionNotFoundException used on opensource projects

Example 6 with FunctionNotFoundException

use of org.apache.phoenix.schema.FunctionNotFoundException in project phoenix by apache.

the class UserDefinedFunctionsIT method testCreateFunction.

@Test
public void testCreateFunction() throws Exception {
    Connection conn = driver.connect(url, EMPTY_PROPS);
    Statement stmt = conn.createStatement();
    conn.createStatement().execute("create table t(k integer primary key, firstname varchar, lastname varchar)");
    stmt.execute("upsert into t values(1,'foo','jock')");
    conn.commit();
    stmt.execute("create function myreverse(VARCHAR) returns VARCHAR as 'org.apache.phoenix.end2end." + MY_REVERSE_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar1.jar" + "'");
    ResultSet rs = stmt.executeQuery("select myreverse(firstname) from t");
    assertTrue(rs.next());
    assertEquals("oof", rs.getString(1));
    assertFalse(rs.next());
    rs = stmt.executeQuery("select * from t where myreverse(firstname)='oof'");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertEquals("foo", rs.getString(2));
    assertEquals("jock", rs.getString(3));
    assertFalse(rs.next());
    try {
        stmt.execute("create function myreverse(VARCHAR) returns VARCHAR as 'org.apache.phoenix.end2end." + MY_REVERSE_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar1.jar" + "'");
        fail("Duplicate function should not be created.");
    } catch (FunctionAlreadyExistsException e) {
    }
    // without specifying the jar should pick the class from path of hbase.dynamic.jars.dir configuration. 
    stmt.execute("create function myreverse2(VARCHAR) returns VARCHAR as 'org.apache.phoenix.end2end." + MY_REVERSE_CLASS_NAME + "'");
    rs = stmt.executeQuery("select myreverse2(firstname) from t");
    assertTrue(rs.next());
    assertEquals("oof", rs.getString(1));
    assertFalse(rs.next());
    rs = stmt.executeQuery("select * from t where myreverse2(firstname)='oof'");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertEquals("foo", rs.getString(2));
    assertEquals("jock", rs.getString(3));
    assertFalse(rs.next());
    conn.createStatement().execute("create table t3(tenant_id varchar not null, k integer not null, firstname varchar, lastname varchar constraint pk primary key(tenant_id,k)) MULTI_TENANT=true");
    // Function created with global id should be accessible.
    Connection conn2 = driver.connect(url + ";" + PhoenixRuntime.TENANT_ID_ATTRIB + "=" + TENANT_ID, EMPTY_PROPS);
    try {
        conn2.createStatement().execute("upsert into t3 values(1,'foo','jock')");
        conn2.commit();
        conn2.createStatement().execute("create function myreverse(VARCHAR) returns VARCHAR as 'org.apache.phoenix.end2end." + MY_REVERSE_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar1.jar" + "'");
        rs = conn2.createStatement().executeQuery("select myreverse(firstname) from t3");
        assertTrue(rs.next());
        assertEquals("oof", rs.getString(1));
    } catch (FunctionAlreadyExistsException e) {
        fail("FunctionAlreadyExistsException should not be thrown");
    }
    // calling global udf on tenant specific specific connection.
    rs = conn2.createStatement().executeQuery("select myreverse2(firstname) from t3");
    assertTrue(rs.next());
    assertEquals("oof", rs.getString(1));
    try {
        conn2.createStatement().execute("drop function myreverse2");
        fail("FunctionNotFoundException should be thrown");
    } catch (FunctionNotFoundException e) {
    }
    conn.createStatement().execute("drop function myreverse2");
    try {
        rs = conn2.createStatement().executeQuery("select myreverse2(firstname) from t3");
        fail("FunctionNotFoundException should be thrown.");
    } catch (FunctionNotFoundException e) {
    }
    try {
        rs = conn2.createStatement().executeQuery("select unknownFunction(firstname) from t3");
        fail("FunctionNotFoundException should be thrown.");
    } catch (FunctionNotFoundException e) {
    }
    conn.createStatement().execute("CREATE TABLE TESTTABLE10(ID VARCHAR NOT NULL, NAME VARCHAR ARRAY, CITY VARCHAR ARRAY CONSTRAINT pk PRIMARY KEY (ID) )");
    conn.createStatement().execute("create function UDF_ARRAY_ELEM(VARCHAR ARRAY, INTEGER) returns VARCHAR as 'org.apache.phoenix.end2end." + MY_ARRAY_INDEX_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar3.jar" + "'");
    conn.createStatement().execute("UPSERT INTO TESTTABLE10(ID,NAME,CITY) VALUES('111', ARRAY['JOHN','MIKE','BOB'], ARRAY['NYC','LA','SF'])");
    conn.createStatement().execute("UPSERT INTO TESTTABLE10(ID,NAME,CITY) VALUES('112', ARRAY['CHEN','CARL','ALICE'], ARRAY['BOSTON','WASHINGTON','PALO ALTO'])");
    conn.commit();
    rs = conn.createStatement().executeQuery("SELECT ID, UDF_ARRAY_ELEM(NAME, 2) FROM TESTTABLE10");
    assertTrue(rs.next());
    assertEquals("111", rs.getString(1));
    assertEquals("MIKE", rs.getString(2));
    assertTrue(rs.next());
    assertEquals("112", rs.getString(1));
    assertEquals("CARL", rs.getString(2));
    assertFalse(rs.next());
    rs = conn2.createStatement().executeQuery("SELECT ID, UDF_ARRAY_ELEM(NAME, 2) FROM TESTTABLE10");
    assertTrue(rs.next());
    assertEquals("111", rs.getString(1));
    assertEquals("MIKE", rs.getString(2));
    assertTrue(rs.next());
    assertEquals("112", rs.getString(1));
    assertEquals("CARL", rs.getString(2));
    assertFalse(rs.next());
}

Example 7 with FunctionNotFoundException

use of org.apache.phoenix.schema.FunctionNotFoundException in project phoenix by apache.

the class UserDefinedFunctionsIT method testTemporaryFunctions.

@Test
public void testTemporaryFunctions() throws Exception {
    Connection conn = driver.connect(url, EMPTY_PROPS);
    Statement stmt = conn.createStatement();
    conn.createStatement().execute("create table t9(k integer primary key, k1 integer, lastname varchar)");
    stmt.execute("upsert into t9 values(1,1,'jock')");
    conn.commit();
    stmt.execute("create temporary function mysum9(INTEGER, INTEGER CONSTANT defaultValue=10 minvalue=1 maxvalue=15 ) returns INTEGER as 'org.apache.phoenix.end2end." + MY_SUM_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar2.jar" + "'");
    ResultSet rs = stmt.executeQuery("select mysum9(k,12) from t9");
    assertTrue(rs.next());
    assertEquals(13, rs.getInt(1));
    rs = stmt.executeQuery("select mysum9(k) from t9");
    assertTrue(rs.next());
    assertEquals(11, rs.getInt(1));
    rs = stmt.executeQuery("select k from t9 where mysum9(k)=11");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    try {
        rs = stmt.executeQuery("select k from t9 where mysum9(k,10,'x')=11");
        fail("FunctionNotFoundException should be thrown");
    } catch (FunctionNotFoundException e) {
    } catch (Exception e) {
        fail("FunctionNotFoundException should be thrown");
    }
    try {
        rs = stmt.executeQuery("select mysum9() from t9");
        fail("FunctionNotFoundException should be thrown");
    } catch (FunctionNotFoundException e) {
    } catch (Exception e) {
        fail("FunctionNotFoundException should be thrown");
    }
    stmt.execute("drop function mysum9");
    try {
        rs = stmt.executeQuery("select k from t9 where mysum9(k)=11");
        fail("FunctionNotFoundException should be thrown");
    } catch (FunctionNotFoundException e) {
    }
}

Example 8 with FunctionNotFoundException

use of org.apache.phoenix.schema.FunctionNotFoundException in project phoenix by apache.

the class UserDefinedFunctionsIT method testDropFunction.

@Test
public void testDropFunction() throws Exception {
    Connection conn = driver.connect(url, EMPTY_PROPS);
    Statement stmt = conn.createStatement();
    String query = "select count(*) from " + SYSTEM_CATALOG_SCHEMA + ".\"" + SYSTEM_FUNCTION_TABLE + "\"";
    ResultSet rs = stmt.executeQuery(query);
    rs.next();
    int numRowsBefore = rs.getInt(1);
    stmt.execute("create function mysum6(INTEGER, INTEGER CONSTANT defaultValue=10 minvalue=1 maxvalue=15 ) returns INTEGER as 'org.apache.phoenix.end2end." + MY_SUM_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar2.jar" + "'");
    rs = stmt.executeQuery(query);
    rs.next();
    int numRowsAfter = rs.getInt(1);
    assertEquals(3, numRowsAfter - numRowsBefore);
    stmt.execute("drop function mysum6");
    rs = stmt.executeQuery(query);
    rs.next();
    assertEquals(numRowsBefore, rs.getInt(1));
    conn.createStatement().execute("create table t6(k integer primary key, k1 integer, lastname varchar)");
    try {
        rs = stmt.executeQuery("select mysum6(k1) from t6");
        fail("FunctionNotFoundException should be thrown");
    } catch (FunctionNotFoundException e) {
    }
    try {
        stmt.execute("drop function mysum6");
        fail("FunctionNotFoundException should be thrown");
    } catch (FunctionNotFoundException e) {
    }
    try {
        stmt.execute("drop function if exists mysum6");
    } catch (FunctionNotFoundException e) {
        fail("FunctionNotFoundException should not be thrown");
    }
    stmt.execute("create function mysum6(INTEGER, INTEGER CONSTANT defaultValue=10 minvalue=1 maxvalue=15 ) returns INTEGER as 'org.apache.phoenix.end2end." + MY_SUM_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar2.jar" + "'");
    try {
        rs = stmt.executeQuery("select mysum6(k1) from t6");
    } catch (FunctionNotFoundException e) {
        fail("FunctionNotFoundException should not be thrown");
    }
}

Example 9 with FunctionNotFoundException

use of org.apache.phoenix.schema.FunctionNotFoundException in project phoenix by apache.

the class UserDefinedFunctionsIT method testUDFsWhenTimestampManagedAtClient.

@Test
public void testUDFsWhenTimestampManagedAtClient() throws Exception {
    long ts = 100;
    Properties props = new Properties();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
    Connection conn = DriverManager.getConnection(url, props);
    Statement stmt = conn.createStatement();
    String query = "select count(*) from " + SYSTEM_CATALOG_SCHEMA + ".\"" + SYSTEM_FUNCTION_TABLE + "\"";
    ResultSet rs = stmt.executeQuery(query);
    rs.next();
    int numRowsBefore = rs.getInt(1);
    stmt.execute("create function mysum61(INTEGER, INTEGER CONSTANT defaultValue=10 minvalue=1 maxvalue=15 ) returns INTEGER as 'org.apache.phoenix.end2end." + MY_SUM_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar2.jar" + "'");
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
    conn = DriverManager.getConnection(url, props);
    stmt = conn.createStatement();
    rs = stmt.executeQuery(query);
    rs.next();
    int numRowsAfter = rs.getInt(1);
    assertEquals(3, numRowsAfter - numRowsBefore);
    stmt.execute("drop function mysum61");
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
    conn = DriverManager.getConnection(url, props);
    stmt = conn.createStatement();
    rs = stmt.executeQuery(query);
    rs.next();
    assertEquals(numRowsBefore, rs.getInt(1));
    conn.createStatement().execute("create table t62(k integer primary key, k1 integer, lastname varchar)");
    try {
        rs = stmt.executeQuery("select mysum61(k1) from t62");
        fail("FunctionNotFoundException should be thrown");
    } catch (FunctionNotFoundException e) {
    }
    try {
        stmt.execute("drop function mysum61");
        fail("FunctionNotFoundException should be thrown");
    } catch (FunctionNotFoundException e) {
    }
    try {
        stmt.execute("drop function if exists mysum61");
    } catch (FunctionNotFoundException e) {
        fail("FunctionNotFoundException should not be thrown");
    }
    stmt.execute("create function mysum61(INTEGER, INTEGER CONSTANT defaultValue=10 minvalue=1 maxvalue=15 ) returns INTEGER as 'org.apache.phoenix.end2end." + MY_SUM_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar2.jar" + "'");
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
    conn = DriverManager.getConnection(url, props);
    stmt = conn.createStatement();
    try {
        rs = stmt.executeQuery("select mysum61(k1) from t62");
    } catch (FunctionNotFoundException e) {
        fail("FunctionNotFoundException should not be thrown");
    }
    conn.createStatement().execute("create table t61(k integer primary key, k1 integer, lastname varchar)");
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
    conn = DriverManager.getConnection(url, props);
    stmt = conn.createStatement();
    stmt.execute("upsert into t61 values(1,1,'jock')");
    conn.commit();
    stmt.execute("create function myfunction6(VARCHAR) returns VARCHAR as 'org.apache.phoenix.end2end." + MY_REVERSE_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar1.jar" + "'");
    stmt.execute("create or replace function myfunction6(INTEGER, INTEGER CONSTANT defaultValue=10 minvalue=1 maxvalue=15 ) returns INTEGER as 'org.apache.phoenix.end2end." + MY_SUM_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar2.jar" + "'");
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 50));
    conn = DriverManager.getConnection(url, props);
    stmt = conn.createStatement();
    rs = stmt.executeQuery("select myfunction6(k,12) from t61");
    assertTrue(rs.next());
    assertEquals(13, rs.getInt(1));
    rs = stmt.executeQuery("select myfunction6(k) from t61");
    assertTrue(rs.next());
    assertEquals(11, rs.getInt(1));
    rs = stmt.executeQuery("select k from t61 where myfunction6(k)=11");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    stmt.execute("create or replace function myfunction6(VARCHAR) returns VARCHAR as 'org.apache.phoenix.end2end." + MY_REVERSE_CLASS_NAME + "' using jar " + "'" + util.getConfiguration().get(DYNAMIC_JARS_DIR_KEY) + "/myjar1.jar" + "'");
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 60));
    conn = DriverManager.getConnection(url, props);
    stmt = conn.createStatement();
    rs = stmt.executeQuery("select k from t61 where myfunction6(lastname)='kcoj'");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 60));
    props.setProperty(QueryServices.ALLOW_USER_DEFINED_FUNCTIONS_ATTRIB, "false");
    conn = DriverManager.getConnection(url, props);
    stmt = conn.createStatement();
    try {
        rs = stmt.executeQuery("select k from t61 where reverse(lastname,11)='kcoj'");
        fail("FunctionNotFoundException should be thrown.");
    } catch (FunctionNotFoundException e) {
    }
}