Result set whit duplicate rows in jpa (Hibernate)

Question

I have the next sql query:

SELECT 
    @row_number:=@row_number+1 AS ID, IGN_DATES.PLACA, IGN_DATES.FECHA, OFF_DATES.FECHA
FROM
    (SELECT 
        a.PLACA AS PLACA, MIN(p.FECHA_GPS) AS FECHA
    FROM
        POSICIONHISTORIAL p
    JOIN AUTOMOVIL a ON p.SECAUTOMOVIL = a.SECAUTOMOVIL
    JOIN EVENTO e ON p.EVENTO_SECEVENTO = e.SECEVENTO
    WHERE
        p.FECHA_GPS BETWEEN '2016-09-01' AND '2016-10-01'
            AND e.CODIGO = 'ENC'
            AND a.PLACA IN ('SHS946', 'SHT011')
    GROUP BY a.PLACA , DAY(p.FECHA_GPS)) AS IGN_DATES
        JOIN
    (SELECT 
        a.PLACA AS PLACA,
            SUBSTRING_INDEX(GROUP_CONCAT(p.FECHA_GPS
                ORDER BY p.FECHA_GPS DESC
                SEPARATOR ' , '), ',', 2) AS FECHA
    FROM
        POSICIONHISTORIAL p
    JOIN AUTOMOVIL a ON p.SECAUTOMOVIL = a.SECAUTOMOVIL
    JOIN EVENTO e ON p.EVENTO_SECEVENTO = e.SECEVENTO
    WHERE
        p.FECHA_GPS BETWEEN '2016-09-01' AND '2016-10-01'
            AND e.CODIGO = 'APA'
            AND a.PLACA IN ('SHS946', 'SHT011')
    GROUP BY a.PLACA , DAY(p.FECHA_GPS)) 
AS OFF_DATES ON DATE(IGN_DATES.FECHA) = DATE(OFF_DATES.FECHA),
(SELECT @row_number:=0) AS t;

I use mysql 5.7 and the result of this query is:

Result Set in Mysql

But the native query called from JPA 2.1 (Hibernate) return duplicate values of OFF_DATES.FECHA, this are the values from eclipse debug.

Result set in JPA

The rowid in the statement was aggregate with intent resolve the problem, but this was unsuccessful

Thanks for help me.

Answer 1

I resolved this question, the problem is not the One-To-Many relationship gets duplicate objects whithout using "distinct".Why?.

The problem was the columns used for the join, I added one column more:

SELECT 
    @row_number:=@row_number+1 AS ID, IGN_DATES.PLACA, IGN_DATES.FECHA, OFF_DATES.FECHA
FROM
    (SELECT 
        a.PLACA AS PLACA, MIN(p.FECHA_GPS) AS FECHA
    FROM
        POSICIONHISTORIAL p
    JOIN AUTOMOVIL a ON p.SECAUTOMOVIL = a.SECAUTOMOVIL
    JOIN EVENTO e ON p.EVENTO_SECEVENTO = e.SECEVENTO
    WHERE
        p.FECHA_GPS BETWEEN '2016-09-01' AND '2016-10-01'
            AND e.CODIGO = 'ENC'
            AND a.PLACA IN ('SHS946', 'SHT011')
    GROUP BY a.PLACA , DAY(p.FECHA_GPS)) AS IGN_DATES
        JOIN
    (SELECT 
        a.PLACA AS PLACA,
            SUBSTRING_INDEX(GROUP_CONCAT(p.FECHA_GPS
                ORDER BY p.FECHA_GPS DESC
                SEPARATOR ' , '), ',', 2) AS FECHA
    FROM
        POSICIONHISTORIAL p
    JOIN AUTOMOVIL a ON p.SECAUTOMOVIL = a.SECAUTOMOVIL
    JOIN EVENTO e ON p.EVENTO_SECEVENTO = e.SECEVENTO
    WHERE
        p.FECHA_GPS BETWEEN '2016-09-01' AND '2016-10-01'
            AND e.CODIGO = 'APA'
            AND a.PLACA IN ('SHS946', 'SHT011')
    GROUP BY a.PLACA , DAY(p.FECHA_GPS)) 
AS OFF_DATES ON DATE(IGN_DATES.FECHA) = DATE(OFF_DATES.FECHA)
AND IGN_DATES.PLACA = OFF_DATES.PLACA,
(SELECT @row_number:=0) AS t;