NamedExec issues expanding `VALUES`

[nicjohnson145]

When populating a values statement with multiple entries, certain query shapes are not expanding as expected. This can be shown with the following example

stmt := `
	INSERT INTO
		foo_table
	VALUES
	(
		:id,
		:foo,
		:bar
	)
`
args := []map[string]any{
	{
		"id": 1,
		"foo": "foo1",
		"bar": "bar1",
	},
	{
		"id": 2,
		"foo": "foo2",
		"bar": "bar2",
	},
}

_, err := db.NamedExec(stmt, args)

If foo_table has 3 columns (or any remaining columns are defaulted) then this is a legal insert statement. Instead this fails with an error similar to

pq: got 6 parameters but the statement requires 3

This is easy enough to work around in the case of an insert, just specify the columns. However this is impossible to work around in the case of an UPDATE ... FROM statement.

	UPDATE
		foo_table f
	SET
		foo = v.foo
	FROM (
		VALUES
		(:id, :foo)
	) AS v ( id, foo )
	WHERE
		f.id = v.id

I believe this is due to the regex here which incorrectly requires that a values statement be preceeded by a closing parenthesis, so the values statements do not get duplicated in the above cases.

[mw9000]

@nicjohnson145 it's a hacky workaround, but you can satisfy the regex with a commented out right parenthesis.

	UPDATE
		foo_table f
	SET
		foo = v.foo
	FROM (
                --)
		VALUES
		(:id, :foo)
	) AS v ( id, foo )
	WHERE
		f.id = v.id

[nicjohnson145]

@mw9000 oof. that is terrible. But it's arguably less terrible than the "dont use named statements, and instead manually specify values lists and do parameter list flattening" that I'm doing right now to work around this. Thanks for the suggestion 👍

[jvnuw]

For the record, this issue has also been reported in at least the following:

• mssql(sql server):batch upsert error... (upsert multiple rows) #828
• use NamedExec to batch update,but got error #810
• Batch update support #796
• fixBound is not expanding VALUES correct #772

PR attempts include at least:

• fix: bindArray on SELECT from VALUES list #811
• Fix regexp for named queries #848