SET sql_mode=ORACLE;
DELIMITER $$
CREATE OR REPLACE PACKAGE pkg AS
-- Declare a package public data type
TYPE varchar_array IS TABLE OF VARCHAR(2000) INDEX BY INTEGER;
END;
$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE PROCEDURE p1 AS
v pkg.varchar_array; -- Use the package public data type
BEGIN
v(0):='test';
SELECT v(0);
END;
$$
DELIMITER ;
Note, the change is done only for sql_mode=ORACLE, because the TYPE
declaration is not available for the default mode.
Where package-wide types are available
--------------------------------------
- Variabe list type:
DECLARE var pkg1.type1;
- RETURN type for a package routine:
CREATE FUNCTION .. RETURN pkg1.type1 ...
- Parameter type for a package routine:
PROCEDURE p1(param1 pkg1.type1);
- Assoc array element type:
TYPE assoc1_t IS TABLE OF pkg1.type1 ...
- REF CURSOR RETURN type:
TYPE cur1_t IS REF CURSOR RETURN pkg1.type1;
Change details
--------------
- Adding a member Lex_length_and_dec_st::m_foreign_module_type
It's set to true when the data type was initialized from a TYPE
in foreign routine (e.g. in PACKAGE spec).
It's needed to prevent use of qualified identifiers in public contexts,
i.e. in schema public routine parameter types and schema publuc function
RETURN types.
Adding a helper method sp_head::check_applicability() which prevents
use of qualified types in public context.
- Adding a helper method sp_head::raise_unknown_data_type().
- Adding methods LEX::set_field_type_typedef_package_spec() for
2-step and 3-step qualified indentifiers.
It's used in field_type_all_with_typedefs which covers cases:
- Variabe list type : DECLARE var pkg1.type1;
- RETURN type : CREATE FUNCTION .. RETURN pkg1.type1 ...
- Parameter type : PROCEDURE p1(param1 pkg1.type1);
- Assoc array element type : TYPE assoc1_t IS TABLE OF pkg1.type1 ...
- Adding a method LEX::declare_type_ref_cursor_return_typedef().
It handles cases when a new TYPE REF CURSOR RETURN is declared,
for both for qualified RETURN types and non-qualified RETURN types:
- TYPE cur0_t IS REF CURSOR RETURN rec1_t;
- TYPE cur0_t IS REF CURSOR RETURN pkg1.rec1_t;
- TYPE cur0_t IS REF CURSOR RETURN db1.pkg1.rec1_t;
The code was moved from LEX::declare_type_ref_cursor() into
LEX::declare_type_ref_cursor_return_typedef() and extended
to cover qualified RETURN types.
- Adding a method Sql_path::find_package_spec_type().
It iterates through all schemas specified in @@path and searches
for the given type in the given package.
- Adding a helper method sp_pcontext::type_defs_add_ref_cursor()
to reuse the code.
- Adding a new method sp_package::get_typedef() to search
for TYPE definitions in PACKAGE specifications.
- Adding a new method sp_head::get_typedef_package_spec()
to search for TYPE definitions used by a PROCEDURE or FUNCTION.
- Adding a helper method
Sp_handler::sp_cache_routine_reentrant_suppress_errors
Adding a method Sp_handler::find_package_spec().
The method Type_handler::Column_definition_fix_attributes() was an
addition for Type_handler::Column_definition_set_attributes().
The code in virtual implementations of Column_definition_fix_attributes()
has now been joined to corresponding virtual implementations of
Column_definition_set_attributes().
After this change everything is done in Column_definition_set_attributes().
This makes the code clearer. Previous implementation was rather confusing.
For example, for temporal data types:
(1) After Column_definition_set_attributes():
- Column_definition::length meant the fractional precision
- Column_definition::decimals was set to 0
(2) After Column_definition_fix_attributes():
- Column_definition::length means the full character length
- Column_definition::decimals means the fractional precision
Now everything gets set to (2) right in Column_definition_set_attributes().
(Cherry-pick into 12.2: the fix is enabled by default,
control via @@new_mode=FIX_INDEX_LOOKUP_COST is removed)
They were based on the maximum possible length key length, which
can be much larger than the actual data.
The return value is used by handler::keyread_time(), which is used
to estimate the cost of range access.
This could cause range access not to be picked, even if it uses
the clustered PK and reads about 8% of the table.
The fix is to add KEY::stat_storage_length (next to KEY::rec_per_key) and
have the storage engine fill it in handler::info(HA_STATUS_CONST).
Currently, only InnoDB fills this based on its internal statistics:
index->stat_index_size and ib_table->stat_n_rows.
Also changed:
- In handler::calculate_costs(), use ha_keyread_clustered_time() when
computing clustered PK read cost, not ha_keyread_time().
They were based on the maximum possible key tuple length, which can be
much larger than the real data size.
The return value is used by handler::keyread_time(), which is used
to estimate the cost of range access.
This could cause range access not to be picked, even if it uses
the clustered PK and reads about 8% of the table.
The fix is to add KEY::stat_storage_length (next to KEY::rec_per_key) and
have the storage engine fill it in handler::info(HA_STATUS_CONST).
Currently, only InnoDB fills this based on its internal statistics:
index->stat_index_size and ib_table->stat_n_rows.
Also changed:
- In handler::calculate_costs(), use ha_keyread_clustered_time() when
computing clustered PK read cost, not ha_keyread_time().
The fix is OFF by default and enabled by setting FIX_INDEX_LOOKUP_COST flag
in @@new_mode.
The NULL-aware index statistics fix is now controlled by the
FIX_INDEX_STATS_FOR_ALL_NULLS flag and disabled by default
for preserving execution plan stability in stable versions.
To enable:
SET @@new_mode = 'FIX_INDEX_STATS_FOR_ALL_NULLS';
Or via command line:
--new-mode=FIX_INDEX_STATS_FOR_ALL_NULLS
Or in configuration file:
[mysqld]
new_mode=FIX_INDEX_STATS_FOR_ALL_NULLS
`all_nulls_key_parts` bitmap is now calculated at set_statistics_for_table()
When all values in an indexed column are NULL, EITS statistics show
avg_frequency == 0. This commit adds logic to distinguish between
"no statistics available" and "all values are NULL" scenarios.
For NULL-rejecting conditions (e.g., t1.col = t2.col), when statistics
confirm all indexed values are NULL, the optimizer can now return a
very low cardinality estimate (1.0) instead of unknown (0.0), since
NULL = NULL never matches.
For non-NULL-rejecting conditions (e.g., t1.col <=> t2.col),
normal cardinality estimation continues to apply since matches are possible.
Changes:
- Added KEY::rec_per_key_null_aware() to check nulls_ratio from column
statistics when avg_frequency is 0
- Modified best_access_path() in sql_select.cc to use the new
rec_per_key_null_aware() method for ref access cost estimation
- The optimization works with single-column and composite indexes,
checking each key part's NULL-rejecting status via notnull_part bitmap
This patch adds support for SYS_REFCURSOR (a weakly typed cursor)
for both sql_mode=ORACLE and sql_mode=DEFAULT.
Works as a regular stored routine variable, parameter and return value:
- can be passed as an IN parameter to stored functions and procedures
- can be passed as an INOUT and OUT parameter to stored procedures
- can be returned from a stored function
Note, strongly typed REF CURSOR will be added separately.
Note, to maintain dependencies easier, some parts of sql_class.h
and item.h were moved to new header files:
- select_results.h:
class select_result_sink
class select_result
class select_result_interceptor
- sp_cursor.h:
class sp_cursor_statistics
class sp_cursor
- sp_rcontext_handler.h
class Sp_rcontext_handler and its descendants
The implementation consists of the following parts:
- A new class sp_cursor_array deriving from Dynamic_array
- A new class Statement_rcontext which contains data shared
between sub-statements of a compound statement.
It has a member m_statement_cursors of the sp_cursor_array data type,
as well as open cursor counter. THD inherits from Statement_rcontext.
- A new data type handler Type_handler_sys_refcursor in plugins/type_cursor/
It is designed to store uint16 references -
positions of the cursor in THD::m_statement_cursors.
- Type_handler_sys_refcursor suppresses some derived numeric features.
When a SYS_REFCURSOR variable is used as an integer an error is raised.
- A new abstract class sp_instr_fetch_cursor. It's needed to share
the common code between "OPEN cur" (for static cursors) and
"OPER cur FOR stmt" (for SYS_REFCURSORs).
- New sp_instr classes:
* sp_instr_copen_by_ref - OPEN sys_ref_curor FOR stmt;
* sp_instr_cfetch_by_ref - FETCH sys_ref_cursor INTO targets;
* sp_instr_cclose_by_ref - CLOSE sys_ref_cursor;
* sp_instr_destruct_variable - to destruct SYS_REFCURSOR variables when
the execution goes out of the BEGIN..END block
where SYS_REFCURSOR variables are declared.
- New methods in LEX:
* sp_open_cursor_for_stmt - handles "OPEN sys_ref_cursor FOR stmt".
* sp_add_instr_fetch_cursor - "FETCH cur INTO targets" for both
static cursors and SYS_REFCURSORs.
* sp_close - handles "CLOSE cur" both for static cursors and SYS_REFCURSORs.
- Changes in cursor functions to handle both static cursors and SYS_REFCURSORs:
* Item_func_cursor_isopen
* Item_func_cursor_found
* Item_func_cursor_notfound
* Item_func_cursor_rowcount
- A new system variable @@max_open_cursors - to limit the number
of cursors (static and SYS_REFCURSORs) opened at the same time.
Its allowed range is [0-65536], with 50 by default.
- A new virtual method Type_handler::can_return_bool() telling
if calling item->val_bool() is allowed for Items of this data type,
or if otherwise the "Illegal parameter for operation" error should be raised
at fix_fields() time.
- New methods in Sp_rcontext_handler:
* get_cursor()
* get_cursor_by_ref()
- A new class Sp_rcontext_handler_statement to handle top level statement
wide cursors which are shared by all substatements.
- A new virtual method expr_event_handler() in classes Item and Field.
It's needed to close (and make available for a new OPEN)
unused THD::m_statement_cursors elements which do not have any references
any more. It can happen in various moments in time, e.g.
* after evaluation parameters of an SQL routine
* after assigning a cursor expression into a SYS_REFCURSOR variable
* when leaving a BEGIN..END block with SYS_REFCURSOR variables
* after setting OUT/INOUT routine actual parameters from formal
parameters.
Attempt to create a procedure with the DEFINER clause resulted in
abnormal server termination in case the server run with the option
--skip-grant-tables=1.
The reason of abnormal termination is that on handling of the DEFINER
clause, not initialized data members of acl_cache is accessed, that led
to server crash.
Behaviour of the server for considered use case must be the same
as for embedded server. Than means, if a security subsytem wasn't
initialized (server is started with the option --skip-grant-tables=1)
return success from get_current_user() without further access to the
acl_cache that obviously not initialized.
Additionlly, AUTHID::is_role was modified to handle the case when
a host part of the user name isn't provided. Treat this case as if
the empty host name is provided.
It was not possible to use a package body variable as a
fetch target:
CREATE PACKAGE BODY pkg AS
vc INT := 0;
FUNCTION f1 RETURN INT AS
CURSOR cur IS SELECT 1 AS c FROM DUAL;
BEGIN
OPEN cur;
FETCH cur INTO vc; -- this returned "Undeclared variable: vc" error.
CLOSE cur;
RETURN vc;
END;
END;
FETCH assumed that all fetch targets reside of the same sp_rcontext
instance with the cursor. This patch fixes the problem.
Now a cursor and its fetch target can reside in different sp_rcontext
instances.
Details:
- Adding a helper class sp_rcontext_addr
(a combination of Sp_rcontext_handler pointer and an offset in the rcontext)
- Adding a new class sp_fetch_target deriving from sp_rcontext_addr.
Fetch targets in "FETCH cur INTO target1, target2 ..." are now collected
into this structure instead of sp_variable.
sp_variable cannot be used any more to store fetch targets,
because it does not have a pointer to Sp_rcontext_handler
(it only has the current rcontext offset).
- Removing members sp_instr_set members m_rcontext_handler and m_offset.
Deriving sp_instr_set from sp_rcontext_addr instead.
- Renaming sp_instr_cfetch member "List<sp_variable> m_varlist"
to "List<sp_fetch_target> m_fetch_target_list".
- Fixing LEX::sp_add_cfetch() to return the pointer to the
created sp_fetch_target instance (instead of returning bool).
This helps to make the grammar in sql_yacc.c simpler
- Renaming LEX::sp_add_cfetch() to LEX::sp_add_instr_cfetch(),
as `if(sp_add_cfetch())` changed its meaning to the opposite,
to avoid automatic wrong merge from earlier versions.
- Chaning the "List<sp_variable> *vars" parameter to sp_cursor::fetch
to have the data type "List<sp_fetch_target> *".
- Changing the data type of "List<sp_variable> &vars" in
sp_cursor::Select_fetch_into_spvars::send_data_to_variable_list()
to "List<sp_fetch_target> &".
- Adding THD helper methods get_rcontext() and get_variable().
- Moving the code from sql_yacc.yy into a new LEX method
LEX::make_fetch_target().
- Simplifying the grammar in sql_yacc.yy using the new LEX method.
Changing the data type of the bison rule sp_fetch_list from "void"
to "List<sp_fetch_target> *".
Discovered this while working on MDEV-34720: test_if_cheaper_ordering()
uses rec_per_key, while the original estimate for the access method
is produced in best_access_path() by using actual_rec_per_key().
Make test_if_cheaper_ordering() also use actual_rec_per_key().
Also make several getter function "const" to make this compile.
Also adjusted the testcase to handle this (the change backported from
11.0)
Trivial batch, using the handler statistics already collected for
the slow query log.
The reason for the changes in test cases was mainly to change to use
select TABLE_SCHEMA ... from information_schema.table_statistics instead
of 'show table_statistics' to avoid future changes to test results
if we add more columns to table_statistics.
Columns added to TABLE_STATISTICS
- ROWS_INSERTED, ROWS_DELETED, ROWS_UPDATED, KEY_READ_HITS and
KEY_READ_MISSES.
Columns added to CLIENT_STATISTICS and USER_STATISTICS:
- KEY_READ_HITS and KEY_READ_MISSES.
User visible changes (except new columns):
- CLIENT_STATISTICS and USER_STATISTICS has columns KEY_READ_HITS and
KEY_READ_MISSES added after column ROWS_UPDATED before SELECT_COMMANDS.
Other changes:
- Do not collect table statistics for system tables like index_stats
table_stats, performance_schema, information_schema etc as the user
has no control of these and the generate noice in the statistics.
- All row variables that are part of user_stats are moved to
'struct rows_stats' to make it easy to clear all of them at once.
- ha_read_key_misses added to STATUS_VAR
Notes:
- userstat.result has a change of numbers of rows for handler_read_key.
This is because use-stat-tables is now disabled for the test.
MDEV-32188 make TIMESTAMP use whole 32-bit unsigned range
- Changed usage of timeval to my_timeval as the timeval parts on windows
are 32-bit long, which causes some compiler issues on windows.
MDEV-32188 make TIMESTAMP use whole 32-bit unsigned range
This is done by changing my_time_t from long to unsigned long.
The effect of this is that on windows compling old clients may
get warnings of if they compare my_time_t with as signed variable.
Other things
- Removed my_time_t from include/*.pp files as it is different on windows
and linux.
- Changed do_abi_check.cmake to first print abi_check and then the
conflicting file (this makes it easier to find the cause of the error).
Other changes:
- Do not collect index statistics for system tables like index_stats
table_stats, performance_schema, information_schema etc as the user
has no control of these and the generate noise in the statistics.
This patch also fixes:
MDEV-33050 Build-in schemas like oracle_schema are accent insensitive
MDEV-33084 LASTVAL(t1) and LASTVAL(T1) do not work well with lower-case-table-names=0
MDEV-33085 Tables T1 and t1 do not work well with ENGINE=CSV and lower-case-table-names=0
MDEV-33086 SHOW OPEN TABLES IN DB1 -- is case insensitive with lower-case-table-names=0
MDEV-33088 Cannot create triggers in the database `MYSQL`
MDEV-33103 LOCK TABLE t1 AS t2 -- alias is not case sensitive with lower-case-table-names=0
MDEV-33109 DROP DATABASE MYSQL -- does not drop SP with lower-case-table-names=0
MDEV-33110 HANDLER commands are case insensitive with lower-case-table-names=0
MDEV-33119 User is case insensitive in INFORMATION_SCHEMA.VIEWS
MDEV-33120 System log table names are case insensitive with lower-cast-table-names=0
- Removing the virtual function strnncoll() from MY_COLLATION_HANDLER
- Adding a wrapper function CHARSET_INFO::streq(), to compare
two strings for equality. For now it calls strnncoll() internally.
In the future it will turn into a virtual function.
- Adding new accent sensitive case insensitive collations:
- utf8mb4_general1400_as_ci
- utf8mb3_general1400_as_ci
They implement accent sensitive case insensitive comparison.
The weight of a character is equal to the code point of its
upper case variant. These collations use Unicode-14.0.0 casefolding data.
The result of
my_charset_utf8mb3_general1400_as_ci.strcoll()
is very close to the former
my_charset_utf8mb3_general_ci.strcasecmp()
There is only a difference in a couple dozen rare characters, because:
- the switch from "tolower" to "toupper" comparison, to make
utf8mb3_general1400_as_ci closer to utf8mb3_general_ci
- the switch from Unicode-3.0.0 to Unicode-14.0.0
This difference should be tolarable. See the list of affected
characters in the MDEV description.
Note, utf8mb4_general1400_as_ci correctly handles non-BMP characters!
Unlike utf8mb4_general_ci, it does not treat all BMP characters
as equal.
- Adding classes representing names of the file based database objects:
Lex_ident_db
Lex_ident_table
Lex_ident_trigger
Their comparison collation depends on the underlying
file system case sensitivity and on --lower-case-table-names
and can be either my_charset_bin or my_charset_utf8mb3_general1400_as_ci.
- Adding classes representing names of other database objects,
whose names have case insensitive comparison style,
using my_charset_utf8mb3_general1400_as_ci:
Lex_ident_column
Lex_ident_sys_var
Lex_ident_user_var
Lex_ident_sp_var
Lex_ident_ps
Lex_ident_i_s_table
Lex_ident_window
Lex_ident_func
Lex_ident_partition
Lex_ident_with_element
Lex_ident_rpl_filter
Lex_ident_master_info
Lex_ident_host
Lex_ident_locale
Lex_ident_plugin
Lex_ident_engine
Lex_ident_server
Lex_ident_savepoint
Lex_ident_charset
engine_option_value::Name
- All the mentioned Lex_ident_xxx classes implement a method streq():
if (ident1.streq(ident2))
do_equal();
This method works as a wrapper for CHARSET_INFO::streq().
- Changing a lot of "LEX_CSTRING name" to "Lex_ident_xxx name"
in class members and in function/method parameters.
- Replacing all calls like
system_charset_info->coll->strcasecmp(ident1, ident2)
to
ident1.streq(ident2)
- Taking advantage of the c++11 user defined literal operator
for LEX_CSTRING (see m_strings.h) and Lex_ident_xxx (see lex_ident.h)
data types. Use example:
const Lex_ident_column primary_key_name= "PRIMARY"_Lex_ident_column;
is now a shorter version of:
const Lex_ident_column primary_key_name=
Lex_ident_column({STRING_WITH_LEN("PRIMARY")});
Changing the way how a the following conditions are evaluated:
WHERE timestamp_column=datetime_const_expr
(for all comparison operators: =, <=>, <, >, <=, >=, <> and for NULLIF)
Before the change it was always performed as DATETIME.
That was not efficient, as involved per-row TIMESTAMP->DATETIME conversion
for timestamp_column. For example, in case of the SYSTEM time zone
it involved a localtime_r() call, which is known to be slow.
After the change it's performed as TIMESTAMP in many cases.
This allows to avoid per-row conversion, as it works the other way around:
datetime_const_expr is converted to TIMESTAMP once before the execution stage.
Note, datetime_const_expr must be inside monotone continuous periods of
the current time zone, i.e. not near these anomalies:
- DST changes (spring forward, fall back)
- leap seconds
