SQL syntax error on DeleteAll()

[dignifiedquire]

• Version: SQLBoiler v2.4.0

I have two models, User and Investment which are connected by a 1-many relationship.
When running the following code

boil.DebugMode = true
user, err := models.Users(
  db,
  qm.Where("email=?", email),
  qm.Load("UserInvestments"),
).One()
if err != nil {
  return err
}
if err := user.UserInvestments(db).DeleteAll(); err != nil {
  return fmt.Errorf("failed to delete investments: %s", err)
}

it results in the following error

SELECT * FROM `Users` WHERE (email=?) LIMIT 1;
[me@gmail.com]
select * from `Investments` where `user_id` in (?)
[104]
DELETE FROM `Investments` as `a` WHERE (`a`.`user_id`=?);
[104]
failed to delete investments: models: unable to delete all from Investments: Error 1064: 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 'as `a` WHERE (`a`.`user_id`=?)' at line 1

When I change the delete line to

if err := models.Investments(db, qm.Where("user_id=?", user.ID)).DeleteAll(); err != nil {

all passes with this output

SELECT * FROM `Users` WHERE (email=?) LIMIT 1;
[me@gmail.com]
select * from `Investments` where `user_id` in (?)
[104]
DELETE FROM `Investments` WHERE (user_id=?);
[104]

[dignifiedquire]

Probably relevant, running against MySQL 5.6

[aarondl]

Well look at this this weekend. Thanks for the report.

[aarondl]

Looked into this. Pondering a fix. What's going on is the Relationship method creates a select query that's good for selecting with, then turns it into a delete query when it hits DeleteAll. In Postgres this is a valid query, in MySQBad it's not. As usual, mysql means we can't have nice things. I have to really think on what to do here because that query it's creating is ideal for appending other types of queries for selecting unambiguously. Might have to pull out the good o'le if mysql which I hate doing if the side effects are bad enough.

[aarondl]

Fixed on dev branch. I decided that we don't exactly need to use aliasing so long as we're consistent in other places.

Output of a similar program to yours on the dev branch, please let me know if your issue is resolved.

SELECT * FROM `parents` LIMIT 1;
[]
DELETE FROM `children` WHERE (`children`.`parent_id`=?);
[1]
<nil>

[dignifiedquire]

@aarondl thank you I can confirm this fixes the issue

[aarondl]

Released.