CREATE TABLE AS does not verify all Exasol column name restrictions

Detail name	Value
Changelog Number	8415
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, Exasol 6.1.0, Exasol 6.2.0, Exasol 7.0.0
Fix Versions	Exasol 7.0.1
Resolution Date	2020-09-23

Description

The "CREATE TABLE AS" statement does not verify all the column name restrictions that
Exasol requires. For example, it does not detect '.' inside column names. This can lead to the creation of tables that have column names with invalid characters like '.'. In such a scenario, it is no longer possible to address the affected columns by their name.

Example using virtual schemas:

CREATE SCHEMA test;

CREATE OR REPLACE PYTHON ADAPTER SCRIPT test.example_adapter AS
import json
import string
def adapter_call(request):
    root = json.loads(request)
    if root["type"] == "createVirtualSchema":
        res = {
            "type": "createVirtualSchema",
            "schemaMetadata": {
                "tables": [
                {
                    "name": "DUMMY",
                    "columns": [{
                        "name": "KEY.",
                        "dataType": {"type": "VARCHAR", "size": 2000000}
                    },{
                        "name": "VALUE",
                        "dataType": {"type": "VARCHAR", "size": 2000000}
                    }]
                }]
            }
        }
        return json.dumps(res).encode('utf-8')
    elif root["type"] == "dropVirtualSchema":
        return json.dumps({"type": "dropVirtualSchema"}).encode('utf-8')
    elif root["type"] == "setProperties":
        return json.dumps({"type": "setProperties"}).encode('utf-8')
    elif root["type"] == "refresh":
        return json.dumps({"type": "refresh"}).encode('utf-8')
    if root["type"] == "getCapabilities":
        return json.dumps({
            "type": "getCapabilities",
            "capabilities": []
            }).encode('utf-8')
    elif root["type"] == "pushdown":
        res = {
            "type": "pushdown",
            "sql": "SELECT * FROM (VALUES ('FOO', 'BAR')) t"
        }
        return json.dumps(res).encode('utf-8')
    else:
        raise ValueError('Unsupported callback')
/
;
DROP VIRTUAL SCHEMA VS CASCADE;
CREATE VIRTUAL SCHEMA VS USING test.EXAMPLE_ADAPTER;

CREATE OR REPLACE TABLE test.t1 as (SELECT * FROM VS.DUMMY);

SELECT * FROM test.t1; -- works
SELECT "KEY." from foo.t2; -- does not work
ALTER TABLE foo.t2 rename column "KEY." to "KEY";  -- does not work

Workaround

Replace the "CREATE TABLE AS"-statement with "CREATE TABLE" and "INSERT statements.

CREATE OR REPLACE TABLE test.t2 (a varchar(2000000), b varchar(2000000));
INSERT INTO test.t2 (SELECT * FROM VS.DUMMY);

Fix

Now there is an error message of the form Column name "KEY." is invalid. Column names must not contain '.'