Just A Weird Bug I Found In Some Client Code
Video Summary
In this video, I delve into an intriguing bug that cropped up recently while working with a client’s codebase. The issue revolves around a peculiar update statement that initially baffled me due to its seemingly incorrect syntax. I explain how the query managed to compile and run despite updating the wrong table, thanks to an unintentional aliasing mistake. By correcting the aliases in the query, we observed a significant improvement in the generated query plan, highlighting the importance of proper alias usage, especially in modification queries. This experience serves as a reminder to always double-check our SQL syntax, particularly when dealing with updates and other modifications, to avoid unintended data changes or performance issues.
Full Transcript
Erik Darling here with Darling Data Enterprise for Business Platinum 365. And in this intentionally short video where I am totally screwing up my green screen by having extra windows open that are bright, look everything magically got better. I want to talk about a weird bug that I found in some client code recently that I have simplified down to this. Now I’ve got two tables created in my database. I’ve got a table called T, which you can see there is already an object named T in the database, and a table called TT. And there is already an object called TT named TT in the database. It’s a little funny that IntelliSense is freaking out about this one, but this one is like, nah, you’re cool. But anyway, I got an email from a client, and they were like, something weird is going on with this update statement. We can’t figure it out. And please take a look at it. And so they sent me the update statement. And of course, I was, you know, I was up late writing demos the night before. And I looked at the query and I was like, well, how does that even work? How does that do anything? I mean, your update, the update T, right? And you have an alias and you’re setting T dot ID equals one from a table called TT alias is TT. How is how is this even doing anything? What what is happening? Send me the query plan. And so they sent me the query plan. And I’m going to show you what this query plan looks like. Here and go over here and look. And I mean, the object, obviously, the object called T in my database is there with a columnstore index on it. That’s that was that was my little cheat code for various things and some some demos. But I looked at the query plan. I was like, Well, you’ve got two tables in there. And they’re like, Well, is it a foreign keys trigger? What’s going on? I was like, No, no, there’s a table named the thing that you’re updating. But you’re still selecting from the wrong table, you didn’t alias the second table correctly. I guess they had some an unnamed code completion and formatting tool that had alias the table automatically for them. And it was it was unintentional there. So I look at the query plan. So well, you’re, you’re, I mean, it’s weird that this even works, right? Like, it’s astounding to me that this compiles and SQL servers like cool, no problem.
Got it. All the reasonable things that I feel like I’ve written where SQL servers like, Whoa, whoa, whoa, whoa, whoa, whoa, whoa, whoa, whoa, whoa, back it up. We can’t possibly do that. This thing compiles and runs. So I sent them back a copy and paste of the query done correctly, where we update tt and we set tt dot ID equals one. And miraculously, we got a much more sensible, a much more sensible query plan that didn’t involve any other tables in the database.
So anyway, if there’s a lesson here, please pay attention to your aliases, especially in modification queries. You may find at various points that if you alias things incorrectly, SQL Server will throw an error. You may find in other circumstances that if you alias things incorrectly, you will muck up a lot of data that you shouldn’t have actually touched. Because the way this thing used to be written, it would have actually updated the ttable and, you know, changed a row to something. Wouldn’t have changed the whole table. Because there is, there is a, what do you call it? There is a, let’s actually step back. There is some manual, oh, sorry, there’s some automatic phase separation in this query plan.
And we’ve talked about this in prior videos. I talked about it with a hash join. But here we have a sort that actually does the does the work that a spool would have done. And in another plan. So you wouldn’t update the entire table, you would just update one row out of there. So isn’t that nice? Anyway, like I said, intentionally short video, blog fodder, felt like recording it, didn’t feel like writing it. There are there are times when I love sitting down and writing blog posts, typing things. Right? Taking screenshots and uploading them to WordPress. And there are other times when, gosh darn it, it’s just so much better to do a video. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you’ll be very cautious with your table aliasing, especially in modification queries.
And if you feel feeling generous today, you’re feeling like I really earned your time and attention. You can like and subscribe to my channel. Right? Thumbs up in the bell thing. Anyway, thank you again for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.