[MDEV-15663] Possible Bug in Variable Handling Created: 2018-03-26  Updated: 2018-03-26  Resolved: 2018-03-26

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Gordan Bobic Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu 16.04, MariaDB 10.1.26

Attachments: File data.sql.gz    

 Description   

I appear to have hit a variable handling bug on MariaDB 10.1.26.

This works:

mysql> select
 
    ->     tc2dev_mips.time,
 
    ->     @rtt := coalesce(sum(rtt*reqs)/sum(reqs), @rtt) as rtt,
 
    ->     (coalesce(sum(rtt*reqs)/sum(reqs), @rtt) - rtt_good) / (rtt_bad - rtt_good) as rtt_mips
 
    -> from
 
    ->     tc2dev_mips
 
    -> left join
 
    ->     (
 
    ->         select time, rtt, reqs from tc2dev_metrics where asn = '33095' and cty = 'us'
 
    ->     ) t1 on tc2dev_mips.time = t1.time
 
    -> group by tc2dev_mips.time
 
    -> order by time asc;
 
+------------+-----------------+----------------------+
 
| time       | rtt             | rtt_mips             |
 
+------------+-----------------+----------------------+
 
| 1521665100 | NULL            |                 NULL |
 
| 1521665400 | NULL            |                 NULL |
 
| 1521665700 | NULL            |                 NULL |
 
| 1521666000 | NULL            |                 NULL |
 
| 1521666300 | NULL            |                 NULL |
 
| 1521666600 | NULL            |                 NULL |
 
| 1521728100 | NULL            |                 NULL |
 
| 1521728700 | NULL            |                 NULL |
 
| 1521729000 | NULL            |                 NULL |
 
| 1521729300 | NULL            |                 NULL |
 
| 1521729600 | NULL            |                 NULL |
 
| 1521729900 | NULL            |                 NULL |
 
| 1521730200 | NULL            |                 NULL |
 
| 1521730500 | NULL            |                 NULL |
 
| 1521730800 | NULL            |                 NULL |
 
| 1521731100 | NULL            |                 NULL |
 
| 1521731400 | NULL            |                 NULL |
 
| 1521731700 | 12593           |  0.04197666666666667 |
 
| 1521732000 | 12593           |  0.04197666666666667 |
 
| 1521732300 | 12593           |  0.04197666666666667 |
 
| 1521732600 | 12593           |  0.04197666666666667 |
 
| 1521732900 | 41266.90234375  |  0.13755634114583334 |
 
| 1521733200 | 41266.90234375  |  0.13755634114583334 |
 
| 1521733500 | 41266.90234375  |  0.13755634114583334 |
 
| 1521733800 | 41266.90234375  |  0.13755634114583334 |
 
| 1521734100 | 41266.90234375  |  0.13755634114583334 |
 
| 1521734400 | 41266.90234375  |  0.13755634114583334 |
 
| 1521734700 | 41266.90234375  |  0.13755634114583334 |
 
| 1521735000 | 14979.439453125 |     0.04993146484375 |
 
| 1521735300 | 11812.119140625 |     0.03937373046875 |
 
| 1521735600 | 11812.119140625 |     0.03937373046875 |
 
| 1521735900 | 8738.2314453125 | 0.029127438151041667 |
 
| 1521736200 | 8738.2314453125 | 0.029127438151041667 |
 
| 1521736500 | 8738.2314453125 | 0.029127438151041667 |
 
| 1521736800 | 8738.2314453125 | 0.029127438151041667 |
 
| 1521752100 | 8738.2314453125 | 0.029127438151041667 |
 
| 1521752700 | 8738.2314453125 | 0.029127438151041667 |
 
+------------+-----------------+----------------------+
 
37 rows in set (0.00 sec)

But if I change:

select tc2dev_mips.time as time

to

select tc2dev_mips.time * 1000 as time

it no longer correctly sees the @rtt value.

mysql> select
 
    ->     tc2dev_mips.time * 1000 as time,
 
    ->     @rtt := coalesce(sum(rtt*reqs)/sum(reqs), @rtt) as rtt,
 
    ->     (coalesce(sum(rtt*reqs)/sum(reqs), @rtt) - rtt_good) / (rtt_bad - rtt_good) as rtt_mips
 
    -> from
 
    ->     tc2dev_mips
 
    -> left join
 
    ->     (
 
    ->         select time, rtt, reqs from tc2dev_metrics where asn = '33095' and cty = 'us'
 
    ->     ) t1 on tc2dev_mips.time = t1.time
 
    -> group by tc2dev_mips.time
 
    -> order by time asc;
 
+---------------+-----------------+----------------------+
 
| time          | rtt             | rtt_mips             |
 
+---------------+-----------------+----------------------+
 
| 1521665100000 | NULL            |                 NULL |
 
| 1521665400000 | NULL            |                 NULL |
 
| 1521665700000 | NULL            |                 NULL |
 
| 1521666000000 | NULL            |                 NULL |
 
| 1521666300000 | NULL            |                 NULL |
 
| 1521666600000 | NULL            |                 NULL |
 
| 1521728100000 | NULL            |                 NULL |
 
| 1521728700000 | NULL            |                 NULL |
 
| 1521729000000 | NULL            |                 NULL |
 
| 1521729300000 | NULL            |                 NULL |
 
| 1521729600000 | NULL            |                 NULL |
 
| 1521729900000 | NULL            |                 NULL |
 
| 1521730200000 | NULL            |                 NULL |
 
| 1521730500000 | NULL            |                 NULL |
 
| 1521730800000 | NULL            |                 NULL |
 
| 1521731100000 | NULL            |                 NULL |
 
| 1521731400000 | NULL            |                 NULL |
 
| 1521731700000 | 12593           |  0.04197666666666667 |
 
| 1521732000000 | NULL            |                 NULL |
 
| 1521732300000 | NULL            |                 NULL |
 
| 1521732600000 | NULL            |                 NULL |
 
| 1521732900000 | 41266.90234375  |  0.13755634114583334 |
 
| 1521733200000 | NULL            |                 NULL |
 
| 1521733500000 | NULL            |                 NULL |
 
| 1521733800000 | NULL            |                 NULL |
 
| 1521734100000 | NULL            |                 NULL |
 
| 1521734400000 | NULL            |                 NULL |
 
| 1521734700000 | NULL            |                 NULL |
 
| 1521735000000 | 14979.439453125 |     0.04993146484375 |
 
| 1521735300000 | 11812.119140625 |     0.03937373046875 |
 
| 1521735600000 | NULL            |                 NULL |
 
| 1521735900000 | 8738.2314453125 | 0.029127438151041667 |
 
| 1521736200000 | NULL            |                 NULL |
 
| 1521736500000 | NULL            |                 NULL |
 
| 1521736800000 | NULL            |                 NULL |
 
| 1521752100000 | NULL            |                 NULL |
 
| 1521752700000 | NULL            |                 NULL |
 
+---------------+-----------------+----------------------+
 
37 rows in set (0.00 sec)

I have attached a small data set required to reproduce this issue.

 Comments   
Comment by Elena Stepanova [ 2018-03-26 ]

As explicitly documented both in MySQL manual and in MariaDB KB, assigning and using variables in the same query causes undefined behavior:
https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement.
...

https://mariadb.com/kb/en/the-mariadb-library/user-defined-variables/

It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined.

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