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

jef-n · jef-n · commit e509fc53ce5c · 2015-05-10T10:55:42.000+02:00
diff --git a/src/providers/postgres/qgspostgresprovider.cpp b/src/providers/postgres/qgspostgresprovider.cpp
@@ -1279,15 +1279,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 ) );
   }
@@ -1307,21 +1309,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 )
     {
@@ -1451,15 +1455,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
`@@ -1279,15 +1279,17 @@ QVariant QgsPostgresProvider::minimumValue( int index )`
`1279`	`1279`	`{`
`1280`	`1280`	`// get the field name`
`1281`	`1281`	`const QgsField &fld = field( index );`
`1282`		`- QString sql = QString( "SELECT %1 FROM %2" )`
`1283`		`- .arg( connectionRO()->fieldExpression( fld, "min(%1)" ) )`
	`1282`	`+ QString sql = QString( "SELECT min(%1) AS %1 FROM %2" )`
	`1283`	`+ .arg( quotedIdentifier( fld.name() ) )`
`1284`	`1284`	`.arg( mQuery );`
`1285`	`1285`
`1286`	`1286`	`if ( !mSqlWhereClause.isEmpty() )`
`1287`	`1287`	`{`
`1288`	`1288`	`sql += QString( " WHERE %1" ).arg( mSqlWhereClause );`
`1289`	`1289`	`}`
`1290`	`1290`
	`1291`	`+ sql = QString( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ) ).arg( sql );`
	`1292`	`+`
`1291`	`1293`	`QgsPostgresResult rmin = connectionRO()->PQexec( sql );`
`1292`	`1294`	`return convertValue( fld.type(), rmin.PQgetvalue( 0, 0 ) );`
`1293`	`1295`	`}`
`@@ -1307,21 +1309,23 @@ void QgsPostgresProvider::uniqueValues( int index, QList<QVariant> &uniqueValues`
`1307`	`1309`	`// get the field name`
`1308`	`1310`	`const QgsField &fld = field( index );`
`1309`	`1311`	`QString sql = QString( "SELECT DISTINCT %1 FROM %2" )`
`1310`		`- .arg( connectionRO()->fieldExpression( fld ) )`
	`1312`	`+ .arg( quotedIdentifier( fld.name() ) )`
`1311`	`1313`	`.arg( mQuery );`
`1312`	`1314`
`1313`	`1315`	`if ( !mSqlWhereClause.isEmpty() )`
`1314`	`1316`	`{`
`1315`	`1317`	`sql += QString( " WHERE %1" ).arg( mSqlWhereClause );`
`1316`	`1318`	`}`
`1317`	`1319`
`1318`		`- sql += QString( " ORDER BY %1" ).arg( connectionRO()->fieldExpression( fld ) );`
	`1320`	`+ sql += QString( " ORDER BY %1" ).arg( quotedIdentifier( fld.name() ) );`
`1319`	`1321`
`1320`	`1322`	`if ( limit >= 0 )`
`1321`	`1323`	`{`
`1322`	`1324`	`sql += QString( " LIMIT %1" ).arg( limit );`
`1323`	`1325`	`}`
`1324`	`1326`
	`1327`	`+ sql = QString( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ) ).arg( sql );`
	`1328`	`+`
`1325`	`1329`	`QgsPostgresResult res = connectionRO()->PQexec( sql );`
`1326`	`1330`	`if ( res.PQresultStatus() == PGRES_TUPLES_OK )`
`1327`	`1331`	`{`
`@@ -1451,15 +1455,17 @@ QVariant QgsPostgresProvider::maximumValue( int index )`
`1451`	`1455`	`{`
`1452`	`1456`	`// get the field name`
`1453`	`1457`	`const QgsField &fld = field( index );`
`1454`		`- QString sql = QString( "SELECT %1 FROM %2" )`
`1455`		`- .arg( connectionRO()->fieldExpression( fld, "max(%1)" ) )`
	`1458`	`+ QString sql = QString( "SELECT max(%1) AS %1 FROM %2" )`
	`1459`	`+ .arg( quotedIdentifier( fld.name() ) )`
`1456`	`1460`	`.arg( mQuery );`
`1457`	`1461`
`1458`	`1462`	`if ( !mSqlWhereClause.isEmpty() )`
`1459`	`1463`	`{`
`1460`	`1464`	`sql += QString( " WHERE %1" ).arg( mSqlWhereClause );`
`1461`	`1465`	`}`
`1462`	`1466`
	`1467`	`+ sql = QString( "SELECT %1 FROM (%2) foo" ).arg( connectionRO()->fieldExpression( fld ) ).arg( sql );`
	`1468`	`+`
`1463`	`1469`	`QgsPostgresResult rmax = connectionRO()->PQexec( sql );`
`1464`	`1470`	`return convertValue( fld.type(), rmax.PQgetvalue( 0, 0 ) );`
`1465`	`1471`	`}`