You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by stanilovsky evgeny <es...@gridgain.com> on 2021/06/11 14:47:18 UTC

[QUESTION] Calcite 1.27.0 Potential Sarg problems

Hello !
After 1.27.0 upgrading i found problems with correctness of condition  
definition.
I have simple query :
SELECT * FROM products WHERE name = 'Canon' OR category = 'Video'

and table like :
         5, "Video", 2, "Camera Media", 21, "Media 3"
         6, "Video", 2, "Camera Lens", 22, "Lens 3"
         7, "Video", 1, null, 0, "Canon"

I suppose to obtain plan with 2 scans and UnionAll and its all ok here,  
but conditions are erroneous:

calcite 1.26
cond:=($t1, _UTF-8'Video')
cond:AND(=($t5, _UTF-8'Canon'), OR(IS NULL($t1), <>($t1, _UTF-8'Video')))

condition:
AND(SEARCH($1, Sarg[_UTF-8'Video':VARCHAR CHARACTER SET "UTF-8"]:VARCHAR  
CHARACTER SET "UTF-8"), SEARCH($5, Sarg[(-∞.._UTF-8'Canon':VARCHAR  
CHARACTER SET "UTF-8"), (_UTF-8'Canon':VARCHAR CHARACTER SET "UTF-8"..+∞),  
null]:VARCHAR CHARACTER SET "UTF-8"))

calcite 1.27
cond:AND(=($t5, _UTF-8'Canon'), OR(IS NULL($t1), <>($t1, _UTF-8'Video')))
cond:AND(=($t1, _UTF-8'Video'), OR(IS NULL($t5), <>($t5, _UTF-8'Canon')))

condition:
AND(=($1, _UTF-8'Video'), SEARCH($5, Sarg[(-∞.._UTF-8'Canon':VARCHAR  
CHARACTER SET "UTF-8"), (_UTF-8'Canon':VARCHAR CHARACTER SET "UTF-8"..+∞);  
NULL AS TRUE]:VARCHAR CHARACTER SET "UTF-8"))

thus i obtain incorrect results

can someone point me possible issue, code or smt else to start  
investigation ?

thanks !

Re: [QUESTION] Calcite 1.27.0 Potential Sarg problems

Posted by stanilovsky evgeny <es...@gridgain.com>.

Sorry, my fault, seems no problem on calcite side here.

> Hello !
> After 1.27.0 upgrading i found problems with correctness of condition  
> definition.
> I have simple query :
> SELECT * FROM products WHERE name = 'Canon' OR category = 'Video'
>
> and table like :
>          5, "Video", 2, "Camera Media", 21, "Media 3"
>          6, "Video", 2, "Camera Lens", 22, "Lens 3"
>          7, "Video", 1, null, 0, "Canon"
>
> I suppose to obtain plan with 2 scans and UnionAll and its all ok here,  
> but conditions are erroneous:
>
> calcite 1.26
> cond:=($t1, _UTF-8'Video')
> cond:AND(=($t5, _UTF-8'Canon'), OR(IS NULL($t1), <>($t1, _UTF-8'Video')))
>
> condition:
> AND(SEARCH($1, Sarg[_UTF-8'Video':VARCHAR CHARACTER SET "UTF-8"]:VARCHAR  
> CHARACTER SET "UTF-8"), SEARCH($5, Sarg[(-∞.._UTF-8'Canon':VARCHAR  
> CHARACTER SET "UTF-8"), (_UTF-8'Canon':VARCHAR CHARACTER SET  
> "UTF-8"..+∞), null]:VARCHAR CHARACTER SET "UTF-8"))
>
> calcite 1.27
> cond:AND(=($t5, _UTF-8'Canon'), OR(IS NULL($t1), <>($t1, _UTF-8'Video')))
> cond:AND(=($t1, _UTF-8'Video'), OR(IS NULL($t5), <>($t5, _UTF-8'Canon')))
>
> condition:
> AND(=($1, _UTF-8'Video'), SEARCH($5, Sarg[(-∞.._UTF-8'Canon':VARCHAR  
> CHARACTER SET "UTF-8"), (_UTF-8'Canon':VARCHAR CHARACTER SET  
> "UTF-8"..+∞); NULL AS TRUE]:VARCHAR CHARACTER SET "UTF-8"))
>
> thus i obtain incorrect results
>
> can someone point me possible issue, code or smt else to start  
> investigation ?
>
> thanks !