October 11, 2019 at 7:03 pm
Greetings,
We are using the T-SQL CHOOSE function for a test fixture. The idea is that we want to randomly select one of 3 different values and have the CHOOSE function do it's thing and pick the correct value out of the array we pass in.
However, sometimes it returns NULL and we can't figure out why. If you repeatedly execute this SQL you will see that it returns NULL intermittently.
SELECT CHOOSE((CAST(RAND()* 10 AS INT))%3.00 + 1, 'one', 'two', 'three')
I understand that CHOOSE "returns NULL if the index value exceeds the bounds of the array of values" but I don't see how the SQL above will ever return anything other than a 1, 2, or 3.
Even stranger is that the COALESCE function doesn't always catch the NULL that is outputted by the CHOOSE function. If you repeatedly execute the SQL below, you will see that sometimes it STILL returns NULL:
SELECT COALESCE(CHOOSE((CAST(RAND()* 10 AS INT))%3.00 + 1, 'one', 'two', 'three'), 'bla')
However, ISNULL always seems to catch the NULL.
This looks like a Microsoft bug. Anyone disagree?
SB
October 11, 2019 at 10:07 pm
The resulting data type in your calculation is Numeric. I am thinking it is probably a floating point precision error that is giving you a near but slightly lower than 1 value. For example, 0.9999999999999999999999999999999999999999999999999999999999999999999999999... instead of 1.
Now, if you take that calculation and put it in a variable where you have the data type specific (INT is what CHOOSE expects), you no longer get NULL for your result.
As for the COALESCE bug, that one looks like a bug.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 12, 2019 at 5:01 am
If you change %3.00 to %3 then you will also remove the floating point issue.
October 12, 2019 at 1:09 pm
But this one doesn't return NULL, so a bit puzzling to me
SELECT CHOOSE((CAST(T.[Rand]* 10 AS INT))%3.00 + 1, 'one', 'two', 'three') Choice,
T.[Rand],
CAST(T.[Rand]* 10 AS INT)%3.00 + 1 RandTimesTenPlusOneMod3
FROM (VALUES (RAND()))T([Rand])
October 14, 2019 at 1:17 pm
But this one doesn't return NULL, so a bit puzzling to me
SELECT CHOOSE((CAST(T.[Rand]* 10 AS INT))%3.00 + 1, 'one', 'two', 'three') Choice,
T.[Rand],
CAST(T.[Rand]* 10 AS INT)%3.00 + 1 RandTimesTenPlusOneMod3
FROM (VALUES (RAND()))T([Rand])
Using the execution plan, this expression and the original expression are completely different. This expression evaluates RAND() once and works with the result thereafter. Here's the original:
SELECT CASE WHEN
CASE WHEN CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(numeric(10,0),CONVERT(int,rand()*(1.000000000000000e+001),0),0)%(3.00)+(1.),0)=(1) THEN 'one'
ELSE CASE WHEN CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(numeric(10,0),CONVERT(int,rand()*(1.000000000000000e+001),0),0)%(3.00)+(1.),0)=(2) THEN 'two'
ELSE CASE WHEN CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(numeric(10,0),CONVERT(int,rand()*(1.000000000000000e+001),0),0)%(3.00)+(1.),0)=(3) THEN 'three'
ELSE NULL END END END
IS NOT NULL THEN
CASE WHEN CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(numeric(10,0),CONVERT(int,rand()*(1.000000000000000e+001),0),0)%(3.00)+(1.),0)=(1) THEN 'one'
ELSE CASE WHEN CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(numeric(10,0),CONVERT(int,rand()*(1.000000000000000e+001),0),0)%(3.00)+(1.),0)=(2) THEN 'two'
ELSE CASE WHEN CONVERT_IMPLICIT(int,CONVERT_IMPLICIT(numeric(10,0),CONVERT(int,rand()*(1.000000000000000e+001),0),0)%(3.00)+(1.),0)=(3) THEN 'three'
ELSE NULL END END END
ELSE 'bla' END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 14, 2019 at 6:19 pm
Jonathan AC Roberts wrote:But this one doesn't return NULL, so a bit puzzling to me
SELECT CHOOSE((CAST(T.[Rand]* 10 AS INT))%3.00 + 1, 'one', 'two', 'three') Choice,
T.[Rand],
CAST(T.[Rand]* 10 AS INT)%3.00 + 1 RandTimesTenPlusOneMod3
FROM (VALUES (RAND()))T([Rand])Using the execution plan, this expression and the original expression are completely different. This expression evaluates RAND() once and works with the result thereafter. Here's the original:
But the statement the OP posted also executes Rand() only once:
SELECT CHOOSE((CAST(RAND()* 10 AS INT))%3.00 + 1, 'one', 'two', 'three')
October 15, 2019 at 7:12 am
ChrisM@Work wrote:Jonathan AC Roberts wrote:But this one doesn't return NULL, so a bit puzzling to me
SELECT CHOOSE((CAST(T.[Rand]* 10 AS INT))%3.00 + 1, 'one', 'two', 'three') Choice,
T.[Rand],
CAST(T.[Rand]* 10 AS INT)%3.00 + 1 RandTimesTenPlusOneMod3
FROM (VALUES (RAND()))T([Rand])Using the execution plan, this expression and the original expression are completely different. This expression evaluates RAND() once and works with the result thereafter. Here's the original:
But the statement the OP posted also executes Rand() only once:
SELECT CHOOSE((CAST(RAND()* 10 AS INT))%3.00 + 1, 'one', 'two', 'three')
The statement and what SQL Server does with it are two different things. SQL Server converts the CHOOSE to a bunch of CASEs and RAND() ends up being evaluated more than once.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply