Major Description
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):
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)
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:
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)
The problematic query:
SELECT * FROM Student WHERE first_name ~ 'Mish%';