Help Center/ GaussDB(for MySQL)/ User Guide/ Kernel Functions/ Parallel Query/ Usage/ Constraints
Updated on 2024-05-21 GMT+08:00
View PDF

Constraints

Currently, there are some restrictions on parallel queries, but they will be gradually resolved in the future.

Unsupported Statements

Parallel queries are not suitable for:

  • Non-SELECT statements
  • Window functions
  • Triggers
  • Prepared Statements
  • Spatial indexes
  • System tables, temporary tables, and non-InnoDB tables
  • Full-text indexes
  • Stored procedures
  • Subqueries that cannot be converted to semi-joins
  • SQL statements that do not meet the only_full_group_by rules
  • Index Merge statements
  • HASH JOIN operations, during which data overflows to disks
  • Lock queries, such as SERIALIZABLE isolation level, FOR UPDATE, or SHARE LOCK
  • Recursive queries
  • With rollup
  • Statements with keyword HIGH_PRIORITY
  • No line of data returned in the execution result. (The execution plan shows: Zero limit, Impossible WHERE, Impossible HAVING, No matching min/max row, Select tables optimized away, Impossible HAVING noticed after reading const tables, or no matching row in const table)
  • Columns with type ZEROFILL. Its column values can be optimized to constants.
  • Generated columns, BLOB, TEXT, JSON, and GEOMETRY
  • Spatial functions (such as SP_WITHIN_FUNC)
  • DISTINCT clauses in Aggregate functions, such as SUM(DISTINCT), AVG(DISTINCT), and COUNT(DISTINCT)
  • GROUP_CONCAT
  • JSON_ARRAYAGG/JSON_OBJECTAGG
  • User-defined functions
  • STD/STDDEV/STDDEV_POP
  • VARIANCE/VAR_POP/VAR_SAMP
  • BIT_AND, BIT_OR and BIT_XOR
  • set_user_var
  • RAND functions with parameters
  • json_* (such as json_length and json_type)
  • st_distance
  • get_lock
  • is_free_lock, is_used_lock, release_lock, and release_all_locks
  • sleep
  • xml_str
  • weight_string
  • REF functions (VIEW_REF, OUTER_REF, AGGREGATE_REF)
  • SHA, SHA1, SHA2, and MD5
  • row_count
  • User-related functions (such as user, current_user, session_user, system_user)
  • extractvalue
  • GeomCollection, GeometryCollection, LineString, MultiLineString, MultiPoint, MultiPolygon, Polygon
  • MASTER_POS_WAIT
  • Spatial relationship functions, such as MBRContains, MBRCoveredBy, MBRCovers, MBRDisjoint, MBREquals, MBRIntersects, MBROverlaps, MBRTouches, MBRWithin
  • Point
  • PS_CURRENT_THREAD_ID()
  • PS_THREAD_ID(CONNECTION_ID())
  • WAIT_FOR_EXECUTED_GTID_SET
  • WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS
  • UNCOMPRESS (COMPRESS ())
  • STATEMENT_DIGEST_TEXT
  • Functions BINARY and CONVERT
  • Functions starting with ST_

Incompatible with the Serial Execution Results

The execution results of parallel queries may be incompatible with that of serial queries.

  • Number of errors or alarms

    If an error or alarm message is displayed during serial queries, the error or alarm message will be displayed in each worker thread during the parallel queries. As a result, the total number of error or alarm messages increases.

  • Precision

    During the parallel queries, if there is a function type in a SELECT statement, additional stored procedures will be generated in the intermediate results. As a result, compared with serial queries, the precision of the floating point part in parallel queries may be different, and the final result may be slightly different.

  • Truncation

    During the parallel queries, if there is a function type in a SELECT statement, additional stored procedures will be generated in the intermediate results. In this process, the calculation result of the function needs to be cached, and data truncation may occur (generally due to data type conversion, for example, covering a floating-point value to a character string). As a result, the final result is different from the serial queries.

  • Sequence of result sets

    Because tasks are executed by multiple worker threads during parallel queries, the sequence of the returned result set may not be consistent with that of serial queries. In the case of a query with LIMIT, this problem is more likely to occur. If fields of GROUP BY are invisible characters, the sequence of the returned result set is also different.

  • UNION ALL result sets

    UNION ALL ignores sort operators. The sequence of the returned result set in parallel execution may be different from that in non-parallel execution. In the case of a query with LIMIT, the result sets are different.

Parent topic: Usage