dynamobi/dynamobi — Issue #83: [LDB-240] should allow dynamic parameter directly beneath IS NULL
Author: dynamobi-build
Created: 2011-12-30 02:11:41+00:00
State: open
Labels: LDB

[reporter="slawo", created="Sun, 27 Feb 2011 16:42:37 -0500 (GMT-05:00)"]
When binding variables in JDBC LucidDB seems to get the operator precedence differently. Consider the following setup:

CREATE SCHEMA "TEST";

CREATE TABLE TEST.DIM_TEST(

TEST_ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,

TEST VARCHAR(100)

);

SELECT TEST_ID FROM TEST.DIM_TEST WHERE ( ( TEST = ? ) OR ( TEST IS NULL AND ? IS NULL ) );

Now if the string 'test value' is bound to both parameters, the query will fail with the error: Value 'test value' cannot be converted to parameter of type BOOLEAN

Note: The behavior is correct if the string is put verbatim into the query

I'm attaching a self-contained PDI 4.x transformation that will show the behavior.

Cheers

Slawo