MaxCompute SQL differs from standard SQL in several important ways. If you are migrating from MySQL, PostgreSQL, or another standard SQL database, review these differences before writing queries or designing your schema. Each entry below describes a specific limitation and a concrete workaround.
Basic differences between MaxCompute SQL and standard SQL
Category | Limitation | Solution |
Transactions | COMMIT and ROLLBACK are not supported. Avoid using INSERT INTO. | Write code with idempotence so it can be re-run safely. Use INSERT OVERWRITE instead of INSERT INTO. |
Indexes and primary keys | Indexes and primary key constraints are not supported. | Enforce uniqueness in your application logic at write time rather than relying on database-level constraints. |
Default values | Field-level default values and default functions are not supported for most fields. When creating a table, you can set default values on BIGINT, DOUBLE, BOOLEAN, and STRING columns. | Assign default values explicitly during data writes when a field requires one. |
Auto-increment fields | Auto-increment fields are not supported. | Generate unique identifiers in your application layer or use a user-defined function (UDF) before inserting data. |
Partition count | A table supports a maximum of 60,000 partitions. Exceeding this limit causes an error. | Choose partition key columns that keep the total partition count well below 60,000. |
Partition query limit | A single query can read at most 10,000 partitions. On two-level partitioned tables, filtering only on level-2 partitions may trigger this error when total partitions exceed 10,000. | |
DOUBLE precision | DOUBLE is not a high-precision type. Using | Subtract the two values and check whether the absolute difference falls below a threshold. For example, treat |
Higher precision | DECIMAL is the high-precision numeric type, but it may still fall short for some use cases. | Store the value as STRING and use a UDF for calculations that require precision beyond DECIMAL. |
Data type conversions | Implicit type conversions during JOIN operations can cause unexpected errors or maintenance issues. | Cast fields to matching types before performing a JOIN on columns of different types. |
DATE and STRING implicit conversion | Passing a STRING value into a function that expects DATE triggers an implicit conversion. The conversion uses the format | N/A. |
Differences between DDL and DML and the related solutions
Category | Limitation | Solution |
Renaming partition key columns | The value of a partition key column can be changed, but its name cannot. | See What is the difference between partitions and partition key columns? |
Column modifications | Columns can be added but not removed. Column data types cannot be changed. | See How do I change the data type of a column? and How do I delete a column from a table? |
INSERT syntax | INSERT INTO and INSERT OVERWRITE require the keyword | Add |
INSERT field mapping | When inserting data, MaxCompute maps fields by position (the order of columns in the SELECT clause matched against the order of columns in the table schema), not by alias. | Make sure the column order in your SELECT clause matches the target table schema, or use explicit column lists. |
UPDATE and DELETE | UPDATE and DELETE can only run on transactional tables. | See How do I delete data from a MaxCompute table or partition? and How do I update data in a MaxCompute table or partition? |
MAPJOIN table limit | A single query can MAPJOIN at most 6 small tables. A query can join at most 16 tables consecutively. | |
IN and NOT IN subquery limit | Subqueries after IN, NOT IN, EXISTS, or NOT EXISTS cannot return more than 1,000 partitions. | See How do I ensure NOT IN subqueries return the expected number of rows? If the subquery result is already unique, remove DISTINCT to improve performance. |
SELECT result limit | A single SELECT statement returns at most 10,000 rows by default. | See LIMIT. |
Retrieving large result sets | Fetching more than 10,000 rows from a SELECT query requires a different approach. | See How do I obtain all data if query results exceed 10,000 rows when using SQLTask? |
Cartesian product | JOIN without an ON condition (Cartesian product) is not supported. | Always specify join conditions with ON. To broadcast a small table, use MAPJOIN HINT. |
ORDER BY requires LIMIT | ORDER BY must be paired with LIMIT N. | Specify a LIMIT value. To sort a large dataset or an entire table, increase N. See After I query data in MaxCompute, how are the query results sorted? |
UNION ALL column consistency | All tables in a UNION ALL must have the same number of columns with matching names and data types. | Align column count, names, and types across all SELECT clauses before using UNION ALL. |
UNION ALL placement | A UNION ALL query must be wrapped in a subquery. | Nest the UNION ALL inside a subquery: |