From 30fc9ac9cebd44914ca79d2d3c334052cdd4d2e8 Mon Sep 17 00:00:00 2001 From: Dirkjan Bussink Date: Fri, 21 Feb 2025 11:47:23 +0100 Subject: [PATCH] Return the correct collation for temporal results When functions like `GREATEST`, `LEAST` and `COALESCE` return a temporal result, the collation is not set to `binary` as expected. The collation for temporal types normally is always `binary` which is the case for fields and for functions inheriting from `Item_temporal_func`. The comparison functions mentioned here though don't inherit from that and can return various types. Internally they do set the numeric collation correctly, but at the protocol level (and for what the `COLLATION` function returns), it is translated to the binary collation. This translation does not happen before this change for the given functions, so we add it here. Before: ``` mysql> select collation(date'2010-01-01'); +-----------------------------+ | collation(date'2010-01-01') | +-----------------------------+ | binary | +-----------------------------+ 1 row in set (0.00 sec) mysql> select collation(greatest(date'2010-01-01', date'2009-01-01')); +---------------------------------------------------------+ | collation(greatest(date'2010-01-01', date'2009-01-01')) | +---------------------------------------------------------+ | latin1_swedish_ci | +---------------------------------------------------------+ 1 row in set (0.00 sec) ``` After: ``` mysql> select collation(date'2010-01-01'); +-----------------------------+ | collation(date'2010-01-01') | +-----------------------------+ | binary | +-----------------------------+ 1 row in set (0.00 sec) mysql> select collation(greatest(date'2010-01-01', date'2009-01-01')); +---------------------------------------------------------+ | collation(greatest(date'2010-01-01', date'2009-01-01')) | +---------------------------------------------------------+ | binary | +---------------------------------------------------------+ 1 row in set (0.00 sec) ``` Signed-off-by: Dirkjan Bussink --- mysql-test/r/func_str.result | 9 +++++++++ mysql-test/t/func_str.test | 3 +++ sql/item_func.cc | 5 +++++ sql/item_func.h | 1 + 4 files changed, 18 insertions(+) diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 54003b50c6e4..a80a304a49ff 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -673,6 +673,15 @@ latin2_general_ci 4 select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')); collation(replace(_latin2'abcd',_latin2'b',_latin2'B')) coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')) latin2_general_ci 4 +select collation(greatest(date'2010-01-01', date'2011-01-01')); +collation(greatest(date'2010-01-01', date'2011-01-01')) +binary +select collation(least(date'2010-01-01', date'2011-01-01')); +collation(least(date'2010-01-01', date'2011-01-01')) +binary +select collation(coalesce(null, date'2010-01-01', date'2011-01-01')); +collation(coalesce(null, date'2010-01-01', date'2011-01-01')) +binary create table t1 charset latin1 select bin(130), diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index c943a571b0f9..c851a8bc41e6 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -352,6 +352,9 @@ select collation(soundex(_latin2'ab')), coercibility(soundex(_latin2'ab')); select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1)); select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef')); select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B')); +select collation(greatest(date'2010-01-01', date'2011-01-01')); +select collation(least(date'2010-01-01', date'2011-01-01')); +select collation(coalesce(null, date'2010-01-01', date'2011-01-01')); create table t1 charset latin1 select diff --git a/sql/item_func.cc b/sql/item_func.cc index 5c20a4cc1ff5..d18d7bdfa3ec 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -1888,6 +1888,11 @@ bool Item_func_numhybrid::get_time(MYSQL_TIME *ltime) { } } +const CHARSET_INFO *Item_func_numhybrid::charset_for_protocol() { + if (is_temporal_type(data_type())) return &my_charset_bin; + return Item_func::charset_for_protocol(); +} + void Item_typecast_signed::print(const THD *thd, String *str, enum_query_type query_type) const { str->append(STRING_WITH_LEN("cast(")); diff --git a/sql/item_func.h b/sql/item_func.h index fc8be1028c82..b0fd91e3f98a 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -909,6 +909,7 @@ class Item_func_numhybrid : public Item_func { bool resolve_type_inner(THD *thd) override; void fix_num_length_and_dec() override; virtual void set_numeric_type() = 0; // To be called from resolve_type() + const CHARSET_INFO *charset_for_protocol() override; double val_real() override; longlong val_int() override;