postgres provider: cast result of a subquery in min/max/uniqueValue(s) (fixes #12630; followup bf56457 ff)

jef-n · jef-n · commit a9cccd2ca3c4 · 2015-05-28T00:11:48.000+02:00
(cherry picked from commit e509fc5)
diff --git a/src/providers/postgres/qgspostgresprovider.cpp b/src/providers/postgres/qgspostgresprovider.cpp
@@ -1234,15 +1234,17 @@ QVariant QgsPostgresProvider::minimumValue( int index )
   {
     // get the field name
     const QgsField &fld = field( index );
-    QString sql = QString( "SELECT %1 FROM %2" )
-                  .arg( connectionRO()->fieldExpression( fld, "min(%1)" ) )
+    QString sql = QString( "SELECT min(%1) AS %1 FROM %2" )
+                  .arg( quotedIdentifier( fld.name() ) )
                   .arg( mQuery );
 
     if ( !mSqlWhereClause.isEmpty() )
     {
       sql += QString( " WHERE %1" ).arg( mSqlWhereClause );
     }
 
+    sql = QString( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ) ).arg( sql );
+
     QgsPostgresResult rmin = connectionRO()->PQexec( sql );
     return convertValue( fld.type(), rmin.PQgetvalue( 0, 0 ) );
   }
@@ -1262,21 +1264,23 @@ void QgsPostgresProvider::uniqueValues( int index, QList<QVariant> &uniqueValues
     // get the field name
     const QgsField &fld = field( index );
     QString sql = QString( "SELECT DISTINCT %1 FROM %2" )
-                  .arg( connectionRO()->fieldExpression( fld ) )
+                  .arg( quotedIdentifier( fld.name() ) )
                   .arg( mQuery );
 
     if ( !mSqlWhereClause.isEmpty() )
     {
       sql += QString( " WHERE %1" ).arg( mSqlWhereClause );
     }
 
-    sql +=  QString( " ORDER BY %1" ).arg( connectionRO()->fieldExpression( fld ) );
+    sql +=  QString( " ORDER BY %1" ).arg( quotedIdentifier( fld.name() ) );
 
     if ( limit >= 0 )
     {
       sql += QString( " LIMIT %1" ).arg( limit );
     }
 
+    sql = QString( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ) ).arg( sql );
+
     QgsPostgresResult res = connectionRO()->PQexec( sql );
     if ( res.PQresultStatus() == PGRES_TUPLES_OK )
     {
@@ -1406,15 +1410,17 @@ QVariant QgsPostgresProvider::maximumValue( int index )
   {
     // get the field name
     const QgsField &fld = field( index );
-    QString sql = QString( "SELECT %1 FROM %2" )
-                  .arg( connectionRO()->fieldExpression( fld, "max(%1)" ) )
+    QString sql = QString( "SELECT max(%1) AS %1 FROM %2" )
+                  .arg( quotedIdentifier( fld.name() ) )
                   .arg( mQuery );
 
     if ( !mSqlWhereClause.isEmpty() )
     {
       sql += QString( " WHERE %1" ).arg( mSqlWhereClause );
     }
 
+    sql = QString( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ) ).arg( sql );
+
     QgsPostgresResult rmax = connectionRO()->PQexec( sql );
     return convertValue( fld.type(), rmax.PQgetvalue( 0, 0 ) );
   }

Original file line number	Diff line number	Diff line change
`@@ -1234,15 +1234,17 @@ QVariant QgsPostgresProvider::minimumValue( int index )`
`1234`	`1234`	`{`
`1235`	`1235`	`// get the field name`
`1236`	`1236`	`const QgsField &fld = field( index );`
`1237`		`- QString sql = QString( "SELECT %1 FROM %2" )`
`1238`		`- .arg( connectionRO()->fieldExpression( fld, "min(%1)" ) )`
	`1237`	`+ QString sql = QString( "SELECT min(%1) AS %1 FROM %2" )`
	`1238`	`+ .arg( quotedIdentifier( fld.name() ) )`
`1239`	`1239`	`.arg( mQuery );`
`1240`	`1240`
`1241`	`1241`	`if ( !mSqlWhereClause.isEmpty() )`
`1242`	`1242`	`{`
`1243`	`1243`	`sql += QString( " WHERE %1" ).arg( mSqlWhereClause );`
`1244`	`1244`	`}`
`1245`	`1245`
	`1246`	`+ sql = QString( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ) ).arg( sql );`
	`1247`	`+`
`1246`	`1248`	`QgsPostgresResult rmin = connectionRO()->PQexec( sql );`
`1247`	`1249`	`return convertValue( fld.type(), rmin.PQgetvalue( 0, 0 ) );`
`1248`	`1250`	`}`
`@@ -1262,21 +1264,23 @@ void QgsPostgresProvider::uniqueValues( int index, QList<QVariant> &uniqueValues`
`1262`	`1264`	`// get the field name`
`1263`	`1265`	`const QgsField &fld = field( index );`
`1264`	`1266`	`QString sql = QString( "SELECT DISTINCT %1 FROM %2" )`
`1265`		`- .arg( connectionRO()->fieldExpression( fld ) )`
	`1267`	`+ .arg( quotedIdentifier( fld.name() ) )`
`1266`	`1268`	`.arg( mQuery );`
`1267`	`1269`
`1268`	`1270`	`if ( !mSqlWhereClause.isEmpty() )`
`1269`	`1271`	`{`
`1270`	`1272`	`sql += QString( " WHERE %1" ).arg( mSqlWhereClause );`
`1271`	`1273`	`}`
`1272`	`1274`
`1273`		`- sql += QString( " ORDER BY %1" ).arg( connectionRO()->fieldExpression( fld ) );`
	`1275`	`+ sql += QString( " ORDER BY %1" ).arg( quotedIdentifier( fld.name() ) );`
`1274`	`1276`
`1275`	`1277`	`if ( limit >= 0 )`
`1276`	`1278`	`{`
`1277`	`1279`	`sql += QString( " LIMIT %1" ).arg( limit );`
`1278`	`1280`	`}`
`1279`	`1281`
	`1282`	`+ sql = QString( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ) ).arg( sql );`
	`1283`	`+`
`1280`	`1284`	`QgsPostgresResult res = connectionRO()->PQexec( sql );`
`1281`	`1285`	`if ( res.PQresultStatus() == PGRES_TUPLES_OK )`
`1282`	`1286`	`{`
`@@ -1406,15 +1410,17 @@ QVariant QgsPostgresProvider::maximumValue( int index )`
`1406`	`1410`	`{`
`1407`	`1411`	`// get the field name`
`1408`	`1412`	`const QgsField &fld = field( index );`
`1409`		`- QString sql = QString( "SELECT %1 FROM %2" )`
`1410`		`- .arg( connectionRO()->fieldExpression( fld, "max(%1)" ) )`
	`1413`	`+ QString sql = QString( "SELECT max(%1) AS %1 FROM %2" )`
	`1414`	`+ .arg( quotedIdentifier( fld.name() ) )`
`1411`	`1415`	`.arg( mQuery );`
`1412`	`1416`
`1413`	`1417`	`if ( !mSqlWhereClause.isEmpty() )`
`1414`	`1418`	`{`
`1415`	`1419`	`sql += QString( " WHERE %1" ).arg( mSqlWhereClause );`
`1416`	`1420`	`}`
`1417`	`1421`
	`1422`	`+ sql = QString( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ) ).arg( sql );`
	`1423`	`+`
`1418`	`1424`	`QgsPostgresResult rmax = connectionRO()->PQexec( sql );`
`1419`	`1425`	`return convertValue( fld.type(), rmax.PQgetvalue( 0, 0 ) );`
`1420`	`1426`	`}`