Major Bug In Cactiverecord::findall With Join And Limit

[gogume (Thebogu)]

This will be a way to reproduce it, I didn’t have time to see how to solve it:

1. create two tables in mysql:

• table1: (id - pk autoincrement)

• table2: (id - pk autoincrement, table1_id - fk)

2. insert rows, the number of inserted rows is importent:

• table1:

  INSERT INTO table1 SET id = NULL; execute it 4 times

• table1:

  INSERT INTO table2 SET table_id = 1; execute it 3 times

  INSERT INTO table2 SET table_id = 2; execute it 2 times

  INSERT INTO table2 SET table_id = 3; execute it 1 time

3. generate models with gii:

4. create criteria:

   $criteria = new CDbCriteria();

   $criteria->with = array(

     "table2s" => array(
   
   
         "joinType" => "INNER JOIN",
   
   
     ),
   

   );

4.1) execute criteria with findAll and get the count of rows:

Table1::model()->findAll($criteria); // returns 3 results which is exactly what it should be

query from log:

SELECT

`t`.`id` AS `t0_c0`, 


`table2s`.`id` AS `t1_c0`, 


`table2s`.`table1_id` AS `t1_c1` 

FROM table1 t

INNER JOIN table2 table2s ON (table2s.table1_id=t.id)

4.2) add limit to the criteria and check the new results:

$criteria->limit = 5;


Table1::model()->findAll($criteria); // returns 4 results, it executes two queries, it doesn't take into account joinType

queries from log:

SELECT t.id AS t0_c0 FROM table1 t LIMIT 5;

SELECT

`t`.`id` AS `t0_c0`, 


`table2s`.`id` AS `t1_c0`, 


`table2s`.`table1_id` AS `t1_c1` 

FROM table1 t

INNER JOIN table2 table2s ON (table2s.table1_id=t.id)

WHERE (t.id IN (‘1’, ‘2’, ‘3’, ‘4’));

4.3) add limit and together on true to the criteria and check the new results:

$criteria->limit = 5;


$criteria->together = true;


Table1::model()->findAll($criteria); // returns 2 results

query from log:

SELECT

`t`.`id` AS `t0_c0`, 


`table2s`.`id` AS `t1_c0`, 


`table2s`.`table1_id` AS `t1_c1` 

FROM table1 t

INNER JOIN table2 table2s ON (table2s.table1_id=t.id)

LIMIT 5

[CeBe]

Created an issue on github for it: https://github.com/yiisoft/yii/issues/1846