- Feature Articles
- CodeSOD
-
Error'd
- Most Recent Articles
- Stop Poking Me!
- Operation Erred Successfully
- A Dark Turn
- Nothing Doing
- Home By Another Way
- Coast Star
- Forsooth
- Epic
- Forums
-
Other Articles
- Random Article
- Other Series
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
SMSS = SQL Management Server Studio?
Admin
ALWAYS properly escape everything in SQL....
Admin
NGL I read this title as something VERY different and was like "What the fuck?" then I read it closer... :D
Admin
The real WTF is that an alledgedly (HP?)C wasn't aware of this "feature." That should have been database 101.
Admin
WTF??? Why is whitespace even allowed in column names at all???
At this point the company is such a circus that it would be run better if the executives were literally replaced with clowns.
Admin
If I had to guess, the people that originally created the tables used some SQL design tool, and that tool works correctly. It saw they put a space in their table name, and when generating SQL statements enclosed it in the bracket syntax to treat it as literal given the users EXACTLY what they asked for in terms of table name.
I would also hazard it is entirely possible something like this was born in something not SQLServer but Access or FoxPro and and the upsize/convert wizards got used along the way. I would fully expect those things to not mangle names but rather do whatever quoting and escaping is required to preserve object names as much as possible to maximum degree of allowable idiocy.
Now I am wondering, if its possible to use emoji tables names if the correct charset and collate options are set. That might be fun....
Admin
Spaces on the end of things is so incredibly common. In my experience it's when the creator has just blindly copy & pasted the value from somewhere else and not spotted - or cared in any way about - the additional space.
Our servicedesk used to be terrible for it, the amount of accounts we had where the displayname field had two spaces between the fore- and surname due to careless copying was incredible.
Admin
The harder cases are non-English "c"s in the English names T_T
Admin
S€ŁÉ©T íd, CöstČentreÇøde, BüsineßValue🤡 FŘÔM DâtåBuśınéssTæblę ШHEЯË things are getting interesting...
Admin
I stumbled on this exact stupid thing using the Microsoft Azure Entra ID, where UserPrincipalNames and Group titles can also have trailing spaces - which you can't fix in the Azure Portal's web UI because the input fields trim the spaces away. The extra cherry on top of the cake: the Microsoft Graph Client framework chokes on those spaces on parsing the returned JSON with a "System.Text.Json.JsonReaderException: '0x0D' is invalid within a JSON string.".
I guess some copy&paste from an EMail or Word document into some third party tool was part of the hellish process that lead to this abomination.
Admin
Definitely not "worst troll in the history of trolling". You can do similar but with a zero-width non-breaking space, in which case you certainly can't see in SSMS that the column name is not what you thought it was.
Admin
This is a hue peeve of mine. Interfaces should NOT be more restrictive of inputs than the underlying storage is. If group names are really labels and any allowed character can appear in any position then the UI should let you put spaces at then end if spaces are allowed. Sure, this probably means more typos and user errors not less, but at least if some other interface does make a mistake you don't have to track someone down with powershell access and the knowledge to fix it.
Admin
I'm guessing it came from someone thinking it would look nicer if the trailing quotes or whatever around a multi-line list of columns being created, were all lined up neatly, like (forgive me, I've forgotten the real syntax and can't be arsed to look it up):
Admin
Spaces in the middle of column names has its uses, and most databases provide ways to escape/quote table and column names to support this.
Admin
The way this story starts, Maria sounds like she deserves to be a Highly Paid Consultant (in a good way).
Admin
Oracle has (had? it's been a while) a worse variation of this.
Oracle pretends to be case insensitive for object names by forcing names to upper case. It does this both on object creation and on object access. So...
CREATE TABLE MyTable (MyColumn VARCHAR2(20)); SELECT * FROM MyTable;
.. works just fine. It creates a table named MYTABLE and selects from it. However if you do...
CREATE TABLE "MyTable" (MyColumn VARCHAR2(20));
... then you have coaxed Oracle into creating a table with a mixed case name. Now SELECT * FROM MyTable will fail even though you seem to have the casing correct. SELECT * FORM "MyTable" will succeed.
Admin
The same happens with PostgreSQL, in reverse (its case-smashing converts to lowercase instead of uppercase).
Admin
Im not familiar with Oracle, but could that maybe be because it is infact a different string and not tricking it into being case sensitive? i.e. the strings
"MyTable"
andMyTable
are not the same.Addendum 2024-09-25 11:35: Replying to Jaime above, not sure why it didn't count as a reply
Admin
Darn! I was hoping to have some "fun" with this. It sounds better than using Greek question marks.
Admin
I can imagine the SQL server triage team coming up across a ticket: "trailing spaces in object names sometimes cause user confusion"
At which point they laughed, lamented about everybody being stupid, and marked it as "not planned", sub-category "ID10T problem", and moved on.
Addendum 2024-09-25 12:26: Before (according to the article) it came up again with probably many more people asking for it and they decided to make a change which in their estimation was the least breaking change that solved the problem.
Admin
Nope. The system catalog would clearly show that this was the only table in the entire system with mixed case (unless you did this more than once) and that the quotes were not part of the name.
Admin
I disagree. The columns names in a database are not meant to look pretty. They're neither meant to be written in the passionate letters you write to your lovers, nor in the reports you write to your managers, they're not Christmas trees either. Column names only have a technical purpose.
It is more important that SQL queries are readable and unambiguous than it is they look pretty.
If you want to show pretty tables, you should code a UI layer.
Admin
In all likelihood, they probably sent all the tickets to the bit bucket. Then at some point a different internal team ran into it, reported it, and probably even fixed it for the managing team. From my understanding, this seems to be how an alarming number of bugs are fixed at Microsoft.
Admin
Someone got a carriage return - 0x0D - into the string. Could be copy paste indeed, could also be line ending mismatch unix / windows
Admin
The phantom-space problem is incredibly common among older people who don't have 100% typing control when entering passwords, there'll be a phantom space before or after the password so that an apparently-correct password is treated as invalid. I don't know how many times I've encountered this, and wish password forms would handle this correctly - no real password is going to have a leading or trailing space, so trim the things before you act on the password.
Admin
Should be SSMS - SQL Server Management Studio
Admin
You monster! You absolute unmitigated monster! This totally cracked me up :-D
Admin
There's a small WTF in that she didn't immediately ask the database to provide the create table definition when she first got the "invalid column" error.
Admin
But wait, wouldn't SSMS's IntelliSense automatically prompt the column name with a space, surrounded by the brackets while you;re typing?
Admin
I've actually created a database that was just the emoji for "Pizza" - just because. You can do it, but it does cause fits with normal database operations because most TSQL just has no idea to what you're referring and standard DB maintenance processes for SQL Server bark at you.
But sadly, the "spaces in columns" thing pops up far too often. Most of the time that's because "We want to see good column names in our reports". Sometimes it's because whatever process they used to create the tables/columns pulled in extra white spaces and that's what stuck. You don't see it unless you actually do what was done here - script it out. (or run some extra queries to query for non-printable characters) Our worst offenders tended to be Vertical Tabs coming from Excel files. :/
Admin
When the intellisense works, it should do that. When it doesn't or you're impatient and just type - no such luck.