Re: [Modeling-users] Double Result

[Sebastien B. <sbi...@us...>]

        Hi,

  A quick answer on this:

Yannick Gingras <yan...@sa...> wrote:
> Hi, I think that something is broken with the MySQL SQL.  I mean, I think
> that MySQL is broken but I have to use it and I wonder if the framework c=
an
> help.

> The fetch spec :=20
>=20
>  ((codelike**) AND (isActive!=3D-255) AND (i18n.titlelike*ABC*))
>=20
> gives me the my 2 rows twice :=20
>=20
> Evaluating: SELECT t0.activity_id, t0.code, t0.enterprise_id, t0.is_activ=
e=20
> FROM ABCI18N t1 INNER JOIN ABC t0 ON ( t0.activity_id=3Dt1.activity_id ) =
 WHERE=20
> (t0.code LIKE '%%' AND t0.is_active <> -255 AND t1.title LIKE '%ABC%')
> rowcount: 4 Returning: {'activityId': 1L, 'code': 'ABC009', 'enterpriseId=
':=20
> 1L, 'isActive': 1L}
> rowcount: 4 Returning: {'activityId': 1L, 'code': 'ABC009', 'enterpriseId=
':=20
> 1L, 'isActive': 1L}
> rowcount: 4 Returning: {'activityId': 2L, 'code': 'ABC123', 'enterpriseId=
':=20
> 1L, 'isActive': 1L}
> rowcount: 4 Returning: {'activityId': 2L, 'code': 'ABC123', 'enterpriseId=
':=20
> 1L, 'isActive': 1L}
>=20
> MySQL returns 4 rows with the same select typed at the prompt.  Is
> there anything that I can do to prevent the duplication ?

Okay. Just to be sure: I assume you really have no duplicates in table
ABC ;)

 As a quick workaround, try typing the same at mysql prompt, but with
SELECT DISTINCT: does it help?

If it does, you can add the following method to
DatabaseAdaptors.MySQLAdaptorLayer.MySQLSQLExpression:

------------------------------------------------------------------------
  def assembleSelectStatementWithAttributes(self, attributes, lock, qualifi=
er,
                                            fetchOrder, selectString,
                                            columnList, tableList, whereCla=
use,
                                            joinClause, orderByClause,
                                            lockClause):
    """
    """
    selectString=3DselectString or 'SELECT DISTINCT'
    super=3DSQLExpression.assembleSelectStatementWithAttributes
    return super(self, attributes, lock, qualifier, fetchOrder, selectStrin=
g,
                 columnList, tableList, whereClause, joinClause,=20
                 orderByClause, lockClause)
------------------------------------------------------------------------

Which version of mysql are you using? I cannot reproduce it here; for
example and from the test database 'AUTHOR_BOOKS', the following
statement produces the correct number of rows (with no duplicates):

select t0.id, t0.title, t0.price=20
from WRITER t1
INNER JOIN BOOK t0
  ON (t0.fk_writer_id=3Dt1.id)
where (t0.title like '%%' AND t0.price is NULL AND
       t1.last_name like '%%');

Mine is: mysql --version
mysql  Ver 12.20 Distrib 4.0.13, for pc-linux-gnu (i686)


  That's intriguing, indeed, but I've no time to investigate by
  now. Does anyone know anything about this behaviour?

Last: could you try reversing the join (see below) at the mysql prompt
      and report what it returns?

 SELECT t0.activity_id, t0.code, t0.enterprise_id, t0.is_active=20
 FROM ABC t0 INNER JOIN ABCI18N t1 ON ( t0.activity_id=3Dt1.activity_id )
 WHERE (t0.code LIKE '%%' AND t0.is_active <> -255 AND t1.title LIKE '%ABC%=
')


-- S=E9bastien.