[MDEV-15459] Window Functions seems to return string instead of numbers Created: 2018-03-04  Updated: 2018-03-06  Resolved: 2018-03-06

Status: Closed
Project: MariaDB Server
Component/s: libmariadb
Affects Version/s: 10.2.13
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Oli Sennhauser Assignee: Georg Richter
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Linux, Ubuntu 14.04, n.a.

Issue Links:
Duplicate
is duplicated by MDEV-15263 Different justification behaviour for... Closed

 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?

 Comments   
Comment by Alice Sherepa [ 2018-03-05 ]

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)

Comment by Oli Sennhauser [ 2018-03-05 ]

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)...

Comment by Oli Sennhauser [ 2018-03-06 ]

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)

Comment by Sergei Golubchik [ 2018-03-06 ]

Duplicate of MDEV-15263

Generated at Thu Feb 08 08:21:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.