QOT

This page contains descriptions of static checks performed by QOT.

WARNING 77000001 at ‘[text]’: string to numeric conversion, all aphabetic values will evaluate to 0

QOT detected a conversion from a string type into an intereger type. This is considered not safe as all non-numeric values will be converted to zero. For example suppose we have the following table:

CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, t VARCHAR(45));

with the following contents:

INSERT INTO t1 (id, t)
VALUES (0, '0'), (1, '1'), (2, '2'), (3, 'a'), (4, 'b'), (5, '1xxxx');

now the following query results could be not what one would mean:

mysql> SELECT * FROM t1 WHERE t = 0;

+----+------+
| id | t    |
+----+------+
|  0 | 0    |
|  3 | a    |
|  4 | b    |
+----+------+

3 rows in set (0.00 sec)

mysql> select * from t1 where t = 1;

+----+-------+
| id | t     |
+----+-------+
|  1 | 1     |
|  5 | 1xxxx |
+----+-------+

2 rows in set (0.00 sec)

WARNING 77000002: multitable query uses * in the select item list

When you use star (*) in the select item list this means that you are going to fetch all fields from all query tables. While for a single-table query this is quite a usual practice (which is still discouraged in many case) but for the case of a multitable query this is most likely not what you wanted.

WARNING 77000003 at ‘[text]’: multicolumn IN-expression, no indexes will be used

This is a warning about a specific MySQL server limitation. The server cannot use indexes with multicolumn IN-expressions.

For example:

mysql> show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `n` int(11) NOT NULL DEFAULT '0',
  `n2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`n`)
) ENGINE=MyISAM AUTO_INCREMENT=29 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> explain select * from t1 where (id, n) in ((1,1), (2,1), (3, 1)) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 28
        Extra: Using where
1 row in set (0.00 sec)

As you can see the server cannot use existing index for lookup. To make sure it’s not just because of * in the select item list here’s another query:

mysql> explain select id, n from t1 where (id, n) in ((1,1), (2,1), (3, 1)) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 28
        Extra: Using where; Using index
1 row in set (0.02 sec)

As you can see server uses the index for scanning, but ignores it as a lookup index.

Information about this limitation originates from this blog message

WARNING 77000004: ORDER BY contains expressions, cannot be optimized
WARNING 77000005: ORDER BY fields have different sort directions, cannot be optimized
WARNING 77000006: ORDER BY contains fields from different tables, cannot be optimized

These 3 checks (added in version 0.0.4) reflect 3 cases in which ORDER BY clause is guaranteed to cause filesort regardless of any indexes.

77000004 is generated when the ORDER BY contains anything but table field references. For example the query

SELECT * FROM t1 ORDER BY col1*2

will lead to filesort, even if there’s an index on col1.

77000005 is generated when sorting directions differ. E.g.:

SELECT * FROM t1 ORDER BY col1 asc, col2 desc

77000006 is generated when ORDER BY fields are from different tables. E.g.:

SELECT t1.col1, t2.col2 FROM t1, t2 ORDER BY t1.col1, t2.col1

actually filesort will happen even in the case of a single physical table:

SELECT a1.col1, a2.col2 FROM t1 a1, t1 a2 ORDER BY a1.col1, a2.col1

here MySQL will do filesort even if there’s an index (col1, col2) on the physical table t1