Every derived table must have its own alias

Question

I keep getting this message every time I try to post a question on my new Q2A page. Has anyone ran into this problem before?

Question2Answer query failed:

SELECT qa_posts.postid, qa_posts.categoryid, qa_posts.type, LEFT(qa_posts.type,1) AS basetype, INSTR(qa_posts.type, '_HIDDEN')>0 AS hidden, qa_posts.acount, qa_posts.selchildid, qa_posts.upvotes, qa_posts.downvotes, BINARY qa_posts.title AS title, BINARY qa_posts.tags AS tags, UNIX_TIMESTAMP(qa_posts.created) AS created, qa_uservotes.vote AS uservote, qa_posts.userid, qa_posts.cookieid, INET_NTOA(qa_posts.createip) AS createip, qa_userpoints.points, qa_users.flags AS flags, BINARY qa_users.email AS email, BINARY qa_users.handle AS handle, qa_users.avatarblobid AS avatarblobid, qa_users.avatarwidth AS avatarwidth, qa_users.avatarheight AS avatarheight, score, matchparts FROM qa_posts LEFT JOIN qa_uservotes ON qa_posts.postid=qa_uservotes.postid AND qa_uservotes.userid=_utf8 '2' LEFT JOIN qa_users ON qa_posts.userid=qa_users.userid LEFT JOIN qa_userpoints ON qa_posts.userid=qa_userpoints.userid JOIN (SELECT questionid, SUM(score)+LOG(questionid)/1000000 AS score, GROUP_CONCAT(CONCAT_WS(':', matchposttype, matchpostid, ROUND(score,3))) AS matchparts FROM ((SELECT postid AS questionid, LOG(10000/titlecount) AS score, _utf8 'Q' AS matchposttype, postid AS matchpostid FROM qa_titlewords JOIN qa_words ON qa_titlewords.wordid=qa_words.wordid WHERE word IN (_utf8 'test',_utf8 'question') AND titlecount<10000)) x GROUP BY questionid ORDER BY score DESC LIMIT 0,500) y ON qa_posts.postid=y.questionid

Error 1248: Every derived table must have its own alias

Comments

Thanks for reporting this. I suspect it's an SQL parsing bug in the specific version of MySQL you are using, since this SQL query runs fine for me. Can you post the MySQL version? You can find it in the Stats section of the Admin panel.

---

Hi my version of mySQL is 4.1.10-standard-log.
Thank you very much for looking at this, you're doing a great job with Q2A

---

Hi, I found the problem... its the double brackets in the second FROM clause of the query. I'd really appreciate it if you could give me a hint as to how to fix it as php really isn't my cup of tea. I have found a code snippet in qa-db-selects.php that has the "from" statement with two brackets but I keep getting the error, this is doing my head in =S

Answer 1

Thanks for clarifying about the double brackets. This problem seems to be specific to early versions of MySQL like the one you're using.

The solution is to modify function qa_db_search_posts_selectspec(...) in qa-db-selects.php.  Assuming you're using the final release of Q2A 1.3, modify line 495 from:

"(SELECT questionid, (1-1/(1+count))*LOG(#/contentcount)*(CASE ^contentwords.type WHEN 'Q' THEN 1.0 WHEN 'A' THEN 0.5 ELSE 0.25 END) AS score, ^contentwords.type AS matchposttype, ^contentwords.postid AS matchpostid FROM ^contentwords JOIN ^words ON ^contentwords.wordid=^words.wordid WHERE word IN ($) AND contentcount<#)";

 

... to ...

 

"SELECT questionid, (1-1/(1+count))*LOG(#/contentcount)*(CASE ^contentwords.type WHEN 'Q' THEN 1.0 WHEN 'A' THEN 0.5 ELSE 0.25 END) AS score, ^contentwords.type AS matchposttype, ^contentwords.postid AS matchpostid FROM ^contentwords JOIN ^words ON ^contentwords.wordid=^words.wordid WHERE word IN ($) AND contentcount<#";

 

(All I did was remove the outermost brackets within the double quotes)

Comments

Got that one resolved and managed to post a question THANKS... but, the qa_db_selects.php file is full of derived tables =( I've managed to work out quite a few of these but at the moment its failing when I try to open a posted question;

 $selectspec['source'].=" JOIN (SELECT postid, SUM(score)+LOG(postid)/1000000 AS score FROM (SELECT ^titlewords.postid, LOG(#/titlecount) AS score FROM ^titlewords JOIN ^words ON ^titlewords.                                         wordid=^words.wordid JOIN ^titlewords AS source ON ^titlewords.wordid=source.wordid WHERE source.postid=# AND titlecount<#) UNION ALL (SELECT ^posttags.postid, 2*                                     LOG(#/tagcount) AS score FROM ^posttags JOIN ^words ON ^posttags.wordid=^words.wordid JOIN ^posttags AS source ON ^posttags.wordid=source.wordid WHERE source.postid=#                                   AND tagcount<#) UNION ALL (SELECT ^posts.postid, LOG(#/^categories.qcount) b FROM ^posts JOIN ^categories ON ^posts.categoryid=^categories.categoryid AND ^posts.type=                                   'Q' WHERE ^categories.categoryid=(SELECT categoryid FROM ^posts WHERE postid=#) c AND ^categories.qcount<#) x GROUP BY postid ORDER BY score DESC LIMIT #) y ON ^posts.                                  postid=y.postid";

I just can't get my head around this one =(

I get the same error:
#1248 - Every derived table must have its own alias

---

I think the general principle is that, for every (SELECT...) clause in brackets before a UNION ALL, you can remove the brackets. Make sure you remove brackets that balance each other - you can use a text editor 'Balance' feature to check that.