Strange UNIQUE INDEX Issue

[tartufella]

Hi There, I have noticed in v1.1.0 that the CREATE UNIQUE INDEX no longer works.

The attached file was created using v1.1.0, previously updated from 1.0.2

In my .NET code I have this line :

duck.Execute("CREATE UNIQUE INDEX sales_idx ON Sales (CalendarID,ItemID,CountryID,ChannelDemandID,PriceType);");

The attached file was created by the .NET client v1.1.0;

If you then open the file in a duckdb cli you can see the following behavior:

If you run the following you will see duplicates :

SELECT * FROM Sales WHERE CalendarID = 202526 AND ItemID = 550

However if you also run :

INSERT INTO Sales (CalendarID,itemID,CountryID,ChannelDemandID,PriceType) VALUES (202526,550,1,9,'PROMO')

you will see an error as expected.

If you run :

SELECT index_name,table_name,is_unique,expressions,sql FROM duckdb_indexes

You can see that the index does exist and it's unique, so I'm not sure how the .NET client created this file with a unique index but then also allowed duplicates into the table without throwing an error. If I run the exact same code but with v1.0.2 then the .NET code will throw an error as expected when a duplicate is added.

I'm not sure I understand why there are both duplicates in the database but I also cannot add a duplicate!
9cfb53b4-faec-4640-a783-d4c9a27ddf94.zip

Many thanks

[Giorgi]

Can you format your code? It's hard to read.

[tartufella]

Thanks @Giorgi for the reply, hope the formatting looks better now. Thanks also for creating this library, it's very much appreciated.

[Giorgi]

Do you get the same behavior if you repeat the same steps from the CLI?

[tartufella]

Hi there, in the .NET code after I have created the index, I have some code which inserts into the Sales table with an INSERT OR IGNORE statement. This is where the issue arises in v1.1.0 as it is allowing the duplicates into the Sales table which we can see from the attached duck db file. This same code with v1.0.2 does not create duplicates and if we don't use the INSERT OR IGNORE and just use INSERT then it will throw an error. v1.1.0 will not throw an error if just INSERT is used, it's like it creates the index but ignores it completely.

duck.Execute("CREATE UNIQUE INDEX sales_idx ON Sales (CalendarID,ItemID,CountryID,ChannelDemandID,PriceType);");
/* This code inserts duplicates in v1.1.0 but not in 1.0.2 */
duck.Execute($@"INSERT OR IGNORE INTO Sales (CalendarID,ItemID,CountryID,ChannelDemandID,PriceType)
SELECT *
FROM
    range(202505::INTEGER,202625::INTEGER) Calendar(CalendarID) 
    CROSS JOIN (VALUES (103),(550),(9693)) Items(ItemID)
    CROSS JOIN (VALUES (1)) Countries(CountryID)
    CROSS JOIN (VALUES (3),(5),(7),(9)) Chanels(ChannelDemandID)
    CROSS JOIN (SELECT DISTINCT PriceType FROM Sales) AS PriceTypes");

[tartufella]

Sorry, to answer your question, the CLI works as expected. If I comment out the code that inserts into the Sales table I will end up with a duckdb file without duplicates. If I then open this file in the CLI and run the same code there it will fail as expected.

So if I run:

INSERT OR IGNORE INTO Sales (CalendarID,ItemID,CountryID,ChannelDemandID,PriceType)
SELECT *
FROM
    range(202505::INTEGER,202625::INTEGER) Calendar(CalendarID) 
    CROSS JOIN (VALUES (103),(550),(9693)) Items(ItemID)
    CROSS JOIN (VALUES (1)) Countries(CountryID)
    CROSS JOIN (VALUES (3),(5),(7),(9)) Chanels(ChannelDemandID)
    CROSS JOIN (SELECT DISTINCT PriceType FROM Sales) AS PriceTypes

in the CLI instead of .NET then it works as expected.

[Giorgi]

Does index creation fail in 1.0.2 version of the .Net library?

[Giorgi]

I downloaded your database, dropped the index and tried to add it from .NET:

What does your process look like? Do you first insert the data and then create an index? Are you doing it from multiple threads, by any chance? Or do you execute it inside a transaction?

[tartufella]

Morning @Giorgi, thanks for this. What tool were you using to create your demo, perhaps I can mock up something similar to show the issue, failing that I can create a small Github project.

If I open the file as created by .NET v1.1.0 in the CLI I see 2 indexes for Sales and Stocks.

The unique index exists on the Sales table, but there are also duplicates. However, because there is a unique index I cannot add a duplicate.

This is the strange issue with v1.1.0 where it has both created the index but also allowed duplicates.

In answer to your question, yes, I do first insert data into the table. The Sales table is created in the database from a parquet file. I then create the unique index before adding some extra rows that need to exists if the do not already.

using (var duck = new DuckDB.NET.Data.DuckDBConnection($"Data Source={fiDuckDb.FullName}"))
{
    duck.Open();
    duck.Execute("INSTALL sqlite; LOAD sqlite");
    duck.Execute($"ATTACH '{fiSqliteDb.FullName}' AS sqlite_db (TYPE SQLITE);");
    duck.Execute($"CREATE TABLE Notes AS SELECT * FROM read_parquet('{fiNotesUser.FullName}');");
    duck.Execute($"CREATE TABLE SalesActuals AS SELECT * FROM read_parquet('{fiSalesActualsForecast.FullName}');");
    duck.Execute($"CREATE TABLE SalesUsers AS SELECT * FROM read_parquet('{fiSalesUsers.FullName}');");
    duck.Execute($"CREATE TABLE Sales AS SELECT * FROM SalesActuals sa FULL JOIN SalesUsers users USING(CalendarID,ItemID,CountryID,ChannelDemandID,PriceType)");
    duck.Execute($"CREATE TABLE SellingPricesActual AS SELECT * FROM read_parquet('{fiSellingPricesActual.FullName}') WHERE ItemID IN ({itemIds});");
    duck.Execute($"CREATE TABLE SellingPricesUser AS SELECT * FROM read_parquet('{fiSellingPricesUser.FullName}');");
    duck.Execute(@"CREATE TABLE SellingPrices AS SELECT * FROM SellingPricesActual FULL JOIN SellingPricesUser USING(CalendarID,ItemID,CountryID,ChannelID) ORDER BY 1 DESC");
    duck.Execute($"CREATE TABLE StocksActual AS SELECT * FROM read_parquet('{fiStocksActual.FullName}');");
    duck.Execute($"CREATE TABLE StocksUser AS SELECT * FROM read_parquet('{fiStocksUser.FullName}');");
    duck.Execute($"CREATE TABLE StocksActualUser AS SELECT * FROM StocksActual sa FULL JOIN StocksUser su USING(CalendarID,ItemID,CountryID,ChannelID)");
    duck.Execute(@$"CREATE TABLE Stocks AS WITH prices AS(
                                SELECT 
                                p1.CalendarID,
                                p1.LyID,
                                p1.ItemID,
                                p1.CountryID,
                                p1.ChannelID,
                                p1.FPSellingPriceActual,
                                p1.FPSellingPriceUser,
                                p1.PromoSellingPriceActual,
                                p1.PromoSellingPriceUser,      
                                p2.FPSellingPriceActual AS FPSellingPriceLY,
                                p2.PromoSellingPriceActual AS PromoSellingPriceLY
                                FROM SellingPrices p1 
                                LEFT JOIN SellingPrices p2 on 
                                p2.CalendarID = p1.LyID AND p2.ItemID = p1.ItemID AND p2.CountryID = p1.CountryID AND p2.ChannelID = p1.ChannelID
                                )
                                SELECT 
                                    COALESCE(s.CalendarID,p.CalendarID) AS CalendarID,
                                    COALESCE(s.ItemID,p.ItemID) AS ItemID,
                                    COALESCE(s.CountryID,p.CountryID) AS CountryID,
                                    COALESCE(s.ChannelID,p.ChannelID) AS ChannelID,
                                    p.* EXCLUDE(CalendarID,ItemID,CountryID,ChannelID,LyID),
                                    s.* EXCLUDE(CalendarID,ItemID,CountryID,ChannelID)
                                FROM StocksActualUser s 
                                FULL JOIN prices p 
                                USING(CalendarID,ItemID,CountryID,ChannelID);");
    /* Create a unique index on Sales table */
    duck.Execute("CREATE UNIQUE INDEX sales_idx ON Sales (CalendarID,ItemID,CountryID,ChannelDemandID,PriceType);");
    /* Ensure that the neccessary rows exist in the Sales table if they do not already 
    * In v1.1.0 this statement will create duplicates in the Sales table even though the index exists.
    * In v1.0.2 this statement will NOT create duplicates
    */
    duck.Execute($@"INSERT OR IGNORE INTO Sales (CalendarID,ItemID,CountryID,ChannelDemandID,PriceType)
                    SELECT *
                    FROM
                        range({calendar.BTAStartWeek.ID}::INTEGER,{query.To}::INTEGER) Calendar(CalendarID) 
                        CROSS JOIN (VALUES {itemIdValues}) Items(ItemID)
                        CROSS JOIN (VALUES {countryIdValues}) Countries(CountryID)
                        CROSS JOIN (VALUES {channelDemandIdValues}) Chanells(ChannelDemandID)
                        CROSS JOIN (SELECT DISTINCT PriceType FROM Sales) AS PriceTypes;");
    duck.Execute("CREATE UNIQUE INDEX stocks_idx ON Stocks (CalendarID,ItemID,CountryID,ChannelID);");
    duck.Execute($@"INSERT OR IGNORE INTO Stocks (CalendarID,ItemID,CountryID,ChannelID)
                    SELECT *
                    FROM 
                        range({calendar.BTAStartWeek.ID}::INTEGER,{query.To}::INTEGER) Calendar(CalendarID)
                        CROSS JOIN (VALUES {itemIdValues}) Items(ItemID)
                        CROSS JOIN (VALUES {countryIdValues}) Countries(CountryID)
                        CROSS JOIN (VALUES {channelIdValues}) Channels(ChannelID);");

[tartufella]

Also, just to clarify, no multi threading and also no transactions, just sequential statements.

[tartufella]

Hi @Giorgi I have created the issue here DuckDB Issue

If you use v1.0.0 you will not see any duplicates in the console

If you use v1.1.0 you will se duplicates

I hope this helps and I also wonder if it's not something to do with Dapper?

Thanks

[Giorgi]

https://github.com/tartufella/DuckDBIssue gives me 404.

[tartufella]

Ahh apologies @Giorgi, forgot to set it to public, that should be available now.