Forum: Building VoltDB Applications

Post: selecting by null values?

selecting by null values?
caravone
Sep 27, 2010
I have a use case where I need a filter like: ... WHERE partitioned_col = ? AND other_col IS NULL ...

Whenever I try to compile, I get:

Error: "Unsupported Operation: 47"

Is there any way to test for a null column value in VoltDB? If not, I suppose I could make an extra column (other_col_is_null) to solve the problem.
re: selecting by null values?
tcallaghan
Sep 27, 2010
We do not currently support the "IS NULL" SQL operator.

For now, you can get "IS NULL" functionality as follows:

1. Add "import org.voltdb.VoltType;" to your stored procedure.
2. Change your SQL statement to "... WHERE partitioned_col = ? AND other_col = ? ..."
3. Pass the appropriate data type's NULL representation (for "other_col") as a parameter when you queue the SQL statement. If you were using a BIGINT column you would use VoltType.NULL_BIGINT. Your call would look similar to "voltQueueSQL(sqlStmt, parmPartitionCol, VoltType.NULL_BIGINT).

The full list of NULL representations is available in src/frontend/org/voltdb/VoltType.java

-Tim
Thanks. That's just what I
caravone
Sep 27, 2010
We do not currently support the "IS NULL" SQL operator.

For now, you can get "IS NULL" functionality as follows:

1. Add "import org.voltdb.VoltType;" to your stored procedure.
2. Change your SQL statement to "... WHERE partitioned_col = ? AND other_col = ? ..."
3. Pass the appropriate data type's NULL representation (for "other_col") as a parameter when you queue the SQL statement. If you were using a BIGINT column you would use VoltType.NULL_BIGINT. Your call would look similar to "voltQueueSQL(sqlStmt, parmPartitionCol, VoltType.NULL_BIGINT).

The full list of NULL representations is available in src/frontend/org/voltdb/VoltType.java

-Tim


Thanks. That's just what I was looking for.
desheikh
Jan 14, 2015
I could make an extra column BUT THANKS...
pmartel
Jan 14, 2015
To: desheikh/OMAIR

Please note that you are replying to a very old thread that contains outdated information.
Are you trying to research a specific issue of your own with a more recent version of the product?
If you'd like to provide details, we can try to help you with it.