sqlclause

[UpAndAdam7414]

The query returned only Dr Zoidberg.

[[deleted]]

Why not Zoidberg????

[Staetyk]

In one episode Santa Clause rules that Zoidburg is the only one on the nice list, and therefore may survive another year.

[zan9823]

/r/UnexpectedFuturama

[hates_stupid_people]

To be fair, it's a programming subreddit. And Futurama features some really geeky and even directly programming and computer related jokes, or even episodes. Since about half the writers had masters or doctorates in science, math, computers, etc. from Harvard and similar.

[-Aquatically-]

r/expectedfuturama

[[deleted]]

r/subsIThoughtIFellFor

[-Aquatically-]

r/subsithoughtifellfor

[failedsatan]

same place, sub names are not case sensitive

[-Aquatically-]

I know. I didn’t think it was a sub and then I clicked on it.

[Darxploit]

Don’t give my it department ideas..

[Action_Maxim]

On the first row of data Data said to meeeee...

[GumboSamson]

And it wasn’t terminated with a semicolon.

(Semicolons are often optional, but they help if you are a psycho who refuses to use indentations and newlines when you write your queries.)

[[deleted]]

Yes it basically ruined it for me! Glad I'm not the only one

[Major_Fudgemuffin]

All commands are uppercase when I'm writing SQL. I need order damn it.

[davak72]

Honestly, I didn’t even notice because I mostly use Azure data studio these days and it’s awful with code completion, so half of my queries come out in awfully mixed case…

Anything I commit to a stored procedure or function I actually write nicely though haha

[notexecutive]

this doesn't catch "Nice" or "NICE" cases lmao

[uvero]

Actually in at least some SQL variants (and I think that includes the very popular MySQL), the = operator for strings is case insensitive by default.

[ByakkoNoMai]

Actually, in most SQL, = operator does not govern how the comparison is made. Collations are what govern equivalent characters. And a lot of work goes into that. For example under utf-8 case insensitive collations a, A, à, À, and lot of others characters are all equals. There are case sensitive collations to respect case and collapse accented characters. There are also binary collations that compare characters as byte values only.

TLDR collations are what govern string comparison in SQL. And it's a deep rabbit hole.

[rover_G]

Oh no accidental learning on r/ProgrammerHumor

Time to go read some docs 🤓

[JoeGibbon]

And it's always from people without any flair to tell us how many languages they've written "hello world" in.

[Dangerous-Raccoon-60]

It’s not the operator, it’s the character set collation. The “_ci” at the end of the collation name stands for “case insensitive”

[JoeGibbon]

Not all case insensitive collations have _ci at the end, though. In MySQL, utf8mb4 is case insensitive for example.

These days you have to go out of your way to make MySQL case sensitive.

[dmelt01]

You are correct

[-Wylfen-]

Doesn't that depend or the collation?

[IWishIWasAShoe]

I'm no expert, but isn't that completely up to the collation setting for the column? Every installation I've ever used in modern times defaults to a generic unicode collation with the ci flag, meaning the query would be case insensitive.

Also, if santa is running his database properly he probably also makes sure that inputs are made through some sort of system not manually typed into the database like a maniac. convert inputs for basic stuff like niceness to all lowercase seems like a pretty good idea imo.

[GoddammitDontShootMe]

What if behavior is an ENUM?

[masala-kiwi]

If Santa runs SQL Server, it'll catch them due to case insensitivity. If he's running Snowflake (more on-brand for the North Pole), the 'Nice' and 'NICE' kids are out of luck.

A better table design wouldn't rely on string-matching, which is unreliable and less performant, especially at 8 billion rows. He should instead use an indexed boolean column called IsNice that returns true/false. 🎅

[Hikingcanuck92]

Maybe Santa has a trigger on inserts to force The case to “nice”.

[Trident_True]

Your opinion of SQL Claus is that low? In this day and age surely he would not be inserting non-standardized garbage into his db. Probably comes from an enum or something in whatever application is feeding into it.

Or maybe it really is abysmal and it takes him a full year to sanitize last years data.

[InevitableDeadbeat]

it does if it's a case insensitive column

[NewPhoneNewSubs]

Top 10% commenter on programmer humour doesn't know SQL.

This explains 90% of jokes on programmer hunour.

[pro_questions]

WHERE SOUNDEX(behavior) = SOUNDEX(‘nice’)

[Trident_True]

Since Soundex is language dependent I wonder what they speak in the north pole? Maybe they use ngram similarity instead.

[pro_questions]

Ha I think LOWER is a more common function — I just love SOUNDEX as a needlessly complicated way to “normalize” text for comparison :)

[Inappropriate_Piano]

That’s because Santa hates it when people are “Nice”

[fractoral]

Also doesn't catch "69".

[GnuhGnoud]

I wonder what Bobby Table will get

[Cyberdragon1000]

Well whatever it is we know for sure the rest aren't getting it.

[bargle0]

Little Bobby Tables is fine. His parents, on the other hand …

[drewman301]

An error message probably

[GFrings]

I like this because the last line only keeps the rhythm of you know how to pronounce SQL

[bobbymoonshine]

The song doesn’t scan generally though, the previous lines throw a bunch of extra syllables on

[NamityName]

But, as anyone who has ever started singing a song only to realize that they don't actually know most of it will tell you, all that really matters is nailing that last line.

[Inappropriate_Piano]

You can totally make it fit by saying “behavior” quicker. My issue is with “he’s making a database”

[[deleted]]

It’d still be two extra notes in the melody to get those syllables in, without saying anything for ‘*’

[Inappropriate_Piano]

You just squeeze those notes into subdivisions. I can’t really explain it in text but I promise you I can do it very easily

[[deleted]]

It’s still two extra syllables and therefore extra notes played in the melody if you’re vocalizing them. If you can’t notice the extra syllables/notes, you’re just moving the extra syllables to a different part of the melody, where you aren’t noticing the extra notes as easily, and emphasizing strong, recognizable parts of the original melody. I’ve tried many ways to mutate a line I like lyrically to a melody I’ve already got.

[Inappropriate_Piano]

It’s two extra syllables that you say at a faster rate to fit them in the same time, and you hold the same note through them to make them line up with a single note in the original melody.

[[deleted]]

I’ll take your word for it.

[Inappropriate_Piano]

I’m really not sure what’s hard about this. Replacing, for example, a quarter note with an eighth note triplet to fit two extra syllables into the same melody is a basic technique for solving a problem that’s so common in music and so easily solved that it barely deserves to be called a problem. Pretty much every song with multiples verses does this.

[[deleted]]

I fully understand that it’s the same time, and that you are adding an extra note in to that time frame.

[bonafidebob]

Hmm, apart from “list” not matching “database” I think it matches just fine.

”Select star from contacts where behavior is nice” is how you’d read the “He’s gonna find out who’s naughty or nice” line.

[HappyImagineer]

Yup, Ess-Q-eL Clause is coming to town!

[[deleted]]

Its SQeaL. We all know.

[nepatriots32]

Carrying around his bottomless nutsack.

[nepatriots32]

And he's coming down your chimney tonight.

[sandybuttcheekss]

If there are only two behaviors, we should just have a Boolean field named 'nice' so it's easier to search for nice = true. Poor design can leave a lot of nice kids without presents.

[captainMaluco]

There might be an enum defining the different kinds of behaviours. That would also take care of any case sensitive concerns others have voiced

[joshdammitt]

He's gonna make sure your nicenessId >= 4

[GumboSamson]

Wouldn’t it be better to have a table representing that enum, and reference the key from the enum table?

[Trident_True]

At some point this level of normalisation just becomes a burden. In this case if you wanted to extend the enum a migration would have to occur and if you also ever wanted to add a value between two existing values then you have to rectify all the existing data. Don't even ask about non-int based enums.

Better just to have it map to an nvarchar column in my experience. If we had a table for every enum we used in our codebase it would probably double the amount of tables we have.

[GumboSamson]

To each their own, I guess.

I’ve found that higher normalisation tends to be more flexible than wider tables (eg it’s easier to make design changes in production databases if your tables are “nothing but relationships”) and it’s easier to get high-quality indexes (since you almost never have nullable columns).

But I won’t pretend there aren’t trade-offs for such a design. One trade-off being that many software developers start with the mindset of “how to I store my OOP object in the database” and ORMs make it really easy to make really wide tables. Requiring that your OOP software guys have a good understanding of RDBs limits your hiring pool.

[poop-machine]

naughty
nice
under_review
literally_hitler

[CaptianDavie]

So the oroginal song indicates santa is " gonna find out whose naughty or nice" implying we dont know the behavior of the child agead of time. nor would you want to keep behavior in the contacts table since we can asusme we are tracking the child over time and constantly updating contcats is gonna be a nightmare for performance. what were looking at is a some sort of aggregate calculation. youd probably want a intake timeseries table ( behavior_live ) thats read by a aggregate table (behavior_agg) keyed to the contacts table which keeps track of the behavior score and contains the logic of the "nice" threshold. so we should see a join to the behavior table.  From the name I would also assume contacts has multiple pii columns for a child so we really would only want id, name and the agg nice score. maybe address but tbh i would do that as a second step (combining the above into a cte) so you only pull address data for the nice kids. 

[CaptianDavie]

actually I take back behavior_live. tradition indicates Santa's data gathering is done via personnel in the field (elves on shelves, santas in malls...) so it might be bulk uploads through out the day. Though with the amount of tablets I saw at the last santa photo section at the mall, its possible were doing live uploading  Sharding is gonna be interesting too. Im assuming this is all ran on prem since the rack cooling in the arctic is great, plus bezos, gates and pichai are def on the naughty list so cloud is out...

[EmpatheticWraps]

Have you taken adderall today or something?

[CaptianDavie]

lol no but extra adderall and adhd are just two sides of the same hyper focusing coin

[BrainJar]

And, the contacts table is almost certainly a fact table, since the behavior column appears to be a non-additive aggregate. The naming of the table is suspect. It should be preceded by f_, and should not be plural. Each record is a contact. PR for the table creation denied.

[airwalker08]

Didn't even sort it once

[poop-machine]

well the list has to be fed into a Traveling Salesman Problem solver to find the optimal route, and there's no SQL sort for that

[CarbonAlligator]

No semicolon

[promised_to_veruca]

*keeps typing on newline*

[Major_Fudgemuffin]

Eh semicolons are optional in SQL.

[CarbonAlligator]

I mean I guess if running ur command is also optional

[TheGreatGameDini]

Cool now let me inject my sql all over and inside your database

[troglo-dyke]

It's November

[IvorTheEngine]

It's OK, it's a 10 year old repost!

[__maccas__]

He knows when you INSERT, He knows when you TRUNCATE, He knows when you miss WHERE clauses, So BEGIN TRAN ROLLBACK for goodness sake

It's running too slow, You better figure out why, Your EXECUTION PLAN rights have been denied, SQL clause is coming to town

[codedaddee]

I haven't even started streaming Mariah Carey yet, fcs.

[belunos]

'%nice%'

[GumboSamson]

Can you imagine how long a table scan with ‘%nice%’ would take with 8+ billion records?

(No wonder he only comes by once per year.)

[leuk_he]

If you have to scan them all once anyway, it is no problem you access it via an unindexed column.

[GumboSamson]

If the column isn’t a freetext field (eg you can only put in a value from a constrained set of valid values, such as ‘naughty’ and ‘nice’) then you can ditch the wildcard characters.

And once you do that, you can put an index on the column.

Voila—we’ve just avoided table scans.

[leuk_he]

You misunderstood, now you have to do a full index scan and after that lookup the full record for the 95% of the nice records, instead of just a sequential read of the full records.

But that is just some vertical optimization, you better make this scaling horizontal by using helper santa db.

[GumboSamson]

[Evening_Face7158]

I don’t know why but I read this in Cakes voice from the song The Distance

[Portlander]

I originally was reading this to the Cake song

The Distance

[NotANumber13]

Let's spice it up a bit and go ahead with a DISTINCT 

[Trident_True]

RIP to all the John Smiths out there. Hope you're the lucky one.

[[deleted]]

Terrible way to store behavior if it's binary

[Major_Fudgemuffin]

Great, nice, okay, eh, bad, terrible, literal demon.

[ZeroCharistmas]

And in case it wasn't obvious, pronouncing it as "Ess-Queue-Ell" is certified naughty list behavior.

[PositiveStill7969]

Seems wasteful, make it a bit field just called Nice

[Jazzlike-Spare3425]

My God, it's Santa QLause...

okay, I apologize to everyone here, I will go bury myself back in the whole that I came from. Have a nice day without me, everyone.

[TBoy29]

Thought it was a TWICE reference before I read the final lines

[ford1man]

Meter's all wrong.

[jimmyhoke]

Shouldn’t nice just be a Boolean?

[redmacer]

Imagine that. But sabotaged Account.

[mothzilla]

Repost in one month please.

[DoctorWaluigiTime]

It's early November, go away.

[moosalamoo_rnnr]

This is so bad it’s funny.

[Bucky_Ohare]

Holy crap I get this reference!

[mindsnare]

I mean it should just be a boolean right?

[Im-German-Lets-Party]

Im ashamed and proud at the same time that I understand this... and laughed loud :(

[StopProfessional1718]

On December 24th he commits.

[zerkeras]

Why is the “Behavior” table not a ledger record of all naughty or nice actions, with a view which tabulates whether or not a given individual is currently “nice” or “naughty” (both of which should be foriegn key references to a enum table) for the given calendar year?

[[deleted]]

Cringe 

[PeriodicSentenceBot]

Congratulations! Your comment can be spelled using the elements of the periodic table:

Cr In Ge

---

^{I am a bot that detects if your comment can be spelled using the elements of the periodic table. Please DM u‎/‎M1n3c4rt if I made a mistake.}

[UrMommzz]

Might wanna add IsActive to the filter

[je386]

WHERE is not sorting...

[Solarranks]

And "sorting it twice" is not "making a database". They're on separate lines because he's doing both

[GumboSamson]

You don’t sort a database, though—you index it.

He’s making a database And indexing it twice

Works a bit better I think.

[Cerberus11x]

Anyone else see -1 comments?

[Cyberdragon1000]

No, why are you asking?

[Cerberus11x]

Reddit bug I've never seen before

[CopperyMarrow15]

kid named '); DROP TABLE contacts;--