sqlite expression compiler: fix ILIKE/NOT ILIKE c(refs #35698)

qgis · Apr 10, 2020 · 29324a2 · 29324a2
1 parent 6f14117
commit 29324a2
Show file tree

Hide file tree

Showing 2 changed files with 28 additions and 2 deletions.
diff --git a/src/core/qgssqliteexpressioncompiler.cpp b/src/core/qgssqliteexpressioncompiler.cpp
@@ -31,12 +31,30 @@ QgsSqlExpressionCompiler::Result QgsSQLiteExpressionCompiler::compileNode( const
   {
     case QgsExpressionNode::ntBinaryOperator:
     {
-      switch ( static_cast<const QgsExpressionNodeBinaryOperator *>( node )->op() )
+      const QgsExpressionNodeBinaryOperator *op = static_cast<const QgsExpressionNodeBinaryOperator *>( node );
+      switch ( op->op() )
       {
         case QgsExpressionNodeBinaryOperator::boPow:
         case QgsExpressionNodeBinaryOperator::boRegexp:
           return Fail; //not supported by SQLite
 
+        case QgsExpressionNodeBinaryOperator::boILike:
+        case QgsExpressionNodeBinaryOperator::boNotILike:
+        {
+          QString opL, opR;
+
+          if ( compileNode( op->opLeft(), opL ) != Complete ||
+               compileNode( op->opRight(), opR ) != Complete )
+            return Fail;
+
+          result = QStringLiteral( "lower(%1) %2 lower(%3) ESCAPE '\\'" )
+                   .arg( opL )
+                   .arg( op->op() == QgsExpressionNodeBinaryOperator::boILike ? QStringLiteral( "LIKE" ) : QStringLiteral( "NOT LIKE" ) )
+                   .arg( opR );
+
+          return Complete;
+        }
+
         default:
           //fallback to default handling
           return QgsSqlExpressionCompiler::compileNode( node, result );

diff --git a/tests/src/core/testqgssqliteexpressioncompiler.cpp b/tests/src/core/testqgssqliteexpressioncompiler.cpp
@@ -101,7 +101,15 @@ void TestQgsSQLiteExpressionCompiler::testCompiler()
   QCOMPARE( compiler.compile( &exp ), QgsSqlExpressionCompiler::Result::Complete );
   // Check that parenthesis matches
   QCOMPARE( compiler.result().count( '(' ),  compiler.result().count( ')' ) );
-  QCOMPARE( compiler.result(), QString( "((((\"Z\" >= 0) AND (\"Bottom\" <= 1)) OR ((\"Z\" >= 1) AND (\"Bottom\" <= 2))) OR ((\"Z\" >= 2) AND (\"Bottom\" <= 3)))" ) );
+  QCOMPARE( compiler.result(), QStringLiteral( "((((\"Z\" >= 0) AND (\"Bottom\" <= 1)) OR ((\"Z\" >= 1) AND (\"Bottom\" <= 2))) OR ((\"Z\" >= 2) AND (\"Bottom\" <= 3)))" ) );
+
+  QgsExpression ilike( QStringLiteral( "'a' ilike 'A'" ) );
+  QCOMPARE( compiler.compile( &ilike ), QgsSqlExpressionCompiler::Result::Complete );
+  QCOMPARE( compiler.result(), QStringLiteral( "lower('a') LIKE lower('A') ESCAPE '\\'" ) );
+
+  QgsExpression nilike( QStringLiteral( "'a' not ilike 'A'" ) );
+  QCOMPARE( compiler.compile( &nilike ), QgsSqlExpressionCompiler::Result::Complete );
+  QCOMPARE( compiler.result(), QStringLiteral( "lower('a') NOT LIKE lower('A') ESCAPE '\\'" ) );
 }