Problem:
Date and time functions - `STR_TO_DATE` and `TO_DATE` (in Oracle mode)
both use `check_word` to check if a string token matches a valid
locale-specific day or month name. Server crashed because of an
un-initialized typelib member(`type_lengths`) for date locales which
are not present in `Oracle_date_locale` (e.g: `ar_DZ`).
Fix:
Initialize `type_lengths` for all supported locales by iterating over
`my_locales` array instead of `Oracle_date_locale` during server
startup.
Months names were taken from https://unicode.org/cldr/charts/48/summary/root.html,
which has two different categories for full month names: "Months - wide - Formatting"
and "Months - wide - Standalone".
Formatting is normaly used when the month is a part of a date (e.g., "12 January 2026").
In many languages (like Slavic or Baltic), the month's ending changes in this context.
Standalone is used when the month name appears alone.
%B prints "formatting" months names, %M - "standalone" names
Syntax:
TO_DATE(string_expression [DEFAULT string_expression ON CONVERSION ERROR],
format_string [,NLS_FORMAT_STRING])
The format_string has the same format elements as TO_CHAR(), except a
few elements that are not supported/usable for TO_DATE().
TO_DATE() returns a datetime or date value, depending on if the format
element FF is used.
Allowed separators, same as TO_CHAR():
space, tab and any of !#%'()*+,-./:;<=>
'&' can also be used if next character is not a character a-z or A-Z
"text' indicates a text string that is verbatim in the format. One cannot
use " as a separator.
Format elements supported by TO_DATE():
AD Anno Domini ("in the year of the Lord")
AD_DOT Anno Domini ("in the year of the Lord")
AM Meridian indicator (Before midday)
AM_DOT Meridian indicator (Before midday)
DAY Name of day
DD Day (1-31)
DDD Day of year (1-336)
DY Abbreviated name of day
FF[1-6] Fractional seconds
HH Hour (1-12)
HH12 Hour (1-12)
HH24 Hour (0-23)
MI Minutes (0-59)
MM Month (1-12)
MON Abbreviated name of month
MONTH Name of Month
PM Meridian indicator (After midday)
PM_DOT Meridian indicator (After midday)
RR 20th century dates in the 21st century. 2 digits
50-99 is assumed from 2000, 0-49 is assumed from 1900.
RRRR 20th century dates in the 21st century. 4 digits
SS Seconds
SYYYY Signed 4 digit year; MariaDB only supports positive years
Y 1 digit year
YY 2 digits year
YYY 3 digits year
YYYY 4 digits year
Note that if there is a missing part of the date, the current date is used!
For example if 'MM-DD HH-MM-SS' then the current year will be used.
(Oracle behaviour)
Not supported options:
- BC, D, DL, DS, E, EE, FM, FX, RM, SSSSS, TS, TZD, TZH, TZR, X,SY
BC is not supported by MariaDB datetime.
- Most of the other are exotic formats does not make sence in MariaDB as
we return datetime or datetime with fractions, not string.
- D (day-of-week) is not supported as it is not clear exactly how it would
map to MariaDB. This element depends on the NLS territory of the session.
- RR only works with 2 digit years (In Oracle RR can also work with 4
digit years in some context but the rules are not clear).
Extensions / differences compared to Oracle;
- MariaDB supports FF (fractional seconds). If FF[#] is used,
then TO_DATE will return a datetime with # of subseconds.
If FF is not used a datetime will be returned.
There is warning (no error) if string contains more digts than what
is specified with F(#]
- Names can be shortened to it's unique prefix. For example January and Ja
works fine.
- No error if the date string is shorter format_string and the next
not used character is not a number.. This is useful to get a date
from a mixed set of strings in date or datetime format.
Oracle gives an error if date string is too short.
- MariaDB supports short locales as language names
- NLS_DATE_FORMAT can use both " and ' for quoting.
- NLS_DATE_FORMAT must be a constant string.
- This is to ensure that the server knows which locale to use
when executing the function.
New formats handled by TO_CHAR():
FF[1-6] Fractional seconds
DDD Daynumber 1-366
IW Week 1-53 according to ISO 8601
I 1 digit year according to ISO 8601
IY 2 digit year according to ISO 8601
IYY 3 digit year according to ISO 8601
IYYY 4 digit year according to ISO 8601
SYYY 4 digit year according to ISO 8601 (Oracle can do signed)
Supported NLS_FORMAT_STRING options are:
NLS_CALENDAR=GREGORIAN
NLS_DATE_LANGUAGE=language
Support languages are:
- All MariaDB short locales, like en_AU.
- The following Oracle language names:
ALBANIAN, AMERICAN, ARABIC, BASQUE, BELARUSIAN, BRAZILIAN PORTUGUESE
BULGARIAN, CANADIAN FRENCH, CATALAN, CROATIAN, CYRILLIC SERBIAN CZECH,
DANISH, DUTCH, ENGLISH, ESTONIAN, FINNISH, FRENCH, GERMAN,
GREEK, HEBREW, HINDI, HUNGARIAN, ICELANDIC, INDONESIAN ITALIAN,
JAPANESE, KANNADA, KOREAN, LATIN AMERICAN SPANISH, LATVIAN,
LITHUANIAN, MACEDONIAN, MALAY, MEXICAN SPANISH, NORWEGIAN, POLISH,
PORTUGUESE, ROMANIAN, RUSSIAN, SIMPLIFIED CHINESE, SLOVAK, SLOVENIAN,
SPANISH, SWAHILI, SWEDISH, TAMIL, THAI, TRADITIONAL CHINESE, TURKISH,
UKRAINIAN, VIETNAMESE
Development bugs fixed:
MDEV-38403 Server crashes in Item_func_to_date::fix_length_and_dec upon
using an invalid argument
MDEV-38400 compat/oracle.func_to_date fails with PS protocol and cursor
protocol (Fixed by Serg)
MDEV-38404 TO_DATE: MTR coverage omissions, round 1
MDEV-38509 TO_DATE: AD_DOT does not appear to be supported
MDEV-38513 TO_DATE: NULL value for format string causes assertion failure
MDEV-38521 TO_DATE: Date strings with non-ASCII symbols cause warnings
and wrong results
MDEV-38578 TO_DATE: Possibly unexpected results upon wrong input
MDEV-38582 TO_DATE: NLS_DATE_LANGUAGE=JAPANESE does not parse values
which work in Oracle
MDEV-38584 TO_DATE: NLS_DATE_LANGUAGE=VIETNAMESE does not parse values
which work in Oracle
MDEV-38703 TO_DATE: Quotation for multi-word NLS_DATE_LANGUAGE leads
to syntax error in view definition
MDEV-38675 TO_DATE: MSAN/Valgrind/UBSAN errors in
extract_oracle_date_time
MDEV-38635 TO_DATE: UBSAN errors in item_timefunc.h upon comparison with
a view column
MDEV-38719 TO_DATE: Assertion `&my_charset_bin != charset()' failed in
String::append_for_single_quote_using_mb_wc
MDEV-38756 TO_DATE: MSAN/Valgrind errors in
Item_func_to_date::fix_length_and_dec upon PREPARE with
parameters
Known issues:
- Format string character matches inside quotes are done
one-letter-to-one-letter, like in LIKE predicate. That means things
like expansions and contractions do not work.
For example 'ss' does not match 'ß' in collations which treat them
as equal for the comparison operator.
Match is done taking into account case and accent sensitivity
of the subject argument collation, so for example this now works:
MariaDB [test]> SELECT TO_DATE('1920á12','YYYY"a"MM') AS c;
+---------------------+
| c |
+---------------------+
| 1920-12-17 00:00:00 |
+---------------------+
Co-author and reviewer: Alexander Barkov <bar@mariadb.com>
Adding support for the "FM" format in function TO_CHAR(date_time, fmt).
"FM" in the format string disables padding of all components following it.
So now TO_CHAR() works as follows:
- By default string format components DAY (weekday name) and
MONTH (month name) are right-padded with spaces to the maximum
possible DAY and MONTH name lengths respectively,
according to the current locale specified in @@lc_time_names.
So for example, with lc_time_names='en_US' all month names are
right-padded with spaces up to 9 characters ('September' is the longest).
SET lc_time_names='en_US';
SELECT TO_CHAR('0001-02-03', 'MONTH'); -> 'February ' (padded to 9 chars)
NEW: When typed after FM, DAY and MONTH names are not right-padded
with trailing spaces any more:
SET lc_time_names='en_US';
SELECT TO_CHAR('0001-02-03', 'FMMONTH'); -> 'February' (not padded)
- By default numeric components YYYY, YYY, YY, Y, DD, H12, H24, MI, SS
are left-padded with leading digits '0' up to the maximum possible
number of digits in the component (e.g. 4 for YYYY):
SELECT TO_CHAR('0001-02-03', 'YYYY'); -> '0001' (padded to 4 chars)
NEW: When typed after FM, these numeric components are not left-padded
with leading zeros any more:
SELECT TO_CHAR('0001-02-03', 'FMYYYY'); -> '1' (not padded)
- If FM is specified multiple times in a format string,
every FM negates the previous padding state:
* an odd FM disables padding
* an even FM enables padding
Implementation details:
- Adding a helper class Date_time_format_oracle.
- Adding a helper method Date_time_format_oracle::append_lex_cstring()
- Moving the function append_val() to Date_time_format_oracle as a method.
- Moving the function make_date_time_oracle() to Date_time_format_oracle
as a method format().
- Adding helper methods month_name() and day_name() in class MY_LOCALE,
to return the corresponding components as LEX_CSTRINGs.
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")});
Stop linking plugins to the server executable on Windows.
Instead, extract whole server functionality into a large DLL, called
server.dll. Link both plugins, and small server "stub" exe to it.
This eliminates plugin dependency on the name of the server executable.
It also reduces the size of the packages (since tiny mysqld.exe
and mariadbd.exe are now both linked to one big DLL)
Also, simplify the functionality of exporing all symbols from selected
static libraries. Rely on WINDOWS_EXPORT_ALL_SYMBOLS, rather than old
self-backed solution.
fix compile error
replace GetProcAddress(GetModuleHandle(NULL), "variable_name")
for server exported data with actual variable names.
Runtime loading was never required,was error prone
, since symbols could be missing at runtime, and now it actually failed,
because we do not export symbols from executable anymore, but from a shared
library
This did require a MYSQL_PLUGIN_IMPORT decoration for the plugin,
but made the code more straightforward, and avoids missing symbols at
runtime (as mentioned before).
The audit plugin is still doing some dynamic loading, as it aims to work
cross-version. Now it won't work cross-version on Windows, as it already
uses some symbols that are *not* dynamically loaded, e.g fn_format
and those symbols now exported from server.dll , when earlier they were
exported by mysqld.exe
Windows, fixes for storage engine plugin loading
after various rebranding stuff
Create server.dll containing functionality of the whole server
make mariadbd.exe/mysqld.exe a stub that is only calling mysqld_main()
fix build
- Added sql/mariadb.h file that should be included first by files in sql
directory, if sql_plugin.h is not used (sql_plugin.h adds SHOW variables
that must be done before my_global.h is included)
- Removed a lot of include my_global.h from include files
- Removed include's of some files that my_global.h automatically includes
- Removed duplicated include's of my_sys.h
- Replaced include my_config.h with my_global.h
This is done by splitting variables.errmsg and locale.errmsg to
variables.errmsg_extra and locale.errmsg_extra
The ER() macros in unireg.h now looks more complex than before, but this
isn't critical as most usage of them are with constants and the compiler
will remove most of the test code.
This patch:
- Moves all definitions from the mysql_priv.h file into
header files for the component where the variable is
defined
- Creates header files if the component lacks one
- Eliminates all include directives from mysql_priv.h
- Eliminates all circular include cycles
- Rename time.cc to sql_time.cc
- Rename mysql_priv.h to sql_priv.h
