Bug 126229 - wrong context browser sorting with postgresql
Summary: wrong context browser sorting with postgresql
Status: RESOLVED DUPLICATE of bug 126228
Alias: None
Product: amarok
Classification: Applications
Component: general (show other bugs)
Version: unspecified
Platform: Gentoo Packages Linux
: NOR normal
Target Milestone: ---
Assignee: Amarok Developers
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2006-04-25 17:55 UTC by simon
Modified: 2006-06-11 12:32 UTC (History)
0 users

See Also:
Latest Commit:
Version Fixed In:
Sentry Crash Report:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description simon 2006-04-25 17:55:50 UTC
Version:           1.4 SVN (using KDE KDE 3.4.3)
Installed from:    Gentoo Packages
Compiler:          gcc 3.4.5-r1 gcc version 3.4.5 (Gentoo 3.4.5-r1, ssp-3.4.5-1.0, pie-8.7.9)
OS:                Linux

The statistics in the contextbrowser where representing score=0 in the suggested field and favourite songs by artist first, when using postgresql. So the order is "never heared"  > 80%. 
This is because in postgresql ORDER BY :
"The null value sorts higher than any other value. In other words, with ascending sort order, null values sort at the end, and with descending sort order, null values sort at the beginning."
So when generating those stats amaroK does an left join of  tags with statistics, where every tags with no matching statistic data hast NULL values then. Which leads to the described behaviour.

To get a sorting like 90% > 60% >0  > NULL one could write:
ORDER BY coalesce(stats.percentage,-1) DESC;
or
ORDER BY stats.percentage is NULL, stats.percentage desc;

The options i see are to catch this everytime in QueryBuilder::sortBy when sorting with respect to score, or have an extra sortyBy function.

Here is the patch i use currently:

--- org_collectiondb.cpp	2006-04-25 12:10:12.000000000 +0200
+++ collectiondb.cpp	2006-04-25 12:12:37.000000000 +0200
@@ -4897,9 +4897,20 @@ QueryBuilder::sortBy( int table, Q_INT64
     if ( !m_sort.isEmpty() ) m_sort += ",";
     if ( b ) m_sort += "LOWER( ";
     if ( table & tabYear ) m_sort += "(";
-
-    m_sort += tableName( table ) + ".";
-    m_sort += valueName( value );
+    // null values to the back
+    if ( CollectionDB::instance()->getType() == DbConnection::postgresql 
+	 && ( descending ) && ( value & valScore )
+       ){ 
+	//only adapt at score	
+	m_sort += "coalesce(";
+	m_sort += tableName( table ) + ".";
+    	m_sort += valueName( value );
+	m_sort += ",0)";	
+	//order by coalesce(stats.percentage,0) 	
+    } else {
+     m_sort += tableName( table ) + ".";
+     m_sort += valueName( value );
+    }
 
     if (CollectionDB::instance()->getType() == DbConnection::postgresql)
     {
Comment 1 Jaison Lee 2006-04-25 18:21:44 UTC

*** This bug has been marked as a duplicate of 126228 ***