MySQL CAST loses decimal digits when source is result from a regexp, but works fine on the same stri-CodePudding

I'm getting completely perplexing results here... I boiled it down to this:

select
    regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.] ') raw_txt,
    cast('56.948959' as decimal(10,8)) just_cast,
    case when regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.] ') = '56.948959' then 'yisss' else 'nope' end text_comparison,
    cast(regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.] ') as decimal(10,8)) full_decimal,
    cast(regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.] ') as double) full_double

And the results that I get are:

raw_txt: 56.948959
just_cast: 56.94895900
text_comparison: yisss
full_decimal: 56.00000000
full_double: 56.948959

Where are the decimal digits in the full_decimal case? Why does the cast work fine when run on a simple string (just_cast), but not when the exact same string (see text_comparison) comes from a regexp match? And yet it works if we cast to a double instead? What is going on here?

MySQL version: 8.0.21

CodePudding user response：

This is reported as a bug.

If you cast first to char and then re-cast to decimal the cast works.

For example:

select cast(cast(regexp_substr('56.948959;24.121503|56.950469;24.119765', '^[0-9\.] ') as char(100)) as decimal(10,8)) full_decimal

Full answer here