| Bug #2195 | CQueryWindow.cpp:470 'Impossible WHERE noticed after reading const tables' | ||
|---|---|---|---|
| Submitted: | 23 Dec 2003 0:35 | Modified: | 31 Dec 2003 7:23 |
| Reporter: | Svetoslav Agafonkin | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQLCC | Severity: | S3 (Non-critical) |
| Version: | 0.9.4 | OS: | Any (All) |
| Assigned to: | Dean Ellis | CPU Architecture: | Any |
[23 Dec 2003 7:44]
Dean Ellis
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?
[28 Dec 2003 3:55]
Svetoslav Agafonkin
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
[28 Dec 2003 3:58]
Svetoslav Agafonkin
:-) sorry, I meant: SELECT * FROM foo WHERE ABS(`ID`-8) < 1
[30 Dec 2003 8:42]
Dean Ellis
What version of MySQL are you using?
[30 Dec 2003 22:26]
Svetoslav Agafonkin
4.0.17, 4.0.16 and all 4.0.x I presume same with 3.23.x
[31 Dec 2003 7:23]
Dean Ellis
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.
[20 May 2004 0:51]
Luis Daniel Rangel Tovar
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
[8 Jun 2004 11:43]
Henri-Maxime Ducoulombier
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
[8 Jun 2004 11:51]
Henri-Maxime Ducoulombier
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.
[22 Sep 2004 16:47]
Gustavo Branco
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.
[4 Oct 2004 9:39]
Tudor Olariu
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
[24 Nov 2004 13:17]
Christian Zemp
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'
[14 Dec 2004 22:29]
Jonathan Lampe
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)
[13 Jul 2007 13:32]
toni peperoni
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.
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; . . .