Parameter binding fails with nested query in WASM OO API

(1) By Agile Assembler (agile_assembler) on 2023-06-04 21:19:42 [source]

(Originally posted on Stack Overflow as SQLite parameter binding fails with nested query)

I have the following query

DELETE FROM Item WHERE rowid IN (
  SELECT rowid FROM ItemFts WHERE path = :path
)

where ItemFts is a FTS5 table that uses the Item table as its external content. My observation is that the query executed but the record was not deleted. I have confirmed that the following queries all work individually:

This returns the correct rowid (e.g. 1):

SELECT rowid FROM Item WHERE path = :path

This correctly deletes the record at rowid = 1:

DELETE FROM Item WHERE rowid IN (1)

And this correctly deleted the item at the hard coded path

DELETE FROM Item WHERE rowid IN (
  SELECT rowid FROM ItemFts WHERE path = 'some/hard/coded/path'
)

I could only deduce that the binding mechanism failed with the subquery syntax.

I'm using the exec method from SQLite's wasm OO API. So the binding looks like this

db.exec(`
DELETE FROM Item WHERE rowid IN (
  SELECT rowid FROM ItemFts WHERE path = :path
)
`, {bind: {":path": "some/path"}})

Could someone confirm if there is a bug or it's not officially supported, or I am doing something wrong? Thank you!

(2) By Stephan Beal (stephan) on 2023-06-04 21:39:46 in reply to 1 [link] [source]

Could someone confirm if there is a bug or it's not officially supported, or I am doing something wrong?

As the OO binding API is simply a thin wrapper around the C API, i'm admittedly skeptical about it being a bug in the JS code. However, i will look into it the next time i'm on dry land with AC power (sometime in the next couple of days).

(Originally posted on Stack Overflow as SQLite parameter binding fails with nested query)

We don't actively follow SO, or any similar platform, for bug reports or support requests. You'll always get more authoritative responses from here than you will "out there."

(3) By Stephan Beal (stephan) on 2023-06-05 11:29:35 in reply to 1 [link] [source]

Could someone confirm if there is a bug or it's not officially supported, or I am doing something wrong?

i'm unable to reproduce any problems with binding of parameters in subqueries, so the parameter binding is not the problem. However, in order to create a full reproduction/test i need to see the whole schema. Please post the full schema (shorn of any parts you deem irrelevant, if you like) and a few data rows to test with.

Aside from tests just added which confirm that binding is working in subqueries, if the bind calls were failing they would trigger an exception via exec(), so we know that the parameter binding itself is working (or "not failing," meaning that the C-level APIs are returning 0). My tests with non-external FTS content cannot reproduce this issue, so my current hypothesis is that the difference lies in the use of external content, as opposed to the parameter binding.

(4) By Agile Assembler (agile_assembler) on 2023-06-06 04:15:10 in reply to 3 [link] [source]

Thank you for looking into this. I just found out that the issue was due to my careless use the API. I used this:

db.exec(`...`, {":path": path})

Instead of this:

db.exec(`...`, { bind: {":path": path} })

Apparently, I had confused db.exec with db.selectObjects, which has a shallower signature for parameter binding. Again, thank you for the help and apologies for my mistake.