GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator
Exception on Teradata for columns with names as reserved words
- Dominant language
- Python
- Stars
- 524
- Forks
- 171
- Avg merge
- 5d 15h
- Merged PRs (30d)
- 4
Description
A test table for this is being added in https://github.com/GoogleCloudPlatform/professional-services-data-validator/issues/1393.
Test for Teradata will be disabled. Example issues below:
Column validion:
```
$ python3 -m pytest tests/system/data_sources/test_teradata.py -k test_column_validation_reserved_words
...
E pandas.errors.DatabaseError: Execution failed on sql 'SELECT count(1) AS "count", count(t0."id") AS "count__id",
E count(t0."length__"SELECT"") AS "count__length__"SELECT"",
E count(t0."length__"COLUMN"") AS "count__length__"COLUMN"",
E count(t0."length__"FROM"") AS "count__length__"FROM"",
E count(t0."length__"WHERE"") AS "count__length__"WHERE"",
E count(t0."length__"DATE"") AS "count__length__"DATE"",
E count(t0."length__"NUMBER"") AS "count__length__"NUMBER"",
E count(t0."length__STRING") AS "count__length__STRING"
E FROM (
E SELECT t1.*, length(t1.""SELECT"") AS "length__"SELECT"",
E length(t1.""COLUMN"") AS "length__"COLUMN"",
E length(t1.""FROM"") AS "length__"FROM"",
E length(t1.""WHERE"") AS "length__"WHERE"",
E length(t1.""DATE"") AS "length__"DATE"",
E length(t1.""NUMBER"") AS "length__"NUMBER"",
E length(t1."STRING") AS "length__STRING"
E FROM udf.dvt_reserved_word_columns t1
E ) t0': [Version 20.0.0.21] [Session 25165] [Teradata Database] [Error 3706] Syntax error: expected something between the word 'length__' and the 'SELECT' keyword.
```
Row hash:
```
$ python3 -m pytest tests/system/data_sources/test_teradata.py -k test_row_validation_reserved_words
...
E pandas.errors.DatabaseError: Execution failed on sql 'WITH t0 AS (
E SELECT t6.*
E FROM udf.dvt_reserved_word_columns t6
E WHERE 1=1
E ),
E t1 AS (
E SELECT t0.*, CAST(t0."id" AS VARCHAR(255)) AS "cast__id",
E t0.""SELECT"" AS "cast__"select"",
E t0.""COLUMN"" AS "cast__"column"", t0.""FROM"" AS "cast__"from"",
E t0.""WHERE"" AS "cast__"where"", t0.""DATE"" AS "cast__"date"",
E t0.""NUMBER"" AS "cast__"number"", t0."STRING" AS "cast__string"
E FROM t0
E ),
E t2 AS (
E SELECT t1.*,
E coalesce(t1."cast__id", 'DEFAULT_REPLACEMENT_STRING') AS "ifnull__cast__id",
E coalesce(t1."cast__"select"", 'DEFAULT_REPLACEMENT_STRING') AS "ifnull__cast__"select"",
E coalesce(t1."cast__"column"", 'DEFAULT_REPLACEMENT_STRING') AS "ifnull__cast__"column"",
E coalesce(t1."cast__"from"", 'DEFAULT_REPLACEMENT_STRING') AS "ifnull__cast__"from"",
E coalesce(t1."cast__"where"", 'DEFAULT_REPLACEMENT_STRING') AS "ifnull__cast__"where"",
E coalesce(t1."cast__"date"", 'DEFAULT_REPLACEMENT_STRING') AS "ifnull__cast__"date"",
E coalesce(t1."cast__"number"", 'DEFAULT_REPLACEMENT_STRING') AS "ifnull__cast__"number"",
E coalesce(t1."cast__string", 'DEFAULT_REPLACEMENT_STRING') AS "ifnull__cast__string"
E FROM t1
E ),
E t3 AS (
E SELECT t2.*,
E RTRIM(t2."ifnull__cast__id", _latin '20090B0A0D0C'XCV) AS "rstrip__ifnull__cast__id",
E RTRIM(t2."ifnull__cast__"select"", _latin '20090B0A0D0C'XCV) AS "rstrip__ifnull__cast__"select"",
E RTRIM(t2."ifnull__cast__"column"", _latin '20090B0A0D0C'XCV) AS "rstrip__ifnull__cast__"column"",
E RTRIM(t2."ifnull__cast__"from"", _latin '20090B0A0D0C'XCV) AS "rstrip__ifnull__cast__"from"",
E RTRIM(t2."ifnull__cast__"where"", _latin '20090B0A0D0C'XCV) AS "rstrip__ifnull__cast__"where"",
E RTRIM(t2."ifnull__cast__"date"", _latin '20090B0A0D0C'XCV) AS "rstrip__ifnull__cast__"date"",
E RTRIM(t2."ifnull__cast__"number"", _latin '20090B0A0D0C'XCV) AS "rstrip__ifnull__cast__"number"",
E RTRIM(t2."ifnull__cast__string", _latin '20090B0A0D0C'XCV) AS "rstrip__ifnull__cast__string"
E FROM t2
E ),
E t4 AS (
E SELECT t3.*,
E t3."rstrip__ifnull__cast__id"||t3."rstrip__ifnull__cast__"select""||t3."rstrip__ifnull__cast__"column""||t3."rstrip__ifnull__cast__"from""||t3."rstrip__ifnull__cast__"where""||t3."rstrip__ifnull__cast__"date""||t3."rstrip__ifnull__cast__"number""||t3."rstrip__ifnull__cast__string" AS "concat__all"
E FROM t3
E )
E SELECT t5."hash__all", t5."id"
E FROM (
E SELECT t4.*,
E rtrim(hash_sha256(TransUnicodeToUTF8(t4."concat__all"))) AS "hash__all"
E FROM t4
E ) t5': [Version 20.0.0.21] [Session 25161] [Teradata Database] [Error 3706] Syntax error: Blank name in quotation marks.
```
Comp fields:
```
$ python3 -m pytest tests/system/data_sources/test_teradata.py -k test_row_validation_comp_fields_reserved_words
...
E pandas.errors.DatabaseError: Execution failed on sql 'WITH t0 AS (
E SELECT t2.*
E FROM udf.dvt_reserved_word_columns t2
E WHERE 1=1
E )
E SELECT t1."rstrip__"select"", t1."rstrip__"column"", t1."rstrip__"from"",
E t1."rstrip__"where"", t1."rstrip__"date"", t1."rstrip__"number"",
E t1."rstrip__string", t1."id"
E FROM (
E SELECT t0.*,
E RTRIM(t0.""SELECT"", _latin '20090B0A0D0C'XCV) AS "rstrip__"select"",
E RTRIM(t0.""COLUMN"", _latin '20090B0A0D0C'XCV) AS "rstrip__"column"",
E RTRIM(t0.""FROM"", _latin '20090B0A0D0C'XCV) AS "rstrip__"from"",
E RTRIM(t0.""WHERE"", _latin '20090B0A0D0C'XCV) AS "rstrip__"where"",
E RTRIM(t0.""DATE"", _latin '20090B0A0D0C'XCV) AS "rstrip__"date"",
E RTRIM(t0.""NUMBER"", _latin '20090B0A0D0C'XCV) AS "rstrip__"number"",
E RTRIM(t0."STRING", _latin '20090B0A0D0C'XCV) AS "rstrip__string"
E FROM t0
E ) t1': [Version 20.0.0.21] [Session 25157] [Teradata Database] [Error 3707] Syntax error, expected something like a name or a Unicode delimited identifier between the word 'rstrip__' and the 'select' keyword.
```
This has not been reported by a customer. I was just adding tests for many engines after finding an issue when using COLUMN keyword on Oracle.
Contributor guide
Assessment
This issue has not been assessed yet.