Description

freya022

opened on Dec 12, 2020

Issue body actions

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

Activity

1. wumpz commented on Dec 17, 2020

   

   wumpz

   on Dec 17, 2020

   Member

   More actions

   This is not (yet) supported. PRs are welcome.
2. manticore-projects commented on Oct 19, 2021

   

   manticore-projects

   on Oct 19, 2021

   Contributor

   More actions

   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;
3. manticore-projects commented on May 11, 2023

   

   manticore-projects

   on May 11, 2023

   Contributor

   More actions

   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)?
4. manticore-projects commented on May 11, 2023

   

   manticore-projects

   on May 11, 2023

   Contributor

   More actions

   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!
5. manticore-projects

   added a commit that references this issue on May 11, 2023

   feat: access Elements of Array Columns

   This issue will close once commit 09a70a4 is merged into the 'master' branch.

   09a70a4
6. manticore-projects

   closed this as completedin 41b6fceon Jun 1, 2023
7. manticore-projects

   closed this as completedin #1778on Jun 1, 2023
8. manticore-projects

   added a commit that references this issue on Jun 15, 2023

   Assorted Fixes #8 (#1807)

   This issue will close once commit a815f5f is merged into the 'master' branch.

   a815f5f
9. manticore-projects

   added a commit that references this issue on Jun 27, 2023

   Backslash Quotation of Single-Quote `'\\''` (#1813)

   This issue will close once commit c79dfff is merged into the 'master' branch.

   c79dfff

Issue actions