Skip to content

Commit b30aa50

Browse files
kanoshioukanoshiou
authored
ESQL: Fix null comparison type checking (elastic#140660)
1 parent bd36d38 commit b30aa50

10 files changed

Lines changed: 837 additions & 101 deletions

File tree

‎docs/changelog/140660.yaml‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
pr: 140660
2+
summary: "ESQL: Fix null comparison type checking"
3+
area: ES|QL
4+
type: bug
5+
issues:
6+
- 140460

‎x-pack/plugin/esql/qa/testFixtures/src/main/resources/null.csv-spec‎

Lines changed: 368 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -216,3 +216,371 @@ emp_no:integer | first_name:keyword | last_name:keyword
216216
10021 | Ramzi | Erde
217217
10022 | Shahaf | Famili
218218
;
219+
220+
###############################################
221+
# Null comparison tests
222+
###############################################
223+
224+
# Field compared with null returns empty result
225+
compareFieldWithNull
226+
required_capability: fix_null_comparison_type_check
227+
228+
FROM employees | WHERE emp_no == null | KEEP emp_no, first_name | LIMIT 1
229+
;
230+
231+
emp_no:integer | first_name:keyword
232+
;
233+
234+
235+
# ROW with null compared to string value
236+
rowNullCompareWithString
237+
required_capability: fix_null_comparison_type_check
238+
239+
ROW x = null, y = "foo" | WHERE x == y | KEEP x, y
240+
;
241+
242+
x:null | y:keyword
243+
;
244+
245+
246+
# null == integer evaluates to null
247+
rowNullEqualsInteger
248+
required_capability: fix_null_comparison_type_check
249+
250+
ROW x = null, y = 1 | EVAL result = x == y | KEEP result
251+
;
252+
253+
result:boolean
254+
null
255+
;
256+
257+
258+
# null != integer evaluates to null
259+
rowNullNotEqualsInteger
260+
required_capability: fix_null_comparison_type_check
261+
262+
ROW x = null, y = 1 | EVAL result = x != y | KEEP result
263+
;
264+
265+
result:boolean
266+
null
267+
;
268+
269+
270+
# null > integer evaluates to null
271+
rowNullGreaterThanInteger
272+
required_capability: fix_null_comparison_type_check
273+
274+
ROW x = null, y = 1 | EVAL result = x > y | KEEP result
275+
;
276+
277+
result:boolean
278+
null
279+
;
280+
281+
282+
# null < integer evaluates to null
283+
rowNullLessThanInteger
284+
required_capability: fix_null_comparison_type_check
285+
286+
ROW x = null, y = 1 | EVAL result = x < y | KEEP result
287+
;
288+
289+
result:boolean
290+
null
291+
;
292+
293+
294+
# null >= integer evaluates to null
295+
rowNullGreaterThanOrEqualInteger
296+
required_capability: fix_null_comparison_type_check
297+
298+
ROW x = null, y = 1 | EVAL result = x >= y | KEEP result
299+
;
300+
301+
result:boolean
302+
null
303+
;
304+
305+
306+
# null <= integer evaluates to null
307+
rowNullLessThanOrEqualInteger
308+
required_capability: fix_null_comparison_type_check
309+
310+
ROW x = null, y = 1 | EVAL result = x <= y | KEEP result
311+
;
312+
313+
result:boolean
314+
null
315+
;
316+
317+
318+
# Two nulls comparison evaluates to null
319+
rowTwoNullsComparison
320+
required_capability: fix_null_comparison_type_check
321+
322+
ROW x = null, y = null | EVAL result = x == y | KEEP result
323+
;
324+
325+
result:boolean
326+
null
327+
;
328+
329+
330+
# Value compared with null (null on right side)
331+
rowValueCompareWithNull
332+
required_capability: fix_null_comparison_type_check
333+
334+
ROW x = 1, y = null | EVAL result = x == y | KEEP result
335+
;
336+
337+
result:boolean
338+
null
339+
;
340+
341+
342+
# null compared with keyword
343+
rowNullCompareWithKeyword
344+
required_capability: fix_null_comparison_type_check
345+
346+
ROW x = null, y = "test" | EVAL result = x == y | KEEP result
347+
;
348+
349+
result:boolean
350+
null
351+
;
352+
353+
354+
# null compared with datetime
355+
rowNullCompareWithDatetime
356+
required_capability: fix_null_comparison_type_check
357+
358+
ROW x = null, y = now() | EVAL result = x == y | KEEP result
359+
;
360+
361+
result:boolean
362+
null
363+
;
364+
365+
366+
###############################################
367+
# Complex null comparison tests (derived null)
368+
###############################################
369+
370+
# null derived from arithmetic: null + 1 produces null, then compared with integer
371+
rowDerivedNullFromAdditionCompare
372+
required_capability: fix_null_comparison_type_check
373+
374+
ROW x = null, y = 1, z = 2 | EVAL t = x + 1 > z | KEEP t
375+
;
376+
377+
t:boolean
378+
null
379+
;
380+
381+
382+
# null derived from multiplication: null * 2 compared with integer
383+
rowDerivedNullFromMultiplicationCompare
384+
required_capability: fix_null_comparison_type_check
385+
386+
ROW x = null, y = 2 | EVAL t = x * 2 == y | KEEP t
387+
;
388+
389+
t:boolean
390+
null
391+
;
392+
393+
394+
# chained null propagation: null + 1 - 2 compared with value
395+
rowChainedNullPropagationCompare
396+
required_capability: fix_null_comparison_type_check
397+
398+
ROW x = null, y = 5 | EVAL t = (x + 1 - 2) >= y | KEEP t
399+
;
400+
401+
t:boolean
402+
null
403+
;
404+
405+
406+
# null derived from function: to_integer(null) compared with value
407+
rowDerivedNullFromFunctionCompare
408+
required_capability: fix_null_comparison_type_check
409+
410+
ROW y = 10 | EVAL t = to_integer(null) < y | KEEP t
411+
;
412+
413+
t:boolean
414+
null
415+
;
416+
417+
418+
# WHERE filters out all rows when derived null is used in condition
419+
rowDerivedNullInWhereClause
420+
required_capability: fix_null_comparison_type_check
421+
422+
ROW x = null, y = 1, z = 2 | WHERE x + 1 > z | KEEP x, y, z
423+
;
424+
425+
x:null | y:integer | z:integer
426+
;
427+
428+
429+
# null propagation through nested arithmetic on both sides
430+
rowDerivedNullBothSidesCompare
431+
required_capability: fix_null_comparison_type_check
432+
433+
ROW a = null, b = null | EVAL t = (a + 1) == (b + 2) | KEEP t
434+
;
435+
436+
t:boolean
437+
null
438+
;
439+
440+
441+
# non-null side produces value, null side propagates null
442+
rowDerivedNullOneSideCompare
443+
required_capability: fix_null_comparison_type_check
444+
445+
ROW x = null, y = 3 | EVAL left_val = x + 10, right_val = y * 2, t = left_val > right_val | KEEP left_val, right_val, t
446+
;
447+
448+
left_val:integer | right_val:integer | t:boolean
449+
null | 6 | null
450+
;
451+
452+
453+
# null from division by zero is different, but null literal through arithmetic should propagate
454+
rowDerivedNullCompareWithString
455+
required_capability: fix_null_comparison_type_check
456+
457+
ROW x = null, y = "hello" | EVAL t = concat(x, " world") == y | KEEP t
458+
;
459+
460+
t:boolean
461+
null
462+
;
463+
464+
465+
###############################################
466+
# Null in IN expression tests
467+
###############################################
468+
469+
# integer value IN list containing null — value matches, result is true
470+
rowIntegerInWithNullMatch
471+
required_capability: fix_null_comparison_type_check
472+
473+
ROW x = 1 | EVAL result = x IN (1, null) | KEEP result
474+
;
475+
476+
result:boolean
477+
true
478+
;
479+
480+
481+
# integer value IN list containing only null — no match, result is null
482+
rowIntegerInWithOnlyNull
483+
required_capability: fix_null_comparison_type_check
484+
485+
ROW x = 1 | EVAL result = x IN (null) | KEEP result
486+
;
487+
488+
result:boolean
489+
null
490+
;
491+
492+
493+
# integer value IN list with null — value does not match non-null entries, result is null
494+
rowIntegerInWithNullNoMatch
495+
required_capability: fix_null_comparison_type_check
496+
497+
ROW x = 5 | EVAL result = x IN (1, 2, null) | KEEP result
498+
;
499+
500+
result:boolean
501+
null
502+
;
503+
504+
505+
# null value IN list of integers — result is null
506+
rowNullInIntegerList
507+
required_capability: fix_null_comparison_type_check
508+
509+
ROW x = null | EVAL result = x IN (1, 2, 3) | KEEP result
510+
;
511+
512+
result:boolean
513+
null
514+
;
515+
516+
517+
# null value IN list containing null — result is null (null == null is null, not true)
518+
rowNullInNullList
519+
required_capability: fix_null_comparison_type_check
520+
521+
ROW x = null | EVAL result = x IN (null) | KEEP result
522+
;
523+
524+
result:boolean
525+
null
526+
;
527+
528+
529+
# keyword value IN list containing null — value matches
530+
rowKeywordInWithNullMatch
531+
required_capability: fix_null_comparison_type_check
532+
533+
ROW x = "foo" | EVAL result = x IN ("foo", null) | KEEP result
534+
;
535+
536+
result:boolean
537+
true
538+
;
539+
540+
541+
# keyword value IN list with null — no match
542+
rowKeywordInWithNullNoMatch
543+
required_capability: fix_null_comparison_type_check
544+
545+
ROW x = "foo" | EVAL result = x IN ("bar", null) | KEEP result
546+
;
547+
548+
result:boolean
549+
null
550+
;
551+
552+
553+
# field IN list with null — matching rows returned
554+
fieldInWithNull
555+
required_capability: fix_null_comparison_type_check
556+
557+
FROM employees | WHERE emp_no IN (10001, 10002, null) | SORT emp_no | KEEP emp_no | LIMIT 3
558+
;
559+
560+
emp_no:integer
561+
10001
562+
10002
563+
;
564+
565+
566+
# field IN list with only null — no rows match
567+
fieldInWithOnlyNull
568+
required_capability: fix_null_comparison_type_check
569+
570+
FROM employees | WHERE emp_no IN (null) | KEEP emp_no | LIMIT 1
571+
;
572+
573+
emp_no:integer
574+
;
575+
576+
577+
# WHERE with IN containing null filters correctly
578+
fieldInWithNullWhereFilter
579+
required_capability: fix_null_comparison_type_check
580+
581+
FROM employees | WHERE emp_no IN (10001, null) AND first_name IS NOT NULL | SORT emp_no | KEEP emp_no, first_name | LIMIT 1
582+
;
583+
584+
emp_no:integer | first_name:keyword
585+
10001 | Georgi
586+
;

0 commit comments

Comments
 (0)