Bug #1238 Select count(*) causes Syntax error
Submitted: 10 Sep 2003 2:23 Modified: 10 Sep 2003 11:33
Reporter: Andrej Konkow Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:all OS:Any (all)
Assigned to: Sinisa Milivojevic CPU Architecture:Any

[10 Sep 2003 2:23] Andrej Konkow
Description:
Sending a SQL statement as:
Select count(*) from (Select * from atable)
over JDBC causes a SQLException. Sending the same statement over MSAccess/ODBC is successful. I have used the latest stable JDBC / ODBC Connectors.

How to repeat:
Send
Select count(*) from (Select * from atable)
over your JDBC driver.
[10 Sep 2003 7:03] Mark Matthews
Unfortunately, you have not provided enough information for us to duplicate this issue. Can you please post a fully-repeatable testcase, including table schema, the Java code you are using that demonstrates this issue, and the _exact_ versions of JDBC driver and MySQL server that you are using? Otherwise we will not be able to track down what might be the problem.

Can you also test with the command-line client 'mysql'? When you use ODBC from Access, many times the 'Jet' database engine becomes involved, and re-writes the query, so you are not sure exactly what it is being sent to MySQL itself.
[10 Sep 2003 11:15] Andrej Konkow
I tried the commandline-tool mysql. Below you have the information you need. There's no need for any java-class. Happy searching, Andrej.

MySQL version is 3.23.47-max-debug

CREATE TABLE Offertype
(
  id           INTEGER UNSIGNED NOT NULL PRIMARY KEY,
  Type         VARCHAR(50)      NOT NULL
);

mysql> select count(*) from offertype;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.42 sec)

mysql> select count(*) from (select * from offertype);
ERROR 1064: You have an error in your SQL syntax near 'select * from offertype)' at line 1
[10 Sep 2003 11:24] Sinisa Milivojevic
This works perfectly since 4.1.0
[10 Sep 2003 11:33] Mark Matthews
MySQL 3.23 does not support subselects. MySQL-4.1 (the version currently under development) does.