update/upsert sets a String field with "0"

[yaitskov]

Hi,

Up on record update/upsert (if update) new value is discarded and column value in db gets "0".

 CREATE TABLE `t1` (
  `pk` int(11) NOT NULL,
  `vl` varchar(200) NOT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

:set +m
:set -XQuasiQuotes
:set -XOverloadedStrings
:set -XTemplateHaskell
:set -XTypeFamilies
:set -XGADTs
:set -XMultiParamTypeClasses
:set -XGeneralizedNewtypeDeriving

import Database.Persist
import Control.Monad.Logger
import Data.Pool
import Database.Persist.TH
import Database.Persist.MySQL

cinf = mkMySQLConnectInfo "localhost" "root" "root" "db1"
p2 <- runNoLoggingT (createMySQLPool cinf 2  :: NoLoggingT IO (Pool SqlBackend))

$(mkPersist sqlSettings  ([persistLowerCase|
MyT8 sql=t1 
  uid Int sql=pk
  value String sql=vl
  Primary uid
  UniqueUid uid
  deriving Show Read Eq Ord  |]));

# runSqlPool (upsert  (MyT8 11 "xxx") [MyT8Value +=. "yyy"]) p2
Entity {entityKey = MyT8Key {unMyT8Key = 11}, entityVal = MyT8 {myT8Uid = 11, myT8Value = "xxx"}}
# runSqlPool (upsert  (MyT8 11 "xxx") [MyT8Value +=. "yyy"]) p2
Entity {entityKey = MyT8Key {unMyT8Key = 11}, entityVal = MyT8 {myT8Uid = 11, myT8Value = "0"}}

Checking db

mysql> select * from t1 where pk = 11;
+----+----+
| pk | vl |
+----+----+
| 11 | 0  |
+----+----+
1 row in set (0.00 sec)

GHCI 8.6.5 windows 10 and mysql 5.6.39 on docker

mysql-haskell-0.8.4.2 persistent-2.9.2

[parsonsmatt]

+=. is the operator for addition. What behavior do you expect here?

Setting to 0 is definitely weird, but I suspect it's a MySQL casting/coercion thing.

mysql> select 'a' + 'b';
+-----------+
| 'a' + 'b' |
+-----------+
|         0 |
+-----------+
1 row in set, 2 warnings (0.00 sec)

So I think this is generating the SQL you're asking for (possibly unintentionally), but it's having a somewhat surprising result. Postgresql fails with a type mismatch error, and sqlite does the same as mysql:

sqlite> select 'a' + 'b';
0

I think we can possibly make this safer (but more inconvenient) by requiring a Num constraint on these functions since they don't really operate sensibly outside of those types.

[yaitskov]

Oh I see. I wanted just override field with new value. It was hard to find info how build Update object.

Another option is to use concat for string ;)

[parsonsmatt]

To overwrite a field value, you can use the (=.) operator.

+=. can't currently be different for strings and numbers, and I don't think I want to introduce overloading like that - if I did, then you'd have submitted a slightly different bug report :)

[Vlix]

I guess this could be closed?