Different result from Golang db.query and actually running on DB

191
July 25, 2017, at 7:50 PM

I have a MySQL DB on which I run a query which gets the top row from each group (according to some grouping criterion):

SELECT ranked.id, ranked.os, ranked.version, ranked.timestamp, agent_metadata.*
FROM
  (SELECT *,
     @os_rank := IF(@current_os = os, @os_rank + 1, 1) AS os_rank,
     @current_os := os
   FROM default_version
   ORDER BY os DESC, timestamp DESC
  ) as ranked INNER JOIN agent_metadata ON ranked.os = agent_metadata.os AND ranked.version = agent_metadata.version
WHERE os_rank = 1;

When I run this query on my DB (using DataGrip) I get the expected result.

However, when I run the same query from my Go code using db.query, instead of getting 2 rows, I get 5 rows.

I have some logic which builds the query but before running it I print it to the log:

// ... build query
query := qb.String()
logger.Debugf("Executing: %s", query)
if rows, err := repository.connection.Query(query); err != nil {
    return defaults, errors.Wrap(err, "get default versions")
} else {
    defer rows.Close()
    for rows.Next() {
        result := DefaultVersionExtended{}
        err := rows.Scan(
            &result.Id,
            &result.DefaultVersion.OS,
            &result.DefaultVersion.Version,
            &result.Timestamp,
            &result.AgentMetadata.OS,
            &result.AgentMetadataExtended.Version,
            &result.AgentMetadataExtended.Name,
            &result.AgentMetadataExtended.Description,
            &result.AgentMetadataExtended.ReleaseNotesUrl,
            &result.AgentMetadataExtended.UploadTime)
        if err != nil {
            return defaults, errors.Wrap(err, "get default versions")
        }
        defaults = append(defaults, result)
    }
    err = rows.Err()
    if err != nil {
        return defaults, errors.Wrap(err, "get default versions")
    }
    return defaults, errors.Wrap(err, "get default versions")
}

This is the output from the log:

time="2017-07-25T15:07:47+03:00" level=debug msg="Executing: SELECT ranked.id, ranked.os, ranked.version, ranked.timestamp, agent_metadata.* FROM (SELECT *, @os_rank := IF(@current_os = os, @os_rank + 1, 1) AS os_rank, @current_os := os FROM default_version ORDER BY os DESC, timestamp DESC) as ranked INNER JOIN agent_metadata ON ranked.os = agent_metadata.os AND ranked.version = agent_metadata.version WHERE os_rank = 1" class="auroradb.agent_repository"

If I copy-paste the query from the log into my DB console and run it I get the correct result (2 rows):

However, my unit test fails, because I expect the result to have 2 rows but it actually has 5:

Error: "[{%!s(int=1) {windows 1.2.3} {{windows 1.2.3 My name This is a test agent for windows http://notes-heaven.com} 2017-07-25 12:07:45.099 +0000 UTC} 2017-07-25 12:07:45.38 +0000 UTC} {%!s(int=3) {windows 1.2.3.1} {{windows 1.2.3.1 My name 2 This is a second test agent for windows http://notes-heaven.com/1} 2017-07-25 12:07:46.237 +0000 UTC} 2017-07-25 12:07:46.519 +0000 UTC} {%!s(int=4) {windows 1.2.3.2} {{windows 1.2.3.2 My name 3 This is a second test agent for windows http://notes-heaven.com/3} 2017-07-25 12:07:46.801 +0000 UTC} 2017-07-25 12:07:47.082 +0000 UTC} {%!s(int=2) {mac 1.2.3.2.M} {{mac 1.2.3.2.M My name 3.M This is a second test agent for windows http://notes-heaven.com/3} 2017-07-25 12:07:45.662 +0000 UTC} 2017-07-25 12:07:45.944 +0000 UTC} {%!s(int=5) {mac 1.2.3.5.M} {{mac 1.2.3.5.M My name 3.M This is a second test agent for windows http://notes-heaven.com/3} 2017-07-25 12:07:47.364 +0000 UTC} 2017-07-25 12:07:47.653 +0000 UTC}]" should have 2 item(s), but has 5

The returned result is in fact the entire data from agent_metadata. It's as if the entire logic selecting the top result or each group didn't work.

READ ALSO
How to get last item for GROUP BY [duplicate]

How to get last item for GROUP BY [duplicate]

This question already has an answer here:

206
Replace entire text with part of text string SQL

Replace entire text with part of text string SQL

Im trying to do an update on my sql db with a replace but not sure how to get the entire text replacedWe are using a field in ps_product that is called "location" and i want to replace all rows that contains "K" with ""

225
ValueError on jsonify

ValueError on jsonify

I am very new to Flask and Python, I have to convert a MySql query to json formatOn converting, I am facing value error and unable to understand, please help me, thankyou in advance

247