#1255 Fix wrong SQL query when using MBS with PostgreSQL.
Closed 4 years ago by jkaluza. Opened 4 years ago by jkaluza.
jkaluza/fm-orchestrator offline-stream  into  master

@@ -444,7 +444,7 @@ 

              session.query(ModuleBuild)

              .filter(ModuleBuild.name == name)

              .filter(ModuleBuild.state == BUILD_STATES["ready"])

-             .order_by(ModuleBuild.version.desc())

+             .order_by(ModuleBuild.id.desc())

          )

  

          query = ModuleBuild._add_stream_version_lte_filter(session, query, stream_version)
@@ -453,8 +453,8 @@ 

          builds = query.all()

  

          # In case there are multiple versions of single name:stream build, we want to return

-         # the latest version only. The `builds` are ordered by "version" desc, so we

-         # can just get the first (greatest) version of name:stream.

+         # the latest version only. The `builds` are ordered by "id" desc, so we

+         # can just get the first (greatest) id and therefore version of name:stream.

          # TODO: Is there a way how to do that nicely in the SQL query itself?

          seen = {}  # {"n:s": v, ...}

          ret = []

This fixes following error:

ProgrammingError: (ProgrammingError) SELECT DISTINCT ON expressions must match initial ORDER BY expressions

As discussed in IRC, this at least fixes the issue, but it's not ideal as it assumes that the highest ID means the newest module. That's not always the case. I'll do some more investigation to see if we can find a better solution.

Let's use what @mprahl proposed here.

Pull-Request has been closed by jkaluza

4 years ago