I'm trying to construct an SQL SELECT statement that takes parameters which, if NULL, do not influence the result. Of course performance should be good as well, i. e. indices should be used. The reason for this is that I want to use stored procedures (CG-SQL), so the SQL statements are not dynamically constructed to only contain expressions for non-NULL parameters.
In this simple example with just one column in the table (apart from the rowid), the first select of course yields no rows if the parameter ?1 is NULL, but uses the index. The second works as expected (i. e. would yield all rows if ?1 IS NULL), but performs a full table scan. The third is the same as the second but with literals instead of parameters; even then a table scan is performed.
```
CREATE TABLE t1 (a TEXT NOT NULL);
CREATE INDEX t1_a ON t1(a);
INSERT INTO t1(a) VALUES ('a1'), ('a2'), ('a3');
.parameter init
.parameter set ?1 'a1'
SELECT * FROM t1 WHERE a=?1;
SELECT * FROM t1 WHERE (?1 IS NULL OR a=?1);
SELECT * FROM t1 WHERE ('a2' IS NULL OR a='a2');
SELECT * FROM t1 WHERE a='a2' OR FALSE;
SELECT * FROM t1 WHERE a='a2' OR TRUE;
```
The second SELECT could be optimized to an index SEARCH after binding, the third right away and the fourth as well. The last one is being optimized and doesn't check a to be 'a2', in fact it has no condition at all (according to EXPLAIN).
Is there any way to arrive at an index-using query plan for SELECT Nr. 2 to 4 (I guess not), or would this require an improvement to the query planner?
This is an example of an actually useful query to illustrate what I'm trying to arrive at:
```
SELECT * FROM person
WHERE (?1 IS NULL OR firstname LIKE ?1)
AND (?2 IS NULL OR lastname LIKE ?2)
AND (?3 IS NULL OR birthdate == ?3)
AND (?4 IS NULL OR maidenname LIKE ?4)
ORDER BY lastname, firstname;
```
Thanks.