MySQL bug #1064 - You have an error ... near 'SELECT ...) AS `DerivedTable`

Posted October 10th, 2008 by WilliamCarrier

Log from IRC server Freenode (IPv6) channel #SQL.

<me> is there something wrong with this query ?
<me> SELECT `dt`.`titre` FROM
<me> (SELECT * FROM `page` WHERE `ID`=5) AS `dt`
<me> the dt query works by itself
<padan> that should work
<Xgc> me: Not enough detail.
<me> Xgc: what detail should I add ?
<Xgc> me: I suspect you dsimply don't have such a column name.
<Xgc> me: * in the subselect doesn't tell us which columns are returned by that SELECT.
<Xgc> me: The easiest approach would be to just show us the *exact* error message.
<me> here's the page table structure: http://pastebin.com/ ...
<Xgc> me: While I'm looking, post the *exact* error message.
<Xgc> me: Also show the result of: SELECT VERSION();
<Xgc> me: You're probably using Version 3.x or 4.0
<||cw> me: what's the error
<Xgc> He doesn't want to say.
<me> sorry was afk
<me> ill say
<Xgc> me: MySQL support for subqueries begins (fully) in Version 4.1.
<me> #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM `page` WHERE `ID` = 5 ) AS `dt`
<me> LIMIT 0, 30' at
<Xgc> me: SELECT VERSION();
<luckyq> $database->setQuery("SELECT c_child.cid, DATE_FORMAT(c_child.date, \'%d\') AS day, LEFT(DATE_FORMAT(c_child.date, \'%M\'), 3) AS month, DATE_FORMAT(c_child.date, \'%Y\') AS year, c_child.name
<me> the query was made on MySQL - 4.0.25
<Xgc> me: MySQL support for subqueries begins (fully) in Version 4.1.
<Xgc> me: You need to update or change the form to not use subqueries.
<me> Xgc: so I'll have to create views ?
<Xgc> me: No views either.
<Xgc> me: 4.0 is very very old.
<me> Xgc: so the only way will be to have to compare the results by the application ?
<Xgc> me: No. There's no need for that subselect in this query.
<me> Xgc: it was just an example, I really need subqueries
<Xgc> me: SELECT titre FROM page WHERE id = 5;
<Xgc> me: Sorry. MYSQL 4.0 does not support them.
<Xgc> me: Use MySQL 4.1 or better. But even 4.1 is now very old.
-->| robo (n=romanb@e178177162.adsl.alicedsl.de) has joined #sql
<me> Xgc: I just emailed my boss, I hope it'll be done ! :\
‹ create/drop transaction DELETE with conditions from another table ›