[jira] [Commented] (CALCITE-4873) Unconditional operand appending on SqlPosixRegexOperator#createCall method call

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4873?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17438219#comment-17438219 ] 

Julian Hyde commented on CALCITE-4873:
--------------------------------------

It seems that no one ever tried running queries with the "~" operator. (In the core parser, only LIKE and ILIKE operators are available. I think "~" is only available in Babel.) So, as part of this fix, let's add tests. Maybe to {{SqlOperatorBaseTest}}, or to {{babel/src/test/resources/sql/select.iq}}, or somewhere else.

> Unconditional operand appending on SqlPosixRegexOperator#createCall method call
> -------------------------------------------------------------------------------
>
>                 Key: CALCITE-4873
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4873
>             Project: Calcite
>          Issue Type: Bug
>          Components: babel, core
>            Reporter: Igor Seliverstov
>            Priority: Major
>
> We tried to use POSIX match syntax in our project and noticed unexpected behaviour on query validation in case the query contains SqlPosixRegexOperator.
> SqlPosixRegexOperator#createCall implementation appends a case sensitivity flag even in case when the fag is already in place, it causes NPE on query validation.
> the flow looks so:
> 1) SqlPosixRegexOperator is created by parser, sensitivity flag is added
> 2) At validation time when the validator tries to expand expression, the operator is created once again using original operands (which already contain sensitivity flag):
> {noformat}
> createCall:88, SqlPosixRegexOperator (org.apache.calcite.sql.fun)
> result:117, SqlShuttle$CallCopyingArgHandler (org.apache.calcite.sql.util)
> result:101, SqlShuttle$CallCopyingArgHandler (org.apache.calcite.sql.util)
> visitScoped:5918, SqlValidatorImpl$Expander (org.apache.calcite.sql.validate)
> visit:50, SqlScopedShuttle (org.apache.calcite.sql.validate)
> visit:33, SqlScopedShuttle (org.apache.calcite.sql.validate)
> accept:139, SqlCall (org.apache.calcite.sql)
> expand:5493, SqlValidatorImpl (org.apache.calcite.sql.validate)
> validateWhereClause:4083, SqlValidatorImpl (org.apache.calcite.sql.validate)
> {noformat}
> at this point of time the call contains two boolean operands in operand set
> 3) At type derivation time the operator tries to lookup the original operand but fails because of the parameter count filter:
> {noformat}
> lookupSubjectRoutines:499, SqlUtil (org.apache.calcite.sql)
> lookupRoutine:443, SqlUtil (org.apache.calcite.sql)
> deriveType:525, SqlOperator (org.apache.calcite.sql)
> deriveType:148, SqlBinaryOperator (org.apache.calcite.sql)
> visit:5766, SqlValidatorImpl$DeriveTypeVisitor (org.apache.calcite.sql.validate)
> visit:5753, SqlValidatorImpl$DeriveTypeVisitor (org.apache.calcite.sql.validate)
> accept:139, SqlCall (org.apache.calcite.sql)
> deriveTypeImpl:1753, SqlValidatorImpl (org.apache.calcite.sql.validate)
> deriveType:1738, SqlValidatorImpl (org.apache.calcite.sql.validate)
> validateWhereOrOn:4099, SqlValidatorImpl (org.apache.calcite.sql.validate)
> validateWhereClause:4085, SqlValidatorImpl (org.apache.calcite.sql.validate)
> {noformat}
> The problematic query:
> {noformat}
> SELECT * FROM Student WHERE first_name ~ 'Mish%';
> {noformat}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)