qm.Select result differs

[Rvtea]

Version: 2.1.4

I came to meet a issue when using Select to build query.

I was trying to join 2 table and find some id info back.

The sql query goes like

models.As(boil.GetDB(), 
  qm.InnerJoin("B on A.b_id = B.id"),
  qm.Where("A.status = ?", "ACTIVE"),
  qm.And("B.status = ?", "INACTIVE"),
  qm.Select("A.id"),
)

in debug mode the SQL turns out to be select `A`.`id` as "A.id" From ... and the result fails all the time.

While when I change the query last part to qm.Select("A.id as id") then the result returned well and SQL turns out to be select A.id as id From ...

Could you help explain this as I could not figure out the reason. Thx.

[aarondl]

Typically the syntax for an inner join would look like:

qm.InnerJoin("B on A.id = B.id"),

Can you try this instead of having the extra characters in there? I'm not sure what that's supposed to do.

[Rvtea]

@aarondl I probably add extra blank space and make a typo in my original issue description, i have fixed that. I am sure that i should use b_id instead of id as A has foreign key constraint. Because when i remove the qm.Select row, it works also well for me. So based on the debug mode info, I believe this should be related to qm.Select part.

[aarondl]

@Rvtea So the way the Select() helper works is that it renames columns to things like "A.id", the reason it does this is to support inner joins actually. In a regular inner join say you have table A and B and you're only interested in their IDs.

Any database server will simply return a result that looks like this: id, id

So which id is which? Where does one struct begin and one end? You can't know other than by keeping some metadata along with the select statement. So instead, we rename the columns and prefix them with their table names so that we can differentiate them.

This is why the behavior that you're seeing exists.

In terms of the solution here, you shouldn't be saying A.id in the select, you should just say id. The select doesn't know about the other model involved in the inner join. Hence it doesn't expect namespaced columns. It's not possible given the nature of the query here that you could do B.name as a select column, because there's no B struct to bind to. Unless there's some other bug I'm not seeing.

Hopefully that's making some sense?

[Rvtea]

@aarondl Thanks for the explanation! Actually I did simply use qm.Select("id") instead of qm.Select("A.id") at the first time, while it turns out to be an error for mysql side: Error 1052: Column 'id' in field list is ambiguous. So i have to add the prefix table name to specify which id I want to select (then I found this issue). So I think it might be related to somewhere else.

[ceshihao]

I think the difference is from the following codes. Query builder will build a select with as statement, if the query has join, selectCols but not count.

https://github.com/volatiletech/sqlboiler/blob/1340e5544bc5880f10808da79ebb3ee80cf8abb1/queries/query_builders.go#L64-L68

[aarondl]

@Rvtea I also noticed you're on an extremely old version of sqlboiler. Might be worth upgrading to at least 2.7.x to make sure you've got all those bugs gone.

I did an inner join query that looks very similar to yours on a sample database that resembles yours and I get no problems.

    vids, err := models.Videos(
        qm.Select("videos.id"),
        qm.InnerJoin("users on users.id = videos.author_id"),
        qm.Where("length(users.name) > 2"),
    ).All(context.Background(), db)

SELECT `videos`.`id` as "videos.id" FROM `videos` INNER JOIN users on users.id = videos.author_id WHERE (length(users.name) > 2);

Can you maybe tell me a bit more about why what you're doing is failing? Is there an error from mysql? I'm actually a bit confused now.

[Rvtea]

Thanks @ceshihao for the code source, I checked the related UT and seems the sql content is expected. https://github.com/volatiletech/sqlboiler/blob/1340e5544bc5880f10808da79ebb3ee80cf8abb1/queries/query_builders_test.go#L515-L548

@aarondl Thanks for your test trial and upgrading recommendation. Our code base is using an old version currently, we would consider upgrading then.

While for your test, actually using qm.Select("A.id") query return no error too at my side while the corresponding model result are all zero value set. Which means mysql side could handle the selectA.idas "A.id" format while when mapping the db result back to models, we encounter this issue. When I tries to use the result's ids, they are all 0. Could you help test the returned result from your side and see if this could be reproduced? Thanks again for your great help.

[aarondl]

Sorry for the delay on this issue @Rvtea. I looked into this again. I'm using my example from above with the users and videos.

• If you use Select with the column id it passes that in to mysql directly and there's an error because it can't differentiate the column.

  qm.Select("id")

• If you use Select with the column videos.id the Bind() call expects a struct that has a substruct for videos. This means it expects you to Bind() to something that looks like the struct below. The reason for this is this is the most common use case for inner joins, where you want the data from both models from the join.

  type VideoUser struct {
      Video models.Video `boil:"video"`
      User  models.User  `boil:"user"`
  }

• If you use Select with the column videos.id as id then it correctly returns the data because it can find the id field in the Video struct.

  qm.Select("videos.id as id")

So to summarize: Use an as statement for your column names, and the Select() query mod will not override it and it will succeed if you've named them correctly.

[Rvtea]

Thx @aarondl for the explanation, totally resolve my confusion, thx again!