I don't understand why Hibernate is duplicating the value of the "fb.id on s.id"
This is the query generated by Hibernate:
2013-09-03 08:18:25,071 INFO com.mchange.v2.log.MLog - MLog clients using log4j logging.
2013-09-03 08:18:25,180 INFO com.mchange.v2.c3p0.C3P0Registry - Initializing c3p0-0.9.1 [built 16-January-2007 14:46:42; debug? true; trace: 10]
2013-09-03 08:18:25,227 INFO com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource - Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@5d7d95bc [ connectionPoolDataSource ->
Hibernate:
Code:
SELECT
f.numero,
t.codigo,
f.nf,
f.apellido || ', ' || f.nombre,
fb.id,
s.id
FROM
familiares f
JOIN
titulares t
ON f.titular = t.codigoafiliado
LEFT JOIN
familiares_baja fb
ON (
f.numero = fb.familiares_id
and fb.vigente = true
)
LEFT JOIN
suspension s
ON (
f.numero = s.familiares_id
and s.vigente = true
)
WHERE
UPPER(f.nombre) like 'PELOSA%'
or UPPER(f.apellido) like 'PELOSA%'
ORDER BY
t.codigo,
f.nf asc LIMIT 20 OFFSET 0
RESULT:
[60354, 099976, 1, PELOSA, FRANCO LAUTARO, 59321,
59321]
[138255, 099976, 2, PELOSA, ANA LUZ, null, null]
Same query executed directly on PostgreSQL (PGAdmin) retrieves (what it should be):
[60354, 099976, 1, PELOSA, FRANCO LAUTARO, 59321,
null]
[138255, 099976, 2, PELOSA, ANA LUZ, null, null]
.....30min later.. changing the strategy (query) reveals another bug:
changed the left JOIN for subqueries in the SELECT
Code:
SELECT
f.numero, t.codigo, f.nf, f.apellido || ', ' || f.nombre,
(SELECT fb.id FROM familiares_baja fb WHERE ( f.numero = fb.familiares_id and fb.vigente = true )),
(SELECT s.id FROM suspension s WHERE ( f.numero = s.familiares_id and s.vigente = true ))
FROM familiares f
JOIN titulares t ON f.titular = t.codigoafiliado
WHERE
(UPPER(f.nombre) like 'PELOSA%' or UPPER(f.apellido) like 'PELOSA%' )
ORDER BY t.codigo, f.nf asc LIMIT 20 OFFSET 0
throws
2013-09-03 09:00:37,884 WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 22003
2013-09-03 09:00:37,884 ERROR org.hibernate.util.JDBCExceptionReporter - Bad value for type int : PELOSA, FRANCO LAUTARO
Login
FAQ
Search