[MDEV-4560] GREATEST/LEAST return value casted to maximum comparison precision Created: 2013-05-22  Updated: 2013-06-13  Resolved: 2013-06-13

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.30
Fix Version/s: 5.5.32

Type: Bug Priority: Minor
Reporter: Pawel Pabian (Inactive) Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

Debian Wheezy


 Description   

GREATEST/LEAST functions behaves differently than in MySQL 5.5

mysql> SELECT GREATEST(2, 1.11);
 
+-------------------+
 
| GREATEST(2, 1.11) |
 
+-------------------+
 
|                 2 |
 
+-------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT GREATEST(2, 1.11);
 
+-------------------+
 
| GREATEST(2, 1.11) |
 
+-------------------+
 
|              2.00 |
 
+-------------------+
 
1 row in set (0.00 sec)

Happens also on datetime type where microseconds appear.

mysql> SELECT GREATEST('2015-01-01 00:00:00', NOW());
 
+----------------------------------------+
 
| GREATEST('2015-01-01 00:00:00', NOW()) |
 
+----------------------------------------+
 
| 2015-01-01 00:00:00                    |
 
+----------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT GREATEST('2015-01-01 00:00:00', NOW());
 
+----------------------------------------+
 
| GREATEST('2015-01-01 00:00:00', NOW()) |
 
+----------------------------------------+
 
| 2015-01-01 00:00:00.000000             |
 
+----------------------------------------+
 
1 row in set (0.00 sec)

If this is desired behavior it should be mentioned in "MariaDB versus MySQL - Compatibility".

However - from users point of view - such behavior is very counterintuitive, one expects the same value and precision as in passed value, not extended to maximum precision of all passed params.

 Comments   
Comment by Sergei Golubchik [ 2013-06-13 ]

it's expected. Column metadata — including any column in the query result — are calculated before any actual data are accessed. That's how the C API works too, you can prepare a statement and the metadata will already be available.

In particular, it means that the result precision and scale of the result are computed based on the precision and scale of the arguments, and not based on argument values.

It's the same both in MySQL and MariaDB. If you run 'mysql --column-type-info' you will see that both in MySQL and MariaDB the result has Decimals=2.

It's a MySQL bug that it shows a number with Decimals=2 incorrectly, as if it had Decimals=0.

Generated at Thu Feb 08 06:57:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.