Columns names required in sql during struct batch insert

[komik966]

Inserting single struct without providing columns names in sql works properly:
https://github.com/graaphscom/dbmigrat/blob/7bcb3998b1160ab1a7cd72eeccdfd4d092ae16c1/integrity_test.go#L38-L47

_, err := db.NamedExec(
    `insert into dbmigrat_log values (:idx, :repo, :migration_serial, :checksum, default, :description)`,
    migrationLog{
        Idx:             0,
        Repo:            "repo1",
        MigrationSerial: 0,
        Checksum:        fmt.Sprintf("%x", sha1.Sum([]byte(upSql))),
        Description:     "example migration",
    },
)

But inserting array of structs without providing columns names will end with error got 15 parameters but the statement requires 5. Adding columns names to the sql solves error:
https://github.com/graaphscom/dbmigrat/blob/7bcb3998b1160ab1a7cd72eeccdfd4d092ae16c1/integrity_test.go#L85-L90

_, err := db.NamedExec(`
    insert into dbmigrat_log (idx, repo, migration_serial, checksum, applied_at, description)
    values (:idx, :repo, :migration_serial, :checksum, default, :description)
    `,
    []migrationLog{invalidChecksum, redundantMigration, redundantRepo},
)

[QuangTung97]

Mentioned in https://github.com/jmoiron/sqlx/pull/734/files/4b6b69ec278c1fbfafba4336f8c84065b0911029
Might need to change the regex

[iambudi]

Yes, I have the same error message when using ON DUPLICATE KEY UPDATE inside batch insert with NamedExec.

[iambudi]

Yes, I have the same error message when using ON DUPLICATE KEY UPDATE inside batch insert with NamedExec.

Based on @QuangTung97 link in TestFixBounds so i'll have to change how to write it.

From INSERT INTO foo (a,b) VALUES (:a, :b) ON DUPLICATE KEY UPDATE a=:a
Become sqlINSERT INTO foo (a,b) VALUES (:a, :b) ON DUPLICATE KEY UPDATE a=VALUES(a)

[eqinox76]

We see the same issue. Surprising as it seems to be a regression caused by #734 .
Before the batch insert worked but now its failing with the unintuitive error

sql: expected 4 arguments, got 5668

There was a #734 (comment) by @w1ck3dg0ph3r which suggested fixing the regex. That sounds like a good solution to me. If not at some kind of usage error would be helpful when teh guard is triggered

sqlx/named.go

Lines 247 to 250 in a62bc60

	// defensive guard when "VALUES (...)" not found
	if len(loc) < 2 {
	return bound
	}

.

In any case thank you very much for sqlx!

[andrebetiolo]

After some search in repo, i found the solution doing to things:

1 - Remove ";" if exists in query (#690 (comment))

2 - Do the downgrade to version 1.3.0