M2M: pk bigserial and 2 fk uuid lead to pk used on fk (seems only appear with uuid type)

[alexisvisco]

I am creating a many 2 many table with one primary key as bigserial auto-increment and then the two FK with a unique group. The two foreign keys ids are UUIDs.

The tables look like that:

type UserTest struct {
	ID          uuid.UUID        `pg:",type:uuid"`
	Permissions []PermissionTest `pg:"many2many:upt"`
}

type PermissionTest struct {
	ID uuid.UUID `pg:",type:uuid"`
}

type UsersPermissionsTest struct {
	tableName struct{} `pg:"upt"`

	ID int `pg:"id,pk"`

	PermissionID uuid.UUID `pg:"permission_test_id,type:uuid,unique:idx_upt_unique"`
	UserID       uuid.UUID `pg:"user_test_id,type:uuid,unique:idx_users_upt_unique"`
}

Current Behavior

When fetching the relation of the many 2 many table instead of setting the permission id to the corresponding column in the select the ORM is trying to set the pk which is int to the FK permission id which is a UUID. (IDK if it's clear)

This is the final selection:

SELECT "users_permissions_test".*, "permission_test"."id" FROM "permission_tests" AS "permission_test" 
JOIN "upt" AS "users_permissions_test" ON ("users_permissions_test"."user_test_id") IN ('25289c7f-e733-4903-a8a3-e1569712ea69')
WHERE ("permission_test"."id" = "users_permissions_test"."permission_test_id")

The ORM is trying to assign "user_permissions_test"."id" to the Permission.ID struct. Which lead to panic: uuid: incorrect UUID length

This error happens with the UUID type. If you replace Permissions Ids and User ids with int it works suddenly as expected. The Permission.ID is correctly set from the "user_permissions_test"."permission_test_id" and not from the "user_permissions_test"."id". (that is surprising)

Expected Behavior

The ORM must correctly set the Permission ID from the "user_permissions_test"."permission_test_id" and not from the "user_permissions_test"."id".

Possible Solution

Yesterday I was diving into your code and it seems like the many to many scanner have an empty columns map field while scanning the first column (which is the id from user_permissions_test).

Likely too deep to understand why this bug happens. I just saw today that the problem only appear with UUID I first though that it was a bug because my foreign keys aren't the primary composite key, but it's not the case.

Steps to Reproduce

Launch this code:

package main

import (
	"fmt"

	"github.com/go-pg/pg/extra/pgdebug"
	"github.com/go-pg/pg/v10"
	"github.com/go-pg/pg/v10/orm"
	uuid "github.com/satori/go.uuid"
)

type UserTest struct {
	ID          uuid.UUID        `pg:",type:uuid"`
	Permissions []PermissionTest `pg:"many2many:upt"`
}

type PermissionTest struct {
	ID uuid.UUID `pg:",type:uuid"`
}

type UsersPermissionsTest struct {
	tableName struct{} `pg:"upt"`

	ID int `pg:"id,pk"`

	PermissionID uuid.UUID `pg:"permission_test_id,type:uuid,unique:idx_upt_unique"`
	UserID       uuid.UUID `pg:"user_test_id,type:uuid,unique:idx_users_upt_unique"`
}

func Example() {
	opts, _ := pg.ParseURL("postgresql://asvf-montagne-api:asvf-montagne-api@localhost:5172/asvf-montagne-api?sslmode=disable")
	db := pg.Connect(opts)

	defer db.Close()

	db.AddQueryHook(pgdebug.DebugHook{
		Verbose: true,
	})

	if err := createManyToManyTables(db); err != nil {
		panic(err)
	}

	userid := uuid.NewV4()
	permid := uuid.NewV4()
	values := []interface{}{
		&UserTest{ID: userid},
		&PermissionTest{ID: permid},
		&UsersPermissionsTest{ID: 30, UserID: userid, PermissionID: permid},
	}
	for _, v := range values {
		_, err := db.Model(v).Insert()
		if err != nil {
			panic(err)
		}
	}

	u := new(UserTest)
	err := db.Model(u).Relation("Permissions").First()
	if err != nil {
		panic(err)
	}

	fmt.Println("User", u.ID, "Permission", u.Permissions)
}

func createManyToManyTables(db *pg.DB) error {
	orm.RegisterTable((*UsersPermissionsTest)(nil))

	models := []interface{}{
		(*UserTest)(nil),
		(*PermissionTest)(nil),
		(*UsersPermissionsTest)(nil),
	}
	for _, model := range models {
		err := db.Model(model).CreateTable(&orm.CreateTableOptions{
			Temp: true,
		})
		if err != nil {
			return err
		}
	}
	
	return nil
}

func main() {

	Example()
}

You should have this output:

CREATE TEMP TABLE "user_tests" ("id" uuid, PRIMARY KEY ("id"))
CREATE TEMP TABLE "permission_tests" ("id" uuid, PRIMARY KEY ("id"))
CREATE TEMP TABLE "upt" ("id" bigserial, "permission_test_id" uuid, "user_test_id" uuid, PRIMARY KEY ("id"), UNIQUE ("permission_test_id"), UNIQUE ("user_test_id"))
INSERT INTO "user_tests" ("id") VALUES ('25289c7f-e733-4903-a8a3-e1569712ea69')
INSERT INTO "permission_tests" ("id") VALUES ('86aca506-d00e-4028-aa22-10939dd4d326')
INSERT INTO "upt" ("id", "permission_test_id", "user_test_id") VALUES (30, '86aca506-d00e-4028-aa22-10939dd4d326', '25289c7f-e733-4903-a8a3-e1569712ea69')
SELECT "user_test"."id" FROM "user_tests" AS "user_test" ORDER BY "user_test"."id" LIMIT 1
SELECT "users_permissions_test".*, "permission_test"."id" FROM "permission_tests" AS "permission_test" JOIN "upt" AS "users_permissions_test" ON ("users_permissions_test"."user_test_id") IN ('25289c7f-e733-4903-a8a3-e1569712ea69') WHERE ("permission_test"."id" = "users_permissions_test"."permission_test_id")
panic: uuid: incorrect UUID length: 30 <--- the error

Context (Environment)

I'm just trying to bind what I have in my database. The link table has fields with the id as a primary key and two other FK as UUIDs.

There is no black magic it's a simple case, I don't know why it isn't working, I was expecting it to works.

[debendraoli]

Simply removing ID as primary key from UsersPermissionsTest struct would solve the issue, many2many table do not work with ID as primary key.

The convention you are using is not a convention to use many2many relationship.

You should use both source tables primaries key as primary keys on many2many referencing table rather using unique constraint.