Using NULL values for positional parameter arguments

SQL++
,
1

I wanted to create a new topic to avoid cluttering the other discussion. In reference to your statement from this post:

@vsr1:

“Best optimal way, if possible use 2 statements (one null , non-null) because when named/positional parameters planner has to generate plan generically assume all possible cases.
i.e. $propCode is NULL it needs all rows from indexer with out field value
$propCode is not null only subset even one push to indexer due to OR other one produces all rows from indexer.”

I have parameters coming in from a REST API endpoint in a Java application. Some of the parameters can be NULL and I want to build a query that can account for that.

Using the Java 3.4.7 SDK with the query below, I have to account for fields that can be NULL such as propCode, roomNumber, macAddress. If I understand correctly, the query planner will generate all possible combinations of every parameter value, grabbing all rows from the indexer for each possible NULL value?

final String statement =
        "SELECT document.*,"
            + "       (SELECT c.*,"
            + "                  (SELECT s.*"
            + "                      FROM c.sessions AS s"
            + "                      WHERE s.duration >= $minDuration ) AS sessions"
            + "           FROM document.channels AS c"
            + "           WHERE c.channelName = $channelName"
            + "               AND c.channelId = $channelId ) AS channels"
            + " FROM tvSessions AS document"
            + " WHERE document.date BETWEEN $startDate AND $endDate"
            + "     AND document.source = 'linearTv'"
            + "     AND ($propCode IS NULL OR document.propCode = $propCode)"
            + "     AND ($roomNumber IS NULL OR document.roomNumber = $roomNumber)"
            + "     AND ($macAddress IS NULL OR document.macAddress = $macAddress)"
            + "     AND ANY c IN document.channels SATISFIES c.channelName = $channelName"
            + "     AND c.channelId = $channelId"
            + "     AND ANY s IN c.sessions SATISFIES s.duration >= $minDuration "
            + "     AND s.isOccupied = 1 END END"
            + " ORDER BY document.date DESC";

My current indexes are on date, source, propCode, roomNumber and macAddress. You mention the best thing to do is to make two statements. One omitting the parameter value for the NULL case and one with the parameter for the NON-NULL case?

This leads to the power set 2^n, n being the number of possible NULL parameters, number of statements. Is there a recommended way to build these dynamically? (Basic string concatenation or other?)

2

That is only

($propCode IS NULL OR document.propCode = $propCode)

As u see it is OR, first one not depend on any document field. That is reason.

Best way do this dynamic build of statement
Wait till parameters available.

select …
where = “WHERE document.date BETWEEN $startDate AND $endDate”
+ " AND document.source = ‘linearTv’"
+ " AND ANY c IN document.channels SATISFIES c.channelName = $channelName"
+ " AND c.channelId = $channelId"
+ " AND ANY s IN c.sessions SATISFIES s.duration >= $minDuration "
+ " AND s.isOccupied = 1 END END"
if pocode is not null {
where += “document.propCode = $propCode”
add to named parameter list
}
…
Add where clause to select
execute with parameter

3

Other option is
try adhoc=true it should prepare every time and planner takes care of it

alos set parameter value to lower case null JSON

4

Ok, so building the statement string dynamically, where possible, based on the available non-null values is the best solution. Currently, .adhoc(false) with this QueryOptions argument approach.

The other option you mention is if I want to have optional null parameters values, use .adhoc(true) because the query planner will take care of it, albeit in an inefficient way?

5

Yes.
If assume statement you execute hundreds/thousands of time might choose option 1 so that you save prepare time.
Otherwise option 2. During prepare time it has parameters and it can drop some preidcates based on true/false and build efficient plan
ex: AND ($propCode IS NULL OR document.propCode = $propCode)

$propCode value is null, first OR true and second or redundant , whole AND is not needed
$propcCoe value is not null, first OR is false and which can be removed becomes AND document.propCode = $propCode, planner can pick right index.

6

Create the statements in a nested for loop.

7

Thanks for the input. The problem comes from a set of api endpoints that have 12-14 search parameters, 5-6 which can be null, and relate to logic in multiple nested collections.

8

There only needs to be variations for the parameters which can be null.

Nested loops will work for any number of parameters. And since output is O(2^n) and nested loops are O(2^n), nested loops are optimal. Note that the fixed parts of the queries do not need to be regenerated.

If you’re asking if I can think of a way “to generate all the possible variations without actually generating all the possible variations?”, my answer is “no”.

use .adhoc(true) because the query planner will take care of it, albeit in an inefficient way?

Instead of the query planner building the query once for each variation (and caching it), the query planner will build the query for each execution (and discard it after execution). So if you had many (hundreds? thousands?) of variations that might only each be executed a few times - you may want to not bother caching/reusing.

9

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.