A parenthesized query expression that already has its own ORDER BY or
LIMIT and is followed by another ORDER BY, e.g.
(SELECT c1 FROM t1 ORDER BY c1 LIMIT 2) ORDER BY <expr> LIMIT 1
is wrapped into a derived table, and the outer ORDER BY/LIMIT is attached
to the wrapping select. The grammar pushed the inner select before
parsing the tail, and add_tail_to_query_expression_body_ext_parens()
created the wrapper only after the tail had been parsed. Anything that
is attached to the current select while the tail is being parsed was
therefore attached to the inner select, while the ORDER BY list itself
was moved to the wrapper:
- Window functions and their window specs were added to the inner
select's window_funcs/window_specs. The same Item_window_func was then
in the wrapper's ORDER BY and in the inner select's window function
list, which triggers the original assertion reported in MDEV-41225.
(SELECT 1 FROM t1 LIMIT 1)
ORDER BY PERCENTILE_DISC(1) WITHIN GROUP(ORDER BY TIME'0') OVER();
The function is computed by the inner select over the rows before its
LIMIT, so the outer ORDER BY sorts on wrong values
(e.g. COUNT(*) OVER () returned 3 for a 2-row result).
- Subqueries were registered as units of the inner select. An
uncorrelated subquery in the tail crashed the server with SIGSEGV when
it was executed by the outer filesort. A correlated one resolved its
outer references in the inner select (t1.c1) instead of the derived
table.
The decision to wrap depends only on whether the inner select already
has a tail and on whether the outer tail has ORDER BY. LIMIT and locking
clauses cannot contain window functions or subqueries.
We replace query_expression_tail with
query_expression_tail_with_order and query_expression_tail_no_order
so the mid-rule action knows whether an ORDER BY follows.
The new LEX::push_select_for_ext_parens_tail() creates the wrapper before
an ORDER BY tail is parsed when wrapping is required, and pushes it instead
of the inner select. The tail's items are then created in the wrapper's
context, and window functions and subqueries are attached to the wrapper.
add_tail_to_query_expression_body_ext_parens() takes the pushed select and
skips wrapping when it has already happened; the other cases keep the
previous logic. The grammar has the same number of conflicts as before.
A subquery in such a tail can no longer refer to the tables of the wrapped
query expression (e.g. t1.c1 above) and gets ER_BAD_FIELD_ERROR,
as those tables are not visible outside the derived table.
This commit was prepared with Claude Code (Opus 5.5). It traced the
wrong-result and crash cases to the parse-time registration of window
functions and subqueries in the inner select, tested the grammar split
with bison, wrote the code change and the brackets test.
Optimization in st_select_lex_unit::prepare() removes ORDER BY for
certain subqueries. That excludes ORDER BY items from being fixed, but
sl->window_funcs still contains window function items and those
related to optimized out ORDER BY are unfixed. The error about missing
window spec is thrown when the item is fixed. Hence we get redundant
processing of window function items without checking window spec
existence.
The fix removes the related window function items when ORDER BY is
optimized out. ORDER accumulates window_funcs at parser stage which
are then removed from SELECT_LEX::window_funcs. The fix also updates
similar optimization in mysql_make_view().
Fixed parser inconsistency where CHANGE MASTER TO master_heartbeat_period
rejected numeric values with an explicit '+' sign, while other parameters
like master_connect_retry accepted them.
The issue was in sql/sql_yacc.yy where master_heartbeat_period used
NUM_literal (which doesn't accept '+'), while other parameters used
ulong_num (which includes opt_plus).
Solution: Added opt_plus before NUM_literal in the master_heartbeat_period
grammar rule, making it consistent with other numeric parameters.
Added test case to verify:
- master_heartbeat_period=+60 now works (was broken)
- master_heartbeat_period=60 still works (backward compatible)
All new code of the whole pull request, including one or several files
that are either new files or modified ones, are contributed under the
BSD-new license. I am contributing on behalf of my employer Amazon Web
Services, Inc.
The Port field in the system table mysql.servers has type INT,
which translates to Field_long.
During parsing it is parsed as ulong_num, and in this patch we add
bound checks there.
SHOW EXPLAIN FOR, and EXPLAIN/DESC FOR CONNECTION should behave
identically. However, for a query with an addition expression containing
INTERVAL and NOT IN sub-select SHOW EXPLAIN FOR was correctly throwing
parse error, where as EXPLAIN/DESC FOR CONNECTION was crashing.
The reason for the crash is that select block was not initialized and
was being accessed inside the NOT IN sub-select in the EXPLAIN/DESC FOR
CONNECTION case.
Routine name resolution performed on a temporary memory root
during execution. So on the second execution LEX::spname members
pointed to a cleared memory.
Fixing to peform the resolution to parse time, like the CALL statement does.
During the execution time LEX::spname members now stay untouched.
mariadb from a git tree
Problems:
1. bison incorrectly issues a warning when it sees "b4_bin"
2. MacOSX linker issues a warning about duplicate mysys/dbug/etc libraries.
On 1: worked around by adding a compiler define with a different name
aliasing the character set variable used with a name that
won't trigger the bison warning
On 2: This is due to the fact that there's a circular dependency between
mysys and dbug (among others). Turned the warning off by adding a compiler
option to be applied to targets lined with mysys to turn the warning off.
forever, cannot be killed
mysql_rm_table_no_locks() does TDC_RT_REMOVE_ALL which waits while
share is closed. The table normally is open only as OPEN_STUB, this is
what parser does for CREATE TABLE. But for SELECT the table is opened
not as a stub. If it is the same table name we anyway have two
TABLE_LIST objects: stub and not stub. So for "not stub"
TDC_RT_REMOVE_ALL sees open count and decides to wait until it is
closed. And it hangs because that was opened in the same thread.
The fix disables subqueries in CHECK expression at parser
level. Thanks to Sergei Golubchik <serg@mariadb.org> for the patch.
The segfault in wsrep_check_sequence is due to a
null pointer deference on:
db_type= thd->lex->create_info.db_type->db_type;
Where create_info.db_type is null. This occured under
a used_engine==true condition which is set in the calling
function based on create_info.used_fields==HA_CREATE_USED_ENGINE.
However the create_info.used_fields was a left over
from the parsing of the previous failed CREATE TABLE where
because of its failure, db_type wasn't populated.
This is corrected by cleaning the create_info when we start
to parse ALTER SEQUENCE statements.
Other paths to wsrep_check_sequence is via CREATE SEQUENCE
and CREATE TABLE LIKE which both initialize the create_info
correctly.
MariaDB in a COLLATE clause supported 'binary' only as an identifier:
COLLATE `binary`
Fixing the parser to understand 'binary' as a keyword:
COLLATE binary
This is for MySQL compatibility.
Search conditions were evaluated using val_int(), which was wrong.
Fixing the code to use val_bool() instead.
Details:
- Adding a new item_base_t::IS_COND flag which marks Items used
as <search condition> in WHERE, HAVING, JOIN ON, CASE WHEN clauses.
The flag is at the parse time.
These expressions must be evaluated using val_bool() rather than val_int().
Note, the optimizer creates more Items which are used as search conditions.
Most of these items are not marked with IS_COND yet. This is OK for now,
but eventually these Items can also be fixed to have the flag.
- Adding a method Item::is_cond() which tests if the Item has the IS_COND flag.
- Implementing Item_cache_bool. It evaluates the cached expression using
val_bool() rather than val_int().
Overriding Type_handler_bool::Item_get_cache() to create Item_cache_bool.
- Implementing Item::save_bool_in_field(). It uses val_bool() rather than
val_int() to evaluate the expression.
- Implementing Type_handler_bool::Item_save_in_field()
using Item::save_bool_in_field().
- Fixing all Item_bool_func descendants to implement a virtual val_bool()
rather than a virtual val_int().
- To find places where val_int() should be fixed to val_bool(), a few
DBUG_ASSERT(!is_cond()) where added into val_int() implementations
of selected (most frequent) classes:
Item_field
Item_str_func
Item_datefunc
Item_timefunc
Item_datetimefunc
Item_cache_bool
Item_bool_func
Item_func_hybrid_field_type
Item_basic_constant descendants
- Fixing all places where DBUG_ASSERT() happened during an "mtr" run
to use val_bool() instead of val_int().
(Polished initial patch by Alexey Botchkov)
Make the code handle DEFAULT values of any datatype
- Make Json_table_column::On_response::m_default be Item*, not LEX_STRING.
- Change the parser to use string literal non-terminals for producing
the DEFAULT value
-- Also, stop updating json_table->m_text_literal_cs for the DEFAULT
value literals as it is not used.
Improve performance of queries like
SELECT * FROM t1 WHERE field = NAME_CONST('a', 4);
by, in this example, replacing the WHERE clause with field = 4
in the case of ref access.
The rewrite is done during fix_fields and we disambiguate this
case from other cases of NAME_CONST by inspecting where we are
in parsing. We rely on THD::where to accomplish this. To
improve performance there, we change the type of THD::where to
be an enumeration, so we can avoid string comparisons during
Item_name_const::fix_fields. Consequently, this patch also
changes all usages of THD::where to conform likewise.
The @@global.character_set_client variable could erroneously be set
to a non-default collation of its character set, which further made
the `SET NAMES DEFAULT` statement crash the server.
Fixing the code to make sure that the global value these variables:
@@character_set_client
@@character_set_connection
@@character_set_server
@@character_set_database
@@character_set_connection
point to the default compiled collations of the character set.
I checked all stack overflow potential problems found with
gcc -Wstack-usage=16384
and
clang -Wframe-larger-than=16384 -no-inline
Fixes:
Added '#pragma clang diagnostic ignored "-Wframe-larger-than="'
to a lot of function to where stack usage large but resonable.
- Added stack check warnings to BUILD scrips when using clang and debug.
Function changed to use malloc instead allocating things on stack:
- read_bootstrap_query() now allocates line_buffer (20000 bytes) with
malloc() instead of using stack. This has a small performance impact
but this is not releant for bootstrap.
- mroonga grn_select() used 65856 bytes on stack. Changed it to use
malloc().
- Wsrep_schema::replay_transaction() and
Wsrep_schema::recover_sr_transactions().
- Connect zipOpen3()
Not fixed:
- mroonga/vendor/groonga/lib/expr.c grn_proc_call() uses
43712 byte on stack. However this is not easy to fix as the stack
used is caused by a lot of code generated by defines.
- Most changes in mroonga/groonga where only adding of pragmas to disable
stack warnings.
- rocksdb/options/options_helper.cc uses 20288 of stack space.
(no reason to fix except to get rid of the compiler warning)
- Causes using alloca() where the allocation size is resonable.
- An issue in libmariadb (reported to connectors).
The IDENT_sys doesn't include keywords, so the function with the
keyword name can be created, but cannot be called.
Moving keywords to new rules keyword_func_sp_var_and_label and
keyword_func_sp_var_not_label so the functions with these
names are allowed.
Like all IF NOT EXISTS syntax, a Note should be generated.
The original commit of Seqeuences cleared the IF NOT EXISTS part
in the sql/sql_yacc.yy with lex->create_info.init(). Without this
bit set there was no way it could do anything other than error.
To remedy this removal, the sql_yacc.yy components have been
minimised as they where all set at the beginning of the ALTER.
This way the opt_if_not_exists correctly set the IF_EXISTS flag.
In MDEV-13005 (bb4dd70e7c) the error code changed, requiring
ER_UNKNOWN_SEQUENCES to be handled in the function
No_such_table_error_handler::handle_condition.
The crash happened with an indexed virtual column whose
value is evaluated using a function that has a different meaning
in sql_mode='' vs sql_mode=ORACLE:
- DECODE()
- LTRIM()
- RTRIM()
- LPAD()
- RPAD()
- REPLACE()
- SUBSTR()
For example:
CREATE TABLE t1 (
b VARCHAR(1),
g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,
KEY g(g)
);
So far we had replacement XXX_ORACLE() functions for all mentioned function,
e.g. SUBSTR_ORACLE() for SUBSTR(). So it was possible to correctly re-parse
SUBSTR_ORACLE() even in sql_mode=''.
But it was not possible to re-parse the MariaDB version of SUBSTR()
after switching to sql_mode=ORACLE. It was erroneously mis-interpreted
as SUBSTR_ORACLE().
As a result, this combination worked fine:
SET sql_mode=ORACLE;
CREATE TABLE t1 ... g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL, ...;
INSERT ...
FLUSH TABLES;
SET sql_mode='';
INSERT ...
But the other way around it crashed:
SET sql_mode='';
CREATE TABLE t1 ... g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL, ...;
INSERT ...
FLUSH TABLES;
SET sql_mode=ORACLE;
INSERT ...
At CREATE time, SUBSTR was instantiated as Item_func_substr and printed
in the FRM file as substr(). At re-open time with sql_mode=ORACLE, "substr()"
was erroneously instantiated as Item_func_substr_oracle.
Fix:
The fix proposes a symmetric solution. It provides a way to re-parse reliably
all sql_mode dependent functions to their original CREATE TABLE time meaning,
no matter what the open-time sql_mode is.
We take advantage of the same idea we previously used to resolve sql_mode
dependent data types.
Now all sql_mode dependent functions are printed by SHOW using a schema
qualifier when the current sql_mode differs from the function sql_mode:
SET sql_mode='';
CREATE TABLE t1 ... SUBSTR(a,b,c) ..;
SET sql_mode=ORACLE;
SHOW CREATE TABLE t1; -> mariadb_schema.substr(a,b,c)
SET sql_mode=ORACLE;
CREATE TABLE t2 ... SUBSTR(a,b,c) ..;
SET sql_mode='';
SHOW CREATE TABLE t1; -> oracle_schema.substr(a,b,c)
Old replacement names like substr_oracle() are still understood for
backward compatibility and used in FRM files (for downgrade compatibility),
but they are not printed by SHOW any more.
