Search before asking
What Happened
CURRENT_TIMESTAMP throws an IndexError: tuple index out of range error when used as a DEFAULT constraint in a table definition for TSQL. Substituting GETDATE() for CURRENT_TIMESTAMP results in no failure.
At first I thought this could be similar to Issue# 3573 , but as you can see from the reproduction steps, CURRENT_TIMESTAMP does not have issues when encountered in SELECT statements, etc. within TSQL.
Expected Behaviour
CURRENT_TIMESTAMP should be interchangeable with GETDATE() in the CREATE TABLE CONSTRAINT context as it is interchangeable within SELECT statements without issue.
Observed Behaviour
Linting fails and attempting to noqa the line does not resolve the error either. The only workaround I've found aside from ignoring the entire file is to substitute CURRENT_TIMESTAMP with GETDATE().
How to reproduce
Take, for instance, the following TSQL files:
-- create_table.sql
CREATE TABLE dbo.tablename (
id INT NOT NULL,
column_data VARCHAR(500) NOT NULL,
created_by VARCHAR(500) NOT NULL CONSTRAINT df_created_by DEFAULT (SUSER_NAME()),
-- following line fails when using CURRENT_TIMESTAMP instead of GETDATE()
created_dt DATETIME NOT NULL CONSTRAINT df_created_dt DEFAULT (GETDATE()),
CONSTRAINT pk_tags PRIMARY KEY CLUSTERED (id ASC)
);
-- select.sql
SELECT
id,
column_data,
created_by,
created_dt
FROM dbo.tablename
WHERE created_dt = CURRENT_TIMESTAMP
When linting these files (excluding LT05 for readability reasons) with the following command, everything works as expected:
%> sqlfluff lint --dialect tsql -e LT05 .
All Finished!
Now, in an attempt to use the ANSI SQL standard CURRENT_TIMESTAMP instead of GETDATE() in the create_table.sql file, we change line 7 to the following:
created_dt DATETIME NOT NULL CONSTRAINT df_created_dt DEFAULT (CURRENT_TIMESTAMP),
Rerunning the same linting command, we get the following error:
%> sqlfluff lint --dialect tsql -e LT05 .
CRITICAL [LT06] Applying rule LT06 to 'create_table.sql' threw an Exception: tuple index out of range
Traceback (most recent call last):
File "%HOMEPATH%\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlfluff\core\rules\base.py", line 796, in crawl
res = self._eval(context=context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "%HOMEPATH%\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlfluff\rules\layout\LT06.py", line 56, in _eval
start_bracket = children.first(sp.is_type("bracketed"))[0]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^
File "%HOMEPATH%\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlfluff\utils\functional\segments.py", line 155, in __getitem__
result = super().__getitem__(item)
^^^^^^^^^^^^^^^^^^^^^^^^^
IndexError: tuple index out of range
== [create_table.sql] FAIL
L: 7 | P: 68 | LT06 | Unexpected exception: tuple index out of range;
Could
| you open an issue at
| https://github.com/sqlfluff/sqlfluff/issues ?
You can
| ignore this exception for now, by adding '-- noqa: LT06'
| at the end
of line 7
[layout.functions]
All Finished!
Attempting to ignore the line in question, also results in failure.
created_dt DATETIME NOT NULL CONSTRAINT df_created_dt DEFAULT (CURRENT_TIMESTAMP), -- noqa: LT06
Reattempting Linting:
%> sqlfluff lint --dialect tsql -e LT05 .
CRITICAL [LT06] Applying rule LT06 to 'create_table.sql' threw an Exception: tuple index out of range
Traceback (most recent call last):
File "%HOMEPATH%\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlfluff\core\rules\base.py", line 796, in crawl
res = self._eval(context=context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "%HOMEPATH%\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlfluff\rules\layout\LT06.py", line 56, in _eval
start_bracket = children.first(sp.is_type("bracketed"))[0]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^
File "%HOMEPATH%\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlfluff\utils\functional\segments.py", line 155, in __getitem__
result = super().__getitem__(item)
^^^^^^^^^^^^^^^^^^^^^^^^^
IndexError: tuple index out of range
All Finished!
Dialect
TSQL
Version
version 2.0.2
Configuration
[sqlfluff]
# defaults only
Are you willing to work on and submit a PR to address the issue?
Code of Conduct
Search before asking
What Happened
CURRENT_TIMESTAMPthrows anIndexError: tuple index out of rangeerror when used as aDEFAULTconstraint in a table definition for TSQL. SubstitutingGETDATE()forCURRENT_TIMESTAMPresults in no failure.At first I thought this could be similar to Issue# 3573 , but as you can see from the reproduction steps,
CURRENT_TIMESTAMPdoes not have issues when encountered inSELECTstatements, etc. within TSQL.Expected Behaviour
CURRENT_TIMESTAMPshould be interchangeable withGETDATE()in theCREATE TABLE CONSTRAINTcontext as it is interchangeable withinSELECTstatements without issue.Observed Behaviour
Linting fails and attempting to
noqathe line does not resolve the error either. The only workaround I've found aside from ignoring the entire file is to substituteCURRENT_TIMESTAMPwithGETDATE().How to reproduce
Take, for instance, the following
TSQLfiles:When linting these files (excluding LT05 for readability reasons) with the following command, everything works as expected:
Now, in an attempt to use the ANSI SQL standard
CURRENT_TIMESTAMPinstead ofGETDATE()in thecreate_table.sqlfile, we change line 7 to the following:Rerunning the same linting command, we get the following error:
Attempting to ignore the line in question, also results in failure.
Reattempting Linting:
Dialect
TSQL
Version
version 2.0.2
Configuration
Are you willing to work on and submit a PR to address the issue?
Code of Conduct