[OGR] Allow CTE SQLite subsetstring

qgis · Sep 21, 2023 · 311b3ad · 311b3ad
1 parent e5e6a13
commit 311b3ad
Show file tree

Hide file tree

Showing 2 changed files with 7 additions and 1 deletion.
diff --git a/src/core/providers/ogr/qgsogrproviderutils.cpp b/src/core/providers/ogr/qgsogrproviderutils.cpp
@@ -1389,7 +1389,8 @@ OGRLayerH QgsOgrProviderUtils::setSubsetString( OGRLayerH layer, GDALDatasetH ds
     }
   }
   OGRLayerH subsetLayer = nullptr;
-  if ( cleanedSubsetString.startsWith( QLatin1String( "SELECT " ), Qt::CaseInsensitive ) )
+  if ( cleanedSubsetString.startsWith( QLatin1String( "SELECT " ), Qt::CaseInsensitive ) ||
+       cleanedSubsetString.startsWith( QLatin1String( "WITH " ), Qt::CaseInsensitive ) )
   {
     QByteArray sql = encoding->fromUnicode( cleanedSubsetString );
 

diff --git a/tests/src/python/test_provider_ogr_gpkg.py b/tests/src/python/test_provider_ogr_gpkg.py
@@ -563,6 +563,11 @@ def testSelectSubsetString(self):
         vl.setSubsetString("SELECT fid, foo FROM test WHERE foo = 'baz'")
         got = [feat for feat in vl.getFeatures()]
         self.assertEqual(len(got), 1)
+
+        # test SQLite CTE Common Table Expression (issue https://github.com/qgis/QGIS/issues/54677)
+        vl.setSubsetString("WITH test_cte AS (SELECT fid, foo FROM test WHERE foo = 'baz') SELECT * FROM test_cte")
+        self.assertEqual(len(got), 1)
+
         del vl
 
         testdata_path = unitTestDataPath('provider')