MySQL insertion error, DEFAULT is parenthesized

[jacobmolby]

I get this error when running a

models.TeamUsers.Insert(ctx,exec,setter)

Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), ?, ?, (DEFAULT), ?, ?)' at line 2

The generated sql looks like this:

INSERT INTO `team_user`  (`id`, `team_id`, `user_id`, `admin`, `created_at`, `updated_at`)
VALUES ((DEFAULT), ?, ?, (DEFAULT), ?, ?)

It's caused by the InsertMod in the model using mysql.Raw. Which wraps values in parenthesis.

Can be fixed be doing something like (forgive the bad naming):

// mysql/starters.go

// Current "Raw" function
func Raw(query string, args ...any) Expression {
	return bmod.Raw(query, args...)
}


// Addition
type RawExpr struct {
	query string
}

func (r RawExpr) WriteSQL(w io.Writer, d bob.Dialect, start int) ([]any, error) {
	return bob.Express(w, d, start, r.query)
}

func RealRaw(query string) bob.Expression {
	return RawExpr{query: query}
}

And then in the generated model using RealRaw instead of Raw:

// modified models/team_user.go
func (s TeamUserSetter) InsertMod() bob.Mod[*dialect.InsertQuery] {
	vals := make([]bob.Expression, 6)
	if s.ID.IsUnset() {
		vals[0] = mysql.RealRaw("DEFAULT") // changed from "raw"
	} else {
		vals[0] = mysql.Arg(s.ID)
	}

	if s.TeamID.IsUnset() {
		vals[1] = mysql.RealRaw("DEFAULT") // changed from "raw"
	} else {
		vals[1] = mysql.Arg(s.TeamID)
	}
        //....

I don't know what you're thinking about this. There might be a better way of dealing with this (for instance changing the current raw function), which is why I haven't made a PR. Let me know if you want me to do something.

[stephenafamo]

The issue is in expr/builder.go:22

Clause should be added to the case that does not get wrapped in parenthesis. That should solve the issue.

I think I added Clause a while back and forgot to add the case.

[jacobmolby]

You're right.

Adding ClausetoX solves it.

Should I make a PR, or do you handle it yourself?

[stephenafamo]

Fixed in d02b707