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 the following:
SELECT statements that acquire locks, such as
SELECT ... FOR [UPDATE | SHARE] ....SELECT statements that contain aggregate functions with frame definitions, such as:
SELECT time, subject, val, SUM(val) OVER ( PARTITION BY subject ORDER BY time ROWS UNBOUNDED PRECEDING -- Frame definitions in the window function. Such a SELECT statement does not support IMCIs. ) AS running_total FROM observations;SELECT statements where subqueries are in the GROUP BY clause, such as
SELECT SUM(a) FROM t1 GROUP BY (SELECT ... FROM ...) as some_subquery;SELECT statements where subqueries are in the ORDER BY clause, such as
SELECT a FROM t1 ORDER BY (SELECT ... FROM ...) as some_subquery;SELECT statements with correlated references in subquery join conditions, such as
WHERE t1.a in (SELECT t2.a FROM t2 INNER JOIN t3 on t2.a = t3.a AND t2.b > t1.b);SELECT statements with window functions in subqueries and with correlated references in subquery HAVING conditions.
SELECT statements with correlated references in the subquery UNION clause.
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 | IMCIs supported? |
SOUNDS LIKE | No |
BETWEEN...AND... | Yes Note If |
GREATEST() | Yes 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() | Yes Note If |
LEAST() | Yes 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... | Yes Note If |
String expressions
Expression | IMCIs supported? |
SOUNDEX() | No |
SOUNDS LIKE | No |
MATCH | No |
LOAD_FILE() | No |
TIMESTAMP() | Yes Note When the IMCI feature is enabled, you can specify only one parameter. |
Aggregate function expressions
Function | IMCIs supported? |
JSON_ARRAYAGG() | No |
JSON_OBJECTAGG() | No |
Window function expressions
Expression | IMCIs supported? |
FIRST_VALUE() | No |
NTH_VALUE() | No |
LAST_VALUE() | No |
Encryption and compression expressions
Function | IMCIs supported? |
AES_DECRYPT() | No |
AES_ENCRYPT() | No |
COMPRESS() | No |
RANDOM_BYTES() | No |
STATEMENT_DIGEST() | No |
STATEMENT_DIGEST_TEXT() | No |
UNCOMPRESS() | No |
UNCOMPRESSED_LENGTH() | No |
VALIDATE_PASSWORD_STRENGTH() | No |
JSON functions
Function | IMCIs supported? |
JSON_ARRAY() | No |
JSON_ARRAY_APPEND() | No |
JSON_ARRAY_INSERT() | No |
JSON_CONTAINS_PATH() | No |
JSON_INSERT() | No |
JSON_MERGE() | No |
JSON_MERGE_PATCH() | No |
JSON_MERGE_PRESERVE() | No |
JSON_OBJECT() | No |
JSON_OVERLAPS() | No |
JSON_REPLACE() | No |
JSON_SCHEMA_VALID() | No |
JSON_SCHEMA_VALIDATION_REPORT() | No |
JSON_SEARCH() | No |
JSON_SET() | No |
JSON_STORAGE_FREE() | No |
JSON_STORAGE_SIZE() | No |
JSON_VALUE() | No |
MEMBER OF() | No |
Spatial functions
Function | IMCIs supported? |
ST_AsGeoJSON() | No |
ST_Buffer() | No |
ST_Buffer_Strategy() | No |
ST_Centroid() | No |
ST_Collect() | No |
ST_ConvexHull() | No |
ST_EndPoint() | No |
ST_ExteriorRing() | No |
ST_FrechetDistance() | No |
ST_GeoHash() | No |
ST_GeometryN() | No |
ST_GeometryType() | No |
ST_GeomFromGeoJSON() | No |
ST_HausdorffDistance() | No |
ST_InteriorRingN() | No |
ST_Intersection() | No |
ST_IsClosed() | No |
ST_IsEmpty() | No |
ST_IsSimple() | No |
ST_IsValid() | No |
ST_LatFromGeoHash() | No |
ST_LineInterpolatePoint() | No |
ST_LineInterpolatePoints() | No |
ST_LongFromGeoHash() | No |
ST_NumGeometries() | No |
ST_NumInteriorRing() | No |
ST_NumInteriorRings() | No |
ST_NumPoints() | No |
ST_PointAtDistance() | No |
ST_PointFromGeoHash() | No |
ST_PointN() | No |
ST_StartPoint() | No |
ST_SwapXY() | No |