wnob commented on code in PR #3184:
URL: https://github.com/apache/calcite/pull/3184#discussion_r1182852333
##########
core/src/main/java/org/apache/calcite/runtime/CalciteResource.java:
##########
@@ -1020,6 +1020,12 @@ ExInstWithCause<CalciteException> failedToAccessField(
@BaseMessage("No operator for ''{0}'' with kind: ''{1}'', syntax: ''{2}''
during JSON deserialization")
ExInst<CalciteException> noOperator(String name, String kind, String syntax);
+ @BaseMessage("Invalid input for position function: from operand value must
not be zero")
+ ExInst<CalciteException> fromNotZero();
+
+ @BaseMessage("Invalid input for position function: occurrence operand value
must not be zero")
Review Comment:
`occurence` must be positive. Update the message and condition.
##########
core/src/main/java/org/apache/calcite/runtime/CalciteResource.java:
##########
@@ -1020,6 +1020,12 @@ ExInstWithCause<CalciteException> failedToAccessField(
@BaseMessage("No operator for ''{0}'' with kind: ''{1}'', syntax: ''{2}''
during JSON deserialization")
ExInst<CalciteException> noOperator(String name, String kind, String syntax);
+ @BaseMessage("Invalid input for position function: from operand value must
not be zero")
Review Comment:
BQ and Oracle both call this `position`, not `from`. Is there another
dialect that calls it `from`?
##########
core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java:
##########
@@ -3104,23 +3104,106 @@ public static int position(ByteString seek, ByteString
s) {
/** SQL {@code POSITION(seek IN string FROM integer)} function. */
public static int position(String seek, String s, int from) {
- final int from0 = from - 1; // 0-based
- if (from0 > s.length() || from0 < 0) {
+ if (from == 0) {
+ throw RESOURCE.fromNotZero().ex();
+ }
+ // Case when from is positive
+ if (from > 0) {
+ final int from0 = from - 1; // 0-based
+ if (from0 >= s.length() || from0 < 0) {
+ return 0;
+ } else {
+ return s.indexOf(seek, from0) + 1;
+ }
+ }
+ // Case when from is negative
+ final int rightIndex = from + s.length(); // negative position to positive
index
Review Comment:
I don't think this is the correct semantics. BQ will actually search
backward when `position` is negative. To see what I mean, try running `SELECT
INSTR("fersher dude", "dude", -3, 1), INSTR("fersher dude", "dude", -4, 1),
INSTR("fersher dude", "dude", -5, 1)` in http://pantheon/bigquery
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
