Changes in TDengine 3.0

Basic SQL Elements

# Element Change Description
1 VARCHAR Added Alias of BINARY.
2 TIMESTAMP literal Added TIMESTAMP ‘timestamp format’ syntax now supported.
3 _ROWTS pseudocolumn Added Indicates the primary key. Alias of _C0.
4 _IROWTS pseudocolumn Added Used to retrieve timestamps with INTERP function.
5 INFORMATION_SCHEMA Added Database for system metadata containing all schema definitions
6 PERFORMANCE_SCHEMA Added Database for system performance information.
7 Connection queries Deprecated Connection queries are no longer supported. The syntax and interfaces are deprecated.
8 Mixed operations Enhanced Mixing scalar and vector operations in queries has been enhanced and is supported in all SELECT clauses.
9 Tag operations Added Tag columns can be used in queries and clauses like data columns.
10 Timeline clauses and time functions in supertables Enhanced When 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
1 ALTER ACCOUNT Deprecated This Enterprise Edition-only statement has been removed. It returns the error “This statement is no longer supported.”
2 ALTER ALL DNODES Added Modifies the configuration of all dnodes.
3 ALTER DATABASE Modified Deprecated - 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.
4 ALTER STABLE Modified Deprecated - CHANGE TAG: Modified the name of a tag. Replaced by RENAME TAG. Added - RENAME TAG: Replaces CHANGE TAG. - COMMENT: Specifies comments for a supertable.
5 ALTER TABLE Modified Deprecated - 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.
6 ALTER USER Modified Deprecated - 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.
7 COMPACT VNODES Not supported Compacted the data on a vnode. Not supported.
8 CREATE ACCOUNT Deprecated This Enterprise Edition-only statement has been removed. It returns the error “This statement is no longer supported.”
9 CREATE DATABASE Modified Deprecated - 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.
10 CREATE DNODE Modified Now supports specifying hostname and port separately - CREATE DNODE dnode_host_name PORT port_val
11 CREATE INDEX Added Creates an SMA index.
12 CREATE MNODE Added Creates an mnode.
13 CREATE QNODE Added Creates a qnode.
14 CREATE STABLE Modified New parameter added- COMMENT: Specifies comments for the supertable.
15 CREATE STREAM Added Creates a stream.
16 CREATE TABLE Modified New 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.
17 CREATE TOPIC Added Creates a topic.
18 DROP ACCOUNT Deprecated This Enterprise Edition-only statement has been removed. It returns the error “This statement is no longer supported.”
19 DROP CONSUMER GROUP Added Deletes a consumer group.
20 DROP INDEX Added Deletes an index.
21 DROP MNODE Added Creates an mnode.
22 DROP QNODE Added Creates a qnode.
23 DROP STREAM Added Deletes a stream.
24 DROP TABLE Modified Added batch deletion syntax.
25 DROP TOPIC Added Deletes a topic.
26 EXPLAIN Added Query the execution plan of a query statement.
27 GRANT Added Grants permissions to a user.
28 KILL TRANSACTION Added Terminates an mnode transaction.
29 KILL STREAM Deprecated Terminated a continuous query. The continuous query feature has been replaced with the stream processing feature.
31 REVOKE Added Revokes permissions from a user.
32 SELECT Modified - 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.
33 SHOW ACCOUNTS Deprecated This Enterprise Edition-only statement has been removed. It returns the error “This statement is no longer supported.”
34 SHOW APPS Added Shows all clients (such as applications) that connect to the cluster.
35 SHOW CONSUMERS Added Shows information about all active consumers in the system.
36 SHOW DATABASES Modified Only shows database names.
37 SHOW FUNCTIONS Modified Only shows UDF names.
38 SHOW LICENCE Added Alias of SHOW GRANTS.
39 SHOW INDEXES Added Shows indices that have been created.
40 SHOW LOCAL VARIABLES Added Shows the working configuration of the client.
41 SHOW MODULES Deprecated Shows information about modules installed in the system.
42 SHOW QNODES Added Shows information about qnodes in the system.
43 SHOW STABLES Modified Only shows supertable names.
44 SHOW STREAMS Modified This 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.
45 SHOW SUBSCRIPTIONS Added Shows all subscriptions in the current database.
46 SHOW TABLES Modified Only shows table names.
47 SHOW TABLE DISTRIBUTED Added Shows how table data is distributed. This replaces the `SELECT _block_dist() FROM { tb_name
48 SHOW TOPICS Added Shows all subscribed topics in the current database.
49 SHOW TRANSACTIONS Added Shows all running transactions in the system.
50 SHOW DNODE VARIABLES Added Shows the configuration of the specified dnode.
51 SHOW VNODES Not supported Shows information about vnodes in the system. Not supported.
53 TRIM DATABASE Added Deletes data that has expired and orders the remaining data in accordance with the storage configuration.

SQL Functions

# Function Change Description
1 TWA Added Can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
2 IRATE Enhanced Can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
3 LEASTSQUARES Enhanced Can be used on supertables.
4 ELAPSED Enhanced Can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
5 DIFF Enhanced Can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
6 DERIVATIVE Enhanced Can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
7 CSUM Enhanced Can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
8 MAVG Enhanced Can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
9 SAMPLE Enhanced Can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
10 STATECOUNT Enhanced Can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.
11 STATEDURATION Enhanced Can be used on supertables. When PARTITION BY is not used, data in supertables is merged into a single timeline.