LinLog

Formatting with psql

pageer@skepticats.com (Peter Geer)

Yes, this is yet another "reminder to myself" Postgres post, because I've had to look this up at least 3 times.

To turn on expanded query output formatting in psql, the Postgres command-line client, run either \pset expanded or simply \x. They both do the same thing - make Postgres output each column in a result set as a separate line. This is equivalent to the mysql command-line client trick of ending the query with \G instead of a semi-colon.

Incidentally both of the above commands are boolean toggles. You run them once to turn on expanded formatting and run them again to turn it off.

Timestamps in PostgreSQL

pageer@skepticats.com (Peter Geer)

So at my new job (maybe I'll post something about that later), we use PostgreSQL for our RDBMS. And, for reasons I won't get into, our application stores dates as UNIX timestamps, rather than, you know, actual dates. Thus this quick note to myself so I don't have to keep looking up how to convert between the two.

Date to UNIX timestamp in Postgres (from this page):
select date_part('epoch',now()) as unixtime; -- This will do it. select extract('epoch' from now()) as unixtime; -- ... and so will this.

UNIX timestamp to Postgres TIMESTAMP (from this page):
SELECT TIMESTAMP 'epoch' + 1195374767 * INTERVAL '1 second';

Weird index breakage

pageer@skepticats.com (Peter Geer)

Here's an odd one. So I was trying to run a simple query for a data import I'm working on. I've got about 11,000 rows in the media table with a site_id of 2 and I wanted to see which media types were represented. So I ran this:

SELECT DISTINCT media_type FROM media WHERE site_id = 2;

To my surprise, this returned...nothing! No rows at all. So I took off the DISTINCT, just to check, and got back all 11,000 rows. What the heck?

Something was obviously wrong. This should never happen - if there are rows with the correct site_id, that query should always return something. So what now? Might as well look at the query plan:

mysql> explain extended select distinct media_type from media where site_id = 2 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: media type: range possible_keys: idx_media_site_media_type,idx_frontpage key: idx_typesiteid key_len: 2 ref: NULL rows: 15 Extra: Using where; Using index for group-by 1 row in set, 1 warning (0.00 sec)

mysql> show warnings \G *************************** 1. row *************************** Level: Note Code: 1003 Message: select distinct `cms`.`media`.`media_type` AS `media_type` from `cms`.`media` where (`cms`.`media`.`site_id` = 2) 1 row in set (0.00 sec)

Nothing out of the ordinary there. It's using the idx_typesiteid index, which is correct. Maybe that index is broken. Let's try another one:

select distinct media_type from media use index (idx_media_site_media_type) where site_id = 2;

Yup, it worked that time - I got 5 rows back, which is about what I was expecting. I get the same results if I use
IGNORE INDEX (idx_typesiteid) as the index hint. So idx_typesiteid must just be broken. I should probably fix that, but it was getting into peak hours for our site and I didn't want to run an OPTIMIZE TABLE or anything that would lock the media table.

But wait, there's one other weird thing. Just for the heck of it, I tried running the query with a few different index hints. And guess what: the query still works if I use FORCE INDEX (idx_typesiteid). Does that make sense to anybody? So if I ignore that index it works, and if I force the same index, it works, but if I give no hint, it claims to use that index and the query returns no data. I got nothin' on that one.

MySQL comment weirdness

pageer@skepticats.com (Peter Geer)

Here's one I discovered for the first time yesterday - MySQL doesn't actually use the ANSI SQL comment sequence. Who knew?

For those who are a little rusty on such things, ANSI SQL-92 specifies the comment character as a doule dash (--). Anything from a double dash to the end of a line is a comment.

Well, in MySQL, that doesn't actually work. You have to have a space (or control character) after the double dash for MySQL to treat is as a comment. This is good style anyway, but MySQL makes it a hard requirement. Apparently the reason for this is that it can cause conflicts with software that auto-generates really crappy queries.

Is it just me, or does that seem like a really gratuitous change? I mean, I can see their reasons - it would be nice if an auto-generated query like UPDATE account SET credit=credit--1 actually worked instead of reducing to a no-op. But, on the other hand, is it really a good idea to change the database server because some vendor is too lazy to make their query-generator produce the more correct and logically equivalent UPDATE account SET credit=credit+1 instead? I don't know. I'm sure they had their reasons, but it just feels like a half-baked hack to me.

Keys and NULLs

pageer@skepticats.com (Peter Geer)

Well, the cat is currently laying on the book I was trying to read, so I guess I might as well blog.

Note to self: the rule in SQL is that you can have NULLs in foreign keys, but not in primary keys. Edwin asked me a question related to this the other day and I had to stop and think about it for a couple of minutes, so I figured I'd write it out to reinforce the concept in my own mind.

When you think about it, these rules make perfect sense. Since NULLs in SQL denote an unknown or inapplicable value, having one in a primary key would be nonsense, because a primary key must uniquely identify a row. Having a NULL in the key would mean part of the unique value was unknown. (Note that you can't say that a tuple with a NULL in it is unique because one NULL is not equal to another due to the 3-valued logic used by SQL.)

As for foreign keys, SQL gives the "benefit of the doubt." If you think of a NULL as an unknown value which may later become known, this makes sense. Sometimes you might have to add the values of foriegn keys in multiple operations, which would not be allowed if NULLs were blocked by the constraint.

From a logical point of view, this is kind of an interesting case as well. You can view a foreign key constraint check as a conjunction of equivalence checks. For example if T1(c1, c2) is foreign keyed to T2(c1, c2), then the constraint check can be viewed as the expression T1.c1 = T2.c1 AND T1.c2 = T2.c2. In other words, if all the key fields match on both records, the constraint is satisfied. If not, it fails. However, with NULLs, the value of one of the conjuncts would be NULL, which would render the entire expression NULL.

But what would that mean?

The DMBS can't say that it doesn't know if a relational constraint holds or not. So, given the interpretation of NULL, it makes sense to use the "benefit of the doubt" principle and defer final judgement on the constraint until all the NULLs are filled in.

Updating from the same table

pageer@skepticats.com (Peter Geer)

Last time, I was bitching about doing an update based on a select from the same table in MySQL. Well, after reading the chapter on "Views, Derived Tables, Materialized Tables, and Temporary Tables" in SQL for Smarties, followed by a search of the MySQL documentation, I figured out a way around that annoying limitation.

If you recall from last time, the query that wasn't working looked something like this:
mysql> UPDATE test_tbl SET val2 = (SELECT val2 FROM test_tbl WHERE val1 = 1) WHERE val1 = 3; ERROR 1093 (HY000): You can't specify target table 'test_tbl' for update in FROM clause
Basically, this is doing an update and setting a column to the value of a scalar subquery. The catch is that, in MySQL, this doesn't work when the select and update are on the same table.

Well, the trick is that you have to make make MySQL materialize the subquery. I actually decided to try that after reading Joe Celko's story about views in the early days of DB2. At the time, it was using inline-expansion for views, which would sometimes generate invalid SQL. Thus it was sometimes necessary to make the database materialize the view.

Well, I figured the same thing might apply here. And so, after a little inspiration from the MySQL manual, here's a working version of the same query:

UPDATE test_tbl SET val2 = (SELECT val2 FROM (SELECT val2 FROM test_tbl AS tmp1 WHERE val1=1) AS tmp2) WHERE val1 = 3;

I stole the sub-subquery idea from the MySQL manual. It is, of course, completely gratuitous from a logical standpoint, but it forces MySQL to materialize the subquery. That, in turn, allows us to bypass the "no updating the same table" restriction, since we're technically selecting from a temporary table.

So there you have it! I was wrong about not being able to do that. It just takes a little finagling. Of course, I still think MySQL should be smart enough to make a temporary table on its own, but at least the end result is still possible.

Another reason to hate MySQL

pageer@skepticats.com (Peter Geer)

I'm currently about half way through Joe Celko's SQL for Smarties and I've got to tell you - it's spoiling me as a LAMP developer.

Let me start out by saying that this book is a must-read for any developer who works with databases. Even if you've been writing SQL for years, you need to read this book. In fact, unless you sat on the ANSI SQL-92 committee with Joe Celko, you can benefit from reading this book. I've been "doing SQL" for years, and I've learned a bunch of new tricks and rediscovered things I had forgoten from reading this. And not only is it informative on SQL, but it's also entertaining and informative on other subjects (e.g., in one chapter, Joe describes the history of the soundex algorithm).

The problem with getting more SQL-literate is that I have to work with MySQL. Now, I don't want to rag on MySQL too much - it's good for what it is. And what it is is an engine for storing data and getting it back out in an efficient manner. The problem is that it's really not focused on implementing standard SQL in any deep way.

My particular annoyance today came when I was writing code to do a simple update. The situation is that I was working on a new variation on the picture galleries for our site, and realized that I wasn't associating a thumbnail image with the galleries I was creating. By virtue of the way our codebase works (all of which pre-dates my involvement), our picture galleries consist of n+1 rows in our main media table, where n is the number of pictures in the gallery - one row per picture and another row for the gallery meta-item. Because of the way I had written my creation methods in PHP and added my foreign key constraints in the database, the path of least resistance was to just set the gallery thumbnail to the same value as one of the pictures. Here's a simplified example of the basic query I used:
-- Create a sample test and populate it with data. CREATE TABLE test_tbl (id INT, val2 INT); INSERT INTO test_tbl VALUES (1, 2); INSERT INTO test_tbl VALUES (3, NULL); -- Here's the query I was running. UPDATE test_tbl SET val2 = (SELECT val2 FROM test_tbl WHERE id = 3) WHERE id=2;
In the routine as I had written it, I had the IDs of the gallery and all the pictures, but that was it. With a query like this, I could just clone the thumbnail of one picture to the gallery in a single query - no need to clutter the code with any lookups or suchlike.

Of course, if you know MySQL, you may already know that this didn't work. When I tried running this, I got:
ERROR 1093 (HY000): You can't specify target table 'test_tbl' for update in FROM clause
I was actually kind of surprised by this. I mean, this query isn't all that exotic. It's perfectly valid in standard SQL and it works in PostgreSQL. Why not MySQL?

Well, the answer seems to be that MySQL disregards the conceptual execution model of subqueries. I say this because, if you do the subquery on a different table than the one being updated, then MySQL doesn't have a problem with it. Example:-- Create a second table and add a row. CREATE TABLE extra_tbl (id INT, val2 INT); INSERT INTO test_tbl VALUES (3, 9); -- This one works just find in MySQL. UPDATE test_tbl SET val2 = (SELECT val2 FROM extra_tbl WHERE id = 3) WHERE id=2;
You see, according to Joe Celko (read the book!), the execution model of a subquery is that it creates a temporary table. So, in the case of my first sample query, it would create a copy of test_tbl, find the target row in that, and then update test_tbl. If you didn't use that model, then it would make some sense that you can't update the same table you're searching in. (Note: Of course, other databases don't actually create temporary tables for every subquery - they just produce the same result as if they did.)

I guess my problem is that I'm just starting to expect too much from MySQL. I've reached the point that I find it annoying that the default storage engine (MyISAM) doesn't support foreign key constraints. I find it even more annoying that MySQL doesn't support the enormously useful CHECK constraints at all. And, really, is it absolutely necessary to invent new versions of standard functions, mangle standard functions (what the hell did they do to CAST()?), or to disable the ANSI string concatenation operator by default? It all just just seems superfluous. After all, PostgreSQL didn't have much trouble with the ANSI standard. Why can't MySQL get it?

Oh, well. I guess it could be worse. At least now I have the wisdom to know what I'm missing. In days gone by, I thought MS Access seemed just fine as a database. Back then, I didn't declare primary keys and didn't even know what indexes were! By comparison to that, MySQL looked like freakin' Oracle! If only I hadn't been so young and stupid then, think of where I'd be now....