Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
6.2.3
-
None
Description
Unless there's a specific reason for it, maybe RAND() in Columnstore should behave like the Server's implementation by using a random seed as default parameter.
Columnstore Test Case
CREATE TABLE `test_rand` (
|
`c1` int(11) DEFAULT NULL |
) ENGINE=Columnstore DEFAULT CHARSET=utf8;
|
|
insert into test_rand values (1),(2),(3),(4),(5),(6); |
|
select * from (select c1,count(*) cnt from test_rand group by c1 order by cnt) x order by rand() limit 3; |
select * from (select c1,count(*) cnt from test_rand group by c1 order by cnt) x order by rand() limit 3; |
MariaDB Server
CREATE TABLE `test_rand2` (
|
`c1` int(11) DEFAULT NULL |
) ENGINE=Innodb DEFAULT CHARSET=utf8;
|
|
insert into test_rand2 values (1),(2),(3),(4),(5),(6); |
|
select * from (select c1,count(*) cnt from test_rand2 group by c1 order by cnt) x order by rand() limit 3; |
select * from (select c1,count(*) cnt from test_rand2 group by c1 order by cnt) x order by rand() limit 3; |
Sorry forgot to add a test case.
Columnstore engine must be summoned in some way, I guess the function has to be overridden by Columnstore.
CREATE TABLE `test_rand` (
`c1` int(11) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8;
insert into test_rand values (1),(2),(3),(4),(5),(6);
select * from (select c1,count(*) cnt from test_rand group by c1 order by cnt) x order by rand() limit 3;
MariaDB [db01]> select * from (select c1,count(*) cnt from test_rand group by c1 order by cnt) x order by rand() limit 3;
+------+-----+
| c1 | cnt |
+------+-----+
| 4 | 1 |
| 2 | 1 |
| 3 | 1 |
+------+-----+
3 rows in set (0,122 sec)
MariaDB [db01]> select * from (select c1,count(*) cnt from test_rand group by c1 order by cnt) x order by rand() limit 3;
+------+-----+
| c1 | cnt |
+------+-----+
| 4 | 1 |
| 2 | 1 |
| 3 | 1 |
+------+-----+
3 rows in set (0,119 sec)
And:
MariaDB [db01]> select rand() from test_rand;
+----------------------------+
| rand() |
+----------------------------+
| 0.000000021420419236105326 |
| 0.00000011641532193535503 |
| 0.0000005485489969593929 |
| 0.000002424232663981833 |
| 0.000010506249974021921 |
| 0.00004528928552315504 |
+----------------------------+
6 rows in set (0,117 sec)
MariaDB [db01]> select rand() from test_rand;
+----------------------------+
| rand() |
+----------------------------+
| 0.000000021420419236105326 |
| 0.00000011641532193535503 |
| 0.0000005485489969593929 |
| 0.000002424232663981833 |
| 0.000010506249974021921 |
| 0.00004528928552315504 |
+----------------------------+
6 rows in set (0,117 sec)