• WTF Linter (unregistered)

    SMSS = SQL Management Server Studio?

  • TheCPUWizard (unregistered)

    ALWAYS properly escape everything in SQL....

  • (nodebb)

    NGL I read this title as something VERY different and was like "What the fuck?" then I read it closer... :D

  • Industrial Automation Engineer (unregistered)

    The real WTF is that an alledgedly (HP?)C wasn't aware of this "feature." That should have been database 101.

  • Sauron (unregistered)

    The column name had a space at the end.

    WTF??? Why is whitespace even allowed in column names at all???

    The challenge is that everyone who wasn't a developer had built the system. Due to the vagaries of internal billing, hiring IT staff to do the work would have put it under a charge code which would have drained the wrong budget

    At this point the company is such a circus that it would be run better if the executives were literally replaced with clowns.

  • Hal (unregistered)

    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....

  • Darren (unregistered)

    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.

  • Denis (unregistered)

    The harder cases are non-English "c"s in the English names T_T

  • Officer Johnny Holzkopf (unregistered)

    S€ŁÉ©T íd, CöstČentreÇøde, BüsineßValue🤡 FŘÔM DâtåBuśınéssTæblę ШHEЯË things are getting interesting...

  • Just20MoreYearsToRetirement (unregistered)

    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.

  • Jules (unregistered)

    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.

  • Hal (unregistered) in reply to Just20MoreYearsToRetirement

    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.

  • Dave Aronson (github)

    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):

    CREATE COLUMN "THIS ONE      ",
              "THAT ONE      ",
              "ANOTHER ONE   ",
              "SOMETHING ELSE";
  • (nodebb) in reply to Sauron

    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.

  • (nodebb)

    The way this story starts, Maria sounds like she deserves to be a Highly Paid Consultant (in a good way).

  • (nodebb)

    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.

  • (nodebb) in reply to Jaime

    The same happens with PostgreSQL, in reverse (its case-smashing converts to lowercase instead of uppercase).

  • (nodebb)

    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" and MyTable are not the same.

    Addendum 2024-09-25 11:35: Replying to Jaime above, not sure why it didn't count as a reply

  • Argle (unregistered)

    According to Maria, this has since been "fixed" in SQL Server

    Darn! I was hoping to have some "fun" with this. It sounds better than using Greek question marks.

  • (nodebb)

    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.

  • (nodebb) in reply to gsckoco

    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" and MyTable are not the same.

    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.

  • Sauron (unregistered) in reply to Barry Margolin

    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.

  • SomeDude (unregistered) in reply to Mr. TA

    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.

  • Fa (unregistered) in reply to Just20MoreYearsToRetirement

    Someone got a carriage return - 0x0D - into the string. Could be copy paste indeed, could also be line ending mismatch unix / windows

  • (nodebb)

    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.

  • lol3000 (unregistered) in reply to WTF Linter

    Should be SSMS - SQL Server Management Studio

  • (nodebb) in reply to Officer Johnny Holzkopf

    S€ŁÉ©T íd, CöstČentreÇøde, BüsineßValue🤡 FŘÔM DâtåBuśınéssTæblę ШHEЯË things

    You monster! You absolute unmitigated monster! This totally cracked me up :-D

  • (nodebb)

    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.

  • SSMS Professional (unregistered)

    But wait, wouldn't SSMS's IntelliSense automatically prompt the column name with a space, surrounded by the brackets while you;re typing?

  • (nodebb)

    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. :/

  • (nodebb) in reply to SSMS Professional

    When the intellisense works, it should do that. When it doesn't or you're impatient and just type - no such luck.

Leave a comment on “Space for Queries”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article