16.20 - Resolving Table References - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

When encountering an unqualified table name, the system looks for a table by that name in all of the following databases.

  • The default database
  • Any databases referenced by the SQL request
  • The logon user database for a volatile table by that name

The search must find the table name in only 1 of those databases. Teradata Database returns the “ambiguous table name” error message if the table name exists in more than 1 of those databases.

The following examples show the consequences of using unqualified table names with volatile tables.

  • If the unqualified table name is a volatile table in the logon user database, but not a table in any other database, the reference defaults to the volatile table in the logon user database.
         .LOGON u1
    
         CREATE VOLATILE TABLE volatile_table_1 (
           f1 INTEGER,
           f2 INTEGER)
         ON COMMIT PRESERVE ROWS;
    
         DATABASE db1;
    
         INSERT volatile_table_1 (1,1);


    The reference here is to the volatile table, u1.vt1, in the logon user database, not the current default database, db1.

  • If the unqualified table name is a volatile table in the logon user database and also a table in another database, an ambiguous table name error results, for example:
         .LOGON u1
    
        CREATE VOLATILE TABLE volatile_table_1 (
          f1 INTEGER,
          f2 INTEGER)
         ON COMMIT PRESERVE ROWS;
    
         DATABASE db1;
    
         CREATE TABLE volatile_table_1 (
           g1 INTEGER,
           g2 INTEGER);
    
         INSERT INTO volatile_table_1 VALUES (1,1);

    The INSERT operation returns an error to the requestor because a table named volatile_table_1 already exists in the logon user database, u1.

  • If the unqualified table name matches a volatile table and a permanent table in the logon user database, the reference defaults to the volatile table.

    This could occur if a user creates a volatile table in session 1 and then creates a permanent table by the same name in the user database in session  2. An unqualified reference to the table name in session 1 defaults to the volatile table. Session 2 does not see the volatile table in session 1, so an unqualified reference to the table name in session 2 defaults to the permanent table.