[MDEV-11936] Wrong Results with User Vars in derived tables with WHERE Created: 2017-01-29  Updated: 2022-12-08  Resolved: 2022-12-08

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Optimizer
Affects Version/s: 10.2.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Bernd Buffen Assignee: Sergei Petrunia
Resolution: Cannot Reproduce Votes: 0
Labels: None
Environment:

MAC OS



 Description   

When i use a user variable in a query, only to count the rows is every thing ok. When i put this select as derived table it also look OK. When i now add WHERE clause in the outer SELECT the count is wrong. I have simplify a sample. in version 10.1 is the result ok

The Table

CREATE TABLE `sampletable` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The Rows

INSERT INTO `sampletable` (`id`)
 
VALUES
 
	(1),
 
	(2),
 
	(3),
 
	(4),
 
	(5);

Easy Select

 
 
MariaDB [test]> SET @nr:=0;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> SELECT
 
    ->         @nr:=(@nr+1) nr
 
    ->         ,st.*
 
    ->     FROM sampletable st;
 
+------+----+
 
| nr   | id |
 
+------+----+
 
|    1 |  1 |
 
|    2 |  2 |
 
|    3 |  3 |
 
|    4 |  4 |
 
|    5 |  5 |
 
+------+----+
 
5 rows in set (0.00 sec)

SELECT with derived table

 
 
MariaDB [test]> SET @nr:=0;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> SELECT * FROM (
 
    ->     SELECT
 
    ->         @nr:=(@nr+1) nr
 
    ->         ,st.*
 
    ->     FROM sampletable st
 
    ->     ) as t
 
    -> ;
 
+------+----+
 
| nr   | id |
 
+------+----+
 
|    1 |  1 |
 
|    2 |  2 |
 
|    3 |  3 |
 
|    4 |  4 |
 
|    5 |  5 |
 
+------+----+
 
5 rows in set (0.00 sec)
 
 
 
MariaDB [test]> SET @nr:=0;
 
Query OK, 0 rows affected (0.00 sec)

Now with WHERE and error

 
 
MariaDB [test]> SET @nr:=0;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> SELECT * FROM (     SELECT          @nr:=(@nr+1) nr         ,st.*      FROM sampletable st     ) as t WHERE nr <> 4;
 
+------+----+
 
| nr   | id |
 
+------+----+
 
|    2 |  1 |
 
|    6 |  3 |
 
|    8 |  4 |
 
|   10 |  5 |
 
+------+----+
 
4 rows in set (0.00 sec)

And also = dosent work (empty result)

 
 
MariaDB [test]> SET @nr:=0;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> SELECT * FROM (
 
    ->     SELECT
 
    ->         @nr:=(@nr+1) nr
 
    ->         ,st.*
 
    ->     FROM sampletable st
 
    ->     ) as t
 
    -> WHERE nr = 4;
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]>



 Comments   
Comment by Elena Stepanova [ 2017-02-01 ]

I am not sure that the scenario as such is guaranteed to work, but the behavior changed between 10.1 and 10.2, and there is a difference in execution plans which at the very least least needs to be looked at and confirmed to be intentional.

10.1 a14638581b

 
MariaDB [test]> EXPLAIN EXTENDED
 
    -> SELECT * FROM ( SELECT @nr:=(@nr+1) nr,st.* FROM sampletable st ) as t WHERE nr <> 4;
 
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
 
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
 
|    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    5 |   100.00 | Using where |
 
|    2 | DERIVED     | st         | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | Using index |
 
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
 
2 rows in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> SHOW WARNINGS;
 
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                               |
 
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select `t`.`nr` AS `nr`,`t`.`id` AS `id` from (select (@nr:=((@`nr`) + 1)) AS `nr`,`test`.`st`.`id` AS `id` from `test`.`sampletable` `st`) `t` where (`t`.`nr` <> 4) |
 
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT * FROM ( SELECT @nr:=(@nr+1) nr,st.* FROM sampletable st ) as t WHERE nr <> 4;
 
+------+----+
 
| nr   | id |
 
+------+----+
 
|    1 |  1 |
 
|    2 |  2 |
 
|    3 |  3 |
 
|    5 |  5 |
 
+------+----+
 
4 rows in set (0.01 sec)

10.2 23628d123bd

 
MariaDB [test]> EXPLAIN EXTENDED
 
    -> SELECT * FROM ( SELECT @nr:=(@nr+1) nr,st.* FROM sampletable st ) as t WHERE nr <> 4;
 
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
 
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
 
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
 
|    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    5 |   100.00 | Using where              |
 
|    2 | DERIVED     | st         | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | Using where; Using index |
 
+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
 
2 rows in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> SHOW WARNINGS;
 
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                   |
 
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select `t`.`nr` AS `nr`,`t`.`id` AS `id` from (select @nr:=@`nr` + 1 AS `nr`,`test`.`st`.`id` AS `id` from `test`.`sampletable` `st` where (@nr:=@`nr` + 1) <> 4) `t` where `t`.`nr` <> 4 |
 
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT * FROM ( SELECT @nr:=(@nr+1) nr,st.* FROM sampletable st ) as t WHERE nr <> 4;
 
+------+----+
 
| nr   | id |
 
+------+----+
 
|    2 |  1 |
 
|    6 |  3 |
 
|    8 |  4 |
 
|   10 |  5 |
 
+------+----+
 
4 rows in set (0.00 sec)

Comment by Sergei Petrunia [ 2017-02-08 ]

Re-formatted outputs of SHOW WARNINGS:

10.1:

select 
  t.nr AS nr,t.id AS id 
from 
  (select 
    (@nr:=((@nr) + 1)) AS nr, test.st.id AS id 
   from test.sampletable st
 
  ) t 
where (t.nr <> 4)

10.2:

select 
  t.nr AS nr,t.id AS id 
from 
  (select 
      @nr:=@nr + 1 AS nr,test.st.id AS id 
   from test.sampletable st 
   where 
     (@nr:=@nr + 1) <> 4
 
  ) t 
where 
  t.nr <> 4

Comment by Sergei Petrunia [ 2017-02-08 ]

This looks to be an effect of "condition pushdown into non-mergeable views" optimization.

Comment by Sergei Petrunia [ 2017-02-08 ]

Will discuss on next optimizer call if this can/should be fixed. It is not clear for me what the optimizer promises are with regards to computing @variable:=... expressions and the rest of WHERE.

As for numbering rows, 10.2 has a new way to do it - window functions and ROW_NUMBER() function in particular.

Comment by Alice Sherepa [ 2022-12-08 ]

Current 10.3 d360fa6fa897d9556dc3813-10.11 return expected result:

10.3 d360fa6fa897d9556dc3813

 
SET @nr:=0;
 
SELECT * FROM ( SELECT @nr:=(@nr+1) nr,st.* FROM sampletable st ) as t WHERE nr <> 4;
 
nr	id
 
1	1
 
2	2
 
3	3
 
5	5
 
SET @nr:=0;
 
SELECT * FROM ( SELECT @nr:=(@nr+1) nr,st.* FROM sampletable st ) as t WHERE nr = 4;
 
nr	id
 
4	4

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