capacitor-community / capacitor-community/sqlite

bug: Plugin fails to parse modern SQL syntax including CTEs and ON CONFLICT clauses.

Open
#670 0 comments 0 reactions 0 assignees View on GitHub
bug/fix needs: triage
Dominant language
Swift
Stars
661
Forks
158
PR merge metrics
No merged PRs in 30d

Description

**Plugin version:**
7.0.2

**Platform(s):**
Android, Web

**Current behavior:**
1) When an INSERT or UPSERT statement contains an ON CONFLICT clause with its own parenthesized expressions (e.g. conflict targets or column-name-lists), the plugin incorrectly splits the statement when detecting RETURNING.
For example a valid statement like:
```sql
INSERT INTO table (col1, col2) VALUES (1,2), (3,4)
ON CONFLICT (col1) DO UPDATE SET col2 = 0
RETURNING *;
```
is transformed into the invalid
```sql
INSERT INTO table (col1, col2) VALUES (1,2), (3,4)
ON CONFLICT (col1);
```
2) The Common Table Expression used for "UPDATE" or "DELETE" is ignored by the getUpdDelReturnedValues and the resulting statement may be invalid.
An update statement with a CTE like `WITH new_data (col1, col2) AS ( VALUES (1,2),(3,4),(5,6) ) SELECT * FROM table WHERE col1 IN (SELECT col1 FROM new_data);` would become `SELECT (col1, col2) FROM table WHERE col1 IN (SELECT col1 FROM new_data);` with the CTE definition missing.

**Expected behavior:**
Since the returning clause is always last in the grammar for the statements INSERT, UPDATE and DELETE, the parsing should just remove the associated part. I understand that the altering of the statement is performed in order to maintain retro-compatibility with older devices running older versions of SQLite, although it was not immediately clear to me while debugging the issue that this was happening behind the scenes.
Resources:
- https://sqlite.org/syntax/insert-stmt.html
- https://sqlite.org/syntax/upsert-clause.html
- https://sqlite.org/syntax/update-stmt.html
- https://sqlite.org/lang_delete.html

**Steps to reproduce:**
Run a query like the above using the plugin methods "execute" or "executeSet", observe the error in response.

**Related code:**
The methods responsible for this behaviour are in the Java implementation:
`android\src\main\java\com\getcapacitor\community\database\sqlite\SQLite\Database.java` and
`android\src\main\java\com\getcapacitor\community\database\sqlite\SQLite\UtilsSQLStatement.java`

**Other information:**
The issue has been isolated, verified and debugged in Android Studio. An attempt for a resolution will be shown in an associated draft PR.

**Capacitor doctor:**

```
Capacitor Doctor

Latest Dependencies:

@capacitor/cli: 7.4.4
@capacitor/core: 7.4.4
@capacitor/android: 7.4.4
@capacitor/ios: 7.4.4

Installed Dependencies:

@capacitor/core: 7.4.3
@capacitor/android: 7.4.3
@capacitor/ios: 7.4.3
@capacitor/cli: 7.4.3

[success] Android looking great! 👌
[error] Xcode is not installed
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.