WL#3681: ALTER TABLE ORDER BY
Affects: Server-5.0 — Status: Complete — Priority: Medium
This Work Log entry is related to BUG#24562. Resolution of the bug requires a design decision, which is the purpose of this WL. An "ALTER TABLE ORDER BY" statement exist in the syntaxes accepted by MySQL. According to the documentation, this syntax: - only accept *one* column, as in "ALTER TABLE t ORDER BY col;" - is used to reorder physically the rows in a table, for optimizations. In the test suite, all the occurrences of this statement are : test 1: alter table t1 add column new_col int, order by payoutid,bandid; select * from t1; alter table t1 order by bandid,payoutid; select * from t1; drop table t1; test 2: alter table t1 order by n; select * from t1; drop table t1; test 3: ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body; DROP TABLE t1; According to the grammar implemented in the parser, the ALTER TABLE ORDER BY statement can accept a list of expressions, with optional ASC or DESC keywords, so that the following is syntactically legal : ALTER TABLE t1 ORDER BY t1.id DESC, length(t1.status) * t1.type_id ASC, sin(rand() * pi()), @my_user_variable, (select ... from ... where ...), my_stored_function(t1.body) / my_udf(t1.body); However, this constructs are neither documented nor tested (not to mention they crash the server), which raise the question of their usefulness and validity. Note that ALTER TABLE ORDER BY is not defined by the SQL-2003 specification, but is a MySQL proprietary feature. Given that: - ALTER TABLE can combine several ALTER clauses in the same statement, - most ALTER clauses are pure DDL statements, - ORDER BY is a pure DML statement, attempting to support general expressions will lead to - very complex dead lock issues, since ALTER on table T1 would need to lock dependent tables involved in the general expression, - very complex replication use cases (stored functions with side effects ?) - bugous code in general Contrary to an ORDER BY clause in a SELECT, which can involve general expressions and is useful for a user to implement application logic, an ALTER TABLE ORDER BY is supposed to be used to optimize data in one table only, and does not need to support full expressions. As a result, the design proposal of this work log is to implement ALTER TABLE ORDER BY with the following order by clause only : - a list of order clauses is accepted, - each order clause consist of a column name only, followed by an optional ASC or DESC keyword. This implies that: - Native functions, - stored functions, - user defined functions, - sub selects, - literals, - arithmetic, - operators in general can not be used. Previous user code that today may use these undocumented features, with an unknown result, will be rejected. This is not considered an incompatible change. Implementation of this WL is proposed for 5.0 and 5.1: it is required in 5.0 to prevent crashes.
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.