Chess Query Language

It's amazing how many tools are available on the web for seemingly obscure tasks. Recently, a friend of mine was writing a short story, and he needed an answer to this question: In high-level chess games, how often do the different pieces survive through the game without being captured (ignoring kings)? In the context of this question, each of the 30 starting pieces is treated as distinct; we want to know how often the pawn that starts on the a2 square survives, how often the b2-pawn survives, etc., rather than how often general pawns survive.

I think this qualifies as an obscure question. It seems simple enough to answer in principle - just get a database of games, and write something to play through each game, tracking which pieces survive. Simple enough, but a fair bit of work. Luckily there's a tool that will do this type of thing: Chess Query Language.

CQL is quite powerful, and it's pretty straightforward to set up a CQL query. For example, to answer the above question about survival rates, I started by creating a query to see how often the white queen's rook survives:

:forany Rook R
(:position :initial $Rook[a1])
(:position :terminal $Rook[a-h1-8])

That's it. The first line creates a Rook piece designator; the second and third lines specify positions that have to exist in a game for the game to match the query. Thus the query will match any game where a rook is on the a1 square in the initial game position, and that same rook is somewhere on the board in the terminal game position.

This query took about 45 minutes to run through a database of about 2.5 million games, and found that this rook survived in about 1.4 million of them. I just had this repeat for all pieces and pawns to generate the final answer.

So, I can advise that if you're ever involved in a Harry Potter-style human chess game, you should volunteer to be one of the wing pawns. Don't allow yourself to play as a knight, whatever you do.

A Few More

Here are a few more photos of Owen.

All ready for the opening ceremonies tonight.

Having fun looking out the window.

Preparing for the new season of Lost in his Dharma Initiative onesie.

Owen at 9 Months

Owen is over 9 months old now, so here are a few new photos.

Playing with the wrapping paper on Christmas morning.

Getting ready to watch the world junior hockey game in his new Canada olympic sweater.

Jumping into the pool!

It took Owen less than a year to abandon daddy's Chargers. Smart kid.

Reading List

I received a few new books over the Christmas holidays. They total somewhere in the neighbourhood of 3,500 pages, so I should be finished reading them all roughly around the end of time. Here they are briefly, and hopefully I'll post some comments as I finish each of them. Don't hold your breath, though.

The Princeton Companion to Mathematics
This is a wonderful thousand-page book that would seem to have an incredibly narrow appeal. It's essentially a survey of current thinking across all mathematical disciplines, written to be as accessible as possible. The original goal for the book was that it could be handled by anyone with high school-level math, but the authors weren't able to meet this for all sections. I find this incredibly interesting, and pretty much everyone else I know would not find it interesting at all :).

Under the Dome, Stephen King
I'm a reasonably big fan of King, although I haven't read some of his more recent stuff. This one has been getting reviews in the range of good to great, and drawing comparisons to The Stand, which is a favourite of mine.

The Book of Basketball, Bill Simmons
Bill Simmons writes for ESPN; he's also known as The Sports Guy. This book is essentially just all of his opinions about basketball, including a few hundred pages ranking the best players of all time.

The Dark Tower: The Gunslinger Born
Stephen King wrote a seven-novel series called The Dark Tower over a period of about 30 years. Recently, Marvel has been publishing comics in the Dark Tower universe. This book is a collection of the first few issues.

A Mathematical Nature Walk, John Adam
I hadn't heard of this one prior to receiving it for Christmas, but it looks to be the kind of thing I like.

Close But No Cigar

A friend of mine, who cheers for the Pittsburgh Steelers, has been talking about how close their losses have been this year. The Steelers finished 9-7, but the 7 losses were by a total of only 28 points, so a couple plays one way or the other could have produced a very different result. I'm quite familiar with this line of thinking, since my team is the Chargers, and last year they lost 8 games by a total of only 34 points (including a 1-point loss on a terrible blown call in a game I drove 1000 miles to see. But I digress.) I have my trusty database, so I thought I'd take a look at how these two seasons stack up historically. The following items all consider the years from 1978 through 2008.

• Among all teams that finished at 9-7, the 2009 Steelers' total margin of defeat (28) was the lowest. The next closest were the 1993 Broncos (30) and the 2002 Saints (35).
• The 2009 Steelers' average margin of defeat (4.0) is the lowest among all teams that lost 7 or more games.
• Among all teams that finished at 8-8, the 2008 Chargers' total margin of defeat (34) is tied for the lowest with the 1999 Raiders.
• The 2008 Chargers' average margin of defeat (4.25) is the lowest among all teams that lost 8 or more games. The 2009 Steelers are the only team with 7 losses to have a lower average margin of defeat.

It looks like these two seasons were in fact quite unusual. Here are a few other interesting facts I came across while tabulating these results.

• The 16-0 2007 Patriots are a bit of a special case, but you might say that they hold the record for smallest average margin of defeat, at zero.
• The 1983 Redskins finished 14-2, and their two losses were by one point each.
• The next best average margin of defeat (2.33) was by the 2000 Titans, who finished 13-3 and whose losses were by a total of 7 points.
• The team with the worst average margin of defeat (24.7) was the 1989 Steelers. They actually made the playoffs at 9-7 (unlike this year's Steelers), but had several huge losses, including a 51-0 game against Cleveland. In the playoffs, the Steelers won their first game, and then lost to the Broncos... by 1.

DateTimes Through OLEDB in VFP

For some time, we've been running queries against a SQL Server database through an OLE DB connection. Recently, I came across some strange behaviour when retrieving datetime values.

In one location, the application populating the SQL database allows for an empty date value. However, SQL Server doesn't allow for an empty datetime value (though it does support a null datetime). The application handles this by using the maximum SQL Server datetime value of 9999-12-31 23:59:59.997 to represent an empty date. The data looks something like this:

This is maybe a bit unusual, but not too strange. However, when querying this data through an OLE DB connection in VFP, this is the result:

That's curious. Why is the date appearing as a blank? Doing some quick investigating on the retrieved record, both Empty(ONDATE) and IsNull(ONDATE) return false. Even more curious. What would make a datetime value display as blank, but still evaluate as not empty and not null?

Ok, let's try connecting to the database using an ODBC connection instead. Here are the results of the same query:

Curious again. The data is appearing correctly here. Something must be different in the way that OLE DB and ODBC are handling these datetime values. Let's add a couple more testing values, and then retrieve them both ways, to see if that sheds any light on the situation.

This shows what's going on. The OLE DB connection is using the milliseconds to round the value to the nearest second, while the ODBC connection is just truncating the milliseconds. The weirdness with the blank value is coming from the fact that 9999-12-31 23:59:59 is also VFP's maximum datetime value, and the rounding is forcing the value past this maximum.

I generated a VFP table using the results of the OLE DB query, and opened it with a hex editor. Sure enough, there is data in the "blank" datetime value. VFP stores a datetime value in two pieces: the date as a Julian day, and the number of milliseconds past midnight. The problem value has a number of milliseconds that evaluates to slightly more than 24 hours. The Empty and IsNull functions are correctly reporting false, based on the fact that there really is a value stored there.

All in all, this is not too much trouble to work around, since we can just use an expression with CASE or DATEADD/DATEPART to have SQL Server adjust the value for us before sending the query results. It's good to understand this behaviour though, since it will also appear in other situations where milliseconds are included in datetime values, and it will be much less noticeable that any rounding is happening.

Pumpkin Carving Madness

For the past few years, we've held a pumpkin carving contest for Halloween. People come over and carve their pumpkins, and then the trick-or-treating kids & parents vote for their favourite. I won the first contest in 2006, but I haven't been able to repeat. Kathleen has won the past two contests.

Here are a few pictures from this year.

Pumpkin Carving Madness 2009 underway

Owen inspecting one of the pumpkins

My entry for this year, good for second place in the voting

Kathleen's winning entry