MDEV-30706 Different results of selects from view and CTE with same definition
MDEV-30668 Set function aggregated in outer select used in view definition
This patch fixes two bugs concerning views whose specifications contain
subqueries with set functions aggregated in outer selects.
Due to the first bug those such views that have implicit grouping were
considered as mergeable. This led to wrong result sets for selects from
these views.
Due to the second bug the aggregation select was determined incorrectly and
this led to bogus error messages.
The patch added several test cases for these two bugs and for four other
duplicate bugs.
The patch also enables view-protocol for many other test cases.
Approved by Oleksandr Byelkin <email address hidden>
While performing SAST scanning using Cppcheck against source code of
commit 81196469, several code vulnerabilities were found.
Fix following issues:
1. Parameters of `snprintf` function are incorrect.
Cppcheck error:
client/mysql_plugin.c:1228: error: snprintf format string requires 6 parameters but only 5 are given.
It is due to commit 630d7229 introduced option `--lc-messages-dir`
in the bootstrap command. However the parameter was not even given
in the `snprintf` after changing the format string.
Fix:
Restructure the code logic and correct the function parameters for
`snprintf`.
2. Null pointer is used in a `snprintf` which could cause a crash.
The code intended to print the swift_project name, if the
opt_swift_project_id is NULL but opt_swift_project is not NULL.
However the parameter of `snprintf` was mistakenly using
`opt_swift_project_id`.
Fix:
Change to use the correct string from `opt_swift_project`.
3. Potential double release of a memory
Cppcheck error:
plugin/auth_pam/testing/pam_mariadb_mtr.c:69: error: Memory pointed to by 'resp' is freed twice.
A pointer `resp` is reused and allocated new memory after it has been
freed. However, `resp` was not set to NULL after freed.
Potential double release of the same pointer if the call back
function doesn't allocate new memory for `resp` pointer.
Fix:
Set the `resp` pointer to NULL after the first free() to make sure
the same address is not freed twice.
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.
MDEV-22683: Ensure system tables are correctly upgraded in MariaDB 10.4
Running mysql_upgrade should end up with the exact same system tables as fresh
installations have after running mysql_install_db. To ensure the upgrade is
correct and complete:
- Remove the redundant modification of thread_id`. On 5.5 version, the
`general_log` table was created as `CREATE TABLE IF NOT EXISTS general_log
(..., thread_id INTEGER NOT NULL, ...)`, and starting from 10.0+, the table is
created as `CREATE TABLE IF NOT EXISTS general_log (..., thread_id BIGINT(21)
UNSIGNED NOT NULL, ...)`, but mysql_upgrade is not properly upgrading the
table. It modifies the `thread_id` twice in one query, which could leave the
table not modified and lead to other potential error when upgrading from
MariaDB 5.5 or older.
- Update `servers` to ensure `Host` and `User` has correct data type if
upgrading from 10.1 or older. On versions 10.0 and 10.1, the `servers` table
was created as `CREATE TABLE IF NOT EXISTS servers (..., Host char(64) NOT
NULL DEFAULT , ..., Owner char(64) NOT NULL DEFAULT , ...)`, and starting
from 10.2, the table is created as `CREATE TABLE IF NOT EXISTS servers (...,
Host varchar(2048) NOT NULL DEFAULT , ..., Owner varchar(512) NOT NULL
DEFAULT , ...)`.
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.
1. Adding a separate MY_COLLATION_HANDLER
my_collation_ucs2_general_mysql500_ci_handler
implementing a proper order for ucs2_general_mysql500_ci
The problem happened because ucs2_general_mysql500_ci
erroneously used my_collation_ucs2_general_ci_handler.
2. Cosmetic changes: Renaming:
- plane00_mysql500 to my_unicase_mysql500_page00
- my_unicase_pages_mysql500 to my_unicase_mysql500_pages
to use the same naming style with:
- my_unicase_default_page00
- my_unicase_defaul_pages
3. Moving code fragments from
- handler::check_collation_compatibility() in handler.cc
- upgrade_collation() in table.cc
into new methods in class Charset, to reuse the code easier.
MDEV-28603 Invalid view when its definition uses TVC as single-value subquery
Subselect_single_value_engine cannot handle table value constructor used as
subquery. That's why any table value constructor TVC used as subquery is
converted into a select over derived table whose specification is TVC.
Currently the names of the columns of the derived table DT are taken from
the first element of TVC and if the k-th component of the element happens
to be a subquery the text representation of this subquery serves as the
name of the k-th column of the derived table. References of all columns of
the derived table DT compose the select list of the result of the conversion.
If a definition of a view contained a table value constructor used as a
subquery and the view was registered after this conversion had been
applied we could register an invalid view definition if the first element
of TVC contained a subquery as its component: the name of this component
was taken from the original subquery, while the name of the corresponding
column of the derived table was taken from the text representation of the
subquery produced by the function SELECT_LEX::print() and these names were
usually differ from each other.
To avoid registration of such invalid views the function SELECT_LEX::print()
now prints the original TVC instead of the select in which this TVC has
been wrapped. Now the specification of registered view looks like as if no
conversions from TVC to selects were done.
Approved by Oleksandr Byelkin <email address hidden>
note that `KILL USER foo` should *not* fail with ER_KILL_DENIED_ERROR
when SHOW PROCESSLIST doesn't show connections of that user.
Because no connections exist or because the caller has no PROCESS -
doesn't matter.
also, fix the error message to make sense
("You are not owner of thread <current connection id>" is ridiculous)
