Dynamic 'in' clause with tuple match

[Amit Sharma]

We use parameter replacement to build dynamic queries. 

We have a table that stores customer returns, each return has a return reason bucket (RTN_BUCKET) and a return reason code (RTN_RSN_CODE). We have a UI that filters the data on some fields. Fields in the filter are optional. We are able to solve this for simple field comparison, however we are not able to figure out how to achieve this for tuple matches  like (RTN_BUCKET, RTN_RSN_CODE). 

Here is a sample query - 

select * From returns

where RTN_DT > :someDateParam

and  (isNull(:upc) OR UPC = :upc)  -- works for UPC if we replace the value with null

and  (isNull(:reasonFilter) OR (RTN_BUCKET, RTN_REASON_CODE) in (:reasonFilter))

We were planning on passing reason filter as - 

reasonFilter: "('damaged','package damaged'), ('damaged', 'item damaged'))

Please let me know if there is any other innovative way to solve this. 

Thanks,

Amit

[Amit Sharma]

Can someone please help with this ? 

[Denis Zhuravlev]

Big mistake.

Ch does not have such optimization (true or somecol = ....) and validates (reads at least this column) all conditions.

create table A(A Int64, S String) Engine=MergeTree order by A;

insert into A select number % 65657, arrayMap(i->toString(number/i), range(20)) from numbers(10000000);

select count() from A where A > 1

Elapsed: 0.005 sec.

select count() from A where A > 0 and (isNull(null) or S=null)

Elapsed: 0.547 sec.

Kind regards,

Denis Zhuravlev.

Tel: +1 (506) 721-4271
email: denis.z...@gmail.com
Skype: den_crane
https://ca.linkedin.com/pub/denis-zhuravlev/8/a45/953

--
You received this message because you are subscribed to the Google Groups "ClickHouse" group.
To unsubscribe from this group and stop receiving emails from it, send an email to clickhouse+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/clickhouse/38493aca-dffc-4568-a575-d7eaf72278e2n%40googlegroups.com.