JPA Repository Query with ordinal parameter return wrong boolean values for the entities (caused by MYISAM Engine)

Question

I have some strange issue with JPA entity (mapped to mysql)

The boolean property "recurring" is actually false in the db. But in the native following query it's returning true with object.

It's happening when I'm working with the old exist data. And indeed if I do all the db from the scratch it's not happening.

Maybe it's because of that I added this property manually on the ongoing development process, but I don't find any reason that should cause it.

@Query(value = "SELECT DISTINCT e from SocialEvent e " + "join e.multiPropsValuesSet m "
            + "join e.multiPropsValuesSet c " + "join e.multiPropsValuesSet a " + "WHERE ("
            + "(COALESCE(?1, NULL) is null  OR c in ?1 ) " 
            + " AND  (COALESCE(?2, NULL) is null  OR c in ?2 ) "
            + "AND  (COALESCE(?3, NULL) is null  OR a in ?3 ) "
            + ") "
            + "AND (e.date BETWEEN ?4 AND ?5) " + "ORDER BY e.date ASC" 
/*, cause problems regarding the ordinal parameter--> nativeQuery=true*/ 
 )                                                                                                      
        List<SocialEvent> filterNotWorking(List<MultiPropValue> eventTypes, List<MultiPropValue> areas,
                List<MultiPropValue> jewLvlKeep
                , Date from, Date to);

In the Named Query like findById() it's coming properly.

I don't see any change from the create-drop version to the exist data version on this field.

Does someone have a clue how to find the solution for this?

UPDATE:

After doing some more debugging I found that when changing the table engine from MyISAM to innoDB, it solved.

I saw that before the change I have also some queries that bringing back a lot of duplicates, although the query defined "distinct".

1. Why this is happening?
2. Is it risk that I'll change my production tables into this engine?
3. And how do I prevent this from happening again in the future ?

Answer 1

Solved

I have succeed to solve it. I will explain briefly, maybe it will help someone in the future.

First - a way of action for solve such problems - is of course to try to create the DB from zero and see if those things are happening too.

I saw that not - so I start to compare the DB types, and didn't see any difference until I noticed that in the new generated DB it's innoDB and at the old it's MyIsam Engine.

See the images in the questions.

The problem was that I've upgraded my DB from mysql 5.7 to 8. And apparently Mysql V8 not functional well with the MyIsam engine that created by the previous version.

I assume that the proper solution for such problems is to change all the tables from MyIsam to InnoDB, also to validate that the hibernate dialect is fit to 8.

Depending on your configuration method (gradle, maven etc).

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

In the production it will derived a proper tests after those changes, but that's the life.