`Like`-operator didn't work in a mysql project

[amxyz-cyber]

Hi, I adapted the yesod webapp espial to a mysql database. And when I performed the following sql query in Esqueleto, it returned no search results:

bookmarksSearch :: UserSearchForm -> Key User -> DB (Int, [(Entity Bookmark, Maybe Text)])
bookmarksSearch (UserSearchForm{bookmarkURL=mu,bookmarkTitle=mt,bookmarkDescr=md,bookmarkTags=tags,bookmarkFilter=mf,bookmarkBefore=mb,bookmarkAfter=ma}) uid  = do
    res <- select $ distinct $ do
        --b <- from $ table @Bookmark
        (b :& t2) <-
            from $ table @Bookmark
            `leftJoin` table @BookmarkTag
            `on` (\(bx :& tx) ->
                just (bx ^. BookmarkId) ==. (tx ?. BookmarkTagBookmarkId))          
        _whereClause b t2
        orderBy [desc (b ^. BookmarkTime)]
        pure (b, subSelect $ from (Table @BookmarkTag) >>= \t -> do
                 where_ (t ^. BookmarkTagBookmarkId ==. b ^. BookmarkId)
                 groupBy (t ^. BookmarkTagBookmarkId)
                 orderBy [asc (t ^. BookmarkTagSeq)]
                 pure $ mysqlGroupConcat (t ^. BookmarkTagTag))
    let xs = map (\(e,mtagValue) -> (e,unValue mtagValue)) res
    return (length xs,xs)
    where
        _whereClause bTab tTab = do
         where_ $ bTab ^. BookmarkUserId ==. val uid 
         where_ $ bTab ^. BookmarkDescription `like` ((%) ++. val "chemistry" ++. (%))

However, the same query worked in Mysql Workbench. Also, the original code from espial didn't work either. Only when I created an unsafe function emulating the match against function from Mysql, did I get the desired search results. Do you think, it could be a bug?

In models.persistentmodels the mysql database is defined as follows:

User json
  name Text sqltype=varchar(500)
  passwordHash BCrypt sqltype=varchar(500)
  apiToken Text Maybe sqltype=varchar(500) default=NULL
  privateDefault Bool sqltype=BOOLEAN 
  archiveDefault Bool sqltype=BOOLEAN 
  privacyLock Bool sqltype=BOOLEAN 
  UniqueUserName name
  deriving Show Eq Typeable Ord

Bookmark json
  -- Id Int64 sqltype=bigint
  userId UserId sqltype=bigint OnDeleteCascade 
  -- slug BmSlug default="(lower(hex(randomblob(6))))"
  slug BmSlug sqltype=varchar(512)
  href Text sqltype=varchar(2048)
  description Text sqltype=varchar(1024)
  extended Text sqltype=TEXT
  time UTCTime sqltype=DATETIME
  shared Bool sqltype=BOOLEAN
  toRead Bool sqltype=BOOLEAN
  selected Bool sqltype=BOOLEAN
  archiveHref Text Maybe sqltype=varchar(1024)
  UniqueUserHref userId href
  UniqueUserSlug userId slug
  deriving Show Eq Typeable Ord

BookmarkTag json
  -- Id Int64 sqltype=bigint
  userId UserId sqltype=int OnDeleteCascade
  tag Text sqltype=varchar(1024)
  bookmarkId BookmarkId sqltype=int OnDeleteCascade
  seq Int sqltype=int 
  UniqueUserTagBookmarkId userId tag bookmarkId
  UniqueUserBookmarkIdTagSeq userId bookmarkId tag seq
  deriving Show Eq Typeable Ord

Note json
  -- Id Int64 sqltype=bigint
  userId UserId sqltype=int OnDeleteCascade
  -- slug NtSlug default="(lower(hex(randomblob(10))))"
  slug NtSlug sqltype=varchar(512)
  length Int sqltype=int 
  title Text sqltype=varchar(4096)
  text Text sqltype=TEXT
  isMarkdown Bool sqltype=BOOLEAN
  shared Bool sqltype=BOOLEAN default=False
  created UTCTime sqltype=DATETIME
  updated UTCTime sqltype=DATETIME
  deriving Show Eq Typeable Ord

The mysql version of espial is using Esqueleto 3.5.5.0 as well as lts-19.9. What's more, I created search indexes for several columns using Mysql Workbench.

[belevy]

Your best bet is to renderSelectQuery to see what is actually being generated by esqueleto and how that is different from what you expected. The query you have provided has a lot going on as it is, if you can provide a minimal example showing that LIKE clauses are being generated incorrectly then we can do something.

[belevy]

If you can give a reproduction case feel free to reopen this issue