Gorm sqlite3 'row value misused' error when preloading a model with composite primary keys and foreign key relations

GORM Playground Link

https://github.com/go-gorm/playground/pull/168

Description

There is an issue when loading a model with composite primary keys from a sqlite3 database where the .Take() after a .Preload() will result in a sqlite3 row value misued error.

The following model (which is used in the playground link, too) can create the issue.

type (
    // Root is the root table with an autoincrement id
    // it references Branch by branch's composite primary key
    Root struct {
        ID       uint64    `gorm:"primaryKey;autoIncrement"`
        Branches []*Branch `gorm:"foreignKey:RootId;constraint:OnUpdate:RESTRICT,OnDelete:CASCADE;"`
    }

    // Branch is has a composite primary key derived from the ID of its Root and its own Name
    // it references Leaf by leaf's composite primary key
    Branch struct {
        RootId uint64  `gorm:"primaryKey"`
        Name   string  `gorm:"primaryKey;size:100"`
        Leaves []*Leaf `gorm:"foreignKey:RootId,BranchName;constraint:OnUpdate:RESTRICT,OnDelete:CASCADE;"`
    }

    // Leaf is has a composite primary key derived from the ID of its Root, Name of its Branch and its own Name
    // it does not reference anything
    Leaf struct {
        RootId     uint64 `gorm:"primaryKey"`
        BranchName string `gorm:"primaryKey;size:100"`
        Name       string `gorm:"primaryKey;size:100"`
    }
)

Basically when having a model composed of one root (ID=1) with two branches (b1 and b2) and a leaf on each branch the query to load the leaves in a preload is created as this:

SELECT * FROM `leafs` WHERE (`leafs`.`root_id`,`leafs`.`branch_name`) IN ((1,"b1"),(1,"b2"))

From my understanding, the tuples in the IN clause cause the issue, as the used sqlite3 database does not seem to handle this. The question is if the query could be build like the following, because this works with the used sqlite3 database, too.

SELECT * FROM `leafs` WHERE (`leafs`.`root_id`,`leafs`.`branch_name`) IN (VALUES (1,"b1"),(1,"b2"));

or

SELECT * FROM `leafs` WHERE (`leafs`.`root_id`) IN (1,1) AND (`leafs`.`branch_name`) IN ("b1","b2");

I verified however, that the above query will work for a MariaDB, so I guess this issue is related to sqlite3 only.

Comment From: fwielstra

This was 'just' a warning, but I recently updated my dependencies (gorm.io/driver/sqlite from v1.1.3 to v1.1.4 and gorm.io/gorm from v1.20.6 to v1.20.9) and it has caused one of my tests to fail because instead of a warning it now returns an error.

I narrowed it down to gorm v1.20.8; it remains a warning at v1.20.7, so it must have changed somewhere in these commits: https://github.com/go-gorm/gorm/compare/v1.20.7...v1.20.8

Comment From: jinzhu

It should not work before @fwielstra

Comment From: RobRoyd

How is the progress on this issue? Will it be fixed or is it a no fix?

Comment From: codemicro

I'm also seeing this with GORM versions 1.20.7 and 1.20.11 and the SQLite driver versions 1.1.4 and 1.1.6, again with a composite key. This time it was as a result of a call to Delete, though, not because of preloading.

// DELETE FROM `user_bios` WHERE (`user_bios`.`user_id`,`user_bios`.`mem_id`) IN (("[...]","[...]"))

type UserBio struct {
    UserId string `gorm:"primarykey"`
    MemId  string `gorm:"primarykey"`
}

For anyone looking for a workaround, check out db.Exec.

Comment From: Z-M-Huang

I've encountered the same for SQLite. This actually breaks some of my test cases for structs having composite keys.

Comment From: foxcpp

Facing the same issue (using Preload)

The generated SQL in my case is:

SELECT * FROM `comments` WHERE `comments`.`group_id`,`comments`.`post_id` IN ((XXXX,YYYY));

The problematic part is ((XXXX,YYYY)).

Quoting https://www.sqlite.org/rowvalue.html:

For a row-value IN operator, the left-hand side (hereafter "LHS") can be either a parenthesized list of values or a subquery with multiple columns. But the right-hand side (hereafter "RHS") must be a subquery expression.

Comment From: foxcpp

It seems like the only way to make it work for SQLite is to generate a SELECT subexpression using SELECT ... UNION. This works correctly.

Comment From: RobRoyd

Can confirm. After updating gorm and the driver to latest, more tests are not running. It seems, it is because of tables containing composite keys. Before it was only when preloading, now it is on delete, create and update, too.

Comment From: JonathonReinhart

Just in case anyone ends up here for the same reason I did, it seems that the use of net.IP fields with the sqlite driver does not work: https://github.com/go-gorm/sqlite/issues/36

Comment From: antoninbas

Just wanted to bump this issue as the problem still seems to exist with the latest Gorm version

Comment From: choyri

I just wanted to use SQLite for unittest in CICD, but unfortunately, I encountered this error, so I had to continue to use MySQL 😢

Comment From: ToughK

Any updates on this problem? I still get the same problem with the latest Gorm version

Comment From: LubosD

I've hit the same problem. Tried to find out if it could be easily remedied via the Dialector, but the code that builds this doesn't use it.

I think it really deserves to be fixed, because I think the "IN with multiple columns" example on GORM's homepage cannot work in SQLite this way.

Comment From: moshuipan

use github.com/glebarez/sqlite to replace gorm.io/driver/sqlite mabye resolve the problem.