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

RobRoyd · 2020-10-08T14:13:48Z

GORM Playground Link

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.

The text was updated successfully, but these errors were encountered:

fwielstra · 2021-01-05T10:11:33Z

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: v1.20.7...v1.20.8

jinzhu · 2021-01-05T10:47:23Z

It should not work before @fwielstra

RobRoyd · 2021-01-12T13:26:06Z

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

codemicro · 2021-01-16T17:41:08Z

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.

Z-M-Huang · 2021-01-17T15:53:01Z

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

foxcpp · 2021-01-31T12:06:38Z

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.

foxcpp · 2021-01-31T12:07:51Z

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

RobRoyd · 2021-02-16T12:32:58Z

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.

JonathonReinhart · 2021-03-21T04:39:47Z

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: go-gorm/sqlite#36

antoninbas · 2021-11-05T01:39:31Z

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

choyri · 2021-11-25T15:06:47Z

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

ToughK · 2023-10-20T10:03:11Z

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

LubosD · 2024-05-31T14:34:52Z

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.

RobRoyd assigned jinzhu Oct 8, 2020

github-actions bot added type:with reproduction steps with reproduction steps type:question general questions labels Oct 8, 2020

jinzhu added the in progress label Oct 9, 2020

fwielstra mentioned this issue Oct 22, 2020

Removing associations in a nested structure #3653

Closed

jinzhu mentioned this issue Oct 29, 2020

Ignore FOR UPDATE and FOR SHARE clause go-gorm/sqlite#20

Merged

3 tasks

codemicro mentioned this issue Jan 16, 2021

Feature: Bios for sysmates codemicro/lgballtDiscordBot#2

Closed

codemicro added a commit to codemicro/lgballtDiscordBot that referenced this issue Jan 20, 2021

Add hacky workaround for go-gorm/gorm#3585

f1bdae7

jinzhu mentioned this issue Nov 29, 2021

Models with composite keys cannot be successfully removed using Delete(...) #4879

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

RobRoyd commented Oct 8, 2020 •

edited

Loading

fwielstra commented Jan 5, 2021

jinzhu commented Jan 5, 2021 •

edited

Loading

RobRoyd commented Jan 12, 2021

codemicro commented Jan 16, 2021 •

edited

Loading

Z-M-Huang commented Jan 17, 2021

foxcpp commented Jan 31, 2021 •

edited

Loading

foxcpp commented Jan 31, 2021

RobRoyd commented Feb 16, 2021

JonathonReinhart commented Mar 21, 2021

antoninbas commented Nov 5, 2021

choyri commented Nov 25, 2021

ToughK commented Oct 20, 2023

LubosD commented May 31, 2024

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

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

Comments

RobRoyd commented Oct 8, 2020 • edited Loading

GORM Playground Link

Description

fwielstra commented Jan 5, 2021

jinzhu commented Jan 5, 2021 • edited Loading

RobRoyd commented Jan 12, 2021

codemicro commented Jan 16, 2021 • edited Loading

Z-M-Huang commented Jan 17, 2021

foxcpp commented Jan 31, 2021 • edited Loading

foxcpp commented Jan 31, 2021

RobRoyd commented Feb 16, 2021

JonathonReinhart commented Mar 21, 2021

antoninbas commented Nov 5, 2021

choyri commented Nov 25, 2021

ToughK commented Oct 20, 2023

LubosD commented May 31, 2024

RobRoyd commented Oct 8, 2020 •

edited

Loading

jinzhu commented Jan 5, 2021 •

edited

Loading

codemicro commented Jan 16, 2021 •

edited

Loading

foxcpp commented Jan 31, 2021 •

edited

Loading