Version 4.0.0 - Incorrect sorting for multiple-version packages.

[bkraul]

I posted comments on the kendar.org website, without knowing there was a github for this project. Phenomenal!

Here is the description and what I have been able to find out about this bug.

Let's say for instance, I have the following versions for a package:

• 1.0.2.19
• 1.0.2.18

These versions should be sorted in the order as they appear above. However, what the package displays as being the latest version is 1.0.2.18.

Researching the code, I found that you are using some kind of data expression translator which then renders to the SQL to be sent to the server. It took me a while to follow it, but finally zeroed into phpnuget/src/inc/commons/mysqldb.php, specifically the GetAll() function.

The actual query hitting the DB is:

SELECT * FROM (SELECT * FROM nugetdb_pkg WHERE `Listed`=true ORDER BY `Title` ASC , Version0 DESC,Version1 DESC,Version2 DESC,Version3 DESC, VersionBeta DESC ) as TB GROUP BY `Id` LIMIT 99999 OFFSET 0;

Which when ran directly against the server, also returns the incorrect 'latest' version. I kept wondering why this happens, because obviously, if you take the subquery and run it by itself, it returns the list in the correct order.

I found that the answer is actually a mySQL bug, yes a long-standing mysql bug. Basically, any sort order that is put in a subquery which is then used in a group by query is completely ignored, and the query engine returns whichever row it wants from the subquery.

There is more information about this in the following links:

https://bugs.mysql.com/bug.php?id=70203 https://stackoverflow.com/questions/18524935/mysql-does-subquery-with-order-by-clause-always-group-by-in-the-parent-query-in

The correct query for the desired outcome would be as follows:

SELECT T2.*
FROM
    (SELECT Id AS _Id, MAX(`Version`) AS _Version FROM nugetdb_pkg GROUP BY Id) T1
INNER JOIN
    nugetdb_pkg T2 ON T2.Id = T1._Id 
    AND T2.`Version` = T1._Version
WHERE
    `Listed`=true
LIMIT 99999 OFFSET 0

Obviously, because of the dynamic nature of the db factory, this cannot be easily done, at least not by me, so I came up with a crude hack for the moment, which takes place on the GetAll function as follows:

public function GetAll($limit=99999,$skip=0,$objectSearch=null)
{
    if($objectSearch==null){
        $select = "SELECT * FROM ".$this->dbFile;
        $select .= " ORDER BY Version0 DESC, Version1 DESC, Version2 DESC, Version3 DESC, VersionBeta DESC";
        $select .= " LIMIT ".$limit." OFFSET ".$skip ;
        return $this->doQuery($select);
    }

    $fieldNames = explode(":|:",$this->FieldNames());
    $fieldTypes = explode(":|:",$this->FieldTypes());

    $select = "SELECT T2.* FROM ";

    if($objectSearch!=null){
        # add the grouping (if selected).
        $gp = $objectSearch->DoGroupByMySql($fieldNames,$fieldTypes);
        if($gp!=""){
            $select .= "(SELECT Id AS _Id, MAX(`Version`) AS _Version FROM nugetdb_pkg GROUP BY Id) AS T1 ";
            $select .= "INNER JOIN ".$this->dbFile." AS T2 ON (T2.Id = T1._Id AND T2.`Version` = T1._Version)";             
        } else {
            $select .= $this->dbFile . " AS T2";                                
        }
        # add the where.
        $where = trim($objectSearch->ToMySql());
        if($where!=null && strlen($where)>0){
            $select = $select." WHERE ".$where;
        }           
        # add the sorting.
        $select = $select." ".$objectSearch->DoSortMySql($fieldNames,$fieldTypes);
    }       

    $select = $select." LIMIT ".$limit." OFFSET ".$skip;

    #echo "<!-- ".$select."-->";
    #echo $select . "<br/><br/>";
    return  $this->doQuery($select);
}

I can verify that this works on all test cases.

Hopefully this information will help to provide a more elegant solution.

[kendarorg]

Fixed on 4.0.0.1

[bkraul]

You da man, dawg! Applied fix on my prod box. Much more elegant. Thanks.

[kendarorg]

(blush)!!