Add SQL compatible margin to SUBSTR's second argument.

[rubyu]

I found an issue that csvq's SUBSTR function is not compatible with other SQL products. The second argument of SUBSTR indicates the start position of new string and it means take from the head both when it equals to zero and 1. Is this an intentional implementation?

Repro

csvq --no-header "SELECT SUBSTR('abcdefghijklmn', 1)"

Expected

+-----------------------------+
| SUBSTR('abcdefghijklmn', 1) |
+-----------------------------+
| abcdefghijklmn              |
+-----------------------------+

Result in BigQuery

Actual

+-----------------------------+
| SUBSTR('abcdefghijklmn', 1) |
+-----------------------------+
| bcdefghijklmn               |
+-----------------------------+

References

• https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions?hl=en#substr
• https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring

[codecov[bot]]

Codecov Report

Merging #10 into master will increase coverage by <.01%. The diff coverage is 100%.

@@            Coverage Diff             @@
##           master      #10      +/-   ##
==========================================
+ Coverage   89.86%   89.87%   +<.01%     
==========================================
  Files          74       74              
  Lines       15745    15747       +2     
==========================================
+ Hits        14150    14152       +2     
  Misses       1225     1225              
  Partials      370      370

Impacted Files	Coverage Δ
lib/query/function.go	99.15% <100%> (ø)	:arrow_up:

---

Continue to review full report at Codecov.

Legend - Click here to learn more Δ = absolute <relative> (impact), ø = not affected, ? = missing data Powered by Codecov. Last update 588d222...73b6d46. Read the comment docs.

[mithrandie]

Thanks.

This program is not intended to be a full standard SQL implementation, and the behavior is intended to be easy to use for programmers of other languages.

However, I am not certain about which behavior is better. Please wait for a few days, I will think about it.

[rubyu]

I think the standard SQL spec about SUBSTR, the position=1 is indicating the head of the string, is misleading too. But csvq is good introduction to new BI users aiming to learn SQL, and they will use the other product if they develop their skills. If so, compatibility of the functions might be important to them.

Lastly, Thank you for your great product! I introduced csvq to my colleagues yesterday and it attracted them👍

[mithrandie]

This request was merged, but SUBSTRING function has been newly implemented as an ANSI-Compliant function, and SUBSTR function has been restored for compatibility.

SUBSTRING function is included in the version 1.11.5. Thank you.