SQL ERROR: Not in aggregate function or group by clause

[Migrated From Jforum.net]

I'll apologize for a long introduction but I hope you will like it :-)

During some test of JForum (2.1.4), I was doing the following with my HSQLDB

1. Stop Tomcat (and JForum)
2. Start and Connect to the HSQLDB (using SqlTools from hsqldb's jar)
3. Delete all the messages from jforum_topics, jforum_posts, jforum_posts_text manually,
4. Run SQL script which populates the jforum_topics, jforum_posts, jforum_posts_text (actually a bunch of IMPORT statements)
5. Stop SqlTools (and DB)
6. Start Tomcat (and JForum)

I did this at least 10 times and I never had any problems - all of my ~1000 imported messages were accessible via forum interface.

BUT,

After repeating above steps several times I decided to change encoding parameter for webpage (from Admin module)
I did that (or something similar - I do not remember:-) and then I tried to restart the Tomcat. It has started Ok but when I tried to go to the JForum I got the following error:

Not in aggregate function or group by clause:
COLUMN F.FORUM_ID in statement [SELECT f.*, COUNT(p.post_id) AS total_posts FROM jforum_forums f LEFT JOIN jforum_topics t ON t.forum_id = f.forum_id LEFT JOIN jforum_posts p ON p.topic_id = t.topic_id GROUP BY f.categories_id, f.forum_order]

I was curios and I tried to execute this particular query using SqlTools and I got the same error. Actually the error is OK because the SQL is really invalid !!! - we can not say SELECT f.* - and then in GROUP BY not to specify ALL the columns from table f - simply as that.

I did not managed to fix the problem so I just reinstalled everything. Everything works fine now (except that I can not reproduce the problem :evil: )

Anyhow, after some investigation I think I understand what happens (but not why):
Above SQL statement is a part of ForumModel.selectAll system variable. BUT, there are two files containing definition of this variable:
generic_queries.sql and hsqldb.sql. The ForumModel.selectAll from generic_queries.sql is exactly the same like the one I described - i.e invalid. The second one, in hsqldb.sql, is OK - I tested it with SqlTools manually (btw in GROUP BY section it contains all the fields from jforum_forums table).

So, I suppose that in my case JForum first tries to find ForumModel.selectAll in hsqldb.sql - and it fails because some unknown reasons. Then, it tries with generic_queries.sql, finds it and then tries to execute it - and then it fails with the above message ...
This would explain why JForum normaly works (it always finds SQL from hsqldb.sql not the one from generic_queries.sql)

Rafaile, could you give your comment on this case if you have a time:
1. Are above assumptions are right ?
2. If 1 is "Yes", then how I ended up with such a problem ?
3. Why "invalid" SQL expr. is included in generic_queries.sql ?
4. What I need to do to put the thinks back in place ?

Keep doing GREAT work !!!

Greetings from BE


[originally posted on jforum.net by Anonymous]

[Migrated From Jforum.net]

Hi,

yes, your assumptions are completely right. I have noticied this bug a while ago, and it's registered in the bug tracker:

http://www.jforum.net/jira/browse/JF-275

About your question 3, the generic version is based on mysql.

Thanks for the detailed description of the problem

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Thanks for the answer.

You gave me some ideas so I was investigating little bit more and I found the following:

The actual cause of the problem is damaged jforum-custom.conf file. A brief comparison between the fresh copy of this file and the old one reviled that some lines are simply missing!

I have attached the Print Screen image which illustrates the differences. It might help you to figure out which process has "forgotten" to update the jorum-custom.conf and why.

Greetings from BE
[originally posted on jforum.net by teolein]

[Migrated From Jforum.net]

The problem is in my internal control of modified files and reloading order. I'll fix it until 2.1.5

Rafael
[originally posted on jforum.net by Rafael Steil]

[Migrated From Jforum.net]

Hi,

I didn't understand last 2 replies to this message, which were about jforum-custom.conf; and I was unable to open http://www.jforum.net/jira/browse/JF-275 address, I get "not found" error.

I'm having the same problem: ForumModel.selectAll in generic_queries.sql doesn't work. I tried to rename sqlserver\sqlserver.sql as generic_queries.sql and to move it to generic folder. But this resulted in more problems, so I gave up.

I then copied only ForumModel.selectAll from sqlserver.sql to generic_queries.sql, but it didn't work either. I don't receive any exceptions, but can not see any forums in the forum index page.

NOTE: When I run ForumModel.selectAll of sqlserver.sql using SQL Server Query analyzer (it sends an sql directly to the sql server and displays results), I can see that the query returns rows properly.

Help pls
[originally posted on jforum.net by cagin]

[Migrated From Jforum.net]

Some more news:

I started from scratch and deleted all forums and categories, and then created one cat. I see it in db. But Admin Ctl Panel\Categories doesn't list it.

So I debugged starting from JForum.service down to GenericCategoryDAO.selectAll(). The List object it returns include the Category object in the db. So the problem may be with FreeMaker.

I tried to download freemaker from http://freemarker.sourceforge.net/freemarkerdownload.html. CVS version of jForum includes freemaker-2.3-rc3, so I tried to download 2.3.3. But downloaded tar file cannot be decompressed, WinRAR complains that the file is corrupted. Therefore I couldn't debug freemaker, nor could I try with an upgraded freemaker version.

Still waiting for help :roll:
[originally posted on jforum.net by cagin]