Bug on rand() ?

General Discussions - English
1

I have a query for an index like this where for showing the behaviour seed is always 1:

select id, weight() as w, rand(1) as r, if ( (now() - fe) < 7776000, 100,0 ) as new, if ( (now() -fe_xtra) < 8640060 , 1000,0 ) as bumped, weight()(1+ new + bumped ) as composite_bumper from spv_101001001 WHERE pwt = 65 order by r desc limit 0, 20;

For the given dataset, If I ran the query a number of times, while r returns always the same values as expected, I get two different sets of results, and always two…

Result 1:
±-------±-----±-----------±-----±-------±-----------------+
| id | w | r | new | bumped | composite_bumper |
±-------±-----±-----------±-----±-------±-----------------+
| 99816 | 1 | 0.99983668 | 100 | 1000 | 1101 |
| 96770 | 1 | 0.99940014 | 0 | 0 | 1 |
| 93531 | 1 | 0.999071 | 0 | 0 | 1 |
| 101062 | 1 | 0.99886215 | 100 | 1000 | 1101 |
| 87562 | 1 | 0.99873304 | 100 | 1000 | 1101 |
| 102042 | 1 | 0.99870396 | 100 | 1000 | 1101 |
| 99630 | 1 | 0.99846452 | 100 | 1000 | 1101 |
| 100220 | 1 | 0.99795610 | 100 | 1000 | 1101 |
| 93560 | 1 | 0.997609 | 0 | 0 | 1 |
| 96801 | 1 | 0.99755889 | 0 | 0 | 1 |
| 98060 | 1 | 0.99746341 | 100 | 1000 | 1101 |
| 98071 | 1 | 0.99724239 | 100 | 1000 | 1101 |
| 98327 | 1 | 0.99717653 | 100 | 1000 | 1101 |
| 102389 | 1 | 0.99688619 | 100 | 1000 | 1101 |
| 88323 | 1 | 0.99667263 | 0 | 0 | 1 |
| 101370 | 1 | 0.99590409 | 100 | 1000 | 1101 |
| 95893 | 1 | 0.99539691 | 0 | 0 | 1 |
| 96771 | 1 | 0.99515986 | 0 | 0 | 1 |
| 93596 | 1 | 0.99505574 | 0 | 0 | 1 |
| 100857 | 1 | 0.99495226 | 100 | 1000 | 1101 |

Result2:

±-------±-----±-----------±-----±-------±-----------------+
| id | w | r | new | bumped | composite_bumper |
±-------±-----±-----------±-----±-------±-----------------+
| 99826 | 1 | 0.99983668 | 100 | 1000 | 1101 |
| 96836 | 1 | 0.99940014 | 0 | 0 | 1 |
| 93542 | 1 | 0.999071 | 0 | 0 | 1 |
| 101090 | 1 | 0.99886215 | 100 | 1000 | 1101 |
| 88039 | 1 | 0.99873304 | 0 | 0 | 1 |
| 102052 | 1 | 0.99870396 | 100 | 1000 | 1101 |
| 99640 | 1 | 0.99846452 | 100 | 1000 | 1101 |
| 100230 | 1 | 0.99795610 | 100 | 1000 | 1101 |
| 93576 | 1 | 0.997609 | 0 | 0 | 1 |
| 96841 | 1 | 0.99755889 | 100 | 0 | 101 |
| 98078 | 1 | 0.99746341 | 100 | 1000 | 1101 |
| 98081 | 1 | 0.99724239 | 100 | 1000 | 1101 |
| 98406 | 1 | 0.99717653 | 100 | 1000 | 1101 |
| 102399 | 1 | 0.99688619 | 100 | 1000 | 1101 |
| 88580 | 1 | 0.99667263 | 0 | 0 | 1 |
| 101380 | 1 | 0.99590409 | 100 | 1000 | 1101 |
| 95912 | 1 | 0.99539691 | 100 | 1000 | 1101 |
| 96837 | 1 | 0.99515986 | 0 | 0 | 1 |
| 93619 | 1 | 0.99505574 | 0 | 0 | 1 |
| 100867 | 1 | 0.99495226 | 100 | 1000 | 1101 |

I have run the same query with different seeds and what I can see is that the id for the first result is always + or - 10 of the other set of results, for some of the remaining results this is also the case, in this example the last result exhibits the same behaviour.

Is this a bug or am i missing something. Thanks.

2

[UPDATE]

Version info:
Server version: 5.0.2 348514c@220530 dev (columnar 1.15.4 2fef34e@220522) (secondary 1.15.4 2fef34e@220522) git branch HEAD (no branch)

One other thing that makes me suspect that its a big is that if I modify the search a bit, then, occasionally, I get rand() to be 0.0000 for some or all of the results as follows:

select id, weight() as w, rand(), if ( (now() - fe) < 7776000, 100,0 ) as new, if ( (now() -fe_xtra) < 8640060 , 1000,0 ) as bumped, weight()(1+ new + bumped ) as composite_bumper from spv_101001001 WHERE pwt = 65 limit 0, 20 option rand_seed=1;

±------±-----±-----------±-----±-------±-----------------+
| id | w | rand() | new | bumped | composite_bumper |
±------±-----±-----------±-----±-------±-----------------+
| 13806 | 1 | 0.12649618 | 0 | 0 | 1 |
| 17852 | 1 | 0.20872745 | 0 | 0 | 1 |
| 31937 | 1 | 0.46578056 | 0 | 0 | 1 |
| 48448 | 1 | 0.62003821 | 0 | 0 | 1 |
| 52590 | 1 | 0.87326658 | 0 | 0 | 1 |
| 52598 | 1 | 0.06641738 | 0 | 0 | 1 |
| 55667 | 1 | 0.993506 | 100 | 1000 | 1101 |
| 55668 | 1 | 0.91852927 | 100 | 1000 | 1101 |
| 56108 | 1 | 0.93055731 | 100 | 1000 | 1101 |
| 56109 | 1 | 0.17995529 | 100 | 1000 | 1101 |
| 63315 | 1 | 0.000000 | 100 | 1000 | 1101 |
| 63316 | 1 | 0.000000 | 100 | 1000 | 1101 |
| 64423 | 1 | 0.000000 | 100 | 0 | 101 |
| 71741 | 1 | 0.000000 | 100 | 1000 | 1101 |
| 71823 | 1 | 0.000000 | 0 | 0 | 1 |
| 72927 | 1 | 0.000000 | 0 | 0 | 1 |
| 73026 | 1 | 0.000000 | 0 | 0 | 1 |
| 74769 | 1 | 0.000000 | 0 | 0 | 1 |
| 75013 | 1 | 0.000000 | 0 | 0 | 1 |
| 75729 | 1 | 0.000000 | 100 | 1000 | 1101 |