[MDEV-9801] Variables in SQL query Created: 2016-03-27  Updated: 2016-03-31  Resolved: 2016-03-31

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.23
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Lukáš Kvídera Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

Raspbian 4.1.17-v7+ #838 SMP Tue Feb 9 13:15:09 GMT 2016 armv7l GNU/Linux


 Description   

The problem is with variables in SQL. Sometimes they are properly calculated, sometimes they are NULL.

How to reproduce:
1) login into MariaDB

mysql -u user -p password network

2) run query

call x(); select bytes_received, bytes_sent from timeserie;

3) output:

+----------------+------------+
 
| bytes_received | bytes_sent |
 
+----------------+------------+
 
|           NULL |       NULL |
 
|           NULL |       NULL |
 
|           NULL |       NULL |
 
|           NULL |       NULL |
 
|           NULL |       NULL |
 
|           NULL |       NULL |
 
|           NULL |       NULL |
 
|           NULL |       NULL |
 
|           NULL |       NULL |
 
|           NULL |       NULL |
 
+----------------+------------+

4) run query again

+----------------+------------+
 
| bytes_received | bytes_sent |
 
+----------------+------------+
 
|     -332305605 |  954862349 |
 
|         367026 |   12973563 |
 
|         421202 |   12788025 |
 
|         262783 |    6121133 |
 
|         306716 |    8915553 |
 
|         396136 |   13731970 |
 
|         373469 |   12127536 |
 
|         317901 |   10592084 |
 
|         380241 |   12344703 |
 
|         331722 |   11424220 |
 
+----------------+------------+

DDL:

delimiter $$
 
CREATE TABLE `traffic` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `date` datetime NOT NULL,
 
  `bytes_received` bigint(20) DEFAULT NULL,
 
  `bytes_sent` bigint(20) DEFAULT NULL,
 
  `hostname` varchar(45) NOT NULL,
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `duplicate_entry` (`hostname`,`date`)
 
) ENGINE=Aria AUTO_INCREMENT=54744 DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1$$
 
 
 
 
 
CREATE PROCEDURE `x`()
 
BEGIN
 
  DROP TEMPORARY TABLE IF EXISTS timeserie;
 
  CREATE TEMPORARY TABLE timeserie ENGINE = Aria AS
 
    SELECT 
        `c`.`date` AS `date`,
 
        @prev_id AS `previous_id`, 
        `c`.`id` AS `current_id`,
 
        @prev_received AS `bytes_received_cumulative_n`,
 
        `c`.`bytes_received` AS `bytes_received_cumulative_c`, 
        @prev_sent AS `bytes_sent_cumulative_n`,
 
        `c`.`bytes_sent` AS `bytes_sent_cumulative_c`,
 
        c.bytes_received - @prev_received AS `bytes_received`,
 
        ( `c`.`bytes_sent` - @prev_sent) AS `bytes_sent`,
 
        @prev_received := c.bytes_received,
 
        @prev_sent := c.bytes_sent,
 
        @prev_id := c.id       
    FROM `network`.`traffic` `c`
 
    WHERE `c`.`hostname` = 'hostname'
 
    ORDER BY `c`.`id`;
 
END



 Comments   
Comment by Elena Stepanova [ 2016-03-30 ]

Did you try to initialize the variables to 0 before using them?
You start with NULL values, and use them in the arithmetic operations, they are bound to break the logic.

Comment by Lukáš Kvídera [ 2016-03-30 ]

If I added:

SET @prev_received := NULL;
SET @prev_sent := NULL;
SET @prev_id := NULL;

The output is correct at first call.

Comment by Sergei Golubchik [ 2016-03-31 ]

This is explicitly documented in the manual:

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.

MySQL manual says the same:

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.

In your example (simplified)

    SELECT 
        @prev_sent AS bytes_sent_cumulative_n,
 
        (c.bytes_sent - @prev_sent) AS bytes_sent,
 
        @prev_sent := c.bytes_sent,
 
    FROM ...

you implicitly assume that the server will first calculate the first column value @prev_sent, then the second c.bytes_sent - @prev_sent, and then the third, that assigns new value to @prev_sent.

But there is no reason to assume that, the server evaluates expressions in the SELECT list in some internal unspecified order, so your SELECT statement is inherently unreliable, it might work and might not work —- which is exactly what you see.

Generated at Thu Feb 08 07:37:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.