Previously parsable statement error

[morvael]

This worked in 4.1, but doesn't work in 4.2:

b1.system

Parser works for

b1.`system`

Sadly our database (Informix) understands only

b1.'system'

which in turn isn't something the parser understands.

[morvael]

No doubt system became reserved word in 4.2, but I think the list of reserved words varies between databases and having one big set of reserved words from any and all databases is leading to such problems. Too many words are reserved because of that.

[manticore-projects]

Sadly our database (Informix) understands only

b1.'system'

b1."system" does not work on informix?
Anyway, system is likely a reserved keyword because of Oracle's ALTER SYSTEM ... statement.

[morvael]

Ok, b1."system" works, so this is a workaround.
But as I wrote, there should be a notion of dialects with different sets of reserved words, instead of one big set covering all databases. Because that is very restrictive. And with every version of JSqlParser that adds new reserved keywords all queries passing through the parser must be examined if they don't contain words that were OK, but suddenly aren't OK anymore...

[morvael]

Looking at your fix, I had column named system, not table.

[morvael]

SELECT b1.system FROM table b1

[morvael]

No a fan of workaround, as columName in parsed statement retains those quote chars, so some other code using that must be aware of that possibility and add conditional stripping...

[manticore-projects]

Looking at your fix, I had column named system, not table.

Well, that is an example why verbose samples and test cases are important.
This works now:

SELECT system from b1.system

[morvael]

Haven't seen such query yet, but ok :)

[manticore-projects]

Haven't seen such query yet, but ok :)

Have not seen system used as a column name or alias without proper quoting either -- so should I reconsider :-D

Btw, there is a comprehensive list of SQL 99 Keywords. You can find it on the internet.
I can only advise to avoid any of these keywords and to apply proper quoting to all table- and column names regardless. Furthermore it is good practice to write RDBMS agnostic, standard compliant queries -- unless the RDBMS itself forces you to use a certain non-compliant syntax (yes, I mean you, Oracle!)

[morvael]

Yes, but it's legacy system still in use, and that field exists for longer than I work with it... Will not change it just to remove potential collision :)

[manticore-projects]

And one more more thing: I believe most of the contributors know Oracle or MS SQL Server, but few use Informix.

So if you have a catalog of your own preferred Informix queries and would like to contribute it, we could actually integrate it into our unit tests and assure that nothing breaks during the development.