[MCOL-3760] rand() returns different results in 1.4 vs 1.2 Created: 2020-02-04  Updated: 2020-11-12  Resolved: 2020-03-30

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.4.2
Fix Version/s: 1.4.4, 1.5.1

Type: Bug Priority: Major
Reporter: David Hall (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
PartOf
is part of MCOL-3594 Failed tests in the 001 suite in 1.4 Closed
Sprint: 2020-2, 2020-3, 2020-4, 2020-5, 2020-6

 Description   

See /home/calpont/mariadb-columnstore-regression-test/mysql/queries/working_tpch1/qa_fe_cnxFunctions/bug3506.sql

The query:
select rand(2) from region order by rand(1);

Expected output:
0.354211017819318
0.8578261098431667
0.64497318737672
0.6555866465490187
0.12234661925802624

Actual output:
0.9252509287793663
0.6555866465490187
0.354211017819318
0.64497318737672
0.3918687630369037

 Comments   
Comment by David Hall (Inactive) [ 2020-02-13 ]

'select rand(2) from region' gives the expected result. It's only when the second rand() is added to the order by that things get broken.

Comment by Jose Rojas (Inactive) [ 2020-02-13 ]

After investigating MCOL-3760, and the rand function, it turns out MDB Server uses separate seeds for each rand() in a statement, while columnstore uses only one set of seeds used across all rands.

I added some regression tests inside working_tpch1/qa_fe_cnxFunctions/RAND.sql that will be of great help when testing this.

Comment by Daniel Lee (Inactive) [ 2020-03-30 ]

Builds verified: 1.4.4-1 source, 1.5.0-1 BB

1.4.4-1

/root/ColumnStore/buildColumnstoreFromGithubSource/server
commit ec0071afa50b78930860a14802b8cfc9791f7d13
Author: Sergei Petrunia <psergey@askmonty.org>
Date: Thu Mar 26 01:26:39 2020 +0300

MDEV-21887: federatedx crashes on SELECT ... INTO query in select_handler code

/root/ColumnStore/buildColumnstoreFromGithubSource/server/engine
commit 6e953d01d268161ac50dabb6aa9a122ed1d43e0f
Merge: 4468c93 ba06727
Author: Gagan Goel <gagan.nith@gmail.com>
Date: Thu Mar 26 11:41:35 2020 -0400

Merge pull request #1117 from drrtuy/MCOL-3828_1_4

MCOL-3828 This commit replaces the method that calls JOIN::optimise()

MariaDB [tpch1]> select rand(0) from region;
---------------------

rand(0)

---------------------

0.15522042769493574
0.620881741513388
0.6387474552157777
0.33109208227236947
0.7392180764481594

---------------------
5 rows in set (0.190 sec)

MariaDB [tpch1]> select rand(-2) from region order by 1;
---------------------

rand(-2)

---------------------

0.11941688239520125
0.12422665872073421
0.6325218050112219
0.6548542125661431
0.804358408604151

---------------------
5 rows in set (0.035 sec)

MariaDB [tpch1]> select rand(2) from region order by rand(1);
---------------------

rand(2)

---------------------

0.354211017819318
0.8578261098431667
0.64497318737672
0.6555866465490187
0.12234661925802624

---------------------
5 rows in set (0.037 sec)

MariaDB [tpch1]> select * from (select n_nationkey, rand(1) from nation limit 1) x;
--------------------------------+

n_nationkey rand(1)

--------------------------------+

0 0.40540353712197724

--------------------------------+
1 row in set (0.105 sec)

MariaDB [tpch1]> select * from (select n_nationkey from nation order by rand(1) limit 1 )a;
-------------

n_nationkey

-------------

22

-------------
1 row in set (0.041 sec)

MariaDB [tpch1]> select rand(r_regionkey) from region;
---------------------

rand(r_regionkey)

---------------------

0.15522042769493574
0.40540353712197724
0.6555866465490187
0.9057697559760601
0.15595286540310166

---------------------
5 rows in set (0.036 sec)

MariaDB [tpch1]> select rand(r_name) from region;
---------------------

rand(r_name)

---------------------

0.15522042769493574
0.15522042769493574
0.15522042769493574
0.15522042769493574
0.15522042769493574

---------------------
5 rows in set (0.032 sec)

Comment by Daniel Lee (Inactive) [ 2020-03-30 ]

Test result match with that of 1.2.5-1

Generated at Thu Feb 08 02:45:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.