postgresql - Why does calling random() inside of a case statement produce unexpected results? -
https://gist.github.com/anonymous/2463d5a8ee2849a6e1f5
query 1 not produce expected results. however, queries 2 , 3 do. why moving call random() outside of case statement matter?
consider first expression:
select (case when round(random()*999999) + 1 between 000001 , 400000 1 when round(random()*999999) + 1 between 400001 , 999998 2 when round(random()*999999) + 1 between 999999 , 999999 3 else 4 end) generate_series(1, 8000000)
presumably, thinking value "4" should never selected. but, problem random()
being called separately each when
clause.
so, chance fails each clause independent:
- about 60% of time random number not match "1".
- about 40% of time random number not match "2".
- about 99.9999% of time random number not match "3" (i apologize if number of nines off, value practically 1).
that means 24% of time (60% * 40% * 99.9999%), value "4" appear. in actual fact, first query returns "4" 23.98% of time. honest, close actual value, given size of data, bit further off expect. however, close enough explain happening.
Comments
Post a Comment