Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
10.2.13
-
None
-
Linux, Ubuntu 14.04, n.a.
Description
Window Functions seems to return string instead of numbers:
Just following your examples here:
https://mariadb.com/kb/en/library/window-functions-overview/
SELECT salary, SUM(salary) OVER () FROM employee_salaries; |
+--------+---------------------+ |
| salary | SUM(salary) OVER () | |
+--------+---------------------+ |
| 3500 | 17750 |
|
| 3000 | 17750 |
|
| 2800 | 17750 |
|
| 2500 | 17750 |
|
| 2200 | 17750 |
|
| 1800 | 17750 |
|
| 500 | 17750 |
|
| 400 | 17750 |
|
| 300 | 17750 |
|
| 300 | 17750 |
|
| 250 | 17750 |
|
| 200 | 17750 |
|
+--------+---------------------+ |
SELECT salary, CAST(SUM(salary) OVER () AS INT) FROM employee_salaries; |
+--------+----------------------------------+ |
| salary | CAST(SUM(salary) OVER () AS INT) | |
+--------+----------------------------------+ |
| 3500 | 17750 |
|
| 3000 | 17750 |
|
| 2800 | 17750 |
|
| 2500 | 17750 |
|
| 2200 | 17750 |
|
| 1800 | 17750 |
|
| 500 | 17750 |
|
| 400 | 17750 |
|
| 300 | 17750 |
|
| 300 | 17750 |
|
| 250 | 17750 |
|
| 200 | 17750 |
|
+--------+----------------------------------+ |
If you did not fake the docu I would say you have introduced a bug...
I do not know if this is dramatic. But I could imagine, that some applications relay on the data type and behave differently/wrong?
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
Activity
Hello Alice
Yes. But it should be displayed at the right sight of the column as numbers do and as it is in MySQL 5.7 and not on the left side (which is IMHO a string)...
I run Example 1 from here: http://php.net/manual/en/mysqli-result.fetch-fields.php
and got:
Name: salary
|
Table: employee_salaries
|
Max. Len: 4
|
Length: 11
|
charsetnr: 63
|
Flags: 32768
|
Type: 3 --> INT
|
|
|
Name: SUM(salary) OVER ()
|
Table:
|
Max. Len: 5
|
Length: 33
|
charsetnr: 63
|
Flags: 32896
|
Type: 246 --> DECIMAL
|
So the client/application gets it right. The it is possibly in the mysql Client!
If I run a MySQL 5.7 mysql Client against MariaDB 10.2 output is correct.
oli@laptop4:/tmp [mysql-57, 3309]> type mysql
|
mysql is hashed (/home/mysql/product/mysql-5.7/bin/mysql)
|
oli@laptop4:/tmp [mysql-57, 3309]> mysql --user=root --port=3336 test --host=127.0.0.1
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 37
|
Server version: 5.5.5-10.2.13-MariaDB-log MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
|
|
|
Oracle is a registered trademark of Oracle Corporation and/or its
|
affiliates. Other names may be trademarks of their respective
|
owners.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
root@mysql-57 [test] SQL> SELECT salary, SUM(salary) OVER () FROM employee_salaries;
|
+--------+---------------------+
|
| salary | SUM(salary) OVER () |
|
+--------+---------------------+
|
| 3500 | 17750 |
|
| 3000 | 17750 |
|
| 2800 | 17750 |
|
| 2500 | 17750 |
|
| 2200 | 17750 |
|
| 1800 | 17750 |
|
| 500 | 17750 |
|
| 400 | 17750 |
|
| 300 | 17750 |
|
| 300 | 17750 |
|
| 250 | 17750 |
|
| 200 | 17750 |
|
+--------+---------------------+
|
12 rows in set (0.00 sec)
|
Hi Oli,
The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE). (from mysql 5.7 documentation, maybe it should be clearly documented in KB)
MariaDB [test]> SELECT salary, SUM(salary) OVER () FROM employee_salaries;+--------+---------------------+| salary | SUM(salary) OVER () |+--------+---------------------+| 3500 | 17750 || 3000 | 17750 || 2800 | 17750 || 2500 | 17750 || 2200 | 17750 || 1800 | 17750 || 500 | 17750 || 400 | 17750 || 300 | 17750 || 300 | 17750 || 250 | 17750 || 200 | 17750 |+--------+---------------------+12 rows in set (0.001 sec)MariaDB [test]> SELECT salary, CAST(SUM(salary) OVER () AS DECIMAL) FROM employee_salaries;+--------+--------------------------------------+| salary | CAST(SUM(salary) OVER () AS DECIMAL) |+--------+--------------------------------------+| 3500 | 17750 || 3000 | 17750 || 2800 | 17750 || 2500 | 17750 || 2200 | 17750 || 1800 | 17750 || 500 | 17750 || 400 | 17750 || 300 | 17750 || 300 | 17750 || 250 | 17750 || 200 | 17750 |+--------+--------------------------------------+12 rows in set (0.001 sec)