[MDEV-29937] GROUP_CONCAT incomplete, not near group_concat_max_len. Created: 2022-11-02  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.5.13, 10.3.36, 10.5.17
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Minor
Reporter: Brian Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None


 Description   

See below. The GROUP_CONCAT is missing the last two characters.

+---------------------------------------+
 
| VERSION()                             |
 
+---------------------------------------+
 
| 10.5.13-MariaDB-1:10.5.13+maria~focal |
 
+---------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [(none)]> select GROUP_CONCAT(a) FROM (SELECT CONCAT(conv('38681F000', 16, 10) / conv('38681F400', 16, 10) * 100 - 100, 'AFTER') as a) xxx;
 
+------------------------------+
 
| GROUP_CONCAT(a)              |
 
+------------------------------+
 
| -0.0000067628410249653825AFT |
 
+------------------------------+
 
1 row in set (0.000 sec)

Just the CONCAT is fine:

MariaDB [(none)]> SELECT CONCAT(conv('38681F000', 16, 10) / conv('38681F400', 16, 10) * 100 - 100, 'AFTER');
 
+------------------------------------------------------------------------------------+
 
| CONCAT(conv('38681F000', 16, 10) / conv('38681F400', 16, 10) * 100 - 100, 'AFTER') |
 
+------------------------------------------------------------------------------------+
 
| -0.0000067628410249653825AFTER                                                     |
 
+------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)

This was initially hit with real data in column type float, but above is same issue with no need for the table. The conv() is needed to get original float values. Just selecting them the two print the same.

 Comments   
Comment by Daniel Black [ 2022-11-03 ]

When real number is pushed to the string buffer the 39 decimals is used (rather than the 23 max_length).

Item_func_hybrid_field_type::val_str_from_real_op (this=0x7fee94011378, str=<optimized out>) at /home/dan/repos/mariadb-server-10.3/sql/item_func.cc:894
 
894	  if (null_value)
 
(gdb) 
896	  str->set_real(nr, decimals, collation.collation);
 
(gdb) p nr
 
$164 = -6.7628410249653825e-06
 
(gdb) p decimals
 
$165 = 39
 
(gdb) p max_length
 
$166 = 23

The 23 maximum is determined by:

Item_num_op::fix_length_and_dec_double()

 
 max_length= float_length(decimals);

Given "-0.0000067628410249653825" is 25 characters this is the correct length (or should it be truncated to 23?)

diff --git a/sql/item.h b/sql/item.h
 
index a291678529e..6b3aacaba8d 100644
 
--- a/sql/item.h
 
+++ b/sql/item.h
 
@@ -1380,7 +1380,7 @@ class Item: public Value_source,
 
   virtual Item* build_clone(THD *thd) { return get_copy(thd); }
 
   virtual cond_result eq_cmp_result() const { return COND_OK; }
 
   inline uint float_length(uint decimals_par) const
 
-  { return decimals < FLOATING_POINT_DECIMALS ? (DBL_DIG+2+decimals_par) : DBL_DIG+8;}
 
+  { return decimals < FLOATING_POINT_DECIMALS ? (DBL_DIG+2+decimals_par) : DBL_DIG+10;}
 
   /* Returns total number of decimal digits */
 
   virtual uint decimal_precision() const
 
   {

Causes test failures main.ctype_utf8 main.ctype_binary main.ctype_cp1251 main.func_hybrid_type main.func_math main.ctype_latin1 main.ctype_ucs main.type_num as the raised with of the tables

There are other DBL_DIG+8 or instances in the code.

bar, what's rthe right fix here? Increase the float length by 2 or constrain the Item_func_hybrid_field_type::val_str_from_real_op / String->set_real length?

Comment by Alexander Barkov [ 2022-11-17 ]

It seems Item_func_concat erroneously calculates its max_length in fix_length_and_dec().

CREATE OR REPLACE TABLE t1 AS SELECT CONCAT(conv('38681F000', 16, 10) / conv('38681F400', 16, 10) * 100 - 100, 'AFTER') as a;


ERROR 1406 (22001): Data too long for column 'a' at row 1


SET sql_mode='';
 
CREATE OR REPLACE TABLE t1 AS SELECT CONCAT(conv('38681F000', 16, 10) / conv('38681F400', 16, 10) * 100 - 100, 'AFTER') as a;


Query OK, 1 row affected, 1 warning (0.017 sec)
 
Records: 1  Duplicates: 0  Warnings: 1


SHOW CREATE TABLE t1;


+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                   |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE `t1` (
 
  `a` varchar(28) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
 
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------+

The expected data type should be at least varchar(30).

Comment by Alexander Barkov [ 2022-11-17 ]

A similar problem is repeatable with this script:

CREATE OR REPLACE TABLE t1 (a DOUBLE);
 
INSERT INTO t1 VALUES (-6.7628410249653825e-06);
 
CREATE OR REPLACE TABLE t2 AS SELECT CONCAT(a) FROM t1;


ERROR 1406 (22001): Data too long for column 'CONCAT(a)' at row 1

Comment by Alexander Barkov [ 2022-11-17 ]

A similar problem is repeatable with this script:

CREATE OR REPLACE TABLE t1 AS SELECT CONCAT(-6.7628410249653825e-06);


ERROR 1406 (22001): Data too long for column 'CONCAT(-6.7628410249653825e-06)' at row 1

Comment by Alexander Barkov [ 2022-11-17 ]

In all cases String::set_real() is called to perform double-to-string conversion, which produces a longer string than Item::max_length calculated. This looks wrong.

We probably need to use something else than String::set_real(), to take into account max_length.

Or we need to calculate max_length differently, to guarantee that any possible String::set_real() output fits.

Generated at Thu Feb 08 10:12:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.