SQL error after updating to propel1.6 #8

Closed
ibr opened this Issue Aug 5, 2011 · 11 comments

Projects

None yet

3 participants

@ibr
ibr commented Aug 5, 2011

Propel 1.5.6: generated SQL is ok

SELECT DISTINCT mateam.ID,.. mabfw.ID_MA, ... FROM mateam CROSS JOIN
mabfw_rollen LEFT JOIN mabfw ON (mateam.ID_MA=mabfw.ID_MA)

Propel 1.6.0 - [wrapped: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'mabfw.ID_MA' in 'on clause']

SELECT DISTINCT mateam.ID, ... mabfw.ID_MA, ... FROM mateam INNER
JOIN mabfw_rollen ON (mabfw.ID_MA=mabfw_rollen.ID_MA) LEFT JOIN mabfw
ON (mateam.ID_MA=mabfw.ID_MA)

Criteria used:

$auswahl->addJoin(MabfwPeer::ID_MA, MabfwRollenPeer::ID_MA);
$auswahl->setDistinct();
$Team->getMateamsJoinMabfw($auswahl)

Following the relevant part of the Schema:

mabfw:
    _attributes: { phpName: Mabfw }
    id_ma: { phpName: IdMa, type: VARCHAR, size: '20', primaryKey: true, required: true }
    name: { phpName: Name, type: VARCHAR, size: '30', required: false }
    vorname: { phpName: Vorname, type: VARCHAR, size: '30', required: false }
    passwort: { phpName: Passwort, type: VARCHAR, size: '60', required: false }

mabfw_rollen:
    _attributes: { phpName: MabfwRollen }
    id_ma: { phpName: IdMa, type: VARCHAR, size: '20', required: true, foreignTable: mabfw, foreignReference: id_ma, onDelete: RESTRICT, onUpdate: RESTRICT }
    id_rolle: { phpName: IdRolle, type: INTEGER, size: '10', required: true, defaultValue: '0', foreignTable: marollen, foreignReference: id_rolle}
    id: { phpName: Id, type: INTEGER, size: '11', primaryKey: true, autoIncrement: true, required: true }
    _indexes: { id_ma: [id_ma] }
    _uniques: { id_ma_2: [id_ma, id_rolle] }

mateam:
    _attributes: { phpName: Mateam }
    id: { phpName: Id, type: INTEGER, size: '11', primaryKey: true, autoIncrement: true, required: true }
    id_team: { phpName: IdTeam, type: INTEGER, size: '11', required: true, foreignTable: team, foreignReference: id, onDelete: CASCADE, onUpdate: CASCADE }
    id_ma: { phpName: IdMa, type: CHAR, size: '20', required: true, foreignTable: mabfw, foreignReference: id_ma, onDelete: RESTRICT, onUpdate: RESTRICT }
    verantwortlich: { phpName: Verantwortlich, type: TINYINT, size: '4', required: true, defaultValue: '0' }
    _indexes: { id_ma: [id_ma] }
    _uniques: { id_team: [id_team, id_ma] }

Also a rewrite with Propel Queries instead of the old Criterias was not successful:

Query used:

$q = MabfwRollenQuery::create()
->useMabfwQuery()
->useMateamQuery()
->orderByVerantwortlich('DESC')
->endUse()
->orderByName('ASC')
->endUse()
->setDistinct()
->find();

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'mateam.ID_MA'
in 'on clause'

SELECT DISTINCT mateam.ID, ..., mabfw.ID_MA FROM `mabfw_rollen` LEFT
JOIN `mabfw` ON (mateam.ID_MA=mabfw.ID_MA)INNER JOIN `mateam` ON
(mabfw.ID_MA=mateam.ID_MA) ORDER BY mateam.VERANTWORTLICH DESC,
mabfw.NAME ASC

Seems to be related with http://trac.propelorm.org/changeset/1959 and http://trac.propelorm.org/changeset/2049

Copy of http://www.propelorm.org/ticket/1382

@ibr

Still the same error after updating Propel and using the newest sfPropelORMPlugin.

No idea why the working CROSS JOIN has been replaced by INNER
JOIN...

@Richtermeister

I ran into a similar issue (might be the same), which is documented here: http://trac.propelorm.org/ticket/1364

@ibr

Great, I'm not alone ;)

This is a blocker for my project, and although I can't fix propel, I can help testing PRs.

@willdurand
Propel member

Yeah, we are all aware of this bug with CROSS JOIN but it's quite hard to fix for now...

@willdurand
Propel member

Btw, could this PR help you ? propelorm/sfPropelORMPlugin#66

@ibr

Still the same error after updating Propel to 1.6.5, what can be done here?

@willdurand
Propel member

Quite hard to fix..

@ibr

Still the same error after updating Propel to 1.6.7, nothing can be done here?

@ibr

Trying with Propel 1.7.0-dev, still the same issue:

SELECT DISTINCT mateam.ID, ... mabfw.ID_MA, ... FROM mateam INNER
JOIN mabfw_rollen ON (mabfw.ID_MA=mabfw_rollen.ID_MA) LEFT JOIN mabfw
ON (mateam.ID_MA=mabfw.ID_MA)

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'mabfw.id_ma' in 'on clause']

This is working:

SELECT DISTINCT mateam.ID, ... mabfw.ID_MA, ... FROM mateam INNER
JOIN mabfw_rollen ON (mateam.ID_MA=mabfw_rollen.ID_MA) LEFT JOIN mabfw
ON (mateam.ID_MA=mabfw.ID_MA)

So Propel is using the wrong table for the ON clause.
Could somebody point me to the relevant source files?

@ibr

Ok, so I got a workaround eventually:
replace

$auswahl->addJoin(MabfwPeer::ID_MA, MabfwRollenPeer::ID_MA);

by

$auswahl->addJoin(MateamPeer::ID_MA, MabfwRollenPeer::ID_MA);

and the error is gone.
Thanks for listening.

@ibr ibr closed this Mar 25, 2013
@ibr

Found another occurrence:
If I use addJoin in an (admin generated) filter AND a custom sort (to sort an related field) the table is
joined TWICE and sql-query fails. This was working in 1.5.6

Unable to execute COUNT statement [SELECT COUNT(*) FROM (SELECT DISTINCT rechnung.id, 
.. FROM `rechnung` 
INNER JOIN `rechnungsposition` ON (rechnung.id=rechnungsposition.rechnung_id) 
LEFT JOIN `rechnungsposition` ON (rechnung.id=rechnungsposition.rechnung_id) 
LEFT JOIN `teilnehmer` ON (rechnung.aufn_nr=teilnehmer.aufn_nr) 
WHERE rechnungsposition.zeitraum=:p1) propelmatch4cnt]
 [wrapped: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'rechnungsposition']
@ibr ibr reopened this Mar 28, 2013
@willdurand willdurand closed this in 7d20c17 Sep 9, 2013
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment