TRUE and FALSE in WHERE clause are parsed as columns #926

sivaraam · 2020-01-16T14:28:20Z

Describe the bug

Boolean values in WHERE clause are identified as columns rather than being identified as primitive values like numbers.

To Reproduce

Steps to reproduce the behavior:

Example SQL

SELECT * FROM tableA WHERE true

Parsing this SQL using JSqlParser with this statements

    public static void main(String[] args) throws JSQLParserException {
        Select sel = (Select) CCJSqlParserUtil.parse("SELECT * FROM tableA WHERE true");
        PlainSelect plainSelect = (PlainSelect) sel.getSelectBody();
        System.out.println(plainSelect.getWhere() instanceof Column);
    }

Expected behavior

Prints false as true in the WHERE clause is not supposed to be considered as a column name.

Actual behavior

Prints true.

System

Database you are using: PostgreSQL
Java Version: 11.0.3
JSqlParser version: 3.1

wumpz · 2020-01-21T07:17:17Z

That is true. Because JSqlParser is not only a PostgreSQL parser and other DBMS do not have this true / false keywords, this is by design. How should we differ between TRUE as a value and TRUE as a column name?

sivaraam · 2020-01-21T10:06:13Z

Because JSqlParser is not only a PostgreSQL parser and other DBMS do not have this true / false keywords, this is by design.

Oh! I didn't realise that. I thought most databases have TRUE/FALSE as keywords. On cursory exploration it does seem MS SQL Server doesn't treat them as keywords.

How should we differ between TRUE as a value and TRUE as a column name?

Just assume we always interpret TRUE/FALSE as a value. AFAICT, the only consequence would be that we cannot use TRUE and FALSE as database identifiers without quoting. Would that be such a bad thing? I'm asking this as I'm not sure.

SerialVelocity · 2020-01-23T10:57:55Z

Just assume we always interpret TRUE/FALSE as a value. AFAICT, the only consequence would be that we cannot use TRUE and FALSE as database identifiers without quoting. Would that be such a bad thing? I'm asking this as I'm not sure.

I think if a query is valid in another database, it should be parsed by JSQLParser (without having to add extra quotes, etc).

@wumpz I think we had a small discussion on the Postgres array syntax issue a while ago that it might be a good idea to have a flag indicating what kind of database syntax is being parsed. It would help here as well because the parser could interpret TRUE/FALSE differently based on the flag (e.g. Postgres has TRUE/FALSE as a keyword but MSSQL has it as an expression).

wumpz · 2020-01-24T05:24:45Z

I don't see the benefit here. The user just need to check the column names. The same would apply to some rownum, sysdate, now functions that could be used without brackets. So it makes things more complicated. Do you suggest a flag for all of these?

SerialVelocity · 2020-01-24T10:42:49Z

The benefit is that users don't need to special case things like this, it gets parsed according to the dialect. I would expect a flag to cover all of these (unrelated thing: rownum is a pseudo-column afaik so maybe it would still be a column?).

For example, if you are creating a visitor that visits all functions, you need to remember to visit column names too and make sure "sysdate", etc aren't being used.

If values are parsed as a function by the dialect, it would be good for JSQLParser to recognise them as functions too.

sivaraam · 2020-01-27T07:14:01Z

Making JsqlParser accept the SQL dialect of the query being parsed would be a good feature to have. It would help with flagging usages such as the usage of NULLS FIRST in MySQL queries and what not. But this seems to be more involved change.

For fixing this issue, I think a short-term solution would be to just always interpret TRUE/FALSE as a value. I think it would be a nice solution for the mean time as I hope that not many would have TRUE/FALSE as table/column names.

JSQLParser / JSqlParser

TRUE and FALSE in WHERE clause are parsed as columns #926

TRUE and FALSE in WHERE clause are parsed as columns #926

sivaraam commented Jan 16, 2020

wumpz commented Jan 21, 2020

sivaraam commented Jan 21, 2020

SerialVelocity commented Jan 23, 2020

wumpz commented Jan 24, 2020

SerialVelocity commented Jan 24, 2020

sivaraam commented Jan 27, 2020

JSQLParser / JSqlParser

Sponsor JSQLParser/JSqlParser

Join GitHub today

TRUE and FALSE in WHERE clause are parsed as columns #926

TRUE and FALSE in WHERE clause are parsed as columns #926

Comments

sivaraam commented Jan 16, 2020

Describe the bug

To Reproduce

Expected behavior

Actual behavior

wumpz commented Jan 21, 2020

sivaraam commented Jan 21, 2020

SerialVelocity commented Jan 23, 2020

wumpz commented Jan 24, 2020

SerialVelocity commented Jan 24, 2020

sivaraam commented Jan 27, 2020

Essential cookies

Always active

Analytics cookies