MySQL CAST loses decimal digits when source is result from a regexp, but works fine on the same string when its hardcoded

huangapple go评论69阅读模式
英文:

MySQL CAST loses decimal digits when source is result from a regexp, but works fine on the same string when its hardcoded

问题

我在这里得到了完全令人困惑的结果... 我将其简化为以下内容:

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

而我得到的结果是:

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

full_decimal情况下,为什么没有小数位数?为什么在对简单字符串运行时转换正常工作(just_cast),但是当完全相同的字符串来自正则表达式匹配时(请参见text_comparison),转换不起作用?然而,如果我们转换为double,它就有效了?这里发生了什么?

MySQL 版本:8.0.21

英文:

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

答案1

得分: 1

这被报告为一个错误

如果你先将其转换为字符,然后再转换为十进制,转换就会成功。

例如:

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

完整答案在此处

英文:

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

huangapple
  • 本文由 发表于 2023年1月9日 19:02:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/75056338.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定