[FRG-97] Support for JDBC escape syntax is incomplete

[reporter="jhyde", created="Wed, 22 Mar 2006 13:11:09 -0500 (GMT-05:00)"] JDBC has 5 escape sequences:

* {fn ...} for function calls
* {escape ...} as in "SELECT * FROM emp WHERE name LIKE 'Fred%' {escape '\'}"
* {call ...} for procedure calls
* {d, t, ts} for date/time/timestamp constants
* {oj ...} for outer joins

We currently support 'fn' and 'd/t/ts'. Need to support the 'escape', 'call', 'oj'.

All need to be implemented as pseudo-functions in the parser, and expanded in the validator. If we expand them in the parser, error messages won't make much sense.

See http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/statement.html#1004825.

Also, escape sequences should only work if Statement.setEscapeProcessing(true) has been called. (I haven't checked whether this works.)

[author="jhyde", created="Thu, 4 Sep 2008 15:33:49 -0500 (GMT-05:00)"] I've made a little progress on this issue. I got {fn power} working. I will check in shortly.

I also added SqlOperatorTests.testJdbcFn with a test for each function described in the JDBC 3.0 spec. Those which don't work are commented out.

15 functions are working:

        checkScalar("{fn ABS(-3)}", 3, "INTEGER NOT NULL");
        checkScalarApprox("{fn EXP(2)}", "DOUBLE NOT NULL", 7, 0.001);
        checkScalarApprox("{fn LOG(10)}", "DOUBLE NOT NULL", 4, 0.001);
        checkScalarApprox("{fn LOG10(100)}", "DOUBLE NOT NULL", 2, 0);
        checkScalar("{fn MOD(19, 4)}", 3, "INTEGER NOT NULL");
        checkScalar("{fn POWER(2, 3)}", 8, "DOUBLE NOT NULL");
        checkScalar("{fn CONCAT('foo', 'bar')}", "foobar", "CHAR(6) NOT NULL");
        checkScalar("{fn INSERT('abc', 1, 2, 'ABCdef')}", "abcCdef", "VARCHAR(9) NOT NULL");
        checkScalar("{fn LCASE('foo' || 'bar')}", "foobar", "CHAR(6) NOT NULL");
        checkScalar("{fn LOCATE('alphabet', 'ha')}", 3, "INTEGER NOT NULL");
        checkScalar("{fn SUBSTRING('abcdef', 2, 3)}", "bcd", "VARCHAR(6) NOT NULL");
        checkScalar("{fn UCASE('xxx')}", "XXX", "CHAR(3) NOT NULL");
        checkScalar("{fn CURDATE()}", null, "DATE NOT NULL");
        checkScalar("{fn CURTIME()}", null, "TIME(0) NOT NULL");
        checkScalar("{fn NOW()}", null, "TIMESTAMP(0) NOT NULL");

LTRIM, RTRIM are implemented but broken. The 3 argument version of LOCATE is not implemented.

The following functions are not implemented:
    {fn ASCII(string)}
    {fn CHAR(code)}
    {fn DIFFERENCE(string1, string2)}
    {fn LEFT(string, count)}
    {fn LENGTH(string)}
    {fn LOCATE(string1, string2[, start])}
    {fn REPEAT(string, count)}
    {fn REPLACE(string1, string2, string3)}
    {fn RIGHT(string, count)}
    {fn SOUNDEX(string)}
    {fn SPACE(count)}
    {fn DAYNAME(date)}
    {fn DAYOFMONTH(date)}
    {fn DAYOFWEEK(date)}
    {fn DAYOFYEAR(date)}
    {fn HOUR(time)}
    {fn MINUTE(time)}
    {fn MONTH(date)}
    {fn MONTHNAME(date)}
    {fn QUARTER(date)}
    {fn SECOND(time)}
    {fn TIMESTAMPADD(interval, count, timestamp)}
    {fn TIMESTAMPDIFF(interval, timestamp1, timestamp2)}
    {fn WEEK(date)}
    {fn YEAR(date)}
    {fn DATABASE()}
    {fn IFNULL(expression, value)}
    {fn USER()}
    {fn CONVERT(value, SQLtype)}

LucidDB / luciddb

[FRG-97] Support for JDBC escape syntax is incomplete #773