Bug #2641 subselect do not work in FROM clause in prepared statemnts
Submitted: 4 Feb 2004 5:52 Modified: 20 Feb 2004 5:52
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1 OS:Any (Any)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[4 Feb 2004 5:52] [ name withheld ]
Description:
The select: "select count(1) from (select id from test) x"

The result:
SQLException: Base table or view not found message from server: "Table 'its4.*' doesn't exist"
 SQLState: 42S02
 VendorError: 1146

("its4" is the name of the database.)
As far as I remember, it worked with MySQL-4.1.0 and
mysql-connector-java-3.1.0-alpha-bin.jar.

It happens both with Statement and PreparedStatement.
This does not occurs with other client tools, JDBC specific.

How to repeat:
See description.
[4 Feb 2004 5:56] [ name withheld ]
Oops, sorry, the environment:
- MySQL server 4.1.1-alpha on Linux. (Mandrake 8.1)
- mysql-connector-java-3.1-nightly-20040203-bin.jar (on Win98) 
- JSDK 1.4.2 b-28 (on Win98)
- Apache Tomcat 4.1.29 (on Win98)
[4 Feb 2004 6:15] [ name withheld ]
Correcting:

It only happens with nested statements when using 
String retval= rs.getString(1);

It works fine if I use
int retval= rs.getInt(1);

So, I think it's again a type recognization bug.
[4 Feb 2004 6:27] [ name withheld ]
I beg your pardon, but I'm very tired. I'm getting confused with my own code...

The error occurs ONLY with PreparedStatement,
immediately when saying:
pstmt=conn.prepareStatement("select count(1) from (select id from test) as x");

It DOES NOT occurs with Statements.
Sorry again.

(This preparedstatement stuff seems to be a bit instable to me...)
[4 Feb 2004 6:35] [ name withheld ]
I've change the synopsys to reflect the problem more accurate.
[4 Feb 2004 7:14] Mark Matthews
This is actually a known bug that Sanja Byelkin is working on (but I can't seem to locate it in the bugs database), and is a server issue, not a JDBC issue. 

I've assigned this bug to Sanja so that he can close it once the server is fixed.
[5 Feb 2004 2:41] Oleksandr Byelkin
Now all subqueries fail with stored procedures from known causes, but when I 
fix it I will not be able to check your certain query, bacuse this bug report 
is incomplite, it lack of tables content for that query which you provided.
[5 Feb 2004 3:16] [ name withheld ]
The reason why I didn't provide such information is because the problem is independent of the table data. You can repeat the problem with ANY table with ANY content in it.
[12 Feb 2004 8:54] Oleksandr Byelkin
ChangeSet 
  1.1710 04/02/12 18:50:00 bell@sanja.is.com.ua +5 -0 
  PS fixed to be compatible with derived tables (BUG#2641)
[20 Feb 2004 5:52] Oleksandr Byelkin
Thank you for bugreport! Patch for this bug is pushed into your source 
repository and will be present in next release.