Incorrect result when having NULL within the CASE...WHEN clause

[YuanchengJiang]

To reproduce

CREATE TABLE test (c0 INT);
INSERT INTO test VALUES (NULL);
SELECT CASE WHEN c0 IS NULL THEN NULL ELSE c0 IN (0) END FROM test;

Result: [(False)] Expected Result: [(None)] or [(Null)]

QuestDB version: nightly

[AkashSingh0021]

Run the test case with the help of automation

[Rushichaudhari2003]

The reason for this discrepancy is that the CASE statement in the SELECT query is returning a boolean value (True or False) instead of NULL when c0 is NULL.

To fix this, we can modify the CASE statement to return NULL when c0 is NULL:

SELECT CASE WHEN c0 IS NULL THEN NULL ELSE c0 IN (0) END FROM test;

This will return [(None)] or [(Null)] as expected.

#include <stdio.h>

typedef struct { int c0; } Row;

int main() { // Create table printf("Creating table...\n"); // Insert values printf("Inserting values...\n"); Row test[] = {{0}, {NULL}}; int nrows = sizeof(test) / sizeof(Row); // Select query printf("Running query...\n"); for (int i = 0; i < nrows; i++) { Row row = test[i]; int result = (row.c0 == NULL) ? NULL : (row.c0 == 0); printf("c0 is %s in row %d\n", (result == NULL) ? "NULL" : (result) ? "true" : "false", i+1); } return 0; }

[kailashv2]

#include <stdio.h>

typedef struct { int c0; } Row;

int main() { // Create table printf("Creating table...\n"); // Insert values printf("Inserting values...\n"); Row test[] = {{0}, {NULL}}; int nrows = sizeof(test) / sizeof(Row); // Select query printf("Running query...\n"); for (int i = 0; i < nrows; i++) { Row row = test[i]; int result = (row.c0 == NULL) ? NULL : (row.c0 == 0); printf("c0 is %s in row %d\n", (result == NULL) ? "NULL" : (result) ? "true" : "false", i+1); } return 0; }