[SQL] Getting different results from Spring JPA query vs raw SQL in console

I have the following SQL query which returns one result, as expected, when I run it in the MySQL console:

SELECT DISTINCT a.* FROM account a
INNER JOIN contact c ON a.id = c.accountId WHERE a.accountName LIKE '%test@test.com%'
OR a.id IN (SELECT e.accountId FROM environment e WHERE e.externalId LIKE '%test@test.com%'
OR e.name LIKE '%test@test.com%')
OR c.email = 'test@test.com'


I use the same query in my application code, yet zero results are returned:

@Query(value = "SELECT DISTINCT a.* FROM account a INNER JOIN contact c ON a.id = c.accountId WHERE a.accountName LIKE %?1% OR a.id IN " +
"(SELECT e.accountId FROM environment e WHERE e.externalId LIKE %?1% OR e.name LIKE %?1%) " +
"OR c.email = ?1", nativeQuery = true)
List<Account> findAccountByKeyword(String keyword);


I see the following SQL being generated, which looks fine to me:

select distinct account0_.id as id1_0_, account0_.createdBy as createdB2_0_, account0_.createdDate as createdD3_0_, account0_.lastUpdatedBy as lastUpda4_0_, account0_.updatedDate as updatedD5_0_, account0_.accountName as accountN6_0_, account0_.globalMessage as globalMe7_0_, account0_.isConsultingPartner as isConsul8_0_, account0_.isNonProfit as isNonPro9_0_, account0_.isSuspended as isSuspe10_0_, account0_.note as note11_0_, account0_.parentId as parentI12_0_, account0_.products as product13_0_, account0_.salesforceId as salesfo14_0_, account0_.type as type15_0_, account0_.typeShort as typeSho16_0_, account0_.usedProducts as usedPro17_0_

from account account0_ inner join contact contact1_ on (account0_.id=contact1_.accountId)
where account0_.accountName like ? or account0_.id in (select environmen2_.accountId from environment environmen2_ where environmen2_.externalId like ?
or environmen2_.name like ?)
or contact1_.email=?


Any ideas what could be going wrong here? I have verified that the value of keyword is correct so I'm at a complete loss what else I can try. Other questions regarding inconsistencies between native SQL and queries within Spring are all talking about problems with date types but I'm not even using dates here or anything dialect-specific.

Please let me know if you need more information and I will edit my question accordingly.

Continue reading...