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

Can not use NULLs in INSERT VALUES #4912

Closed
big-andy-coates opened this issue Mar 27, 2020 · 2 comments · Fixed by #5019
Closed

Can not use NULLs in INSERT VALUES #4912

big-andy-coates opened this issue Mar 27, 2020 · 2 comments · Fixed by #5019
Labels
bug

Comments

@big-andy-coates
Copy link
Contributor

Running:

INSERT INTO FOO (colA, colB) VALUES (NULL, NULL);

Should work, but fails with NPE.
@big-andy-coates big-andy-coates mentioned this issue Mar 27, 2020
Closed
2 tasks
@archy-bold
Copy link

archy-bold commented Apr 7, 2020 •
edited
Loading

I've found you can use the CAST() function to achieve this with primitives, but not sure if it can be used in all statements. It certainly doesn't work for non-primitive types eg STRUCT

CAST(null AS VARCHAR)

big-andy-coates added a commit to big-andy-coates/ksql that referenced this issue Apr 7, 2020
@big-andy-coates
fix: improve handling of NULLs
1a1153b 
fixes: confluentinc#4912 and may other bugs around use of NULL.

fixes a few issues with NULLs in ksqlDB. You can now:

 - insert NULL values using `INSERT INTO`, for example `INSERT INTO foo VALUES (NULL, NULL);`, including use of `ARRAY`, `MAP` & `STRUCT` constructors with `NULL`s.
 - create MAPs with null keys and values, for example `MAP('k0' := NULL, CAST(NULL AS STRING) := 10)`.  (At least one key needs to be non-null or CAST so that ksqlDB can determine the schema of the map).
 - CAST NULL to any type, including the complex types `ARRAY`, `MAP` and `STRUCT`.

Also, better error messages, on:

 - Pull query on NULL key.  Previously, blew up for non-string keys.
 - Pull query with non-literal expression for the key. Previously blew up.
@big-andy-coates big-andy-coates mentioned this issue Apr 7, 2020
Merged
2 tasks
big-andy-coates added a commit that referenced this issue Apr 14, 2020
@big-andy-coates
fix: improve handling of NULLs (#5019)
c53dd68 
* fix: improve handling of NULLs

fixes: #4912 and may other bugs around use of NULL.

fixes a few issues with NULLs in ksqlDB. You can now:

 - insert NULL values using `INSERT INTO`, for example `INSERT INTO foo VALUES (NULL, NULL);`, including use of `ARRAY`, `MAP` & `STRUCT` constructors with `NULL`s.
 - create MAPs with null keys and values, for example `MAP('k0' := NULL, CAST(NULL AS STRING) := 10)`.  (At least one key needs to be non-null or CAST so that ksqlDB can determine the schema of the map).
 - CAST NULL to any type, including the complex types `ARRAY`, `MAP` and `STRUCT`.

Also, better error messages, on:

 - Pull query on NULL key.  Previously, blew up for non-string keys.
 - Pull query with non-literal expression for the key. Previously blew up.

Co-authored-by: Andy Coates <[email protected]>
@deuscapturus
Copy link

I've found you can use the CAST() function to achieve this with primitives, but not sure if it can be used in all statements. It certainly doesn't work for non-primitive types eg STRUCT

CAST(null AS VARCHAR)

I was able to INSERT INTO SELECT and set null values for STRUCTS and other non-primitive types with CAST() as of at least v0.27.2

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug
Projects
None yet
Milestone
No milestone
3 participants
@deuscapturus @archy-bold @big-andy-coates