Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.2.3
-
None
-
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]>
|