SQL "update" statement does not recognize queries as array indexes

[freya022]

Describe the bug SQL "update" statement with an array index are not recognized

To Reproduce Steps to reproduce the behavior:

1. Example SQL update utilisateur set listes[(select cardinality(listes))] = 1;
2. Parsing this SQL using JSqlParser with this statements

final String query = "update utilisateur set listes[(select cardinality(listes))] = 1;";
final List<ValidationError> validate = Validation.validate(List.of(DatabaseType.POSTGRESQL), query);

3. Exception

Exception in thread "main" net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "[" "["
    at line 1, column 30.

Was expecting one of:

    ","
    "="

	at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:26615)
	at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:26454)
	at net.sf.jsqlparser.parser.CCJSqlParser.Update(CCJSqlParser.java:1382)

Expected behavior The query should be valid

This SQL statement is valid though : select messages from liste where login = 'test' order by messages[(select cardinality(messages) from liste where idliste = 1)] asc;

System

• Database you are using : PostgreSQL 13
• Java Version : 15
• JSqlParser version : 4.0-SNAPSHOT

[wumpz]

This is not (yet) supported. PRs are welcome.

[manticore-projects]

Just for clarification, any UPDATE of an ARRAY fails even with a simple index:

-- SELECT works
SELECT listes[(SELECT cardinality(listes))]
FROM dual
;

-- UPDATE fails
update utilisateur set listes[0] = 1;

[manticore-projects]

Greetings!

Apologies for responding so late, I really do not use Arrays in Databases. However, I stumbled over this when refactoring a lot of the Grammar for a 5.0 relase: UPDATE ... SET ( UPDATESET )* with UPDATESET := COLUMN = EXPRESSION, we strictly expect columns here.

So am I correct, that we are still referring to a COLUMN, but one that is defined as an ARRAY and so we actually will need to amend the COLUMN production (for allowing arrays, and not just identifiers)?

[manticore-projects]

we actually will need to amend the COLUMN production (for allowing arrays, and not just identifiers)

I just did that and it seems to work!

Also, I have no idea if this is valid SQL (while I am certain, anywhere a startup is working right now on inventing this particular dialect):

update utilisateur set listes[0:3] = (1,2,3,4)

But it works too!