Skip to content

Commit 4757556

Browse files
matrivmatriv
committed
SQL: Implement NULLIF(expr1, expr2) function (#35826)
NULLIF returns null if the 2 expressions are equal or the expr1 otherwise. Closes: #35818
1 parent 3132c29 commit 4757556

15 files changed

Lines changed: 316 additions & 17 deletions

File tree

‎docs/reference/sql/functions/conditional.asciidoc‎

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -155,3 +155,38 @@ include-tagged::{sql-specs}/docs.csv-spec[nvlReturnFirst]
155155
----
156156
include-tagged::{sql-specs}/docs.csv-spec[nvlReturnSecond]
157157
----
158+
159+
160+
[[sql-functions-conditional-nullif]]
161+
==== `NULLIF`
162+
163+
.Synopsis
164+
[source, sql]
165+
----
166+
NULLIF ( expression<1>, expression<2> )
167+
----
168+
169+
*Input*:
170+
171+
<1> 1st expression
172+
173+
<2> 2nd expression
174+
175+
176+
*Output*: `null` if the 2 expressions are equal, otherwise the 1st expression.
177+
178+
.Description
179+
180+
Returns `null` when the two input expressions are equal and
181+
if not, it returns the 1st expression.
182+
183+
184+
["source","sql",subs="attributes,callouts,macros"]
185+
----
186+
include-tagged::{sql-specs}/docs.csv-spec[nullIfReturnFirst]
187+
----
188+
189+
["source","sql",subs="attributes,callouts,macros"]
190+
----
191+
include-tagged::{sql-specs}/docs.csv-spec[nullIfReturnNull]
192+
----

‎x-pack/plugin/sql/qa/src/main/resources/command.csv-spec‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -23,6 +23,7 @@ COALESCE |CONDITIONAL
2323
IFNULL |CONDITIONAL
2424
ISNULL |CONDITIONAL
2525
NVL |CONDITIONAL
26+
NULLIF |CONDITIONAL
2627
DAY |SCALAR
2728
DAYNAME |SCALAR
2829
DAYOFMONTH |SCALAR

‎x-pack/plugin/sql/qa/src/main/resources/docs.csv-spec‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -200,6 +200,7 @@ COALESCE |CONDITIONAL
200200
IFNULL |CONDITIONAL
201201
ISNULL |CONDITIONAL
202202
NVL |CONDITIONAL
203+
NULLIF |CONDITIONAL
203204
DAY |SCALAR
204205
DAYNAME |SCALAR
205206
DAYOFMONTH |SCALAR
@@ -1597,3 +1598,24 @@ SELECT NVL(null, 'search') AS "nvl";
15971598
search
15981599
// end::nvlReturnSecond
15991600
;
1601+
1602+
1603+
nullIfReturnFirst
1604+
// tag::nullIfReturnFirst
1605+
SELECT NULLIF('elastic', 'search') AS "nullif";
1606+
nullif
1607+
---------------
1608+
elastic
1609+
// end::nullIfReturnFirst
1610+
;
1611+
1612+
1613+
nullIfReturnNull
1614+
// tag::nullIfReturnNull
1615+
SELECT NULLIF('elastic', 'elastic') AS "nullif";
1616+
1617+
nullif:s
1618+
---------------
1619+
null
1620+
// end::nullIfReturnNull
1621+
;

‎x-pack/plugin/sql/qa/src/main/resources/null.sql-spec‎

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,4 +12,13 @@ coalesceWhere
1212
SELECT COALESCE(null, ABS(emp_no) + 1, 123) AS c FROM test_emp WHERE COALESCE(null, ABS(emp_no) + 1, 123, 321) > 100 ORDER BY emp_no NULLS FIRST LIMIT 5;
1313

1414
ifNullField
15-
SELECT IFNULL(null, ABS(emp_no) + 1) AS c FROM test_emp ORDER BY emp_no LIMIT 5;
15+
SELECT IFNULL(null, ABS(emp_no) + 1) AS "ifnull" FROM test_emp ORDER BY emp_no LIMIT 5;
16+
17+
nullIfField
18+
SELECT NULLIF(emp_no - 2 + 3, ABS(emp_no) + 1) AS "nullif1", NULLIF(emp_no + 1, emp_no - 1) as "nullif2" FROM test_emp ORDER BY emp_no LIMIT 5;
19+
20+
nullIfWhere
21+
SELECT NULLIF(10002, ABS(emp_no) + 1) AS c, emp_no FROM test_emp WHERE NULLIF(10003, ABS(emp_no) + 1) IS NOT NULL ORDER BY emp_no NULLS FIRST LIMIT 5;
22+
23+
nullIfHaving
24+
SELECT NULLIF(10030, ABS(MAX(emp_no)) + 1) AS nif FROM test_emp GROUP BY languages HAVING nif IS NOT NULL ORDER BY languages;

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/expression/function/FunctionRegistry.java‎

Lines changed: 20 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -83,7 +83,8 @@
8383
import org.elasticsearch.xpack.sql.expression.function.scalar.string.Substring;
8484
import org.elasticsearch.xpack.sql.expression.function.scalar.string.UCase;
8585
import org.elasticsearch.xpack.sql.expression.predicate.conditional.Coalesce;
86-
import org.elasticsearch.xpack.sql.expression.predicate.conditional.IFNull;
86+
import org.elasticsearch.xpack.sql.expression.predicate.conditional.IfNull;
87+
import org.elasticsearch.xpack.sql.expression.predicate.conditional.NullIf;
8788
import org.elasticsearch.xpack.sql.expression.predicate.operator.arithmetic.Mod;
8889
import org.elasticsearch.xpack.sql.parser.ParsingException;
8990
import org.elasticsearch.xpack.sql.tree.Location;
@@ -93,11 +94,13 @@
9394
import java.util.Arrays;
9495
import java.util.Collection;
9596
import java.util.HashMap;
97+
import java.util.HashSet;
9698
import java.util.LinkedHashMap;
9799
import java.util.List;
98100
import java.util.Locale;
99101
import java.util.Map;
100102
import java.util.Map.Entry;
103+
import java.util.Set;
101104
import java.util.TimeZone;
102105
import java.util.function.BiFunction;
103106
import java.util.regex.Pattern;
@@ -108,6 +111,15 @@
108111
import static java.util.stream.Collectors.toList;
109112

110113
public class FunctionRegistry {
114+
115+
private static final Set<String> EXCLUDE_FROM_NAME_NORMALIZATION = new HashSet<>();
116+
117+
static {
118+
EXCLUDE_FROM_NAME_NORMALIZATION.add(IfNull.class.getSimpleName());
119+
EXCLUDE_FROM_NAME_NORMALIZATION.add(NullIf.class.getSimpleName());
120+
}
121+
122+
111123
// list of functions grouped by type of functions (aggregate, statistics, math etc) and ordered alphabetically inside each group
112124
// a single function will have one entry for itself with its name associated to its instance and, also, one entry for each alias
113125
// it has with the alias name associated to the FunctionDefinition instance
@@ -146,7 +158,8 @@ private void defineDefaultFunctions() {
146158
// Scalar functions
147159
// conditional
148160
addToMap(def(Coalesce.class, Coalesce::new));
149-
addToMap(def(IFNull.class, IFNull::new, "ISNULL", "NVL"));
161+
addToMap(def(IfNull.class, IfNull::new, "ISNULL", "NVL"));
162+
addToMap(def(NullIf.class, NullIf::new));
150163
// Date
151164
addToMap(def(DayName.class, DayName::new, "DAYNAME"),
152165
def(DayOfMonth.class, DayOfMonth::new, "DAYOFMONTH", "DAY", "DOM"),
@@ -485,7 +498,11 @@ private interface CastFunctionBuilder<T> {
485498
}
486499

487500
private static String normalize(String name) {
488-
// translate CamelCase to camel_case
501+
if (EXCLUDE_FROM_NAME_NORMALIZATION.contains(name)) {
502+
return name.toUpperCase(Locale.ROOT);
503+
}
504+
505+
// translate CamelCase to CAMEL_CASE
489506
return StringUtils.camelCaseToUnderscore(name);
490507
}
491508
}

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/expression/function/scalar/Processors.java‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,7 @@
2626
import org.elasticsearch.xpack.sql.expression.gen.processor.HitExtractorProcessor;
2727
import org.elasticsearch.xpack.sql.expression.gen.processor.Processor;
2828
import org.elasticsearch.xpack.sql.expression.predicate.conditional.CoalesceProcessor;
29+
import org.elasticsearch.xpack.sql.expression.predicate.conditional.NullIfProcessor;
2930
import org.elasticsearch.xpack.sql.expression.predicate.logical.BinaryLogicProcessor;
3031
import org.elasticsearch.xpack.sql.expression.predicate.logical.NotProcessor;
3132
import org.elasticsearch.xpack.sql.expression.predicate.nulls.CheckNullProcessor;
@@ -61,6 +62,7 @@ public static List<NamedWriteableRegistry.Entry> getNamedWriteables() {
6162
// null
6263
entries.add(new Entry(Processor.class, CheckNullProcessor.NAME, CheckNullProcessor::new));
6364
entries.add(new Entry(Processor.class, CoalesceProcessor.NAME, CoalesceProcessor::new));
65+
entries.add(new Entry(Processor.class, NullIfProcessor.NAME, NullIfProcessor::new));
6466

6567
// arithmetic
6668
entries.add(new Entry(Processor.class, BinaryArithmeticProcessor.NAME, BinaryArithmeticProcessor::new));

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/expression/function/scalar/whitelist/InternalSqlScriptUtils.java‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@
2424
import org.elasticsearch.xpack.sql.expression.literal.IntervalDayTime;
2525
import org.elasticsearch.xpack.sql.expression.literal.IntervalYearMonth;
2626
import org.elasticsearch.xpack.sql.expression.predicate.conditional.CoalesceProcessor;
27+
import org.elasticsearch.xpack.sql.expression.predicate.conditional.NullIfProcessor;
2728
import org.elasticsearch.xpack.sql.expression.predicate.logical.BinaryLogicProcessor.BinaryLogicOperation;
2829
import org.elasticsearch.xpack.sql.expression.predicate.logical.NotProcessor;
2930
import org.elasticsearch.xpack.sql.expression.predicate.nulls.CheckNullProcessor.CheckNullOperation;
@@ -142,6 +143,10 @@ public static Object coalesce(List<Object> expressions) {
142143
return CoalesceProcessor.apply(expressions);
143144
}
144145

146+
public static Object nullif(Object left, Object right) {
147+
return NullIfProcessor.apply(left, right);
148+
}
149+
145150
//
146151
// Regex
147152
//

x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/expression/predicate/conditional/IFNull.java renamed to x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/expression/predicate/conditional/IfNull.java

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -15,22 +15,20 @@
1515

1616
/**
1717
* Variant of {@link Coalesce} with two args used by MySQL and ODBC.
18-
*
19-
* Name is `IFNull` to avoid having it registered as `IF_NULL` instead of `IFNULL`.
2018
*/
21-
public class IFNull extends Coalesce {
19+
public class IfNull extends Coalesce {
2220

23-
public IFNull(Location location, Expression first, Expression second) {
21+
public IfNull(Location location, Expression first, Expression second) {
2422
super(location, Arrays.asList(first, second));
2523
}
2624

2725
@Override
2826
public Expression replaceChildren(List<Expression> newChildren) {
29-
return new IFNull(location(), newChildren.get(0), newChildren.get(1));
27+
return new IfNull(location(), newChildren.get(0), newChildren.get(1));
3028
}
3129

3230
@Override
33-
protected NodeInfo<IFNull> info() {
34-
return NodeInfo.create(this, IFNull::new, children().get(0), children().get(1));
31+
protected NodeInfo<IfNull> info() {
32+
return NodeInfo.create(this, IfNull::new, children().get(0), children().get(1));
3533
}
3634
}

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/expression/predicate/conditional/NullIf.java‎

Lines changed: 88 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,88 @@
1+
/*
2+
* Copyright Elasticsearch B.V. and/or licensed to Elasticsearch B.V. under one
3+
* or more contributor license agreements. Licensed under the Elastic License;
4+
* you may not use this file except in compliance with the Elastic License.
5+
*/
6+
7+
package org.elasticsearch.xpack.sql.expression.predicate.conditional;
8+
9+
import org.elasticsearch.xpack.sql.expression.Expression;
10+
import org.elasticsearch.xpack.sql.expression.Expressions;
11+
import org.elasticsearch.xpack.sql.expression.gen.pipeline.Pipe;
12+
import org.elasticsearch.xpack.sql.expression.gen.script.ParamsBuilder;
13+
import org.elasticsearch.xpack.sql.expression.gen.script.ScriptTemplate;
14+
import org.elasticsearch.xpack.sql.tree.Location;
15+
import org.elasticsearch.xpack.sql.tree.NodeInfo;
16+
import org.elasticsearch.xpack.sql.type.DataType;
17+
18+
import java.util.Arrays;
19+
import java.util.List;
20+
21+
import static org.elasticsearch.xpack.sql.expression.gen.script.ParamsBuilder.paramsBuilder;
22+
23+
/**
24+
* Accepts 2 arguments of any data type and returns null if they are equal,
25+
* and the 1st argument otherwise.
26+
*/
27+
public class NullIf extends ConditionalFunction {
28+
29+
private DataType dataType;
30+
31+
public NullIf(Location location, Expression left, Expression right) {
32+
super(location, Arrays.asList(left, right));
33+
}
34+
35+
@Override
36+
protected NodeInfo<? extends NullIf> info() {
37+
return NodeInfo.create(this, NullIf::new, children().get(0), children().get(1));
38+
}
39+
40+
@Override
41+
public Expression replaceChildren(List<Expression> newChildren) {
42+
return new NullIf(location(), newChildren.get(0), newChildren.get(1));
43+
}
44+
45+
@Override
46+
protected TypeResolution resolveType() {
47+
dataType = children().get(0).dataType();
48+
return TypeResolution.TYPE_RESOLVED;
49+
}
50+
51+
@Override
52+
public DataType dataType() {
53+
return dataType;
54+
}
55+
56+
@Override
57+
public boolean foldable() {
58+
return Expressions.foldable(children());
59+
}
60+
61+
@Override
62+
public boolean nullable() {
63+
return true;
64+
}
65+
66+
@Override
67+
public Object fold() {
68+
return NullIfProcessor.apply(children().get(0).fold(), children().get(1).fold());
69+
}
70+
71+
@Override
72+
public ScriptTemplate asScript() {
73+
ScriptTemplate left = asScript(children().get(0));
74+
ScriptTemplate right = asScript(children().get(1));
75+
String template = "{sql}.nullif(" + left.template() + "," + right.template() + ")";
76+
ParamsBuilder params = paramsBuilder();
77+
params.script(left.params());
78+
params.script(right.params());
79+
80+
return new ScriptTemplate(template, params.build(), dataType);
81+
}
82+
83+
@Override
84+
protected Pipe makePipe() {
85+
return new NullIfPipe(location(), this,
86+
Expressions.pipe(children().get(0)), Expressions.pipe(children().get(1)));
87+
}
88+
}

‎x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/expression/predicate/conditional/NullIfPipe.java‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
/*
2+
* Copyright Elasticsearch B.V. and/or licensed to Elasticsearch B.V. under one
3+
* or more contributor license agreements. Licensed under the Elastic License;
4+
* you may not use this file except in compliance with the Elastic License.
5+
*/
6+
7+
package org.elasticsearch.xpack.sql.expression.predicate.conditional;
8+
9+
import org.elasticsearch.xpack.sql.expression.Expression;
10+
import org.elasticsearch.xpack.sql.expression.gen.pipeline.BinaryPipe;
11+
import org.elasticsearch.xpack.sql.expression.gen.pipeline.Pipe;
12+
import org.elasticsearch.xpack.sql.expression.gen.processor.Processor;
13+
import org.elasticsearch.xpack.sql.tree.Location;
14+
import org.elasticsearch.xpack.sql.tree.NodeInfo;
15+
16+
public class NullIfPipe extends BinaryPipe {
17+
18+
public NullIfPipe(Location location, Expression expression, Pipe left, Pipe right) {
19+
super(location, expression, left, right);
20+
}
21+
22+
@Override
23+
protected BinaryPipe replaceChildren(Pipe left, Pipe right) {
24+
return new NullIfPipe(location(), expression(), left, right);
25+
}
26+
27+
@Override
28+
protected NodeInfo<NullIfPipe> info() {
29+
return NodeInfo.create(this, NullIfPipe::new, expression(), children().get(0), children().get(1));
30+
}
31+
32+
@Override
33+
public Processor asProcessor() {
34+
return new NullIfProcessor(left().asProcessor(), right().asProcessor());
35+
}
36+
}

0 commit comments

Comments
 (0)