Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Incorrect result when having NULL with IN operation #4496

Closed
1 task done
YuanchengJiang opened this issue May 13, 2024 · 1 comment · Fixed by #4499
Closed
1 task done

Incorrect result when having NULL with IN operation #4496

YuanchengJiang opened this issue May 13, 2024 · 1 comment · Fixed by #4499
Assignees
@nwoolmer
Labels
Bug Incorrect or unexpected behavior SQL Issues or changes relating to SQL execution

Comments

@YuanchengJiang
Copy link

To reproduce

Q1:

SELECT NULL IN (NULL)

Result: True. No problem

Q2:

SELECT NULL IN ('0', NULL)

Result: False. Wondering why additional element in list leads to False?

QuestDB version:

7.4

OS, in case of Docker specify Docker and the Host OS:

ubuntu 22

File System, in case of Docker specify Host File System:

ext4

Full Name:

Yuancheng Jiang

Affiliation:

National University of Singapore

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

  • Yes, I have

Additional context

No response
@nwoolmer
Copy link
Contributor

nwoolmer commented May 13, 2024 •
edited 

SELECT NULL IN ('0', NULL) -- false
SELECT NULL IN (null::string, NULL) -- true
SELECT NULL IN (NULL, '0') -- false

NULL values get added to the deferred list:

questdb/core/src/main/java/io/questdb/griffin/engine/functions/bool/InStrFunctionFactory.java

Lines 72 to 78 in 4d5890b

if (func.isRuntimeConstant()) { // bind variables
if (deferredValues == null) {
deferredValues = new ObjList<>();
}
deferredValues.add(func);
continue;
}

Deferred list never gets into the set, so lookup fails:

questdb/core/src/main/java/io/questdb/griffin/engine/functions/bool/InStrFunctionFactory.java

Lines 105 to 110 in 4d5890b

public Func(Function arg, CharSequenceHashSet set, ObjList<Function> deferredValues) {
this.arg = arg;
this.set = set;
this.deferredValues = deferredValues;
this.deferredSet = deferredValues != null ? new CharSequenceHashSet() : null;
}

questdb/core/src/main/java/io/questdb/griffin/engine/functions/bool/InStrFunctionFactory.java

Lines 118 to 122 in 4d5890b

public boolean getBool(Record rec) {
CharSequence val = arg.getStrA(rec);
return set.contains(val)
|| (deferredSet != null && deferredSet.contains(val));
}

@nwoolmer nwoolmer added Bug Incorrect or unexpected behavior SQL Issues or changes relating to SQL execution labels May 13, 2024
@nwoolmer nwoolmer self-assigned this May 13, 2024
@nwoolmer nwoolmer mentioned this issue May 13, 2024
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@nwoolmer nwoolmer

Labels
Bug Incorrect or unexpected behavior SQL Issues or changes relating to SQL execution
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

fix(sql): IN with NULLs and STRINGs sometimes may return the wrong result questdb/questdb
2 participants
@nwoolmer @YuanchengJiang