ORA-00937: not a single-group group function

Sorry, this is a nerds-only post, the rest of you will not find this amusing or interesting. BTW, this problem is NOT well documented on the web.

Last week, I was working on a Delphi program. I needed to get the next version number of something from the Oracle database. So I wrote a SQL statement that went something like this:

select max(version) from table where ...

This worked great, and gave me the exact result I was looking for, until I added it into my TADOQuery and tried to use it. It gave me a "ORA-00937: not a single-group group function" error.

The only problem is that the SQL I sent in is a single-group group function.

After getting the database guy involved, and doing a major rectal exam on my program while it was running, we found that the SQL I had crafted was being transmogrified before it reached the database for execution. It looked vaguely like this (emphasis added):

select max(version), RowID from table where ...

RowID?!? Where the heck did that come from? As it turns out, there is a property in Delphi's TADOQuery object called locktype. By default, this property is set to ltOptimistic. This mode assumes that I want to read data out of the database, edit it, and send the updates back in. In order to do that, it needs to know exactly which row to update, so of course it had to add RowID to the query.

The only bad thing about that is that a component assumed it would know better than me what my program was doing, and modified my code in secret to help me out. This help led to a debugging nightmare that took me all day to diagnose and crush. The actual fix is to set the locktype property to ltReadOnly. This mode doesn't need the RowID, so it leaves your SQL alone.

Great design, guys.