GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator

Exception on Teradata for columns with names as reserved words

Open
#1,436 1 comment 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.