Better logic for Array Expressions vs. Squared Bracket Quotation

[manticore-projects]

Maybe we can apply an automatic detection based on Regex

// Who ever can explain what this REGEX does will get a cookie
public static final Pattern SQUARED_BRACKET_QUOTATION_PATTERN =
      Pattern.compile(
          "(((?!\\[\\d+\\])\\[.*\\]\\.\\.?)|(\\.\\[\\w+( +\\w+)*\\])|((?!\\s\\[\\d+\\])\\s\\[\\w+( +\\w+)*\\]))");

boolean foundSquareBracketQuotes =
          SQUARED_BRACKET_QUOTATION_PATTERN.matcher(statementSql).find();
      LOGGER.log(Level.INFO, "MSQL Server Square Bracket Quations is {0}", foundSquareBracketQuotes);

      Statement statement =
          CCJSqlParserUtil.parse(
              statementSql, parser -> parser.withSquareBracketQuotation(foundSquareBracketQuotes));

It will find() for:

SELECT columnName FROM [server-name\\server-instance]..schemaName.[table Name];
SELECT columnName FROM [server-name\\server-instance]..[schemaName].[tableName];
SELECT columnName FROM [server-name\\server-instance]..[schemaName].[tableName];
SELECT columnName FROM [schemaName].tableName;
SELECT columnName FROM [schemaName].[table Name];
SELECT columnName FROM schemaName.[table Name];
SELECT columnName FROM servername..[schemaName].[tableName];
SELECT columnName FROM [tableName];
SELECT [columnName] FROM [tableName];
SELECT [columnName] FROM [tableName] as a;
SELECT [columnName] FROM tableName;
SELECT [column Name] FROM tableName;

It will NOT find() for:

SELECT column A FROM tableName;
SELECT columnName FROM servername..schemaName.tableName;
SELECT a[1] from dual;
SELECT a[1] as a from dual;

-- this was a hard one!
SELECT a[1],b [2], a[ 2 ], a [ 3 ] as a from dual; 

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);
INSERT INTO sal_emp VALUES ('Carol',ARRAY[20000, 25000, 25000, 25000], ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

[wumpz]

Sorry for my late answer. I was some days out of being connected :(.

I don't know why you want to detect this automatically, since it is mutually exclusive for the used databases.

Additionally, using a regexp does not work here. Quotation means, you could write anything into your title, so:

select a [1 + 2] 

means for one database return array value of index 3 and for the other return value of a with the column alias "1 + 2".

Expressions like ARRAY[20000, 25000, 25000, 25000] (array is a keyword), will be processed as arrays either way.

[manticore-projects]

Welcome back!

You are right of course, your corner cases will confuse the detection. So definitely, there must always be a hard overwrite possible (e.g. enforce SQUARED BRACKETS when not set or disable it when detected wrongly).
However, I still feel that these are rare(?) corner cases and believe, the REGEX would make the right decision in maybe 90+ percent?

So I see it as a good guess and have implemented it like that in JSQLFormatter already.
Based on your remarks, I will add an ON/OFF overwrite CLI option.

We can close this discussion.

[wumpz]

How?? At the moment I am realizing that there is no close button for a discussion. Strange.

[manticore-projects]

There are still a lot of "invalid" error reports linked to this topic. And honestly, I do understand where these guys are coming from: any T-SQL person would expect to be first citizen and would wonder, why his SQL with squared bracket quotes is not parsed. Keep in mind, that also the Parser Error messages are not helping to understand what is going on.

So I wonder if we:

1. still try to implement a kind of smart auto detection, which will make the correct decision in 90% of the cases (failing 10% of the cases)
2. throw a special exception in case that [ ] were found in the SQL, but parsing fails and useSquaredBracketQuotation was not set (e. g. "Found [ ] which can be Array or Quote, please double check if you want to set useSquaredBracketQuotation"

I would like to expect a better user experience and also less frequent error reports on this issue.

[manticore-projects]

4 years later and I am getting back to this with a solution candidate. I rewrote this whole logic, got rid of the Token and its manipulation and turned it into a production instead.

This allows now for defining any Quoting Character or Tag at will.