yesodweb / yesodweb/persistent

Composite primary keys require field order to be the same as in model

Open
#780 6 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
Haskell
Stars
486
Forks
306
PR merge metrics
No merged PRs in 30d

Description

It appears that composite primary keys need to be declared using field order to be exact like the order of fileds in model declaration.
E.g.

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
Person
    isMail Bool
    name String
    age Int Maybe
    deriving Show
    Primary name isMail
|]

main :: IO ()
main = runSqlite ":memory:" $ do
    runMigration migrateAll

    johnId <- insert $ Person True "John Doe" $ Just 35
    janeId <- insert $ Person False "Jane Doe" Nothing

    people <- selectList [PersonName >. ""] []
    liftIO $ print people

results in

Migrating: CREATE TABLE "person"("is_mail" BOOLEAN NOT NULL,"name" VARCHAR NOT NULL,"age" INTEGER NULL, PRIMARY KEY ("name","is_mail"))
pk-order: ISRManyKeys: unexpected keyvals result: field isMail: Expected Bool, received: PersistText "John Doe"

In out production code we use MySQL and there we get only

fromPersistValuesComposite': keyFromValues failed

from https://github.com/yesodweb/persistent/blob/master/persistent/Database/Persist/Sql/Util.hs#L90 (BTW wasn't removal of errors a point of release 2.7.3.1 of `persistent?)

Also it looks that if primary key fields will be of the same SQL type you could get just incorrect values returned from persistent.
I suppose that persistent should either check that the order of fields is the same or use the correct one itself.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start in persistent/Database/Persist/Sql/Util.hs around fromPersistValuesComposite' and reproduce the issue with the composite-key Person example using SQLite. Compare the behavior with MySQL and verify that composite primary keys do not depend on model field order or return incorrectly mapped values.

Written by the indexing model from the issue text.

Assessment

Tech stack
haskell, mysql, sqlite
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.