Encountered unexpected token: "DATE_SUB" S_IDENTIFIER

[jacobwwang]

Actual Behavior

public static void main(String[] args) throws JSQLParserException {
    String sql = "-- type 1 stands for sth1, 2 stands for sth2...\n" +
                 "SELECT count(1)\n" +
                 "FROM table_name\n" +
                 "WHERE 1 = 1\n" +
                 "AND uid = 1\n" +
                 "AND type IN (1, 2, 3)\n" +
                 "AND time >= TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 2 DAY),'00:00:00')\n" +
                 "AND time < TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL (2 - 1) DAY),'00:00:00')";

    Statement parse = new CCJSqlParserManager().parse(new StringReader(sql));
    System.out.println(parse);
}

above code throws and exception:

Exception in thread "main" net.sf.jsqlparser.JSQLParserException
	at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:40)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "DATE_SUB" <S_IDENTIFIER>
    at line 8, column 22.

Was expecting one of:

    "+"

	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:18597)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:18447)
	at net.sf.jsqlparser.parser.CCJSqlParser.RegularCondition(CCJSqlParser.java:6278)
	at net.sf.jsqlparser.parser.CCJSqlParser.Condition(CCJSqlParser.java:5931)
	at net.sf.jsqlparser.parser.CCJSqlParser.AndExpression(CCJSqlParser.java:5893)
	at net.sf.jsqlparser.parser.CCJSqlParser.OrExpression(CCJSqlParser.java:5818)
	at net.sf.jsqlparser.parser.CCJSqlParser.Expression(CCJSqlParser.java:5789)
	at net.sf.jsqlparser.parser.CCJSqlParser.WhereClause(CCJSqlParser.java:5146)
	at net.sf.jsqlparser.parser.CCJSqlParser.PlainSelect(CCJSqlParser.java:3082)
	at net.sf.jsqlparser.parser.CCJSqlParser.SetOperationList(CCJSqlParser.java:3235)
	at net.sf.jsqlparser.parser.CCJSqlParser.SelectBody(CCJSqlParser.java:2946)
	at net.sf.jsqlparser.parser.CCJSqlParser.Select(CCJSqlParser.java:2939)
	at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:130)
	at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:81)
	at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:38)
	... 1 more

Expected Behavior

hope that can support date_add / date_sub method

Steps to Reproduce the Problem

1. import jsqlparse-1.3
2. run java code in IntelliJ IDEA
3. exception happened

Specifications

• Version: jsqlparser-1.3
• Platform: OS X EI Capitan(10.11.6), jdk8, mysql
• Subsystem:

[An0nymous0]

same question...

[shiyifan]

i meet this with "date_add"...

[cristiker]

I have the same problem with following sql

SELECT
	*
FROM
	stlr_case_instance
WHERE
	execute_user = ?
AND create_time > date_sub(curdate(), INTERVAL ? DAY);

when I replace date_sub(curdate(), INTERVAL ? DAY) with date_sub(curdate(), INTERVAL 3 DAY)
it works fine.

[cristiker]

it seems SqlParser can not recognize the placeholder in function expression

[wumpz]

It does not recognize this placeholder within this interval expression.

[cristiker]

It does not recognize this placeholder within this interval expression.

@wumpz thanks for your reply, any plan to resolve this problem. I can escape this problem by change sql ,not blocking problem for me.

[manticore-projects]

Parses fine with JSQLParser 4.6 Snapshot:

-- type 1 stands for sth1, 2 stands for sth2...
SELECT Count( 1 )
FROM table_name
WHERE 1 = 1
    AND uid = 1
    AND type IN ( 1, 2, 3 )
    AND time >= Timestamp( Date_Sub( Curdate(), INTERVAL 2 DAY ), '00:00:00' )
    AND time < Timestamp( Date_Sub( Curdate(), INTERVAL(2 - 1) DAY ), '00:00:00' )
;

c
Closed.