[Resolved] Invalid "Invalid column name 'RAND()' for database table..."

[sonemonu]

Hello, I am trying to execute the following SQL query:

Code:

$finder = BattleRecord::finder();
$battleR = $finder->findBySql('SELECT participant_a_id,participant_b_id FROM Battle ORDER BY RAND() LIMIT 1');

And I am getting the following exception:

Code:

TDbException
Description

Invalid column name 'RAND()' for database table '`battle`'.

The exception obviously occurs because 'RAND()' is not a column of the database table 'battle', however, The SQL query that I am trying to run is valid, and should not to produce this exception .

Greetings from Cartagena / Colombia.
Roger Padilla C.

[Thor]

I've posted a trac ticket for this, as it happens to me too, even since the alphas. I didn't report it because i thought the active record implementation wasn't yet finished. See http://trac.pradosoft.com/prado/ticket/834 for progress. Let's see who can fix this. Also, I wonder if they can change the current release, because in it's current state, prado IS NOT backwards compatible!

Any status on this? maybe a reason why this is happening with the 3.1.2 version and not with the 3.1.1 version?

[rojaro]

I just found this really interesting analysis on this topic written by Jan Kneschke (author of the excellent LightTPD web server), in which he suggests the following alternative to the ORDER BY RAND() method. I already tested it (i also just needed that functionality) and found that it, as promised, just works perfectly (at least for me).

Code:

$battleR = BattleRecord::finder()->findBySql('
    SELECT participant_a_id, participant_b_id FROM Battle AS r1 JOIN(
        SELECT(RAND() * (SELECT MAX(id) FROM Battle)) AS id
    )
    AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1;
');

He also posted a proper benchmark

Performance

Now let's see what happends to our performance. We have 3 different queries for solving our problems.

    * Q1. ORDER BY RAND()
    * Q2. RAND() * MAX(ID)
    * Q3. RAND() * MAX(ID) + ORDER BY ID

Q1 is expected to cost N * log2(N), Q2 and Q3 are nearly constant.

The get real values we filled the table with N rows ( one thousand to one million) and executed each query 1000 times.

Code:

   100        1.000      10.000     100.000    1.000.000
Q1  0:00.718s  0:02.092s  0:18.684s  2:59.081s  58:20.000s
Q2  0:00.519s  0:00.607s  0:00.614s  0:00.628s   0:00.637s
Q3  0:00.570s  0:00.607s  0:00.614s  0:00.628s   0:00.637s

As you can see the plain ORDER BY RAND() is already behind the optimized query at only 100 rows in the table.

Read the analysis. I think it is a really interesting read for every developer that has to write SQL and it also offers a really nice solution for database tables with ID holes. There are also a few other interesting essays about various other MySQL problems on his Playing with MySQL page.

Greetings from Hamburg / Germany
- rojaro -

[low]

I think I've found where the bug is:

prado-3.1.2.r2448/framework/Data/DataGateway/TSqlCriteria.php, public function setCondition($value)

this part is problematic:
               if(!empty($value) && preg_match('/ORDER\s+BY\s+(.*?)$/i',$value,$matches)>0)
               {
                        // condition contains ORDER BY, we need to strip it output
                      $this->_condition=substr($value,0,strpos($value,$matches[0]));
                      $this->setOrdersBy($matches[1]);
                }

I've commented out the if else part, now it works ok with a request like that one:
select distinct extract(year from s.date)||'/'||extract(month from s.date)||'/'||extract (day from s.date)||'/'||extract(hour from s.date)||'/'||extract(minute from s.date)||'/'||extract(second from s.date) as dates,path as valeur , avg("sunzen") as avg from boussole_data_20_107 as b,satellite as s where st_contains( region , (select position from capteur where cid = '107')) and s.date between (b.date - time '00:15') and (b.date + time '00:15' ) and s.date> '2007-05-19 00:00:00+00' and b.date> '2007-05-19 00:00:00+00' and s.date < '2008-05-19 00:00:00+00' and b.date < '2008-05-19 00:00:00+00'group by dates,path order by dates;
(previously, it said the colum dates doesn't exists).
Probably not the best fix, but at least it works.
i'm going to add a comment to the #834 ticket right now.
Regards.

[Knut]

This issue is now fixed in [2487]. Even though it's bad practice to put function calls in an ORDER BY clause TDbCommandBuilder should not fail. A small check in TDbCommandBuilder::applyOrdering() is now ensuring that both columns and functions can be used.

Please test this fix with your applications.