How to use UNLESS CONFLICT in this query

[clmnin]

Question

1. Here even though product ABC 1 Product exit with the same exclusivity constraints a new product was created. How do I modify the schema or the query such that a new product is not created?

Minimal Reproducible Example

START MIGRATION TO {
    module default {
        type Company {
            required property name -> str {
                constraint exclusive;
            }
        }
        type Category {
            # a category per company
            required property name -> str;
            required link company -> Company;
            constraint exclusive on ( (.company, .name) );

        }
        type Product {
            # a product per company but sku and name can differ for exclusivity
            required property name -> str;
            required link company -> Company;
            required link category -> Category;
            property sku -> str;
            constraint exclusive on ( (.company, .name, .sku) );
        }
    }
};
POPULATE MIGRATION;
COMMIT MIGRATION;

INSERT Company {name := "ABC Company"};

# insert one category
INSERT Category {name:= "ABC 1 Category", company := (SELECT Company FILTER .name = "ABC Company")};

# insert two products under the same category and product
WITH this_company := (SELECT Company FILTER .name = "ABC Company")
INSERT Product {
    name := "ABC 1 Product",
    company := this_company, 
    category := (SELECT Category FILTER .name = "ABC 1 Category" AND .company = this_company)
};
WITH this_company := (SELECT Company FILTER .name = "ABC Company")
INSERT Product {
    name := "ABC 2 Product",
    company := this_company, 
    category := (SELECT Category FILTER .name = "ABC 1 Category" AND .company = this_company)
};

# try inserting a product with conflicting properties. Should fail
WITH this_company := (SELECT Company FILTER .name = "ABC Company")
SELECT (
    INSERT Product {
        company := this_company, 
        name := "ABC 1 Product", 
        category := (
            INSERT Category {
                name := "ABC 1 Category", 
                company := this_company
            }
            UNLESS CONFLICT ON (.name, .company)
            ELSE (
                SELECT Category
            )
        )
    } UNLESS CONFLICT ON (.company, .name, .sku)
    ELSE (
        SELECT Product
    )
);

SELECT Product {id, name, company, category, sku};

# {
#   default::Product {
#     id: fabe53b4-0f50-11ec-8ae4-7377d18bcf3f,
#     name: 'ABC 1 Product',
#     company: default::Company {id: fa9624d4-0f50-11ec-8ae4-83c85d232d92},
#     category: default::Category {id: faa65728-0f50-11ec-8ae4-dfe4246b9355},
#     sku: {},
#   },
#   default::Product {
#     id: fac01f8c-0f50-11ec-8ae4-af7086533900,
#     name: 'ABC 2 Product',
#     company: default::Company {id: fa9624d4-0f50-11ec-8ae4-83c85d232d92},
#     category: default::Category {id: faa65728-0f50-11ec-8ae4-dfe4246b9355},
#     sku: {},
#   },
#   default::Product {
#     id: 0a8cce9c-0f51-11ec-8ae4-9fd733321f0f,
#     name: 'ABC 1 Product',
#     company: default::Company {id: fa9624d4-0f50-11ec-8ae4-83c85d232d92},
#     category: default::Category {id: faa65728-0f50-11ec-8ae4-dfe4246b9355},
#     sku: {},
#   },
# }

[clmnin]

I've used

constraint exclusive on ( (.company, .name, .sku) );

But the property .sku is not required. Which means when .sku is null (empty set) even if .company and .name are duplicated it won't raise an exception.

property sku -> str;

I believe this is the same for postgres.

[elprans]

One solution is to make the constraint condition non-OPTIONAL, e.g exclusive on (.company, .name, .sku ?? 'N/A').

[limx0]

Hey @elprans thanks for the quick response! In my case the optionalsku field is actually an Enum and when I try and do as you mention above, I'm seeing the attached error.
exception.log

I'll try and make a MRE

[limx0]

Okay - I tried creating a simple example and it seems enums are not supported? I've also tried casting to a string.

    scalar type SKU extending enum<NA,A,B,>;

    type Product {
        required property name -> str;
        required link company -> Company;
        property sku -> SKU;
        constraint exclusive on ( (.company, .name, sku ?? SKU.NA) );

Same error as above

[elprans]

Oh, it's a bug. I opened #2975. Should be relatively easy to fix, I'm looking at it now.

[limx0]

Thank you!