1ae65c6298ac909c2636306070c2e3d4a89fa971
angie
  Fri Jan 20 10:50:10 2017 -0800
In hGetGenomeOptionsForClade, orderKey was added to the SELECT list in order to support MySQL 5.7.  Jim pointed out in code review that adding another column to the output can cause extra rows if there are multiple values per distinct(whatever) -- in this case, multiple values of orderKey per genome.  Adding 'group by genome' to the query removes the duplicates.  refs #18673

diff --git src/hg/lib/hdb.c src/hg/lib/hdb.c
index 2a89571..cb29132 100644
--- src/hg/lib/hdb.c
+++ src/hg/lib/hdb.c
@@ -4408,55 +4408,54 @@
  * useful for constructing a clade menu. */
 {
 // get only the clades that have actual active genomes
 char *query = NOSQLINJ ""
     "SELECT DISTINCT(c.name), c.label "
     // mysql 5.7: SELECT list w/DISTINCT must include all fields in ORDER BY list (#18626)
     ", c.priority "
     "FROM %s c, %s g, %s d "
     "WHERE c.name=g.clade AND d.organism=g.genome AND d.active=1 "
     "ORDER BY c.priority";
 char queryBuf[4096];
 safef(queryBuf, sizeof queryBuf, query, cladeTable(),  genomeCladeTable(), dbDbTable());
 struct sqlConnection *conn = hConnectCentral();
 struct slPair *nativeClades = sqlQuickPairList(conn, queryBuf);
 hDisconnectCentral(&conn);
-
 struct slPair *trackHubClades = trackHubGetCladeLabels();
-
 return slCat(nativeClades, trackHubClades);
 }
 
 struct slPair *hGetGenomeOptionsForClade(char *clade)
 /* Return a list of slPairs, each containing genome menu value and menu label,
  * useful for constructing a genome menu for the given clade. */
 {
 struct slPair *pairList = NULL;
 if (isHubTrack(clade))
     {
     struct dbDb *hubDbDbList = trackHubGetDbDbs(clade), *dbDb;
     for (dbDb = hubDbDbList;  dbDb != NULL;  dbDb = dbDb->next)
 	slAddHead(&pairList, slPairNew(dbDb->genome, cloneString(dbDb->genome)));
     }
 else
     {
     struct dyString *dy =
 	sqlDyStringCreate("select distinct(d.genome) "
     // mysql 5.7: SELECT list w/DISTINCT must include all fields in ORDER BY list (#18626)
                           ", orderKey "
                           "from %s d,%s g "
 			  "where d.genome=g.genome and g.clade = '%s' "
+                          "group by genome " // necessary since we added orderKey to SELECT list
 			  "order by orderKey", dbDbTable(), genomeCladeTable(), clade);
     // Although clade and db menus have distinct values vs. labels, we actually use the
     // same strings for values and labels in the genome menu!  So we get a plain list
     // from the query and turn it into a pair list.
     struct sqlConnection *conn = hConnectCentral();
     struct slName *nativeGenomes = sqlQuickList(conn, dy->string), *g;
     hDisconnectCentral(&conn);
     dyStringFree(&dy);
     for (g = nativeGenomes;  g != NULL;  g = g->next)
 	slAddHead(&pairList, slPairNew(g->name, cloneString(g->name)));
     }
 slReverse(&pairList);
 return pairList;
 }