This topic describes the limits on IMCIs in SELECT statements.
Limits on IMCIs in SELECT statements
IMCIs can be used only in SELECT statements, excluding for the following SELECT statements:
The SELECT statements containing lock operations. Example:
SELECT ... FOR [UPDATE | SHARE] ....The SELECT statements containing the frame aggregate function. Example:
SELECT time, subject, val, SUM(val) OVER ( PARTITION BY subject ORDER BY time ROWS UNBOUNDED PRECEDING --- Frame definitions in window functions do not support IMCIs. ) AS running_total FROM observations;The SELECT statements where subqueries are in the GROUP BY clause. Example:
SELECT SUM(a) FROM t1 GROUP BY (SELECT ... FROM ...) as some_subquery;The SELECT statements where subqueries are in the ORDER BY clause. Example:
SELECT a FROM t1 ORDER BY (SELECT ... FROM ...) as some_subquery;The SELECT statements where subqueries are nested in join conditions. Example:
WHERE t1.a in (SELECT t2.a FROM t2 INNER JOIN t3 on t2.a = t3.a AND t2.b > t1.b);The SELECT statements subqueries contain window functions and are nested in HAVING conditions.
The SELECT statements subqueries contain UNION clauses and are nested in UNION conditions.
Limits on IMCIs in expressions
If a SELECT statement contains an expression that does not support IMCIs, the SELECT statement does not support IMCIs.
Comparison expressions
Expression | IMCI. |
SOUNDS LIKE | Not supported |
BETWEEN...AND... | Supported Note If |
GREATEST() | Supported Note If the TIME and STRING data types are used in input parameters, the comparison results may be different from the results where IMCIs are not used. |
IN() | Supported Note If |
LEAST() | Supported Note If the TIME and STRING data types are used in input parameters, the comparison results may be different from the results where IMCIs are not used. |
NOT BETWEEN...AND... | Supported Note If |
String expressions
Expression | IMCI. |
SOUNDEX() | Not supported |
SOUNDS LIKE | Not supported |
MATCH | Not supported |
LOAD_FILE() | Not supported |
TIMESTAMP() | Supported Note When the IMCI feature is enabled, you can specify only one parameter. You cannot specify two parameters. |
Aggregate function expressions
Expression | IMCI. |
JSON_ARRAYAGG() | Not supported |
JSON_OBJECTAGG() | Not supported |
Window function expressions
Expression | IMCI. |
FIRST_VALUE() | Not supported |
NTH_VALUE() | Not supported |
LAST_VALUE() | Not supported |
Encryption and compression expressions
Expression | IMCI. |
AES_DECRYPT() | Not supported |
AES_ENCRYPT() | Not supported |
COMPRESS() | Not supported |
RANDOM_BYTES() | Not supported |
STATEMENT_DIGEST() | Not supported |
STATEMENT_DIGEST_TEXT() | Not supported |
UNCOMPRESS() | Not supported |
UNCOMPRESSED_LENGTH() | Not supported |
VALIDATE_PASSWORD_STRENGTH() | Not supported |
JSON functions
Function | IMCI. |
JSON_ARRAY() | Not supported |
JSON_ARRAY_APPEND() | Not supported |
JSON_ARRAY_INSERT() | Not supported |
JSON_CONTAINS_PATH() | Not supported |
JSON_INSERT() | Not supported |
JSON_MERGE() | Not supported |
JSON_MERGE_PATCH() | Not supported |
JSON_MERGE_PRESERVE() | Not supported |
JSON_OBJECT() | Not supported |
JSON_OVERLAPS() | Not supported |
JSON_REPLACE() | Not supported |
JSON_SCHEMA_VALID() | Not supported |
JSON_SCHEMA_VALIDATION_REPORT() | Not supported |
JSON_SEARCH() | Not supported |
JSON_SET() | Not supported |
JSON_STORAGE_FREE() | Not supported |
JSON_STORAGE_SIZE() | Not supported |
JSON_TABLE() | Not supported |
JSON_VALUE() | Not supported |
MEMBER OF() | Not supported |
Spatial functions
Function | IMCI. |
ST_AsGeoJSON() | Not supported |
ST_Buffer() | Not supported |
ST_Buffer_Strategy() | Not supported |
ST_Centroid() | Not supported |
ST_Collect() | Not supported |
ST_ConvexHull() | Not supported |
ST_EndPoint() | Not supported |
ST_ExteriorRing() | Not supported |
ST_FrechetDistance() | Not supported |
ST_GeoHash() | Not supported |
ST_GeometryN() | Not supported |
ST_GeometryType() | Not supported |
ST_GeomFromGeoJSON() | Not supported |
ST_HausdorffDistance() | Not supported |
ST_InteriorRingN() | Not supported |
ST_Intersection() | Not supported |
ST_IsClosed() | Not supported |
ST_IsEmpty() | Not supported |
ST_IsSimple() | Not supported |
ST_IsValid() | Not supported |
ST_LatFromGeoHash() | Not supported |
ST_LineInterpolatePoint() | Not supported |
ST_LineInterpolatePoints() | Not supported |
ST_LongFromGeoHash() | Not supported |
ST_NumGeometries() | Not supported |
ST_NumInteriorRing() | Not supported |
ST_NumInteriorRings() | Not supported |
ST_NumPoints() | Not supported |
ST_PointAtDistance() | Not supported |
ST_PointFromGeoHash() | Not supported |
ST_PointN() | Not supported |
ST_StartPoint() | Not supported |
ST_SwapXY() | Not supported |