Bug #2195 CQueryWindow.cpp:470 'Impossible WHERE noticed after reading const tables'

Description:
In a Query window when the currently executed query is SELECT which results in empty set, for example
 
 SELECT * 
 FROM foo
 WHERE ID = 6

  (and there is no row with ID = 6)

 EXPLAIN SELECT * FROM foo WHERE ID = 6 
 
 return the following result set:

  1 row with one field:
    Field name: comment
    Field value: Impossible WHERE noticed after reading const tables

 and that code from CQueryWindow.cpp:470-481   
   . . .

  explain_query->next(true);
  default_table = explain_query->row(mysql()->mysql()->version().major >= 4 && 
              mysql()->mysql()->version().minor >= 1 ? 2 : 0);
  if (!default_table.isEmpty())
  {
    finished_evt->read_only = default_database.isEmpty() || 
           !last_query.simplifyWhiteSpace().lower().startsWith("select * from");
              query_type = SQL_ALL_ROWS;
  }
  else
    default_table = QString::null;
   . . .

  loads defaul_table with the value of row 1, field 0 which is not name of
  a valid table but the string 'Impossible WHERE noticed after reading const tables'. This gives us the following error:
   
  [root@localhost:3306] ERROR 1146: Table 'test.impossible where noticed after reading const tables' doesn't exist
  
   and also, when you try to use 'Query type' button it generates this:

   INSERT INTO Impossible WHERE noticed after reading const tables
   ([Field1,...N])
   VALUES([Value1,...N])

  

How to repeat:
Just try to execute SELECT that will return empty result set. The EXPLAIN on such
query will return:

   -------------------------------------------------------------
   |   Comment                                                  |
   -------------------------------------------------------------
   |  Impossible WHERE noticed after reading const tables       |
   -------------------------------------------------------------

 instead of something like this:
   -------------------------------------------- . . .
   |  table    |    .     .      .
   -------------------------------------------- . . .
   | tablename |    .      .      .
   -------------------------------------------- . . .

 and default_table from CQueryWindow.cpp will be loaded with wrong value.

Suggested fix:
  
  See the line with XXX FIX comment
  . . .

  explain_query->next(true);
  default_table = explain_query->row(mysql()->mysql()->version().major >= 4 && 
              mysql()->mysql()->version().minor >= 1 ? 2 : 0);
  if (!default_table.isEmpty() && explain_cols > 1) // XXX FIX '&& explain_cols>1'
  {
    finished_evt->read_only = default_database.isEmpty() || 
           !last_query.simplifyWhiteSpace().lower().startsWith("select * from");
              query_type = SQL_ALL_ROWS;
  }
  else
    default_table = QString::null;

  . . .

I cannot repeat this with our MySQLCC 0.9.4 binary.  The result pane shows column headings; the message pane shows the EXPLAIN output.  Are you sure you are using 0.9.4?

Yes, I'm sure, I'm using 0.9.4. Older versions have the bug too of course.

 The result set has to be of type 'const'. For example with:

 SELECT * 
 FROM foo
 WHERE ID = 8;

 and ID _is primary key, uniq, etc. _ and there is no row with ID = 8

 EXPLAIN on such SELECT will return 
 
 'Impossible WHERE noticed after reading const tables'
 
 
 One, of course can rewrite the SELECT like this:

 SELECT *
 FROM foo
 WHERE ABS(`ID`-8) > 1

 and EXPLAIN will return normal output, and default_table will be loaded
 with the proper value.

    Svetoslav Agafonkin

:-)
 
 sorry, I meant:

 SELECT *
 FROM foo
 WHERE ABS(`ID`-8) < 1

What version of MySQL are you using?

4.0.17, 4.0.16 and all 4.0.x I presume
  same with 3.23.x

I still cannot repeat it with MySQLCC 0.9.4.  I'll see if someone else can spot something that may be causing it for you.

Hi there.

Got the same error, on MySQLcc 0.9.2 and 0.9.4

I want to get all records fields except those with a NULL value, so I tought using

       SELECT * FROM MyTable WHERE FIELD IS NOT NULL

or somthing like that, but the result was

     [MyServer] ERROR 1146: Table 'MyTable.Impossible WHERE noticed after
     reading const tables' doesn't exist

then I tried other ways to do this, doing it in code was like accepting to be defeated, so I insisted on doing it in the query, help files in MySQL home page recommended funtions like ISNULL() and NULL-safe equal, so I tried functions:

     NOT ISNULL(FIELD)
     NOT FIELD IS NULL
     NOT FIELD <=> NULL
     LENGTH(FIELD) > 0
           ( Wich works when needs to return several records,
           but one or none fails with same error )

and many combinations with some other functions but always got the error above, or no NULL-Field filtration at all.

My Server is a SparcII running RedHat 7 and MySQL 3.23.54

Same issue here.

Simple query :

SELECT * FROM sales WHERE REFFACTURE = 310273

Explain says : "Impossible WHERE noticed after reading const tables"

And mySQLCC has an error :
[Localhost] ERREUR 1146: Table 'myBase.impossible where noticed after reading const tables' doesn't exist

I'd like to add that this bug happen only when selecting *

SELECT * FROM myTable WHERE myKey = 123456
if rows with myKey = 123456 doesn't exists, the bug will happen.

SELECT myValue FROM myTable WHERE myKey = 123456
Will not bug, but return empty set, which is correct.

I've noticed that to repeat this bug the table must be InnoDB with only one primary key. The query mut execute SELECT *  , return empty result set, and in the where clasuse only de primary key should be used.

SELECT * 
 FROM foo
 WHERE ID = 6
 
ID is the only primary key of foo and the table is InnoDB.

Hello there,

I got the same error message, and here are some details :

I am using version 4.0.17-nt (source distribution) on windows XP. To produce this bug, do this (for example):

1. create a table named test
2. create a field called UniqueID, varchar(100)
3. create a second field called TextField, varchar(100).

Don't insert anything into the table, but do 

EXPLAIN SELECT * FROM test WHERE TextField='1'

and this will result in a table with one row and one column with the message:
Impossible WHERE noticed after reading const tables

Hello 

I have the same problem, if I use *.
But when I use for example 'SELECT id FROM Component WHERE id=6', then it's OK!

if you want use *, try this:
SELECT Component.* FROM Component WHERE id=6'

Here's my example (on MySQL 4.0.22-nt-log):

EXPLAIN SELECT Relationship FROM FolderPerms WHERE SessionID='0yqus5njv01nhr55wthsft45' AND ID='496532';
| Impossible WHERE noticed after reading const tables |

mysql> SELECT Relationship FROM FolderPerms WHERE SessionID='0yqus5njv01nhr55wth
sft45' AND ID='496532';
Empty set (0.00 sec)

mysql> desc folderperms;
+--------------+--------------+------+-----+---------------------+-------+
| Field        | Type         | Null | Key | Default             | Extra |
+--------------+--------------+------+-----+---------------------+-------+
| SessionID    | varchar(32)  |      | PRI |                     |       |
| PermTime     | datetime     | YES  | MUL | 0000-00-00 00:00:00 |       |
| Relationship | int(11)      |      |     | 0                   |       |
| ID           | int(11)      |      | PRI | 0                   |       |
| InstID       | int(11)      |      |     | 0                   |       |
| Name         | varchar(64)  |      |     |                     |       |
| FolderPath   | varchar(255) |      |     |                     |       |
+--------------+--------------+------+-----+---------------------+-------+
7 rows in set (0.00 sec)

I received the same error while executing one of my querys, but i found another solution:
I tried to put in a list of possible values the values of the variable in the where, using an IN command:

select * from table a where a.col IN (2,4,6,7,8)

It runs. Maybe isn't the best solution but it functions.