13 November 2018

I Discovered a Bug...That Started the Product Team Crying

It's a pretty rare thing for we footsoldier DBAs to discover a true bug in Microsoft's code. This one, however, stunned me, a bit, if only because it was located not in an obscure corner of the SQL Server product, such as the bug we found several years ago in Change Data Capture, but in the basic Query Optimizer, and it resulted in false truncation errors, a logical flaw. It is basic and quite reproducible.

There are several things about this that shocked me:
  1. I encountered this bug over several months in two completely separate pieces of code, unrelated to each other at all.
  2. The bug is reproducible in SQL Server 2012, 2014, 2016, and 2017 (v11-v14).
  3. In light of the above, no one had noticed or reported this! Apparently Microsoft was unaware of the bug, and despite me running into it twice in two completely different contexts, no one else had? Just seems odd and not what I would expect given the extremely wide distribution of the product.
So what is it? Basically, if you are inserting into a table from a select statement that joins two or more tables, the join order in the execution plan can have an unintended logical result on your query. Say one table contains some values that would cause truncation errors if inserted...but the other table is inner joined, which would restrict the values on the first table to only those which are of valid length...you'll still get truncation errors depending on the join order in the execution plan, assumedly due to validation happening at a point prior to the join.

Let's reproduce! No, *ahem*, that is not intended as a strikingly unromantic pickup line, what I meant was, let's reproduce the problem in code, first the setup, of two input tables and an output table. Note the input table A has two values, both of which map to the shorter length values of input table B (col1 = 1,2):

USE tempdb
CREATE TABLE InputTblA (col1 INT, col2 CHAR(10));
CREATE TABLE InputTblB (col1 INT, col2 CHAR(10));
CREATE TABLE OutputTbl (col1 INT, col2 CHAR(5));

INSERT INTO InputTblA VALUES (1,'value'),(2,'value');

So, now that we have those tables set up, let's do our insert.

SELECT a.col1, b.col2
FROM InputTblA a
INNER JOIN InputTblB b ON a.col1 = b.col1

We get, in this case, the expected and valid "(8 row(s) affected)". Looking at the execution plan, we see that it starts with InputTblA and then hash joins to InputTblB.

Normally SQL Server does this right, it's just when the table order gets just so that the bug shows up...so we must force it, using FORCE ORDER.

SELECT a.col1, b.col2
FROM InputTblB b
INNER JOIN InputTblA a ON a.col1 = b.col1

Msg 8152, Level 16, State 14, Line 26
String or binary data would be truncated.
The statement has been terminated.

And here's the plan...we can see it scanned InputTblB and ran a compute scalar before joining to TblA which had the constraining data:

That's all fairly easy to duplicate and I went a step further, utilizing the latest feature of SQL 2019 backported to SQL 2017 CU12, the advanced truncation error message 2628 that actually calls out truncated columns and values. Once patched to CU12 I repeated my test with trace flag 460 enabled, and the error message was more descriptive:

Msg 2628, Level 16, State 1, Line 26
String or binary data would be truncated in table 'tempdb.dbo.OutputTbl', column 'col2'. Truncated value: 'longe'.
The statement has been terminated.

It lists the truncated value that we know by definition is impossible, with that query and data, to be in the result set.

Having successfully hunted a Microsoft SQL Server Query Processor bug, I was pleased to notch my tag and cape the hide for my taxidermist, who will mount it for me for my living room wall.  The product team at Microsoft has acknowledged to me (via the Premier Support rep I've been dealing with) that this is in fact a defect and they will be fixing it in a subsequent release (hopefully backported via CU or SP).

31 July 2018

Colorado 2018

Well, another roadtrip but less road and more destination this time...shorter, too.

Day One: Across Kansas

Well, it's a bit late tonight due to "technical difficulties" (however Microsoft has managed to retain a bug for 10 years with Windows Explorer and video file thumbnails...but never mind), but I'll try to bash out a few quick notes. A relatively short road trip this time, a one day haul to Estes Park Colorado. Less driving, more hiking.

We headed out around 6:40 or so, later than usual. The decision to do so had more to do with our first destination, the US Cavalry Musuem at Fort Riley which opens at 9am. Well, opens in theory...we pulled in to the VISITOR CONTROL CENTER as it is officially known, and the very ALERT and somewhat DISTRUSTFUL MP on duty informed us that the museum was under construction. We gratefully utilized their restrooms and headed back on the road.

The long haul across Kansas was...well, any of you who've done it know. But we thought we would punctuate the interminable slog with a stop in the border town of Burlington Colorado, where we had previously visited an old carousel museum...a taste of Disneyland perhaps, or at least, an excuse to stretch the limbs. We pulled into the rather rough looking Burlington and headed towards the fairgrounds where the carousel and museum were located, and found that the fairgrounds were in active use...the place was packed and no parking spots in site. We were 0 for 2...back onto the highway.

Finally into Denver and our required stop, Casa Bonita:

We were mostly just happy to be out of the car.

This shot is always required, I don't know why. I first went here as a 15 year old kid on a church camp trip, having never heard of the place before (later realizing, via South Park, that it is a Colorado institution).

Walk in, and the pleasing dankness of the Pirates of the Caribbean is immediately invoked...chlorine, fried food, and aging, well-used infrastructure.

Debra and I forced ourselves to stick to our plan (an underwhelming chicken fried steak for her and a taco salad for me), as we had previously made good plans to avoid the worst of the food only to think, well hey, here I am, why shouldn't I order the Mexican platter? THERE ARE REASONS

Kids actually loved the food but loved the show more...the cliff divers both horrified and entertained Gretchen.

Got a closer look after the sopapillas...

THEN TO BLACK BART'S CAVE! The kids tried to welch on their intentions but we forced them, as good parents, to stiffen the sinews, summon up the blood, and trot on through.

Over by the waterfall:

We then explored the lower depths of the place which was enchanting in its dated way. It actually has quite the capacity...this was in a "cave" section.

Thence out to the main area and off again.

Debra noted several Winchell's in Denver, having long lamented the loss of the one in Raytown.

We drove north and basically slogged through traffic for a couple hours, getting through the clot of dispensaries in Boulder and eventually getting into the mountains at long last.

I think Longs might be back there, but in any case, right outside of Estes.

Debra caught sight of the "Shining" hotel.

At our cabin, Gretchen grabbed the optics and started in on scanning. We were advised that the local bears 1.) have learned to open car doors, and 2.) will open unlatched cabin windows, so we'll be on our guard.

It's a great cabin and we're quite pleased to actually stay here for an extended bit (not our usual land-then-immediately-depart approach).

We walked over to play a bit of foosball and pingpong, even though by Missouri time, it was past bedtime.

The kids played on the playground and met some new friends, while I fought with this benighted PC. Tomorrow, into the park! We'll play it by ear (weather is a major factor) but the idea is Bear/Dream/Emerald lakes, then Trail Ridge Road with a couple small hikes on the way, the visitor's center up there, then back down to buy groceries and cook a nice dinner.

The moon setting behind Gianttrack Mountain...

Day Two: Emerald Lake and Trail Ridge

We woke up at 0530 or so and started to get ready for our "easy" day of hikes. But in the dawn light, noted a cow elk right outside. Kids were excited to see her.

Small muley doe outside with a collection of magpies later.

I get a bit more terse in my prose style, admittedly, as I get more tired. Anyway, we rallied the troops and got on the road towards the Bear Lake junction where we planned to hike to Emerald Lake. We were concerned as we got up there, but the parking lot was filling up to the back just as we pulled in...another 5-10 minutes and the parking lot would've shut down.

It's I think a seemingly modest 1.8 miles one way, with elevation gain around 600-700 feet. We got to the first stop, Nymph Lake, but didn't stay long.

Stretches of stairs proceeded between the lakes as we moved further up and further in.

There was a light sprinkle of rain and a slight uptick of wind that made us quicken our pace.

Kids were surprisingly...non-whiny. Good little hikers, if a bit apt to take more risks than their mother's anxiety prefers.

Then we arrived at Dream Lake, a long, narrow lake that reminded me a bit of Glacier NP lakes. Fishermen were fly fishing out in it and Gretchen and Peter spotted a rainbow trout (or some kind of colourful trout).

We stopped a bit there and the kids ate dried mangos up on the rocks.

More of the same as we passed Dream Lake en route to the destination, Emerald Lake. Saw it as we came through here, at last.

The most exciting thing there for the kids were the obviously fed and not-even-a-little-shy chipmunks. They were vicious and unruly, as chipmunks tend to be, and Debra was at risk of braining one of them as she tried to ward his attacks off with a hiking pole.

Colors are probably better on a sunny day, but this lake has a nice cathedral effect with the surrounding mountains.

We didn't stay long...

Water proceeding downhill much as we, at this point, were.

Nice mountain vista to the south (Long's in there somewhere?) on the route back.

Then into the car and up north to Trail Ridge Road. This was at the Rainbow Bend spot...

We returned to the Toll Memorial Trail at around 12k feet. We never made it very far on this last time.

Kids persevered this time (it's only 1mi roundtrip) and got to spot marmots. I had previously worn out the annoying pun about what a bear likes on its toast (marmotlade) (I'm not sorry).

Up at the top, and all downhill from there.

People get winded on the first hundred feet due to steepness and the shock of the biting, oxygen-lite winds. But you get used to it.

Then we went to the Alpine Visitor Center (it was closed last time we went here). I spotted my targeted mountains...Chapin, Chiquita, and Ypsilon (left to right).

Lunch at the visitor's center and the kids got a well-deserved toy.

Came back to Estes, I did some grocery shopping, and the kids will likely play around this area tonight (minigolf, pingpong, foosball, shuffleboard, a playground, tetherball, etc etc) and I'll cook dinner. Tomorrow, Debra and the kids will relax here while I try to make the best of the weather forecast and get on top of a mountain or two.

Deer grazing outside in the evening:

Day Three: Mount Chapin and Mount Chiquita

Another big day today, at least for me. Up at 0430 and was packed and on the road prior to 0530. Into the park, where a full moon was shining. However, not the usual highways but Old Fall River road to get to the trailhead.

Key takeaways when I was reading online about this route was "steep dropoffs", "narrow one-way dirt road" and it was a rather exciting drive, made more thrilling by Wagner I was playing for effect.

Had to drive very slowly but I got there eventually. The air was thin and the initial ascent rather vigorous. The mountains also were not exactly underwhelming in person. Nevertheless, I kept on.

A view back after some ascent...at the upper left, to the right of the snow banks, you can see the Alpine visitor center on Trail Ridge Road.

Plodding up along the side of Mount Chapin, sun about to emerge.

One of a great many pika I found, running here or there with vegetation in their mouths, making loud chew-toy squeaks from among the rocks.

I passed through a talus field which was misery with my ill-footing cheap boots, and the way got a bit easier. A look back to the west:

As I started to ascend Chapin, the view of Chiquita to the north looked ominous. At this point, possibly influenced by a mistaken understanding of the time (it was still early, but my Garmin was on Eastern time and I assumed much time had slipped by), I opted to just try for Chapin and Chiquita and leave Ypsilon for another day.

First peak bagged of the day, Mount Chapin. 12,454'.

Panorama from the summit...I didn't stay long (again under the impression that I had fallen behind schedule).

Starting to descend from the summit, I saw a ptarmigan with a handful of chicks...didn't manage to get any serviceable pics but you can at least see the chick here, zoomed in.

Then the big slog...up the interminable staircase to Mount Chiquita. A look back (mostly to catch my breath but taking pictures cleverly disguises that) and Chapin doesn't look unimposing from this angle. I'd been tempted to skip it in favor of Chiquita and Ypsilon, glad I did not.

It's a good haul but has some good views. This is the less challenging tundra section. Blue skies are reassuring when you're this exposed. The winds were intense, but still, don't compare to the winds at the summit.

There was no trail to speak of at a certain point in the vast talus fields. This is ankle turning stuff. But with regular breaks to make sure my spoiled Missourian lungs were keeping pace with the thin air. But eventually I hit the summit. It was well populated by a troupe of teenage girls I had past while they were descending Chapin and a few other hikers. View to the east:

Broader panoramic with Ypsilon to the north:

I sat down by these rocks and had a quick restorative of plastic-flavoured water from my insufficiently cleaned water pack (LaCroix "Plastique", look for it at your local supermarket and ask for it by name!) and a bit of beef jerky.

I've never been one for the whole "selfie" thing, and the teenagers were doing what was essentially one giant collective selfie up there so I felt no need to do so, but a friendly hiker offered to take my picture:

Apologies for the second German Romanticism reference, but hey, this is the right sort of place for that kind of thing. I took another look at Ypsilon, and thought...you know, maybe another time. No regrets of that decision.

Descent is easier than ascent. In some ways. Going up is all legs and lungs. Going down, it's feet and joints. The inadequacies of my boots were highlighted in a very long descent through a talus field. Consider this picture looking back on what I had just descended:

Where's the trail? Can you see it? No? Well that may be because it wasn't there. I kept trying to catch a glimpse of cairns but nope, I was off track. I started to consider veering to port, assuming the trail was further east, but I doublechecked my sat/GPS device with topographic maps, and realized, I had that quite backwards. I swore vigorously at the talus with creative new epithets and found myself being squeaked at by a throng of shrill pikas, (quick, what's a bear's favorite dessert? Pika-in-Pie GET IT) but eventually got back on trail.

Back in the saddle (no, the saddle of the mountain, not the metaphor) between Chapin and Chiquita...the painful bits mostly over.

I took a quick break to plot my return journey...an unmarked trail stretched a bit further downward in grassy areas, away from the talus on the slopes of Chapin. I had no assurance it ended up where I hoped, but it appeared to move in the same direction, so I opted for it.

Turkey print? Hard to think of turkeys in this climate, but they do live here. I normally envision turkeys in the humid spring forests (borderline tropical) in Missouri as that's where I usually encounter them. Strike that...that's where I usually fail to encounter them.

Bear scat maybe? Shape seems off but variation in diets, maybe. Certainly doesn't look like cervid droppings, and small for buffalo or horse I would think. Volunteers to taste?

Eventually I got back to the car, and headed back on the road...which took us up to the visitor center again (happily I was not queueing for a spot this time). The route back was basically Trail Ridge Road, and I got to witness what ends up being my third bad pun of the vacation. What is a bear's second favorite thing to spread on toast? ELK JAM

She wanted to join the herd up on the slope, but was being too tentative, so eventually they let us proceed.

On the descent, noted a storm brewing, and saw some lightning flashes. Another good reason to be off the summits by noon.

Now, back at the cabin with all of nothing planned for the rest of the day. We sat out on the deck a bit and the kids were into spotting wildlife before the rain started in.

Local less-exotic versions of the lagomorph family were abundant in the neighboring field. Kids found them exciting though.

Day Four: Lily Mountain

Up early the next morning to get to the Lily Mountain trailhead, this time with kids in tow.

Attitudes were mostly quite positive the whole way, but with 1000' of elevation gain, it's not an easy hike for them.

It's two miles up and two miles down. Most of the elevation gain is on the second half of the ascent, so the first one is relatively modest but with a variety of exposed areas.

Some nice views to the northeast. It was a tactical victory to get there so early...no parking worries and it stayed cool. There was a washed out section of trail that was a bit perilous to the little ones but we traversed it well enough.

Well, cool enough...we had to stop and shed outer layers at one point.

The summit looks high up there, which is an impression borne of that fact that it is.

Gretchen named this structure Raven Rock, but when Pete and I saw it, it reminded me of those huge Jawa tank things in the desert on Tatooine in Star Wars: A New Hope. Here he is waving his arms like C-3P0 asking for help.

We took some breaks for water and jerky here and there. Gretchen called the hill in the valley below Mount Buttcrack, for obvious reasons.

Twin Sisters Peak over to the east. I had this considered for my second solo hike on the fourth day, but having done as much as we have, I think I'll take a day off.

We hit around 1 mile of the easier stretch and then, nowhere to go but up, up, up.

Breaks had to be more frequent here. I think it averages...well, I'm not sure, but over 15% grade as I recall.

We got further than where the family stopped last time, with some minor rock scrabbling that we took very, very slowly...that was a bit after this point I think

We made the strategic (and probably wise) decision to again not prod the kids all the way to the summit...they were tired, not especially eager, and there were indeed some tricky bits up there. I went up and spent a couple minutes sitting on top. Had to sit on my hat lest it fly off.

Pretty expansive views. My camera apparently goofed up this panorama.

To the north is Estes.

I trotted back down, and rejoined the family and we were on our merry way. It became even more merry, when after we had gotten down the tricky rock scrambling bits, we noticed we were down one hiking pole which Gretchen had left up at their high-altitude camp. So back up I went to get it, and back down, then on down we proceeded again.

Going down is, as I've said, a bit rougher on feet and joints, and it was a lot quicker going, but it still felt long...we weren't going for speed.

Then we got down, happily reunited with our car, and headed to the Beaver Meadows visitor center and the kids picked out a couple stuffed animals (Peter's red fox is Max, and Gretchen's mountain lion she named "Lily" in honor of our hike).

Back to the cabin, and I drove into town to get gas, groceries, and a large assortment of Korean food for a late lunch (and probably a number of additional meals). We ate and I drank several glasses of water (my water pack still tastes horrible so I drank less water than I probably should have). Then to the pool, and probably minigolf, foosball, ping pong, etc. But going to take it easy the rest of today, and tomorrow. Bit of ping pong:

Followed by watching Napoleon Dynamite (they have a DVD library and the kids were first confused, then amused), and a late evening tetherball championship.

Day Five: Lily Lake

Next morning we had nowhere specific to go so we took it easy. Kids watched first the resident muleys visit the salad bar out front:

And then the local lagomorphs:

Eventually we figured we should head somewhere or get stir crazy, so having promised the kids no more hikes, we dubbed this one a "walk"...0.8mi and 10 feet of elevation gain, around Lily Lake at the foot of Lily Mountain.

Place was thick with ducklings.

Chipmunks likewise, but they were old news to the kids.

Ducks rooting around in the mud by the shore.

To the south, Longs and Meeker. For a second I got a stupid twinge of, I could do that someday. I looked at Debra who said with her eyes, absolutely not, and I was free of the spell. I like challenges but at a point there is the Stupid Idea Factor that takes over, and Longs Peak for a guy like me is well in that territory.

Hard to see, but a swimming muskrat out in the lake.

Canadian tourists, as usual, strutting around like they own the place and openly defecating all over public walkways. Twas ever thus...

Twin Sisters Peak over to the east. Pretty ominous but was rated easier than what I did a couple days ago, so maybe someday. It was on my "optional" list when I thought I'd be hiking this fourth day.

A view of what they hiked...Lily Mountain in the background.

Gretchen surmised this was a muskrat, not beaver structure. Beats me!

Another shot of Lily Mountain.

Interesting shorebirds, not sure what they are.

The old abandoned visitor center. Not sure why they closed it.

As we headed to the car, Gretchen caught sight of a line of horseriders.

Then up into the town for some ice cream and traffic fun:

We're back now, preparing for an early departure, and passing the day with fun stuff at the resort (lots of ping pong and foosball and there is talk of another tetherball faceoff). We did some horseshoes:

We loved the place:

Day Six: To Home

Well, funnily enough, no pictures. The hallmark of an efficient travel day. We stopped twice for gas but that was it, getting up before dawn and getting into KC well in time to pick up the Beast from being kenneled another night. Glad to be home.