Changes in TDengine 3.0

Basic SQL Elements

#Element
Change
Description
1VARCHARAddedAlias of BINARY.
2TIMESTAMP literalAddedTIMESTAMP ‘timestamp format’ syntax now supported.
3_ROWTS pseudocolumnAddedIndicates the primary key. Alias of _C0.
4_IROWTS pseudocolumnAddedUsed to retrieve timestamps with INTERP function.
5INFORMATION_SCHEMAAddedDatabase for system metadata containing all schema definitions
6PERFORMANCE_SCHEMAAddedDatabase for system performance information.
7Connection queriesDeprecatedConnection queries are no longer supported. The syntax and interfaces are deprecated.
8Mixed operationsEnhancedMixing scalar and vector operations in queries has been enhanced and is supported in all SELECT clauses.
9Tag operationsAddedTag columns can be used in queries and clauses like data columns.
10Timeline clauses and time functions in supertablesEnhancedWhen PARTITION BY is not used, data in supertables is merged into a single timeline.

SQL Syntax

The following data types can be used in the schema for standard tables.

#Statement
Change
Description
1ALTER ACCOUNTDeprecatedThis Enterprise Edition-only statement has been removed. It returns the error “This statement is no longer supported.”
2ALTER ALL DNODESAddedModifies the configuration of all dnodes.
3ALTER DATABASEModifiedDeprecated
  • QUORUM: Specified the required number of confirmations. STRICT is now used to specify strong or weak consistency. The STRICT parameter cannot be modified.
  • BLOCKS: Specified the memory blocks used by each vnode. BUFFER is now used to specify the size of the write cache pool for each vnode.
  • UPDATE: Specified whether update operations were supported. All databases now support updating data in certain columns.
  • CACHELAST: Specified how to cache the newest row of data. CACHEMODEL now replaces CACHELAST.
  • COMP: Cannot be modified.
    Added
  • CACHEMODEL: Specifies whether to cache the latest subtable data.
  • CACHESIZE: Specifies the size of the cache for the newest subtable data.
  • WAL_FSYNC_PERIOD: Replaces the FSYNC parameter.
  • WAL_LEVEL: Replaces the WAL parameter.
    Modified
  • REPLICA: Cannot be modified.
  • KEEP: Now supports units.
4ALTER STABLEModifiedDeprecated
  • CHANGE TAG: Modified the name of a tag. Replaced by RENAME TAG.
    Added
  • RENAME TAG: Replaces CHANGE TAG.
  • COMMENT: Specifies comments for a supertable.
5ALTER TABLEModifiedDeprecated
  • CHANGE TAG: Modified the name of a tag. Replaced by RENAME TAG.
    Added
  • RENAME TAG: Replaces CHANGE TAG.
  • COMMENT: Specifies comments for a standard table.
  • TTL: Specifies the time-to-live for a standard table.
6ALTER USERModifiedDeprecated
  • PRIVILEGE: Specified user permissions. Replaced by GRANT and REVOKE.
    Added
  • ENABLE: Enables or disables a user.
  • SYSINFO: Specifies whether a user can query system information.
7COMPACT VNODESNot supportedCompacted the data on a vnode. Not supported.
8CREATE ACCOUNTDeprecatedThis Enterprise Edition-only statement has been removed. It returns the error “This statement is no longer supported.”
9CREATE DATABASEModifiedDeprecated
  • BLOCKS: Specified the number of blocks for each vnode. BUFFER is now used to specify the size of the write cache pool for each vnode.
  • CACHE: Specified the size of the memory blocks used by each vnode. BUFFER is now used to specify the size of the write cache pool for each vnode.
  • CACHELAST: Specified how to cache the newest row of data. CACHEMODEL now replaces CACHELAST.
  • DAYS: The length of time to store in a single file. Replaced by DURATION.
  • FSYNC: Specified the fsync interval when WAL was set to 2. Replaced by WAL_FSYNC_PERIOD.
  • QUORUM: Specified the number of confirmations required. STRICT is now used to specify strong or weak consistency.
  • UPDATE: Specified whether update operations were supported. All databases now support updating data in certain columns.
  • WAL: Specified the WAL level. Replaced by WAL_LEVEL.
    Added
  • BUFFER: Specifies the size of the write cache pool for each vnode.
  • CACHEMODEL: Specifies whether to cache the latest subtable data.
  • CACHESIZE: Specifies the size of the cache for the newest subtable data.
  • DURATION: Replaces DAYS. Now supports units.
  • PAGES: Specifies the number of pages in the metadata storage engine cache on each vnode.
  • PAGESIZE: specifies the size (in KB) of each page in the metadata storage engine cache on each vnode.
  • RETENTIONS: Specifies the aggregation interval and retention period
  • STRICT: Specifies whether strong data consistency is enabled.
  • SINGLE_STABLE: Specifies whether a database can contain multiple supertables.
  • VGROUPS: Specifies the initial number of vgroups when a database is created.
  • WAL_FSYNC_PERIOD: Replaces the FSYNC parameter.
  • WAL_LEVEL: Replaces the WAL parameter.
  • WAL_RETENTION_PERIOD: specifies the time after which WAL files are deleted. This parameter is used for data subscription.
  • WAL_RETENTION_SIZE: specifies the size at which WAL files are deleted. This parameter is used for data subscription.
  • WAL_ROLL_PERIOD: Specifies the WAL rotation period.
  • WAL_SEGMENT_SIZE: specifies the maximum size of a WAL file.
    Modified
  • KEEP: Now supports units.
10CREATE DNODEModifiedNow supports specifying hostname and port separately
  • CREATE DNODE dnode_host_name PORT port_val
11CREATE INDEXAddedCreates an SMA index.
12CREATE MNODEAddedCreates an mnode.
13CREATE QNODEAddedCreates a qnode.
14CREATE STABLEModifiedNew parameter added
  • COMMENT: Specifies comments for the supertable.
  • 15CREATE STREAMAddedCreates a stream.
    16CREATE TABLEModifiedNew parameters added
    • COMMENT: Specifies comments for the table
    • WATERMARK: Specifies the window closing time.
    • MAX_DELAY: Specifies the maximum delay for pushing stream processing results.
    • ROLLUP: Specifies aggregate functions to roll up. Rolling up a function provides downsampled results based on multiple axes.
    • SMA: Provides user-defined precomputation of aggregates based on data blocks.
    • TTL: Specifies the time-to-live for a standard table.
    17CREATE TOPICAddedCreates a topic.
    18DROP ACCOUNTDeprecatedThis Enterprise Edition-only statement has been removed. It returns the error “This statement is no longer supported.”
    19DROP CONSUMER GROUPAddedDeletes a consumer group.
    20DROP INDEXAddedDeletes an index.
    21DROP MNODEAddedCreates an mnode.
    22DROP QNODEAddedCreates a qnode.
    23DROP STREAMAddedDeletes a stream.
    24DROP TABLEModifiedAdded batch deletion syntax.
    25DROP TOPICAddedDeletes a topic.
    26EXPLAINAddedQuery the execution plan of a query statement.
    27GRANTAddedGrants permissions to a user.
    28KILL TRANSACTIONAddedTerminates an mnode transaction.
    29KILL STREAMDeprecatedTerminated a continuous query. The continuous query feature has been replaced with the stream processing feature.
    31REVOKEAddedRevokes permissions from a user.
    32SELECTModified
    • SELECT does not use the implicit results column. Output columns must be specified in the SELECT clause.
    • DISTINCT support is enhanced. In previous versions, DISTINCT only worked on the tag column and could not be used with JOIN or GROUP BY.
    • JOIN support is enhanced. The following are now supported after JOIN: a WHERE clause with OR, operations on multiple tables, and GROUP BY on multiple tables.
    • Subqueries after FROM are enhanced. Levels of nesting are no longer restricted. Subqueries can be used with UNION ALL. Other syntax restrictions are eliminated.
    • All scalar functions can be used after WHERE.
    • GROUP BY is enhanced. You can group by any scalar expression or combination thereof.
    • SESSION can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
    • STATE_WINDOW can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
    • ORDER BY is enhanced. It is no longer required to use ORDER BY and GROUP BY together. There is no longer a restriction on the number of order expressions. NULLS FIRST and NULLS LAST syntax has been added. Any expression that conforms to the ORDER BY semantics can be used.
    • Added PARTITION BY syntax. PARTITION BY replaces GROUP BY tags.
    33SHOW ACCOUNTSDeprecatedThis Enterprise Edition-only statement has been removed. It returns the error “This statement is no longer supported.”
    34SHOW APPSAddedShows all clients (such as applications) that connect to the cluster.
    35SHOW CONSUMERSAddedShows information about all active consumers in the system.
    36SHOW DATABASESModifiedOnly shows database names.
    37SHOW FUNCTIONSModifiedOnly shows UDF names.
    38SHOW LICENCEAddedAlias of SHOW GRANTS.
    39SHOW INDEXESAddedShows indices that have been created.
    40SHOW LOCAL VARIABLESAddedShows the working configuration of the client.
    41SHOW MODULESDeprecatedShows information about modules installed in the system.
    42SHOW QNODESAddedShows information about qnodes in the system.
    43SHOW STABLESModifiedOnly shows supertable names.
    44SHOW STREAMSModifiedThis statement previously showed continuous queries. The continuous query feature has been replaced with the stream processing feature. This statement now shows streams that have been created.
    45SHOW SUBSCRIPTIONSAddedShows all subscriptions in the current database.
    46SHOW TABLESModifiedOnly shows table names.
    47SHOW TABLE DISTRIBUTEDAddedShows how table data is distributed. This replaces the `SELECT _block_dist() FROM { tb_name
    48SHOW TOPICSAddedShows all subscribed topics in the current database.
    49SHOW TRANSACTIONSAddedShows all running transactions in the system.
    50SHOW DNODE VARIABLESAddedShows the configuration of the specified dnode.
    51SHOW VNODESNot supportedShows information about vnodes in the system. Not supported.
    53TRIM DATABASEAddedDeletes data that has expired and orders the remaining data in accordance with the storage configuration.

    SQL Functions

    #Function
    Change
    Description
    1TWAAddedCan be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
    2IRATEEnhancedCan be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
    3LEASTSQUARESEnhancedCan be used on supertables.
    4ELAPSEDEnhancedCan be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
    5DIFFEnhancedCan be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
    6DERIVATIVEEnhancedCan be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
    7CSUMEnhancedCan be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
    8MAVGEnhancedCan be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
    9SAMPLEEnhancedCan be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
    10STATECOUNTEnhancedCan be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
    11STATEDURATIONEnhancedCan be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.