I am one of the developers on a very small team and have just found the following query
I would love to hear your ideas for what you think was being attempted here!
SELECT ... FROM client WHERE CAST(ABS(SIN(clientId)) AS BIT) = 0
Well abs(sin(x)) is always going to return a value between 0 and 1, and I dunno how casting to bit works… if it rounds this might be used to consistently grab half the users. If CAST(anything except 0) as BIT —> 1, then this could be used to grab a very small subset of users? If their clientIds happen to be clientId%180 == 0
You’re right in that any non-zero value casted to bit becomes 1, but that includes negative values, so I’m even more confused why you’d need ABS there…
Well then, to answer OP’s question about intent, I don’t have much of a clue, but here’s some Idea Ore that maybe someone can refine into a plausible explanation:
- clientId basically has to be sequential or none of this makes sense
- conceptually, I believe this statement is equivalent to clientId%180 == 0
- i can’t fathom CAST(ABS(SIN())) being more efficient than modular division, so maybe the dev was trying to be clever, hated the hardcoded 180, or some other unknown going on
- a sine wave goes -∿- so this is where it hits the x-axis
- given that, there has to be some periodic or cyclical purpose, relating to the why/how of the clientId creation. For example, when the results of this are graphed with a created_at timestamp, it might give useful insights to growth (or something)
I forgot to mention this is in SQL Server, so SIN operates on radians. So I THINK this can only ever cast to a 0 when
clientIdis also 0It certainly doesn’t for any of the 100,000 existing rows
Personally, I’d look very closely at any records this returns and verify that they are normal. A previous developer could have left some sort of backdoor or other nasty surprise in the code/database such could only be tripped with a very specific condition.
Alternatively, consider the context around this code. What is done with those records? Maybe there is a very specific bug elsewhere in the code or in a front end tied to this database. Sure, the right solution was to fix that other bug. But, that may not have been an option. So, this strange bit of code “solved” the problem and was then promptly forgotten.
The
clienttable has around 100,000 rows each with a uniqueclientId, none of which are returned from the CAST / ABS / SINI think you are right and this is a ‘fix’ for something lost to time. I am going to talk to the original dev tomorrow to see if they remember what it was for
Let us know, ya?
Update: The original dev does not remember exactly. However they have said that
clientIdwas originally a VARCHAR, so this may have been checking for both'0'or''So an over-engineered workaround to a bad datatype perhaps?
I’m not that sturdy with SQL functions, but my understanding is this:
Get the client ID, and use that as input to a sine function to get a new number. Then get the absolute value of this number. Then you cast this as a bit, and you end up selecting all clients who through this lovecraftian horror ends up as 0.
Why?? I have no idea. It looks to me like they want only to select a subset of clientIDs, but with something that is hard to predict but with the same result every time for the same clients.
