16.20 - General Limitations and Restrictions on Table Functions - 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)
These general restrictions apply to table functions:
  • Table functions cannot execute against fallback data when an AMP is down. Once the AMP returns to service, the query can complete as normal.
  • If the UDF library for your database contains any objects with multibyte characters in their name, you cannot use a single-byte session character set to create a new UDF, UDT, method, or Java external procedure object even if the new object name contains only single-byte characters. Otherwise, Teradata Database returns an error to the requestor. Instead, use a multibyte session character set.
  • You should always run in protected mode if a table function accesses data external to Teradata Database or causes the OS to consume system resources. This includes anything that causes the OS to allocate system context, including open files, pipes, semaphores, tokens, threads (processes), and so on.

    You can expand the number of protected mode servers for table functions from the default value of 2 to a maximum of 20 per AMP or PE vprocs. For more information, see the cufconfig utility in Teradata Vantage™ - Database Utilities , B035-1102 and Teradata Vantage™ SQL External Routine Programming , B035-1147 . The minimum is 0.

    Protected mode servers consume disk resources as follows:



  • For functions running in protected mode, you need at least one protected mode server for each table function you want to run concurrently from separate sessions. For more information, see the cufconfig utility in Teradata Vantage™ - Database Utilities , B035-1102 and Teradata Vantage™ SQL External Routine Programming , B035-1147 . A table function reserves one protected mode UDF server per vproc for the duration of the table function step.
  • A table function can be deadlocked if too few protected mode servers are configured.

    The system does not detect or report this deadlock.

  • You cannot specify more than one table UDF in a FROM clause.
  • You cannot invoke a table function in a FROM clause join condition using the FROM … JOIN …ON syntax.

    Suppose you create the following table and table function.

         CREATE TABLE supp_indata (
           a FLOAT,
           b FLOAT;
         CREATE FUNCTION c_mytable (a FLOAT, b FLOAT)
         RETURNS TABLE (c3 FLOAT, c4 FLOAT )
         LANGUAGE C
         NO SQL
         PARAMETER STYLE SQL
         EXTERNAL NAME 'CS!c_mytable!c_mytable.c';

    The following SELECT request is not valid because it uses the FROM … JOIN …ON syntax.

         SELECT t1.c4
         FROM TABLE (c_mytable(supp_indata.a, supp_indata.b)) AS t1
         JOIN supp_indata AS t2 ON t1.c3 = t2.b;

    However, the following SELECT request that uses slightly different syntax for the join is valid.

         SELECT t1.c4
         FROM TABLE(c_mytable(supp_indata.a, supp_indata.b)) AS t1,
                              supp_indata AS t2
         WHERE t1.c3 = t2b;
  • You cannot create or replace a table function from an embedded SQL application.
  • A table function cannot reference a recursive view.
  • The size of any row returned by a table function cannot exceed the system limit.

    If a row does exceed this limit, the system aborts the request in Teradata session mode or its containing transaction in ANSI session mode, rolls it back, and returns an error message to the requestor.

    The system defers the maximum row size check until you invoke the function because it cannot know whether the limit will be exceeded by a table function defined with dynamic result rows until the function runs.

  • The maximum number of columns that can be returned by any table function is 2,048.
  • When you run in protected mode, all instances of a table function run as tdatuser.
  • You must troubleshoot code problems outside Teradata Database.

    You can do a limited amount of code checking by building the trace facility into the code with the FNC_Trace_Write or FNC_Trace_Write_DL library calls. See CREATE GLOBAL TEMPORARY TRACE TABLE. For details about these library calls and other troubleshooting methods you can build into table functions, see Teradata Vantage™ SQL External Routine Programming , B035-1147 .

  • You can specify the TD_ANYTYPE data type as an input parameter for a table function.
  • You cannot specify the TD_ANYTYPE data type as the return type for a table function.
  • Any non-deterministic elements referenced in the RETURN expression of a UDF can be replaced by values predefined by Teradata Unity. For more information about Teradata Unity, see the Teradata Unity documentation.
  • The mandatory way you must specify columns having a BLOB or CLOB data type depends on whether you are specifying a data type for a parameter column or for a RETURN TABLE clause column:
    Clause LOB Type
    Parameter With an AS LOCATOR phrase.
    RETURNS TABLE Without an AS LOCATOR phrase.