MySQL Bugs: #21456: SELECT DISTINCT(x) produces incorrect results when using order by – Version 4.1.21 has an error in the query optimizer where SELECT DISTINCT doesn’t work when using ORDER BY. Guess which version of MySQL I was using when I found this out.
Tags: mysql bug sql
From Jeremy’s MySQL wish list (emphasis mine):
e. Fix subquery optimization
Subqueries have been available in the same broken state for over 4 years now. Why are subqueries in an IN (…) clause still optimized in an incredibly stupid and slow way, such as to make them useless? We have customers tripping over this all the time. MySQL can check off “subqueries” on the to-do list, since they do in fact work. The SQL standard doesn’t say anything about not sucking.
Reading this, I had a mental image of the next SQL spec having a line in there about sub-queries that don’t suck :-)
Greg’s Implementing a queue in SQL (Postgres version) is an interesting exercise. The goal was to implement a simple queue that can be managed via SQL. The example is a simple first in, first out (FIFO) queue, with a limit of 5 items.
Two methods are demonstrated to accomplish this, the first one makes use of the PostgreSQL Rules and is very short. The second one is a little bit longer and uses a PL/pgSQL function as a trigger. One advantage of the second method is that it lets you know how many rows in your queue were recycled. Both methods require only one table.
There’s also a queue in MySQL article as well.
Is it practical to implement FIFO queues in a DBMS? Maybe not, but it is a good example of thinking outside the box.
I recently had the need format a datetime field in SQL Server 2000. My first thought was to look for to_char(), which does the job in PostgreSQL and Oracle. No luck there because SQL Server doesn’t have a to_char() function. After some hunting around I found out about the convert() function. This does the same thing as to_char() (more or less) but instead of using traditional date formatting codes, “style codes”.
Yes, you read that right. Instead of being able to get a four digit year by specifying Y or YYYY, you have to look up the style code that matches the format you want. In my case I was look for DD MON YYYY format. This turns out to be style code 106. Here is an example:
SELECT CONVERT(VARCHAR(11), datetime_field, 106) AS formatted_date
Some time with Google turned up a page listing examples of various style codes. Here is another one about 40% down the page.
There is an alternative to convert(), datepart(). This allows you to pull out specific parts of the datetime field. So you could do the same thing as the convert() example above, but it would be much more verbose.
UPDATE Fri 7 Jul 2006 @ 11:30am : I really should have included a list at least some of the format codes in this post for future reference:
Curtis Poe takes a turn at shooting down people who think foreign keys aren’t important with a O’Reilly blog post: Misunderstanding Foreign Keys. Curtis seems to have been inspired by this Are Foreign Keys Worth Your Time? blog post. It looks like Ruby on Rails is partly to blame for this attitude by not properly supporting foreign keys. That points back to MySQL and their off again on again history for foreign keys.
It is unfortunate that there are people who think that databases without foreign keys should be the norm and not the exception.
Catching errors and problems at the application level is a good thing, by all means please do so. But don’t, ever, EVER, use that as an excuse to not have proper checks in place at the database layer. I’d liken this to the same people who feel that they don’t need to validate web data on the server side because they are doing that on the browser side with javascript. So what happens when someone comes along who has javascript turned off? So much for you data validation. The same thing is true of the database and application relationship. What happens when someone writes a new tool for accessing your database and all of you application level checks are skipped?
Very bad things, that’s what.
The chapter on SQL Injection from the book Guide to PHP Security by Ilia Alshanetsky is available in PDF format. This can be a rather fun topic so I downloaded a copy of the PDF and started reading. I’d read less two pages before I was ready to toss this chapter out the window.
The first example showed a very simple inject that terminated the original query with a ; and appended another query that deleted some data. The example used MySQL functions and the author noted the following after explaining the injection:
Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking, or executing multiple queries in a single function call. If you try to stack queries, the call fails.
However, other PHP database extensions, such as SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.
The emphasis is mine. I couldn’t believe that the author was basically claiming that MySQL is more secure than SQLite and PostgreSQL because they allowed you to run multiple SQL statements in one string. Then something else came to mind, didn’t MySQL add this feature not too long ago? So I went hunting around and came up with the C API Handling of Multiple Query Execution in MySQL, which was introduced in version 4.1. Although still correct, the mysql_query() function in PHP doesn’t allow for multiple statements, the underlying C libraries do. I’d expect that at somepoint in the future PHP will catch up and support that feature for MySQL 4.1 and above.
But back to my first point, being able to run multiple queries in a single string is not a security threat. Not proplerly filtering data before using in an SQL statement is. If you allow unfiltered data to be injected into your SQL statement then you’ve got problems, even if you database doesn’t support multiple statements in a single string.
I hope that this isn’t typical of the rest of the book.
While I was scanning through the Managing Hierarchical Data in MySQL article at MySQL.com I started to wonder if someone had come up with a way to manage hierarchical data using geometric types. PostgreSQL supports geometric types like point, line, box, circle and polygon and several geometric functions and operators.
If you started out with one box (the universe) and then created new child boxes as needed then you should be able to accurately describe a hierarchy. You’d have to be able to shrink and grow each box (and all the boxes above it) as needed to make room for more or less child boxes. It would also be nice to have an easy to way move a branch of the hierarchy (along with everything below it) to some other position in the hierarchy.
It would take some work to sit down and come up with the functions and triggers that would make managing something like this as simple as possible. Anyone know where something like this has already been implemented?