DISTINCT does not work if the table is aliased

[doubleface]

This true at least with sqlite 3 from PDO extension.

Suppose you have the following DAO :

<?xml version="1.0"?>
<dao xmlns="http://jelix.org/ns/dao/1.0">
   <datasources>
      <primarytable name="tablealias" realname="products" primarykey="id" />
   </datasources>
   <record>
      <property name="id"   fieldname="id" datatype="autoincrement"/>
      <property name="alias" fieldname="alias" datatype="string"  required="true"/>
   </record>
</dao>

If you try to run the following code :

$factory = jDao::get('products', 'testapp_sqlite');
$condition = new jDaoConditions();
$factory->countBy($condition, 'id');

you get the following error message :

[exception HY000] SQLSTATE[HY000]: General error: 1 no such column: products.id

The generated SQL request is the following :

SELECT COUNT(DISTINCT products.id) as c  FROM products AS tablealias

The real name of the table has been used instead of the alias and it does not work with sqlite (I don't know for mySQL or postgreSQL)

I think the generated request should be :

SELECT COUNT(DISTINCT tablealias.id) as c  FROM products AS tablealias

See the corresponding joined patch.

[laurentj]

ok for me. should be land into 1.1.x branch and trunk

[laurentj]

pushed

• ​http://hg.jelix.org/jelix/jelix-trunk/rev/3f72d3b88982
• ​http://hg.jelix.org/jelix/releases/jelix-1.1.x/rev/7f1b91853644