Wouldn’t it be nice if you could get expert advice on your SQL queries to find problems in them, the same way that programmers can use lint-check tools to warn about bugs in their C?
1 2 3 | if ( execute = 1 ) { launch_missile(); } |
Such a simple mistake, but it’s the kind of thing that James Bond movies are made of, isn’t it? Well, a lot of SQL queries have similar bugs, and thanks to Miguel Trias, now there’s a tool to help you find them. This is the second addition to our online suite of tools for MySQL users. You paste a query, it tells you what’s wrong with it. Simple as that.
Find the bug in this query:
1 2 3 | select * from t1 left join t2 using(id) where t2.created_date < 2012-02-15; |
Do you see it? Congratulations! I’ve analyzed that query with the tool, and shared the results with you. Click here to see if you were right.
That demonstrates another feature of the tool: you can share queries so they’re public, and other people can see them. The tool uses the same account that you might have already created for our online MySQL Configuration Wizard, which helps you choose good settings for your server.
Give it a try, and tell your friends! Enjoy hunting for bugs and problems in your SQL queries!
That’s a pretty neat tool. Any plans to be able to submit a batch of queries for analysis (controlled via an API key)?
Nice tool. I missed the quoting around the date but this is a trade-off since I caught the fact that the query is also doing a full join of t1 to records in t2 with created_date < '2012-02-15' which is probably also a logic error.
Darn, I wish I could delete that comment, its “using” not use (facepalm)
Yeah, a lot of people don’t know the USING() syntax, but it is a nice shortcut. Shortcuts that are visually confusing are one of the biggest reasons to use a tool like this. What’s wrong with THIS query?
select tbl1.* tbl2 from tbl1 join tbl2…..
It aliases the last column from tbl1 and renames it “tbl2”.
That’s exactly the kind of thing I’ve been noting down for the last few years as I solve query problems for clients, and built into this tool.
Derek, that’s an idea, isn’t it 😉
select id from (select I am not sure this tool works with sub-queries)
Apparently I’m awesome… I guess its not working with sub-queries yet….
Sure it does, if you give it a REAL subquery 🙂
select * from t1 where id in (select id from t2)
The SQL parser is pretty sophisticated; it’s not just looking for a regular expression like “select….in(select…” or anything like that.
One common mistake that it didn’t catch:
UPDATE tbl SET c1=4 AND c2=5 WHERE id = 1
Harrison, that’s a good one. Thanks!
This is a great tool. Thanks!!!
I was really confused for a minute, and then I realized that the mysqlperformanceblog RSS feed must have a bug in its HTML escaping.
I mean, isn’t it obvious where the bug in “select * from t1 left join t2 using(id) where t2.created_date” is? “t2.created_date” is not a condition! 🙂
Sorry to be a party pooper Baren… maybe I am not supposed to be finding bugs, or maybe I am just using it incorrectly…
But shouldnt the query
“id from something”
produce a warning with this tool?
Hey Baron, nice tool. This blog post and comments actually made me learn something about Postgres. 🙂 The query you gave about “select tbl1.* tbl2 from tbl1 join tbl2…..” when I looked at it, I thought, “that should through a parse error”, and was surprised to hear it doesn’t. So then I went to try it in postgres and did the following:
pagila=# select actor.* film_actor from actor join film_actor using (actor_id) limit 1;
actor_id | first_name | last_name | last_update
———-+————+———–+———————
1 | PENELOPE | GUINESS | 2006-02-15 09:34:33
I was surprised it worked! But, I’d note that it doesn’t alias the column in Postgres. Subtle difference. The thing is, it should throw a parse error. I asked on irc and we found this line in the standard:
an is only allowed for a , not for an or or (which has its own AS (x,y,z) syntax)
there’s a bunch of different corner cases that crop up in this area too… so yeah, again, cool tool, I’m sure we will make use of it.
It’s not an SQL query, so the tool has no opinion on it 🙂 This tool isn’t meant to find syntax errors in queries that wouldn’t parse correctly, but rather to point out constructs in legal SQL that might hold traps for the unwary. Mark Callaghan once blogged that the problem with SQL is that it lets people do bad things; this tool’s goal is to help uncover those things as much as possible with static inspection of the SQL (i.e. no knowledge of the table structures and so on).
Nice tool!
Some input:
In the following query, there isn’t a problem with the GROUP BY, yet the advisor claims a “Non-deterministic GROUP BY”.
The next query is invalid but the advisor fails to detect the error (mixture of aggregate and non-aggregate).
I’m not sure, based on your description, that the advisor is supposed to catch that (is it only supposed to work with queries that are valid?)
In the following there is nothing wrong syntactically, but the author of the query might be confused to think that DISTINCT is a function, and that it only works for the ‘continent’ column, whereas it works for the entire set of columns.
Sorry, link for first example went missing. Here it is:
correct query with unfair warning
Another input:
Comparison of column = NULL is a common error. It should be reported.
Realy appreciate the tools on tools.percona.com.
Please make the signup mail plain text instead of html only
Robert, glad you like it — it’s meant to work best with MySQL, but a lot of the advice is DB-agnostic, so although I hadn’t thought about it, it should work in a lot of cases with any database. I’d report the bug you found, but I think I can predict the outcome: http://bugs.mysql.com/bug.php?id=44833
Shlomi, thanks for the bug reports!
Henk, I’ll check into that, thanks for the suggestion!
By the way, I didn’t really say this in explicitly, but the reason I personally wanted to build this tool is for the hard queries, not the easy ones. I’m thinking about times that someone sends me a massive query with a 20-table join and 50 WHERE clauses, and I want to know whether any of the WHERE clauses converts a LEFT to INNER join. That kind of thing. Reading SQL can be hard work, and I’m lazy 🙂
Excellent work guys! It’s amazing you make tools like this public (not to mention the Toolkit) – I bet Oracle would charge $$.
Is it possible for you guys to make the signups based on any OpenID-like service? It would streamline usage heavily if I connect from several locations but didn’t grab my login details on the way out.
We’ve had a few requests for OpenID logins, and I will put it on our roadmap to investigate. Thanks!
On the subject of OpenID logins: why login at all?
What the hell is wrong with this captcha system? And why does it keep reselecting “notify me”?
please help this query
SELECT rep_name FROM representative WHERE rep_id IN (SELECT rep_id FROM rep_area WHERE PIN_ID IN (SELECT pin_id FROM pincode_list WHERE pincode IN (SELECT PINCODE FROM
areawise_tempWHERE pro_name = ‘PRODUCT 125’ AND (bill_date BETWEEN ‘2015/02/01’ AND ‘2015/02/04’) AND our_cust_id “” AND our_product_id “” GROUP BY pincode) GROUP BY PIN_ID) GROUP BY rep_id)