Impossible WHERE in explain plan is incorrect

scottself · #1 (**permalink**) 07-05-11, 12:12

I am running into anissue with a table I've loaded for months with no problems. There are 4,301,290,034 records in the LoanMonth table and each new month I insert approximately 40 million more. This last month I loaded the data as usual with no errors and the total record counts are correct. When I attempt to query the table for the most recently added data I get an empty set. When I check the explain plan I get the message "Impossible WHERE noticed after reading const tables " which doesn't make sense because I can query on any of the previous months data. I have recreated the table and indexes as well as reloaded the most recent month's data, but I am still getting the same empty set problem when I query for the last set of data I loaded. I have also verified that the data I am loading doesn't have any hidden, non-printable, or special characters.

Please take a look at the queries and their results below and let me know if anyone has any ideas of what could be causing this problem.
Thanks,
Scott

Version 5.0.75-0ubuntu10.3-log

Code:

mysql> desc LoanMonth;
+-----------+----------------------+------+-----+---------+-------+
| Field     | Type                 | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| LoanId    | int(10) unsigned     | NO   | PRI | NULL    |       |
| AsOfMonth | smallint(5) unsigned | NO   | PRI | NULL    |       |
+-----------+----------------------+------+-----+---------+-------+

mysql> show indexes from LoanMonth;
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| LoanMonth |          0 | PRIMARY   |            1 | LoanId      | A         |   138751291 |     NULL | NULL   |      | BTREE      |         |
| LoanMonth |          0 | PRIMARY   |            2 | AsOfMonth   | A         |  4301290034 |     NULL | NULL   |      | BTREE      |         |
| LoanMonth |          1 | LOAM_1_UK |            1 | AsOfMonth   | A         |         268 |     NULL | NULL   |      | BTREE      |         |
| LoanMonth |          1 | LOAM_1_UK |            2 | LoanId      | A         |  4301290034 |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Code:

mysql> select AsOfMonth, count(*) 
         from LoanMonth 
        where AsOfMonth = 376 
     group by AsOfMonth;
+-----------+----------+
| AsOfMonth | count(*) |
+-----------+----------+
|       376 | 39193907 |
+-----------+----------+

mysql> select AsOfMonth, count(*) 
         from LoanMonth 
        where AsOfMonth >= 376 
     group by AsOfMonth;
+-----------+----------+
| AsOfMonth | count(*) |
+-----------+----------+
|       376 | 39193907 |
|       377 | 39009879 |
+-----------+----------+

mysql> select AsOfMonth, count(*) 
        from LoanMonth 
       where AsOfMonth >= 377 
    group by AsOfMonth;
+-----------+----------+
| AsOfMonth | count(*) |
+-----------+----------+
|       377 | 39009879 |
+-----------+----------+

mysql> explain 
      select AsOfMonth, count(*) 
        from LoanMonth 
       where AsOfMonth >= 377 
    group by AsOfMonth;;
+----+-------------+-----------+-------+---------------+-----------+---------+------+----------+--------------------------+
| id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows     | Extra                    |
+----+-------------+-----------+-------+---------------+-----------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | LoanMonth | range | LOAM_1_UK     | LOAM_1_UK | 2       | NULL | 85595562 | Using where; Using index |
+----+-------------+-----------+-------+---------------+-----------+---------+------+----------+--------------------------+

mysql> select AsOfMonth, count(*) 
         from LoanMonth 
        where AsOfMonth = 377 
     group by AsOfMonth;
Empty set (0.00 sec)

mysql> explain 
       select AsOfMonth, count(*) 
         from LoanMonth 
        where AsOfMonth = 377 
     group by AsOfMonth;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

r937 · #2 (**permalink**) 07-05-11, 13:13

i think you've exceeded the capacity of your loanId

the signed range for INTEGER is -2147483648 to 2147483647, while the unsigned range is 0 to 4294967295

scottself · #3 (**permalink**) 07-05-11, 14:03

Thanks for the quick response. I checked the maximum LoanId value in my table and found that it is 2,147,483,598. This is close to the maximum signed integer value of 2,147,483,647 but not quite greater. I am going to change the datatype to be safe (which will take hours), but I'm not sure that is the problem since I haven't actually gone over the max value yet.
The primary key on the table is a combination of LoanId and AsOfMonth which is how I can have 4 billion rows with a max LoanId of 2 billion. I have 268 distinct values of AsOfMonth, from 109 to 377.
Is there any more information I can provide that would be helpful?

Thanks again for your input,
Scott

r937 · #4 (**permalink**) 07-05-11, 15:46

oh yeah, i see it now, i'm sorry, i completely overlooked your compound PK

and i also don't know what the "impossible WHERE clause" means

scottself · #5 (**permalink**) 07-05-11, 16:09

No problem. The "impossible WHERE" is generated by the explain plan when the where clause is always false and will never return rows.

For example:

Code:

SELECT * FROM dummy_table WHERE 1=0;

From the MySQL docs:

Impossible WHERE noticed after reading const tables

MySQL has read all const (and system) tables and noticed that the WHERE clause is always false.

The annoying part of this is that this query returns data:

Code:

  select AsOfMonth, count(*) 
    from LoanMonth 
   where AsOfMonth >= 377 
group by AsOfMonth;
+-----------+----------+
| AsOfMonth | count(*) |
+-----------+----------+
|       377 | 39009879 |
+-----------+----------+

And this one doesn't:

Code:

 
  select AsOfMonth, count(*) 
    from LoanMonth 
   where AsOfMonth = 377 
group by AsOfMonth;
Empty set (0.00 sec)

Internet Services

Web Development

Digital Marketing

Consumer Tech