Russian Version

Chapter 2. Why you get wrong data.

We learned how to find the problem query.

We already examined 2 examples of syntax error and one example of logic error. But do include these examples every possibilyty of analysing of any problem query? Of course, not! Although, strictly say, every error in the query can be reduced to one of these subtypes.

In this chapter we discuss what else can we do with problem query.

Syntax errors are trivial, therefore we would not study them more.

One of most frequent problems is SELECT query which returns wrong data. This can happen because a number of reasons.

Lets look at easiest - in terms of solving - one.

mysql> select count(*) as b from t3 order by b,a;
+---+
| b |
+---+
| 2 |
| 2 |
+---+

mysql> select count(*) as b from t3;
+---+
| b |
+---+
| 2 |
+---+
1 row in set (0.01 sec)

mysql> show create table t3\G
************ 1. row ************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `a` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Although query looks strange it is clear what MySQL behaves in a wrong way: count(*) can not return more than single row if GROUP BY does not exist. Query is trivial and there is no way to make it easier. In this case our way goes to MySQL Bugs Database at http://bugs.mysql.com: using search feature we can can find corresponding bug report at http://bugs.mysql.com/bug.php?id=47280 At the same page we can find what bug was firstly found in version 5.1.38 and is fixed now. Therefore upgrade to version 5.1.41 or later will fix the problem.

Of course, if you found original bug which does not exists in MySQL Bugs Database yet you should post it to http://bugs.mysql.com

But what to do if you can not upgrade MySQL server now? In this case you should use workaround: remove order by. This example does not only demontsrates the fact what MySQL Server has bugs in own code, but yet another method of working with problem query.

Method #3: after you found a query which causes the problem run it using command line client and analyze result.

But there are bugs with workarounds which does not require to modify SQL. Usually if workaround is not clear it is described in the bug report.

Lets look at the next example:

mysql> create table `a` (
    ->   `id` bigint(20) not null auto_increment,
    ->   primary key (`id`)
    -> ) engine=myisam;
Query OK, 0 rows affected (0.04 sec)

mysql> create table `b` (
    ->   `id` bigint(20) not null auto_increment,
    ->   `a_id` bigint(20) default null,
    ->   primary key (`id`)
    -> ) engine=myisam;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into `a` values (1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into `b` values (1,1),(2,1),(3,1),(4,2),(5,2),(6,2),(7,3),(8,3);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> select a.id as a_id, count(distinct b.id) as cnt from a a left join b b on a.id = b.a_id
    -> where a.id = 1 group by a.id with rollup limit 100;
+------+-----+
| a_id | cnt |
+------+-----+
|    1 |   8 |
| NULL |   8 |
+------+-----+
2 rows in set (0.01 sec)

Why do we have 8 rows with a_id=1? We only inserted 3 rows with a_id = 1:

mysql> insert into `b` values (1,1),(2,1),(3,1),
(4,2),(5,2),(6,2),(7,3),(8,3);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

We can clearly see this if we skip GROUP BY.

mysql> select a.id as a_id, b.id from a a left join b b on a.id = b.a_id where a.id = 1;
+------+------+
| a_id | id   |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
+------+------+
3 rows in set (0.00 sec)

This case described at http://bugs.mysql.com/bug.php?id=47650. It was also found in version 5.1.38, but was not fixed in time when this text was originally written.

But here we have workaround:

mysql> alter table b add index(a_id);
Query OK, 8 rows affected (0.05 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> select a.id as a_id, count(distinct b.id) as cnt from a a left join b b on a.id = b.a_id
    -> where a.id = 1 group by a.id with rollup limit 100;
+------+-----+
| a_id | cnt |
+------+-----+
|    1 |   3 |
| NULL |   3 |
+------+-----+
2 rows in set (0.02 sec)

As you can see result is correct now.

Method #4: try to modify SQL in such a way what you get correct result. Use search engines to find a workaround.

Although cases with bugs in MySQL code are easy for the user to solve, but you will meet them much more seldom than bugs in SQL code of the user.

But what to do if correct SELECT query doesn't work as expected?

Lets examine the example

mysql> create table t1(f1 int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2(f2 int);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+
| f1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

But

mysql> select * from t1, t2; Empty set (0.00 sec)

Why SELECT from 2 tables returns empty set while there is a row in table t1?

EXPLAIN EXTENDED will help us:

mysql> \W
Show warnings enabled.

mysql> explain extended select * from t1, t2;
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | t1    | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 |                     |
|  1 | SIMPLE      | t2    | system | NULL          | NULL | NULL    | NULL |    0 |     0.00 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
2 rows in set, 1 warning (0.20 sec)

Note (Code 1003): select '1' AS `f1`,'0' AS `f2` from `test`.`t1` join `test`.`t2`

As you can see, query modified to JOIN query which is synonim of INNER JOIN and which can not return rows from table t1 if there is no crresponding row in table t2. Query returns no row, because table t2 has no record.

For more complicated (more lenghty) queries algorithm is similar: run EXPLAIN EXTENDED, if it does not show cause of the problem, devide or simplify the query, then repeat until cause is found.

EXPLAIN also helps if your query runs very slow while returns correct result set. We would not describe this main feature of EXPLAIN here, because it is described in details in official MySQL User Manual. Read corresponding chapters.

Method #5: use EXPLAIN EXTENDED for finding how optimized (and executed) SQL query.

Back Content Forward



Author 2010 Sveta Smirnova
COPYRIGHT © 2010 S.Smirnova and S. Lasunov
sveta_at_js-client_dot_com