[MDEV-17375] sql_mode=ORACLE: Incompatibility with CAST(number AS CHAR) Created: 2018-10-05  Updated: 2019-04-02

Status: Open
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.3, 10.4
Fix Version/s: 10.4

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


 Description   

Oracle removes insignificant fractional digits when converting a number to char:

SQL> select cast(10/10 as char) from dual;


C
 
-
 
1

while MariaDB preserves zeros:

MariaDB [test]> select cast(10/10 as char);


+---------------------+
 
| cast(10/10 as char) |
 
+---------------------+
 
| 1.0000              |
 
+---------------------+

As a result, Oracle returns one row in all these queries:

select 'X' from dual where  1 like 10/10;
 
select 'X' from dual where 1 like 1.00;
 
select 'x' from dual where 1.000 like 1.00;

MariaDB returns empty set.
Generated at Thu Feb 08 08:35:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.