ece174d55d
Adding support for UPDATE .. RETURNING .. INTO queries.
For example:
UPDATE t1 SET a=10,b=20 RETURNING a,b INTO va,vb;
UPDATE t1 SET a=10,b=20 RETURNING a,b INTO @a,@b;
Limitations:
1. These types of queries:
- REPLACE .. RETURNING .. INTO
- DELETE .. RETURNING .. INTO
- INSERT .. RETURNING .. INTO
do not work - they return an error.
They will be implemented separately, when needed.
2. UPDATE..RETURNING..INTO with --binlog_format=statement is not allowed
and an error is raised.
3. Using OLD_VALUE(col) inside UPDATE..RETURNING..INTO is not allowed
and an error is raised.
4. Multi-table updates, as well as single table updates with a subquery
to the same table in WHERE (which get converted to multi-table) do
not work and an error is raised.
Notes:
1. ANALYZE and EXPLAIN
Both
ANALYZE UPDATE .. RETURNING .. INTO ..
EXPLAIN UPDATE .. RETURNING .. INTO ..
return this error:
'RETURNING..INTO' is not allowed in this context
2. Behavior on no data
a. In case of degenerated plans (WHERE 1=0, LIMIT 0),
no errors are raised.
b. If the updated table contains no rows, the behavior depends on the engine,
for example:
- MyISAM returns no errors
- InnoDB raises
No data - zero rows fetched, selected, or processed
This behavior is engine dependent because some engines (e.g. MyISAM)
quickly know that the table has no records and execute the statement
using a degenerated plan.
c. If there are some rows, but non of them match the WHERE condition,
then this error is raised:
No data - zero rows fetched, selected, or processed
d. If some rows where found but none of them actually
got changed by the SET, still this error is raised:
No data - zero rows fetched, selected, or processed
The error message might be misleading. However, if we read
it as "zero rows [that required updates] fetched", it looks OK.
Let's not introduce a new error message for now.
3. Behavior on VIEW with CHECK OPTION
The "too many rows" check is done after the view's CHECK OPTION check.
With UPDATE IGNORE, a row rejected by WITH CHECK OPTION is skipped
(with a warning) and does not count as an updated row, so
UPDATE IGNORE .. RETURNING .. INTO does not raise ER_TOO_MANY_ROWS
if only one row is actually updated.
Helper changes:
1. The grammar in analyze_stmt_command was changed to have
LEX::analyze_stmt set to true earlier, so
LEX::set_returning_into_result() already knows if this
is an ANALYZE statement.
2. The Sql_cmd_update constructor is now called earlier in the grammar,
to be able to call Sql_cmd_update::set_with_old_value_items()
in the SET and RETURNING clauses.
3. Sql_cmd_dml::lex is now set during the constructor time.
It makes things easier:
- Sql_cmd_update::returns_result_set() needs the lex.
- Sql_cmd_delete::orig_multitable and Sql_cmd_update::orig_multitable
are not needed any more.
They were used only in Sql_cmd_delete::sql_command_code() and
Sql_cmd_update::sql_command_code().
Sql_cmd_dml::sql_command_code() now returns lex->sql_command.
The overrides Sql_cmd_delete::sql_command_code() and
Sql_cmd_update::sql_command_code() were removed.
This commit also fixes bugs found during testing:
- MDEV-41262 ER_TOO_MANY_ROWS should be checked after CHECK OPTION in view
while Update Returning Into
- MDEV-41265 SELECT/ UPDATE RETURNING INTO a single row-field fails with
ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT, even though the column count matches
This bug was also fixed in 12.3.4.
- MDEV-41260 Misleading message text "uses a LIMIT clause" after Update
Returning Into
MariaDB Server
MariaDB Server is one of the most widely deployed open source relational databases. It is built by a global community of contributors together with the MariaDB Foundation and MariaDB plc, and it powers workloads from small applications to large-scale production systems.
MariaDB began as a fork of MySQL, led by the original MySQL developers, and it maintains a high degree of MySQL compatibility. It has since grown into a database with its own capabilities, including native vector search, multiple pluggable storage engines, synchronous clustering, and analytical features, while remaining a straightforward migration target for existing MySQL deployments.
Key features
- Native vector search. A built-in VECTOR data type with approximate nearest-neighbour indexing (HNSW), available since MariaDB 11.8 with no extension required.
- Pluggable storage engines. InnoDB (default for transactional workloads), Aria, MyRocks, ColumnStore for analytics, Spider for sharding, and S3 for archival, among others.
- Replication and clustering. Asynchronous, semi-synchronous, and parallel replication with global transaction IDs, plus Galera synchronous multi-primary clustering.
- Advanced SQL. Common table expressions and recursive CTEs, window functions, system-versioned (temporal) tables, sequences, and a broad set of JSON functions.
- MySQL and Oracle compatibility. Wire-protocol and syntax compatibility with MySQL, plus an Oracle SQL mode supporting PL/SQL-style stored routines.
- Spatial and full-text search. GIS data types and functions, and built-in full-text indexing.
- Security. Role-based access control, pluggable authentication (ed25519, PAM, GSSAPI, and more), data-at-rest encryption, and TLS for connections.
Documentation
- Project home, community, and getting involved
- Reference manual and release notes
- MariaDB compared to MySQL
Releases
MariaDB Server follows a yearly long-term support (LTS) model alongside quarterly rolling releases. Binary LTS releases are maintained for three years and are recommended for production; rolling releases deliver new features sooner with a shorter support window.
For the current version and full history, see Releases and the MariaDB release calendar.
Installing
Packages and installation instructions for all supported platforms are available at https://mariadb.org/download/.
Building from source
To build MariaDB from source and run the test suite, follow the developer guide
It covers building the code correctly, running the MariaDB testing framework, and choosing the right branch to target for contributions.
Contributing
Contributions are welcome, from code and documentation to bug reports and reviews. See CONTRIBUTING.md to get started, and CODING_STANDARDS.md for code style. Contributors and community members are recognised in CREDITS.
Getting help
- Zulip chat
- Maria Discuss mailing list
- Bug reports
- Security vulnerabilities: see SECURITY.md
Licensing
MariaDB Server is licensed under version 2 of the GNU General Public License (GPLv2), without the "any later version" clause. This is inherited from MySQL. License information is in the COPYING file, and third-party license information is in the THIRDPARTY file.
About MariaDB Foundation
MariaDB is brought to you by the MariaDB Foundation and MariaDB plc. The MariaDB Foundation is the custodian of the MariaDB Server codebase, ensuring it stays open and that anyone can contribute. See CREDITS for details on the Foundation and the people developing MariaDB.
MySQL, the base of MariaDB, is a product and trademark of Oracle Corporation, Inc.