Cast left node to text on string comparison

m-kuhn · m-kuhn · commit 6a88bfcaeb10 · 2018-05-30T16:45:30.000+02:00
diff --git a/src/core/qgssqlexpressioncompiler.cpp b/src/core/qgssqlexpressioncompiler.cpp
@@ -38,6 +38,18 @@ QString QgsSqlExpressionCompiler::result()
   return mResult;
 }
 
+bool QgsSqlExpressionCompiler::opIsStringComparison( QgsExpressionNodeBinaryOperator::BinaryOperator op )
+{
+  if ( op == QgsExpressionNodeBinaryOperator::BinaryOperator::boILike ||
+       op == QgsExpressionNodeBinaryOperator::BinaryOperator::boLike ||
+       op == QgsExpressionNodeBinaryOperator::BinaryOperator::boNotILike ||
+       op == QgsExpressionNodeBinaryOperator::BinaryOperator::boNotLike ||
+       op == QgsExpressionNodeBinaryOperator::BinaryOperator::boRegexp )
+    return true;
+  else
+    return false;
+}
+
 QString QgsSqlExpressionCompiler::quotedIdentifier( const QString &identifier )
 {
   QString quoted = identifier;
@@ -253,6 +265,9 @@ QgsSqlExpressionCompiler::Result QgsSqlExpressionCompiler::compileNode( const Qg
       QString left;
       Result lr( compileNode( n->opLeft(), left ) );
 
+      if ( opIsStringComparison( n ->op() ) )
+        left = castToText( left );
+
       QString right;
       Result rr( compileNode( n->opRight(), right ) );
 
@@ -409,6 +424,11 @@ QString QgsSqlExpressionCompiler::castToReal( const QString &value ) const
   return QString();
 }
 
+QString QgsSqlExpressionCompiler::castToText( const QString &value ) const
+{
+  return value;
+}
+
 QString QgsSqlExpressionCompiler::castToInt( const QString &value ) const
 {
   Q_UNUSED( value );
diff --git a/src/core/qgssqlexpressioncompiler.h b/src/core/qgssqlexpressioncompiler.h
@@ -20,6 +20,7 @@
 
 #include "qgis_core.h"
 #include "qgsfields.h"
+#include "qgsexpressionnodeimpl.h"
 
 class QgsExpression;
 class QgsExpressionNode;
@@ -80,6 +81,22 @@ class CORE_EXPORT QgsSqlExpressionCompiler
      */
     virtual QString result();
 
+    /**
+     * Returns true if \a op is one of
+     *
+     * - LIKE
+     * - ILIKE
+     * - NOT LIKE
+     * - NOT ILIKE
+     * - ~ (regexp)
+     *
+     * In such cases the left operator will be cast to string to behave equal to
+     * QGIS own expression engine.
+     *
+     * \since QGIS 3.2
+     */
+    bool opIsStringComparison( QgsExpressionNodeBinaryOperator::BinaryOperator op );
+
   protected:
 
     /**
@@ -132,6 +149,23 @@ class CORE_EXPORT QgsSqlExpressionCompiler
      */
     virtual QString castToReal( const QString &value ) const;
 
+    /**
+     * Casts a value to a text result. Subclasses that support casting to text may implement this function
+     * to get equal behavior to the QGIS expression engine when string comparison operators are applied
+     * on non-string data.
+     *
+     * Example:
+     *
+     *     579 LIKE '5%'
+     *
+     * which on a postgres database needs to be
+     *
+     *     579::text LIKE '5%'
+     *
+     * \since QGIS 3.2
+     */
+    virtual QString castToText( const QString &value ) const;
+
     /**
      * Casts a value to a integer result. Subclasses must reimplement this to cast a numeric value to a integer
      * type value so that integer division results in a integer value result instead of real.
diff --git a/src/core/qgssqliteexpressioncompiler.cpp b/src/core/qgssqliteexpressioncompiler.cpp
@@ -111,4 +111,9 @@ QString QgsSQLiteExpressionCompiler::castToInt( const QString &value ) const
   return QStringLiteral( "CAST((%1) AS INTEGER)" ).arg( value );
 }
 
+QString QgsSQLiteExpressionCompiler::castToText( const QString &value ) const
+{
+  return QStringLiteral( "CAST((%1) AS TEXT)" ).arg( value );
+}
+
 ///@endcond
diff --git a/src/core/qgssqliteexpressioncompiler.h b/src/core/qgssqliteexpressioncompiler.h
@@ -52,6 +52,7 @@ class CORE_EXPORT QgsSQLiteExpressionCompiler : public QgsSqlExpressionCompiler
     QString sqlFunctionFromFunctionName( const QString &fnName ) const override;
     QString castToReal( const QString &value ) const override;
     QString castToInt( const QString &value ) const override;
+    QString castToText( const QString &value ) const override;
 
 };
 
diff --git a/src/providers/postgres/qgspostgresexpressioncompiler.cpp b/src/providers/postgres/qgspostgresexpressioncompiler.cpp
@@ -144,6 +144,11 @@ QString QgsPostgresExpressionCompiler::castToInt( const QString &value ) const
   return QStringLiteral( "((%1)::int)" ).arg( value );
 }
 
+QString QgsPostgresExpressionCompiler::castToText( const QString &value ) const
+{
+  return QStringLiteral( "((%1)::text)" ).arg( value );
+}
+
 QgsSqlExpressionCompiler::Result QgsPostgresExpressionCompiler::compileNode( const QgsExpressionNode *node, QString &result )
 {
   switch ( node->nodeType() )
diff --git a/src/providers/postgres/qgspostgresexpressioncompiler.h b/src/providers/postgres/qgspostgresexpressioncompiler.h
@@ -36,6 +36,7 @@ class QgsPostgresExpressionCompiler : public QgsSqlExpressionCompiler
     QStringList sqlArgumentsFromFunctionName( const QString &fnName, const QStringList &fnArgs ) const override;
     QString castToReal( const QString &value ) const override;
     QString castToInt( const QString &value ) const override;
+    QString castToText( const QString &value ) const override;
 
     QString mGeometryColumn;
     QgsPostgresGeometryColumnType mSpatialColType;

Original file line number	Diff line number	Diff line change
`@@ -111,4 +111,9 @@ QString QgsSQLiteExpressionCompiler::castToInt( const QString &value ) const`
`111`	`111`	`return QStringLiteral( "CAST((%1) AS INTEGER)" ).arg( value );`
`112`	`112`	`}`
`113`	`113`
	`114`	`+QString QgsSQLiteExpressionCompiler::castToText( const QString &value ) const`
	`115`	`+{`
	`116`	`+ return QStringLiteral( "CAST((%1) AS TEXT)" ).arg( value );`
	`117`	`+}`
	`118`	`+`
`114`	`119`	`///@endcond`
Original file line number	Diff line number	Diff line change
`@@ -144,6 +144,11 @@ QString QgsPostgresExpressionCompiler::castToInt( const QString &value ) const`
`144`	`144`	`return QStringLiteral( "((%1)::int)" ).arg( value );`
`145`	`145`	`}`
`146`	`146`
	`147`	`+QString QgsPostgresExpressionCompiler::castToText( const QString &value ) const`
	`148`	`+{`
	`149`	`+ return QStringLiteral( "((%1)::text)" ).arg( value );`
	`150`	`+}`
	`151`	`+`
`147`	`152`	`QgsSqlExpressionCompiler::Result QgsPostgresExpressionCompiler::compileNode( const QgsExpressionNode *node, QString &result )`
`148`	`153`	`{`
`149`	`154`	`switch ( node->nodeType() )`