Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
Merge pull request #8809 from m-kuhn/expression_nullif
Add NULLIF expression function [FEATURE]
  • Loading branch information
m-kuhn committed Jan 8, 2019
2 parents 6855a1c + 8754d55 commit dd0b33e
Show file tree
Hide file tree
Showing 3 changed files with 30 additions and 1 deletion.
11 changes: 11 additions & 0 deletions resources/function_help/json/nullif
@@ -0,0 +1,11 @@
{
"name": "nullif",
"type": "function",
"description": "Returns a null value if value1 equals value2; otherwise it returns value1. This can be used to conditionally substitute values with NULL.",
"arguments": [ {"arg":"value1", "description": "The value that should either be used or substituted with NULL."},
{"arg":"value2", "description": "The control value that will trigger the NULL substitution."}],
"examples": [ { "expression":"nullif('(none)', '(none)')", "returns":"NULL"},
{ "expression":"nullif('text', '(none)')", "returns":"'text'"},
{ "expression":"nullif(\"name\", '')", "returns":"NULL, if name is an empty string (or already NULL), the name in any other case."} ]
}

13 changes: 13 additions & 0 deletions src/core/expression/qgsexpressionfunction.cpp
Expand Up @@ -947,6 +947,18 @@ static QVariant fcnCoalesce( const QVariantList &values, const QgsExpressionCont
}
return QVariant();
}

static QVariant fcnNullIf( const QVariantList &values, const QgsExpressionContext *, QgsExpression *, const QgsExpressionNodeFunction * )
{
const QVariant val1 = values.at( 0 );
const QVariant val2 = values.at( 1 );

if ( val1 == val2 )
return QVariant();
else
return val1;
}

static QVariant fcnLower( const QVariantList &values, const QgsExpressionContext *, QgsExpression *parent, const QgsExpressionNodeFunction * )
{
QString str = QgsExpressionUtils::getStringValue( values.at( 0 ), parent );
Expand Down Expand Up @@ -4575,6 +4587,7 @@ const QList<QgsExpressionFunction *> &QgsExpression::Functions()
<< 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" ) )
<< 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" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "coalesce" ), -1, fcnCoalesce, QStringLiteral( "Conditionals" ), QString(), false, QSet<QString>(), false, QStringList(), true )
<< new QgsStaticExpressionFunction( QStringLiteral( "nullif" ), QgsExpressionFunction::ParameterList() << QgsExpressionFunction::Parameter( QStringLiteral( "value1" ) ) << QgsExpressionFunction::Parameter( QStringLiteral( "value2" ) ), fcnNullIf, QStringLiteral( "Conditionals" ) )
<< new QgsStaticExpressionFunction( QStringLiteral( "if" ), 3, fcnIf, QStringLiteral( "Conditionals" ), QString(), false, QSet<QString>(), true )

<< new QgsStaticExpressionFunction( QStringLiteral( "aggregate" ),
Expand Down
7 changes: 6 additions & 1 deletion tests/src/core/testqgsexpression.cpp
Expand Up @@ -1129,6 +1129,11 @@ class TestQgsExpression: public QObject
QTest::newRow( "coalesce null" ) << "coalesce(NULL)" << false << QVariant();
QTest::newRow( "coalesce mid-null" ) << "coalesce(1, NULL, 3)" << false << QVariant( 1 );
QTest::newRow( "coalesce exp" ) << "coalesce(NULL, 1+1)" << false << QVariant( 2 );
QTest::newRow( "nullif no substitution" ) << "nullif(3, '(none)')" << false << QVariant( 3 );
QTest::newRow( "nullif NULL" ) << "nullif(NULL, '(none)')" << false << QVariant();
QTest::newRow( "nullif substitute string" ) << "nullif('(none)', '(none)')" << false << QVariant();
QTest::newRow( "nullif substitute double" ) << "nullif(3.3, 3.3)" << false << QVariant();
QTest::newRow( "nullif substitute int" ) << "nullif(0, 0)" << false << QVariant();
QTest::newRow( "regexp match" ) << "regexp_match('abc','.b.')" << false << QVariant( 1 );
QTest::newRow( "regexp match invalid" ) << "regexp_match('abc DEF','[[[')" << true << QVariant();
QTest::newRow( "regexp match escaped" ) << "regexp_match('abc DEF','\\\\s[A-Z]+')" << false << QVariant( 4 );
Expand Down Expand Up @@ -1552,7 +1557,7 @@ class TestQgsExpression: public QObject
if ( featureMatched )
{
QgsFeature feat = res.value<QgsFeature>();
QCOMPARE( feat.id(), ( long long )featureId );
QCOMPARE( feat.id(), static_cast<QgsFeatureId>( featureId ) );
}
}

Expand Down

0 comments on commit dd0b33e

Please sign in to comment.