use of org.apache.calcite.sql.SqlJdbcFunctionCall in project ignite by apache.
the class IgniteSqlParserImpl method JdbcFunctionCall.
/**
* Parses a function call expression with JDBC syntax.
*/
public final SqlNode JdbcFunctionCall() throws ParseException {
String name;
SqlIdentifier id;
SqlNode e;
SqlLiteral tl;
SqlNodeList args;
SqlCall call;
final Span s, s1;
jj_consume_token(LBRACE_FN);
s = span();
switch((jj_ntk == -1) ? jj_ntk() : jj_ntk) {
case TIMESTAMPADD:
call = TimestampAddFunctionCall();
name = call.getOperator().getName();
args = new SqlNodeList(call.getOperandList(), getPos());
break;
default:
jj_la1[9] = jj_gen;
if (jj_2_923(3)) {
call = TimestampDiffFunctionCall();
name = call.getOperator().getName();
args = new SqlNodeList(call.getOperandList(), getPos());
} else if (jj_2_924(2)) {
jj_consume_token(CONVERT);
name = unquotedIdentifier();
jj_consume_token(LPAREN);
e = Expression(ExprContext.ACCEPT_SUB_QUERY);
args = new SqlNodeList(getPos());
args.add(e);
jj_consume_token(COMMA);
tl = JdbcOdbcDataType();
args.add(tl);
jj_consume_token(RPAREN);
} else if (jj_2_925(2)) {
switch((jj_ntk == -1) ? jj_ntk() : jj_ntk) {
case INSERT:
case LEFT:
case RIGHT:
case TRUNCATE:
if (jj_2_913(2)) {
jj_consume_token(INSERT);
} else if (jj_2_914(2)) {
jj_consume_token(LEFT);
} else if (jj_2_915(2)) {
jj_consume_token(RIGHT);
} else if (jj_2_916(2)) {
jj_consume_token(TRUNCATE);
} else {
jj_consume_token(-1);
throw new ParseException();
}
name = unquotedIdentifier();
break;
default:
jj_la1[8] = jj_gen;
if (jj_2_917(2)) {
// For cases like {fn power(1,2)} and {fn lower('a')}
id = ReservedFunctionName();
name = id.getSimple();
} else if (jj_2_918(2)) {
// For cases like {fn substring('foo', 1,2)}
name = NonReservedJdbcFunctionName();
} else if (jj_2_919(2)) {
name = Identifier();
} else {
jj_consume_token(-1);
throw new ParseException();
}
}
if (jj_2_920(2)) {
jj_consume_token(LPAREN);
jj_consume_token(STAR);
s1 = span();
jj_consume_token(RPAREN);
args = new SqlNodeList(s1.pos());
args.add(SqlIdentifier.star(s1.pos()));
} else if (jj_2_921(2)) {
jj_consume_token(LPAREN);
jj_consume_token(RPAREN);
args = SqlNodeList.EMPTY;
} else if (jj_2_922(2)) {
args = ParenthesizedQueryOrCommaList(ExprContext.ACCEPT_SUB_QUERY);
} else {
jj_consume_token(-1);
throw new ParseException();
}
} else {
jj_consume_token(-1);
throw new ParseException();
}
}
jj_consume_token(RBRACE);
{
if (true)
return new SqlJdbcFunctionCall(name).createCall(s.end(this), args.getList());
}
throw new Error("Missing return statement in function");
}
use of org.apache.calcite.sql.SqlJdbcFunctionCall in project calcite by apache.
the class SqlOperatorBaseTest method testJdbcFn.
/**
* Tests support for JDBC functions.
*
* <p>See FRG-97 "Support for JDBC escape syntax is incomplete".
*/
@Test
public void testJdbcFn() {
tester.setFor(new SqlJdbcFunctionCall("dummy"));
// There follows one test for each function in appendix C of the JDBC
// 3.0 specification. The test is 'if-false'd out if the function is
// not implemented or is broken.
// Numeric Functions
tester.checkScalar("{fn ABS(-3)}", 3, "INTEGER NOT NULL");
tester.checkScalarApprox("{fn ACOS(0.2)}", "DOUBLE NOT NULL", 1.36943, 0.001);
tester.checkScalarApprox("{fn ASIN(0.2)}", "DOUBLE NOT NULL", 0.20135, 0.001);
tester.checkScalarApprox("{fn ATAN(0.2)}", "DOUBLE NOT NULL", 0.19739, 0.001);
tester.checkScalarApprox("{fn ATAN2(-2, 2)}", "DOUBLE NOT NULL", -0.78539, 0.001);
tester.checkScalar("{fn CEILING(-2.6)}", -2, "DECIMAL(2, 0) NOT NULL");
tester.checkScalarApprox("{fn COS(0.2)}", "DOUBLE NOT NULL", 0.98007, 0.001);
tester.checkScalarApprox("{fn COT(0.2)}", "DOUBLE NOT NULL", 4.93315, 0.001);
tester.checkScalarApprox("{fn DEGREES(-1)}", "DOUBLE NOT NULL", -57.29578, 0.001);
tester.checkScalarApprox("{fn EXP(2)}", "DOUBLE NOT NULL", 7.389, 0.001);
tester.checkScalar("{fn FLOOR(2.6)}", 2, "DECIMAL(2, 0) NOT NULL");
tester.checkScalarApprox("{fn LOG(10)}", "DOUBLE NOT NULL", 2.30258, 0.001);
tester.checkScalarApprox("{fn LOG10(100)}", "DOUBLE NOT NULL", 2, 0);
tester.checkScalar("{fn MOD(19, 4)}", 3, "INTEGER NOT NULL");
tester.checkScalarApprox("{fn PI()}", "DOUBLE NOT NULL", 3.14159, 0.0001);
tester.checkScalarApprox("{fn POWER(2, 3)}", "DOUBLE NOT NULL", 8.0, 0.001);
tester.checkScalarApprox("{fn RADIANS(90)}", "DOUBLE NOT NULL", 1.57080, 0.001);
tester.checkScalarApprox("{fn RAND(42)}", "DOUBLE NOT NULL", 0.63708, 0.001);
tester.checkScalar("{fn ROUND(1251, -2)}", 1300, "INTEGER NOT NULL");
tester.checkFails("^{fn ROUND(1251)}^", "Cannot apply '\\{fn ROUND\\}' to " + "arguments of type '\\{fn ROUND\\}\\(<INTEGER>\\)'.*", false);
tester.checkScalar("{fn SIGN(-1)}", -1, "INTEGER NOT NULL");
tester.checkScalarApprox("{fn SIN(0.2)}", "DOUBLE NOT NULL", 0.19867, 0.001);
tester.checkScalarApprox("{fn SQRT(4.2)}", "DOUBLE NOT NULL", 2.04939, 0.001);
tester.checkScalarApprox("{fn TAN(0.2)}", "DOUBLE NOT NULL", 0.20271, 0.001);
tester.checkScalar("{fn TRUNCATE(12.34, 1)}", 12.3, "DECIMAL(4, 2) NOT NULL");
tester.checkScalar("{fn TRUNCATE(-12.34, -1)}", -10, "DECIMAL(4, 2) NOT NULL");
// String Functions
if (false) {
tester.checkScalar("{fn ASCII(string)}", null, "");
}
if (false) {
tester.checkScalar("{fn CHAR(code)}", null, "");
}
tester.checkScalar("{fn CONCAT('foo', 'bar')}", "foobar", "CHAR(6) NOT NULL");
if (false) {
tester.checkScalar("{fn DIFFERENCE(string1, string2)}", null, "");
}
// REVIEW: is this result correct? I think it should be "abcCdef"
tester.checkScalar("{fn INSERT('abc', 1, 2, 'ABCdef')}", "ABCdefc", "VARCHAR(9) NOT NULL");
tester.checkScalar("{fn LCASE('foo' || 'bar')}", "foobar", "CHAR(6) NOT NULL");
if (false) {
tester.checkScalar("{fn LEFT(string, count)}", null, "");
}
if (false) {
tester.checkScalar("{fn LENGTH(string)}", null, "");
}
tester.checkScalar("{fn LOCATE('ha', 'alphabet')}", 4, "INTEGER NOT NULL");
tester.checkScalar("{fn LOCATE('ha', 'alphabet', 6)}", 0, "INTEGER NOT NULL");
tester.checkScalar("{fn LTRIM(' xxx ')}", "xxx ", "VARCHAR(6) NOT NULL");
if (false) {
tester.checkScalar("{fn REPEAT(string, count)}", null, "");
}
tester.checkString("{fn REPLACE('JACK and JUE','J','BL')}", "BLACK and BLUE", "VARCHAR(12) NOT NULL");
// REPLACE returns NULL in Oracle but not in Postgres or in Calcite.
// When [CALCITE-815] is implemented and SqlConformance#emptyStringIsNull is
// enabled, it will return empty string as NULL.
tester.checkString("{fn REPLACE('ciao', 'ciao', '')}", "", "VARCHAR(4) NOT NULL");
tester.checkString("{fn REPLACE('hello world', 'o', '')}", "hell wrld", "VARCHAR(11) NOT NULL");
tester.checkNull("{fn REPLACE(cast(null as varchar(5)), 'ciao', '')}");
tester.checkNull("{fn REPLACE('ciao', cast(null as varchar(3)), 'zz')}");
tester.checkNull("{fn REPLACE('ciao', 'bella', cast(null as varchar(3)))}");
if (false) {
tester.checkScalar("{fn RIGHT(string, count)}", null, "");
}
tester.checkScalar("{fn RTRIM(' xxx ')}", " xxx", "VARCHAR(6) NOT NULL");
if (false) {
tester.checkScalar("{fn SOUNDEX(string)}", null, "");
}
if (false) {
tester.checkScalar("{fn SPACE(count)}", null, "");
}
tester.checkScalar("{fn SUBSTRING('abcdef', 2, 3)}", "bcd", "VARCHAR(6) NOT NULL");
tester.checkScalar("{fn UCASE('xxx')}", "XXX", "CHAR(3) NOT NULL");
// Time and Date Functions
tester.checkType("{fn CURDATE()}", "DATE NOT NULL");
tester.checkType("{fn CURTIME()}", "TIME(0) NOT NULL");
if (false) {
tester.checkScalar("{fn DAYNAME(date)}", null, "");
}
tester.checkScalar("{fn DAYOFMONTH(DATE '2014-12-10')}", 10, "BIGINT NOT NULL");
tester.checkFails("{fn DAYOFWEEK(DATE '2014-12-10')}", "cannot translate call EXTRACT.*", true);
tester.checkFails("{fn DAYOFYEAR(DATE '2014-12-10')}", "cannot translate call EXTRACT.*", true);
tester.checkScalar("{fn HOUR(TIMESTAMP '2014-12-10 12:34:56')}", 12, "BIGINT NOT NULL");
tester.checkScalar("{fn MINUTE(TIMESTAMP '2014-12-10 12:34:56')}", 34, "BIGINT NOT NULL");
tester.checkScalar("{fn MONTH(DATE '2014-12-10')}", 12, "BIGINT NOT NULL");
if (false) {
tester.checkScalar("{fn MONTHNAME(date)}", null, "");
}
tester.checkType("{fn NOW()}", "TIMESTAMP(0) NOT NULL");
tester.checkScalar("{fn QUARTER(DATE '2014-12-10')}", "4", "BIGINT NOT NULL");
tester.checkScalar("{fn SECOND(TIMESTAMP '2014-12-10 12:34:56')}", 56, "BIGINT NOT NULL");
tester.checkScalar("{fn TIMESTAMPADD(HOUR, 5," + " TIMESTAMP '2014-03-29 12:34:56')}", "2014-03-29 17:34:56", "TIMESTAMP(0) NOT NULL");
tester.checkScalar("{fn TIMESTAMPDIFF(HOUR," + " TIMESTAMP '2014-03-29 12:34:56'," + " TIMESTAMP '2014-03-29 12:34:56')}", "0", "INTEGER NOT NULL");
tester.checkFails("{fn WEEK(DATE '2014-12-10')}", "cannot translate call EXTRACT.*", true);
tester.checkScalar("{fn YEAR(DATE '2014-12-10')}", 2014, "BIGINT NOT NULL");
// System Functions
tester.checkType("{fn DATABASE()}", "VARCHAR(2000) NOT NULL");
tester.checkString("{fn IFNULL('a', 'b')}", "a", "CHAR(1) NOT NULL");
tester.checkString("{fn USER()}", "sa", "VARCHAR(2000) NOT NULL");
// Conversion Functions
// Legacy JDBC style
tester.checkScalar("{fn CONVERT('123', INTEGER)}", 123, "INTEGER NOT NULL");
// ODBC/JDBC style
tester.checkScalar("{fn CONVERT('123', SQL_INTEGER)}", 123, "INTEGER NOT NULL");
tester.checkScalar("{fn CONVERT(INTERVAL '1' DAY, SQL_INTERVAL_DAY_TO_SECOND)}", "+1 00:00:00.000000", "INTERVAL DAY TO SECOND NOT NULL");
}
use of org.apache.calcite.sql.SqlJdbcFunctionCall in project calcite by apache.
the class SqlOperatorTest method testJdbcFn.
/**
* Tests support for JDBC functions.
*
* <p>See FRG-97 "Support for JDBC escape syntax is incomplete".
*/
@Test
void testJdbcFn() {
final SqlOperatorFixture f = fixture();
f.setFor(new SqlJdbcFunctionCall("dummy"), VmName.EXPAND);
// There follows one test for each function in appendix C of the JDBC
// 3.0 specification. The test is 'if-false'd out if the function is
// not implemented or is broken.
// Numeric Functions
f.checkScalar("{fn ABS(-3)}", 3, "INTEGER NOT NULL");
f.checkScalarApprox("{fn ACOS(0.2)}", "DOUBLE NOT NULL", isWithin(1.36943, 0.001));
f.checkScalarApprox("{fn ASIN(0.2)}", "DOUBLE NOT NULL", isWithin(0.20135, 0.001));
f.checkScalarApprox("{fn ATAN(0.2)}", "DOUBLE NOT NULL", isWithin(0.19739, 0.001));
f.checkScalarApprox("{fn ATAN2(-2, 2)}", "DOUBLE NOT NULL", isWithin(-0.78539, 0.001));
f.checkScalar("{fn CBRT(8)}", 2.0, "DOUBLE NOT NULL");
f.checkScalar("{fn CEILING(-2.6)}", -2, "DECIMAL(2, 0) NOT NULL");
f.checkScalarApprox("{fn COS(0.2)}", "DOUBLE NOT NULL", isWithin(0.98007, 0.001));
f.checkScalarApprox("{fn COT(0.2)}", "DOUBLE NOT NULL", isWithin(4.93315, 0.001));
f.checkScalarApprox("{fn DEGREES(-1)}", "DOUBLE NOT NULL", isWithin(-57.29578, 0.001));
f.checkScalarApprox("{fn EXP(2)}", "DOUBLE NOT NULL", isWithin(7.389, 0.001));
f.checkScalar("{fn FLOOR(2.6)}", 2, "DECIMAL(2, 0) NOT NULL");
f.checkScalarApprox("{fn LOG(10)}", "DOUBLE NOT NULL", isWithin(2.30258, 0.001));
f.checkScalarApprox("{fn LOG10(100)}", "DOUBLE NOT NULL", isExactly(2));
f.checkScalar("{fn MOD(19, 4)}", 3, "INTEGER NOT NULL");
f.checkScalarApprox("{fn PI()}", "DOUBLE NOT NULL", isWithin(3.14159, 0.0001));
f.checkScalarApprox("{fn POWER(2, 3)}", "DOUBLE NOT NULL", isWithin(8.0, 0.001));
f.checkScalarApprox("{fn RADIANS(90)}", "DOUBLE NOT NULL", isWithin(1.57080, 0.001));
f.checkScalarApprox("{fn RAND(42)}", "DOUBLE NOT NULL", isWithin(0.63708, 0.001));
f.checkScalar("{fn ROUND(1251, -2)}", 1300, "INTEGER NOT NULL");
f.checkFails("^{fn ROUND(1251)}^", "Cannot apply '\\{fn ROUND\\}' to " + "arguments of type '\\{fn ROUND\\}\\(<INTEGER>\\)'.*", false);
f.checkScalar("{fn SIGN(-1)}", -1, "INTEGER NOT NULL");
f.checkScalarApprox("{fn SIN(0.2)}", "DOUBLE NOT NULL", isWithin(0.19867, 0.001));
f.checkScalarApprox("{fn SQRT(4.2)}", "DOUBLE NOT NULL", isWithin(2.04939, 0.001));
f.checkScalarApprox("{fn TAN(0.2)}", "DOUBLE NOT NULL", isWithin(0.20271, 0.001));
f.checkScalar("{fn TRUNCATE(12.34, 1)}", 12.3, "DECIMAL(4, 2) NOT NULL");
f.checkScalar("{fn TRUNCATE(-12.34, -1)}", -10, "DECIMAL(4, 2) NOT NULL");
// String Functions
f.checkScalar("{fn ASCII('a')}", 97, "INTEGER NOT NULL");
f.checkScalar("{fn ASCII('ABC')}", "65", "INTEGER NOT NULL");
f.checkNull("{fn ASCII(cast(null as varchar(1)))}");
if (false) {
f.checkScalar("{fn CHAR(code)}", null, "");
}
f.checkScalar("{fn CONCAT('foo', 'bar')}", "foobar", "CHAR(6) NOT NULL");
f.checkScalar("{fn DIFFERENCE('Miller', 'miller')}", "4", "INTEGER NOT NULL");
f.checkNull("{fn DIFFERENCE('muller', cast(null as varchar(1)))}");
f.checkString("{fn REVERSE('abc')}", "cba", "VARCHAR(3) NOT NULL");
f.checkNull("{fn REVERSE(cast(null as varchar(1)))}");
f.checkString("{fn LEFT('abcd', 3)}", "abc", "VARCHAR(4) NOT NULL");
f.checkString("{fn LEFT('abcd', 4)}", "abcd", "VARCHAR(4) NOT NULL");
f.checkString("{fn LEFT('abcd', 5)}", "abcd", "VARCHAR(4) NOT NULL");
f.checkNull("{fn LEFT(cast(null as varchar(1)), 3)}");
f.checkString("{fn RIGHT('abcd', 3)}", "bcd", "VARCHAR(4) NOT NULL");
f.checkString("{fn RIGHT('abcd', 4)}", "abcd", "VARCHAR(4) NOT NULL");
f.checkString("{fn RIGHT('abcd', 5)}", "abcd", "VARCHAR(4) NOT NULL");
f.checkNull("{fn RIGHT(cast(null as varchar(1)), 3)}");
// REVIEW: is this result correct? I think it should be "abcCdef"
f.checkScalar("{fn INSERT('abc', 1, 2, 'ABCdef')}", "ABCdefc", "VARCHAR(9) NOT NULL");
f.checkScalar("{fn LCASE('foo' || 'bar')}", "foobar", "CHAR(6) NOT NULL");
if (false) {
f.checkScalar("{fn LENGTH(string)}", null, "");
}
f.checkScalar("{fn LOCATE('ha', 'alphabet')}", 4, "INTEGER NOT NULL");
f.checkScalar("{fn LOCATE('ha', 'alphabet', 6)}", 0, "INTEGER NOT NULL");
f.checkScalar("{fn LTRIM(' xxx ')}", "xxx ", "VARCHAR(6) NOT NULL");
f.checkScalar("{fn REPEAT('a', -100)}", "", "VARCHAR(1) NOT NULL");
f.checkNull("{fn REPEAT('abc', cast(null as integer))}");
f.checkNull("{fn REPEAT(cast(null as varchar(1)), cast(null as integer))}");
f.checkString("{fn REPLACE('JACK and JUE','J','BL')}", "BLACK and BLUE", "VARCHAR(12) NOT NULL");
// REPLACE returns NULL in Oracle but not in Postgres or in Calcite.
// When [CALCITE-815] is implemented and SqlConformance#emptyStringIsNull is
// enabled, it will return empty string as NULL.
f.checkString("{fn REPLACE('ciao', 'ciao', '')}", "", "VARCHAR(4) NOT NULL");
f.checkString("{fn REPLACE('hello world', 'o', '')}", "hell wrld", "VARCHAR(11) NOT NULL");
f.checkNull("{fn REPLACE(cast(null as varchar(5)), 'ciao', '')}");
f.checkNull("{fn REPLACE('ciao', cast(null as varchar(3)), 'zz')}");
f.checkNull("{fn REPLACE('ciao', 'bella', cast(null as varchar(3)))}");
f.checkScalar("{fn RTRIM(' xxx ')}", " xxx", "VARCHAR(6) NOT NULL");
f.checkScalar("{fn SOUNDEX('Miller')}", "M460", "VARCHAR(4) NOT NULL");
f.checkNull("{fn SOUNDEX(cast(null as varchar(1)))}");
f.checkScalar("{fn SPACE(-100)}", "", "VARCHAR(2000) NOT NULL");
f.checkNull("{fn SPACE(cast(null as integer))}");
f.checkScalar("{fn SUBSTRING('abcdef', 2, 3)}", "bcd", "VARCHAR(6) NOT NULL");
f.checkScalar("{fn UCASE('xxx')}", "XXX", "CHAR(3) NOT NULL");
// Time and Date Functions
f.checkType("{fn CURDATE()}", "DATE NOT NULL");
f.checkType("{fn CURTIME()}", "TIME(0) NOT NULL");
f.checkScalar("{fn DAYNAME(DATE '2014-12-10')}", // Day names in root locale changed from long to short in JDK 9
TestUtil.getJavaMajorVersion() <= 8 ? "Wednesday" : "Wed", "VARCHAR(2000) NOT NULL");
f.checkScalar("{fn DAYOFMONTH(DATE '2014-12-10')}", 10, "BIGINT NOT NULL");
if (Bug.CALCITE_2539_FIXED) {
f.checkFails("{fn DAYOFWEEK(DATE '2014-12-10')}", "cannot translate call EXTRACT.*", true);
f.checkFails("{fn DAYOFYEAR(DATE '2014-12-10')}", "cannot translate call EXTRACT.*", true);
}
f.checkScalar("{fn HOUR(TIMESTAMP '2014-12-10 12:34:56')}", 12, "BIGINT NOT NULL");
f.checkScalar("{fn MINUTE(TIMESTAMP '2014-12-10 12:34:56')}", 34, "BIGINT NOT NULL");
f.checkScalar("{fn MONTH(DATE '2014-12-10')}", 12, "BIGINT NOT NULL");
f.checkScalar("{fn MONTHNAME(DATE '2014-12-10')}", // Month names in root locale changed from long to short in JDK 9
TestUtil.getJavaMajorVersion() <= 8 ? "December" : "Dec", "VARCHAR(2000) NOT NULL");
f.checkType("{fn NOW()}", "TIMESTAMP(0) NOT NULL");
f.checkScalar("{fn QUARTER(DATE '2014-12-10')}", "4", "BIGINT NOT NULL");
f.checkScalar("{fn SECOND(TIMESTAMP '2014-12-10 12:34:56')}", 56, "BIGINT NOT NULL");
f.checkScalar("{fn TIMESTAMPADD(HOUR, 5," + " TIMESTAMP '2014-03-29 12:34:56')}", "2014-03-29 17:34:56", "TIMESTAMP(0) NOT NULL");
f.checkScalar("{fn TIMESTAMPDIFF(HOUR," + " TIMESTAMP '2014-03-29 12:34:56'," + " TIMESTAMP '2014-03-29 12:34:56')}", "0", "INTEGER NOT NULL");
f.checkScalar("{fn TIMESTAMPDIFF(MONTH," + " TIMESTAMP '2019-09-01 00:00:00'," + " TIMESTAMP '2020-03-01 00:00:00')}", "6", "INTEGER NOT NULL");
if (Bug.CALCITE_2539_FIXED) {
f.checkFails("{fn WEEK(DATE '2014-12-10')}", "cannot translate call EXTRACT.*", true);
}
f.checkScalar("{fn YEAR(DATE '2014-12-10')}", 2014, "BIGINT NOT NULL");
// System Functions
f.checkType("{fn DATABASE()}", "VARCHAR(2000) NOT NULL");
f.checkString("{fn IFNULL('a', 'b')}", "a", "CHAR(1) NOT NULL");
f.checkString("{fn USER()}", "sa", "VARCHAR(2000) NOT NULL");
// Conversion Functions
// Legacy JDBC style
f.checkScalar("{fn CONVERT('123', INTEGER)}", 123, "INTEGER NOT NULL");
// ODBC/JDBC style
f.checkScalar("{fn CONVERT('123', SQL_INTEGER)}", 123, "INTEGER NOT NULL");
f.checkScalar("{fn CONVERT(INTERVAL '1' DAY, SQL_INTERVAL_DAY_TO_SECOND)}", "+1 00:00:00.000000", "INTERVAL DAY TO SECOND NOT NULL");
}
Aggregations