A small bug/feature, which I created a new bug for at http://bugs.mysql.com/25706. Basically, the CURRENT_DATE() function seems to assign a time to dates, and that time is 00:00:00. In fact, all DATE formats are actually DATETIMEs with the date field of 00:00:00 and hidden.

This interferes with queries that use the date as an actual date and expect the date to include everything up until 23:59:59 of that day. The easiest way to reproduce this:

SELECT IF(NOW() BETWEEN '2007-01-17' AND '2007-01-18','yes','no') AS test\G
test: no
1 row in set (0.00 sec)

In fact, the following query always returns “no”, unless it’s exactly midnight:

SELECT IF(NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE(),'yes','no') AS test\G
test: no
1 row in set (0.00 sec)

This does not make logical …

A bit ago ago I posted some optimizer hints and recommended using them to write more readable queries. Today I stumbled across an example of my own work that I changed. This is not quite a case of using BETWEEN instead of AND statements, but it sort of is. Basically, I needed to reuse this query:

SELECT count(*),city,
CASE WHEN lastLogin < now ()-interval 90 day THEN 90
WHEN lastLogin < NOW()-interval 60 day THEN 60
WHEN lastLogin < NOW()-interval 30 day THEN 30
WHEN lastLogin > NOW()-interval 30 day THEN 0
ELSE -1
END
FROM . . .

I wrote this query less than a month ago.

I looked at this query today and wondered, “why did I not put lastLogin < now ()-interval 60 AND lastLogin>NOW()-interval 90 in there?” I then realized what I did.

Because the CASE statement …

A bit ago ago I posted some optimizer hints and recommended using them to write more readable queries. Today I stumbled across an example of my own work that I changed. This is not quite a case of using BETWEEN instead of AND statements, but it sort of is. Basically, I needed to reuse this query:

SELECT count(*),city,
CASE WHEN lastLogin < now ()-interval 90 day THEN 90
WHEN lastLogin < NOW()-interval 60 day THEN 60
WHEN lastLogin < NOW()-interval 30 day THEN 30
WHEN lastLogin > NOW()-interval 30 day THEN 0
ELSE -1
END
FROM . . .

I wrote this query less than a month ago.

I looked at this query today and wondered, “why did I not put lastLogin < now ()-interval 60 AND lastLogin>NOW()-interval 90 in there?” I then realized what I did.

Because the CASE statement …

Like many other in the “Hacks” series, this book offers 100 tips and tricks for making the most of your database through querying.

See more information at:
http://www.oreilly.com/catalog/sqlhks/

It is about to be published (Nov. 2006). I believe you can order the final cut (as opposed to the “rough cut”), although last week they were still advertising it as a rough cut…now you can order it online, in a print book or to read on Safari.

As a technical editor for this book, I have read through all of it (or at least, all of it that was there in the draft I received) and I can say it is definitely worth the $30 USD. Most of the examples at the time I read it were in MySQL format (though they might have changed that to be more fair to other databases); while it has some material for DB2 and Access, it’s mostly a book for MySQL, Oracle and SQL …

A few meetups ago, we had Jim Starkey come and speak. During that evening, he asked “Why is MVCC not serializable?” It was a “go and do your homework” kind of question.

So, in order to answer that question, let’s explore the concepts of isolation and concurrency, the “i” and “c” in ACID compliance. For this post, I will refer to “transactions”, by which I mean actual transactions, as well as statements where autocommit=0. In other words, every statement is its own transaction, unless it is within an explicit transaction, and for the purpose of this article, those statements are “transactions”. Of course, for many of the explanations below, if there’s only one statement in the transaction, there’s no problem. (As usual, if I make a mistake, please correct me. I will make sure all comments from all sources get to the article at …

Given the following scenario:

In a database, that could be represented as such:

CREATE TABLE GroupNum (
…

What is it about the folks on Planet MySQL having twin brothers?

Roland Bouman
Sheeri Kritzer
Jay Pipes
Zach Urlocker

That’s 4 of the top 25 posters to Planet MySQL. Anyone else want to reveal having a twin? Anyone on here have a twin sister? If you’re a twin and aren’t on the Planet, note that here too……

OSCON 2006 workshop

Building “Sane” Query Interfaces, by Mark Newsome e-mail is newsome@acm.org

Insane query interfaces have no help, no format, no range, and no help on how many results will be returned.

Better — pull-downs with a descriptive default like “select state”. Find min and max date from db. Gives a count for how many items in the pulldown menu. UI automatically reverts to use a popup button when pulldown gets too long.

“Refine” button can be pressed when the form is filled, and it will get a count, so you can “preview” your results. When the “refine” button is pressed, it refines EVERYTHING — ie, the counts for the range for the other items. Very smart! (see the song database demo below, when you enter in a lyrics search and click refine, it repopulates the pulldown menus with only the artists and titles that have the lyrics you requested). Also, if there’s …

I was wracking my brain for a bit, and could not figure out how to do a partial outer join where I was limiting rows from one table. So, I went to post to the MySQL user list — in writing my post, I found the answer. This often happens. It’s not that I don’t have questions to ask the MySQL user list (or forums), it’s just that most of the time, while I’m writing up the question, I find my answer.

First, I simplified my question. Many people do this, but they leave out important information. The trick is to come up with a simplification that you test to make sure it breaks in the same way the more complex, “real” query breaks.

The problem is basically this:
I want an outer join of a static table with a data table, such that I get a report of how many rows in the data table match the static table — including “0″ if there are none, but for a subset of the data in the data table. Consider a …

I finally got around to reading The Cathedral and the Bazaar — I’d known about the collection of essays since it was first published in 1999, and kept meaning to read it. Heck, that link goes to the entirety of the essays, free, online. However, I never did read it, but I recently received a copy of the book, and have been reading it on my commute to work.

It’s very engaging, and still accurate, even now. Of course I think about how MySQL’s growth and business model as I’m reading it.

One of the tenets that keeps appearing is that “Brooks’ law that adding more people adds more complexity and actually slows a project down doesn’t apply to open source.” He gives a few reasons, mostly communication, but there’s one crucial point he misses.

Brooks’ law doesn’t apply to open source because there are little or no startup …