Repository navigation
v5: SUBSTRING with a start of zero or below follows SQLite #2598
Description
Decided for v5 (2026-10-15): the default alasql dialect follows SQLite for SUBSTRING / SUBSTR / MID with a start of zero or below. PR #2521 implements MySQL's behaviour; that becomes part of the mysql dialect module in v5, credited to its author.
Five engines, four behaviours. alasql today matches none of them (JavaScript's substr, off by one):
Call on 'abcdef' |
alasql today | Standard, PostgreSQL, SQL Server | MySQL, MariaDB (this PR) | Oracle SUBSTR |
SQLite: v5 default |
|---|---|---|---|---|---|
(1, 3) |
'abc' |
'abc' |
'abc' |
'abc' |
'abc' |
(0, 3) |
'f' |
'ab' |
'' |
'abc' |
'ab' |
(-1, 3) |
'ef' |
'a' |
'f' |
'f' |
'f' |
(-2) |
'def' |
'abcdef' |
'ef' |
'ef' |
'ef' |
(-2, 1) |
'd' |
'' |
'e' |
'e' |
'e' |
(-7, 3) |
'abc' |
'' |
'' |
NULL | 'ab' |
SQLite 3.43 and PostgreSQL 18 were measured. The others are from their documentation.
Two more faults go in the same change:
SUBSTRING(12345, 2, 2)throwsy.substr is not a function. SQLite gives'23'.SUBSTRING(s FROM 2 FOR 3)does not parse.
It is a breaking change for a start of zero or below. A positive start does not change. #854 (2017) reported the 0 case.
Activity
manas24103 commentedon Oct 18, 2026
Hi @mathiasrw, I’d like to work on this issue as my next contribution.
So far, I’ve explored the existing implementation in src/55functions.js and found that SUBSTRING, SUBSTR, and MID currently share an implementation that uses JavaScript’s substr() method with start - 1. I added a regression test in test/test042.js for zero and negative starting positions and was able to reproduce the reported issue: SUBSTRING('abcdef', 0, 3) returns 'f' instead of 'ab', and SUBSTRING('abcdef', -1, 3) returns 'ef' instead of 'f'.
My next steps are to investigate the dialect-specific handling, update the implementation to match SQLite’s behavior while preserving positive-start behavior, and add tests for the other reported cases, including numeric inputs and the SUBSTRING(s FROM 2 FOR 3) syntax. I’ll also verify that the changes don’t interfere with MySQL-specific behavior and run the relevant tests.
Would it be possible to assign this issue to me? I’d be happy to continue working on it and submit a PR. Thank you!
manas24103 commentedon Oct 19, 2026
Hi @mathiasrw,
Here's a quick update on my progress with this issue.
So far, I have:
I'm currently investigating the parser grammar in src/alasqlparser.jison to understand how to support the alternate syntax SUBSTRING(s FROM 2 FOR 3).
Next, I'll work on completing SQLite-compatible behavior for the remaining edge cases, investigate MySQL-specific behavior, and run the relevant tests to check for regressions.
I'll share another update once these changes are complete. Thank you!
manas24103 commentedon Oct 19, 2026
Hi @mathiasrw,
Just following up on my request to be assigned to this issue.
I've now implemented the SQLite-compatible SUBSTRING behavior, added support for both comma-separated and standard SQL syntax (
SUBSTRING(s FROM start FOR length)), and added regression coverage in test/test042.js. All 6 targeted tests are passing locally.I'm also working on verifying the MySQL-specific behavior so that it remains confined to the MySQL dialect, as described in the issue.
I'd be happy to continue working on this and submit a PR. Could you please let me know if I can take ownership of this issue?
Thank you!
fix: follow SQLite for SUBSTRING with start <= 0 and add FROM/FOR syn…
fix: follow SQLite for SUBSTRING with start <= 0 and add FROM/FOR syn…
fix: follow SQLite for SUBSTRING with start <= 0 and add FROM/FOR syn…
fix: follow SQLite for SUBSTRING with start <= 0 and add FROM/FOR syn…
fix: follow SQLite for SUBSTRING with start <= 0 and add FROM/FOR syn…