Skip to content

Commit 6a88bfc

Browse files
committedMay 30, 2018
Cast left node to text on string comparison
1 parent ad1fef9 commit 6a88bfc

6 files changed

+66
-0
lines changed
 

‎src/core/qgssqlexpressioncompiler.cpp

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,18 @@ QString QgsSqlExpressionCompiler::result()
3838
return mResult;
3939
}
4040

41+
bool QgsSqlExpressionCompiler::opIsStringComparison( QgsExpressionNodeBinaryOperator::BinaryOperator op )
42+
{
43+
if ( op == QgsExpressionNodeBinaryOperator::BinaryOperator::boILike ||
44+
op == QgsExpressionNodeBinaryOperator::BinaryOperator::boLike ||
45+
op == QgsExpressionNodeBinaryOperator::BinaryOperator::boNotILike ||
46+
op == QgsExpressionNodeBinaryOperator::BinaryOperator::boNotLike ||
47+
op == QgsExpressionNodeBinaryOperator::BinaryOperator::boRegexp )
48+
return true;
49+
else
50+
return false;
51+
}
52+
4153
QString QgsSqlExpressionCompiler::quotedIdentifier( const QString &identifier )
4254
{
4355
QString quoted = identifier;
@@ -253,6 +265,9 @@ QgsSqlExpressionCompiler::Result QgsSqlExpressionCompiler::compileNode( const Qg
253265
QString left;
254266
Result lr( compileNode( n->opLeft(), left ) );
255267

268+
if ( opIsStringComparison( n ->op() ) )
269+
left = castToText( left );
270+
256271
QString right;
257272
Result rr( compileNode( n->opRight(), right ) );
258273

@@ -409,6 +424,11 @@ QString QgsSqlExpressionCompiler::castToReal( const QString &value ) const
409424
return QString();
410425
}
411426

427+
QString QgsSqlExpressionCompiler::castToText( const QString &value ) const
428+
{
429+
return value;
430+
}
431+
412432
QString QgsSqlExpressionCompiler::castToInt( const QString &value ) const
413433
{
414434
Q_UNUSED( value );

‎src/core/qgssqlexpressioncompiler.h

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020

2121
#include "qgis_core.h"
2222
#include "qgsfields.h"
23+
#include "qgsexpressionnodeimpl.h"
2324

2425
class QgsExpression;
2526
class QgsExpressionNode;
@@ -80,6 +81,22 @@ class CORE_EXPORT QgsSqlExpressionCompiler
8081
*/
8182
virtual QString result();
8283

84+
/**
85+
* Returns true if \a op is one of
86+
*
87+
* - LIKE
88+
* - ILIKE
89+
* - NOT LIKE
90+
* - NOT ILIKE
91+
* - ~ (regexp)
92+
*
93+
* In such cases the left operator will be cast to string to behave equal to
94+
* QGIS own expression engine.
95+
*
96+
* \since QGIS 3.2
97+
*/
98+
bool opIsStringComparison( QgsExpressionNodeBinaryOperator::BinaryOperator op );
99+
83100
protected:
84101

85102
/**
@@ -132,6 +149,23 @@ class CORE_EXPORT QgsSqlExpressionCompiler
132149
*/
133150
virtual QString castToReal( const QString &value ) const;
134151

152+
/**
153+
* Casts a value to a text result. Subclasses that support casting to text may implement this function
154+
* to get equal behavior to the QGIS expression engine when string comparison operators are applied
155+
* on non-string data.
156+
*
157+
* Example:
158+
*
159+
* 579 LIKE '5%'
160+
*
161+
* which on a postgres database needs to be
162+
*
163+
* 579::text LIKE '5%'
164+
*
165+
* \since QGIS 3.2
166+
*/
167+
virtual QString castToText( const QString &value ) const;
168+
135169
/**
136170
* Casts a value to a integer result. Subclasses must reimplement this to cast a numeric value to a integer
137171
* type value so that integer division results in a integer value result instead of real.

‎src/core/qgssqliteexpressioncompiler.cpp

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -111,4 +111,9 @@ QString QgsSQLiteExpressionCompiler::castToInt( const QString &value ) const
111111
return QStringLiteral( "CAST((%1) AS INTEGER)" ).arg( value );
112112
}
113113

114+
QString QgsSQLiteExpressionCompiler::castToText( const QString &value ) const
115+
{
116+
return QStringLiteral( "CAST((%1) AS TEXT)" ).arg( value );
117+
}
118+
114119
///@endcond

‎src/core/qgssqliteexpressioncompiler.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,7 @@ class CORE_EXPORT QgsSQLiteExpressionCompiler : public QgsSqlExpressionCompiler
5252
QString sqlFunctionFromFunctionName( const QString &fnName ) const override;
5353
QString castToReal( const QString &value ) const override;
5454
QString castToInt( const QString &value ) const override;
55+
QString castToText( const QString &value ) const override;
5556

5657
};
5758

‎src/providers/postgres/qgspostgresexpressioncompiler.cpp

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -144,6 +144,11 @@ QString QgsPostgresExpressionCompiler::castToInt( const QString &value ) const
144144
return QStringLiteral( "((%1)::int)" ).arg( value );
145145
}
146146

147+
QString QgsPostgresExpressionCompiler::castToText( const QString &value ) const
148+
{
149+
return QStringLiteral( "((%1)::text)" ).arg( value );
150+
}
151+
147152
QgsSqlExpressionCompiler::Result QgsPostgresExpressionCompiler::compileNode( const QgsExpressionNode *node, QString &result )
148153
{
149154
switch ( node->nodeType() )

‎src/providers/postgres/qgspostgresexpressioncompiler.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ class QgsPostgresExpressionCompiler : public QgsSqlExpressionCompiler
3636
QStringList sqlArgumentsFromFunctionName( const QString &fnName, const QStringList &fnArgs ) const override;
3737
QString castToReal( const QString &value ) const override;
3838
QString castToInt( const QString &value ) const override;
39+
QString castToText( const QString &value ) const override;
3940

4041
QString mGeometryColumn;
4142
QgsPostgresGeometryColumnType mSpatialColType;

0 commit comments

Comments
 (0)
Please sign in to comment.