Skip to content

Commit dd0b33e

Browse files
m-kuhnm-kuhn
authoredJan 8, 2019
Merge pull request #8809 from m-kuhn/expression_nullif
Add NULLIF expression function [FEATURE]
·
ltr-3_40final-3_6_0
2 parents 6855a1c + 8754d55 commit dd0b33e

File tree

3 files changed

+30
-1
lines changed

3 files changed

+30
-1
lines changed
 

‎resources/function_help/json/nullif

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
{
2+
"name": "nullif",
3+
"type": "function",
4+
"description": "Returns a null value if value1 equals value2; otherwise it returns value1. This can be used to conditionally substitute values with NULL.",
5+
"arguments": [ {"arg":"value1", "description": "The value that should either be used or substituted with NULL."},
6+
{"arg":"value2", "description": "The control value that will trigger the NULL substitution."}],
7+
"examples": [ { "expression":"nullif('(none)', '(none)')", "returns":"NULL"},
8+
{ "expression":"nullif('text', '(none)')", "returns":"'text'"},
9+
{ "expression":"nullif(\"name\", '')", "returns":"NULL, if name is an empty string (or already NULL), the name in any other case."} ]
10+
}
11+

‎src/core/expression/qgsexpressionfunction.cpp

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -947,6 +947,18 @@ static QVariant fcnCoalesce( const QVariantList &values, const QgsExpressionCont
947947
}
948948
return QVariant();
949949
}
950+
951+
static QVariant fcnNullIf( const QVariantList &values, const QgsExpressionContext *, QgsExpression *, const QgsExpressionNodeFunction * )
952+
{
953+
const QVariant val1 = values.at( 0 );
954+
const QVariant val2 = values.at( 1 );
955+
956+
if ( val1 == val2 )
957+
return QVariant();
958+
else
959+
return val1;
960+
}
961+
950962
static QVariant fcnLower( const QVariantList &values, const QgsExpressionContext *, QgsExpression *parent, const QgsExpressionNodeFunction * )
951963
{
952964
QString str = QgsExpressionUtils::getStringValue( values.at( 0 ), parent );
@@ -4575,6 +4587,7 @@ const QList<QgsExpressionFunction *> &QgsExpression::Functions()
45754587
<< new QgsStaticExpressionFunction( QStringLiteral( "to_dm" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "value" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "axis" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "precision" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "formatting" ), true ), fcnToDegreeMinute, QStringLiteral( "Conversions" ), QString(), false, QSet<QString>(), false, QStringList() << QStringLiteral( "todm" ) )
45764588
<< new QgsStaticExpressionFunction( QStringLiteral( "to_dms" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "value" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "axis" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "precision" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "formatting" ), true ), fcnToDegreeMinuteSecond, QStringLiteral( "Conversions" ), QString(), false, QSet<QString>(), false, QStringList() << QStringLiteral( "todms" ) )
45774589
<< new QgsStaticExpressionFunction( QStringLiteral( "coalesce" ), -1, fcnCoalesce, QStringLiteral( "Conditionals" ), QString(), false, QSet<QString>(), false, QStringList(), true )
4590+
<< new QgsStaticExpressionFunction( QStringLiteral( "nullif" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "value1" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "value2" ) ), fcnNullIf, QStringLiteral( "Conditionals" ) )
45784591
<< new QgsStaticExpressionFunction( QStringLiteral( "if" ), 3, fcnIf, QStringLiteral( "Conditionals" ), QString(), false, QSet<QString>(), true )
45794592

45804593
<< new QgsStaticExpressionFunction( QStringLiteral( "aggregate" ),

‎tests/src/core/testqgsexpression.cpp

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1129,6 +1129,11 @@ class TestQgsExpression: public QObject
11291129
QTest::newRow( "coalesce null" ) << "coalesce(NULL)" << false << QVariant();
11301130
QTest::newRow( "coalesce mid-null" ) << "coalesce(1, NULL, 3)" << false << QVariant( 1 );
11311131
QTest::newRow( "coalesce exp" ) << "coalesce(NULL, 1+1)" << false << QVariant( 2 );
1132+
QTest::newRow( "nullif no substitution" ) << "nullif(3, '(none)')" << false << QVariant( 3 );
1133+
QTest::newRow( "nullif NULL" ) << "nullif(NULL, '(none)')" << false << QVariant();
1134+
QTest::newRow( "nullif substitute string" ) << "nullif('(none)', '(none)')" << false << QVariant();
1135+
QTest::newRow( "nullif substitute double" ) << "nullif(3.3, 3.3)" << false << QVariant();
1136+
QTest::newRow( "nullif substitute int" ) << "nullif(0, 0)" << false << QVariant();
11321137
QTest::newRow( "regexp match" ) << "regexp_match('abc','.b.')" << false << QVariant( 1 );
11331138
QTest::newRow( "regexp match invalid" ) << "regexp_match('abc DEF','[[[')" << true << QVariant();
11341139
QTest::newRow( "regexp match escaped" ) << "regexp_match('abc DEF','\\\\s[A-Z]+')" << false << QVariant( 4 );
@@ -1552,7 +1557,7 @@ class TestQgsExpression: public QObject
15521557
if ( featureMatched )
15531558
{
15541559
QgsFeature feat = res.value<QgsFeature>();
1555-
QCOMPARE( feat.id(), ( long long )featureId );
1560+
QCOMPARE( feat.id(), static_cast<QgsFeatureId>( featureId ) );
15561561
}
15571562
}
15581563

0 commit comments

Comments
 (0)
Please sign in to comment.