Checking and preventing similar strings while insertion in MySQL

Question

Brief info

I have 3 tables:

Set:

id
name

SetItem:

set_id
item_id
position

TempSet:

id

I have a function that generates new random combinations from Item table. Basically, always after successful generation, I create a new row in Set table, get it's id and add all item ids into SetItem table.

Problem

Every time before generating new combination I truncate the TempSet table, fill new item ids into this table and check for similarity percentage by comparing with previous combinations in SetItem table. if new combination similarity greater or equal to 30%, I need to prevent this combination and re-generate new combination.

Similarity means - existence of elements on previously generated combinations. So, the idea is:

if more than 3 element of newly generated set repeated on some previously generated set, prevent it and try to generate another combination.

Here is function that generates new combinations:

  CREATE DEFINER = `root` @`localhost` FUNCTION `gen_uniq_perm_by_kw` (
    comboSize INT ( 5 ),
    tries INT ( 3 ) 
    ) RETURNS text CHARSET utf8 SQL SECURITY INVOKER BEGIN
    iterat :
    LOOP
        DELETE 
        FROM
            `TempSet`;
        INSERT INTO `TempSet` ( `id` ) (
            SELECT
                `i`.`id` 
            FROM
                `Item` AS `i`
            ORDER BY
                RAND( ) 
                LIMIT comboSize 
            );
        IF
            (
            SELECT
                1 
            FROM
                `SetItem` 
            GROUP BY
                `set_id` 
            HAVING
                sum(
                CASE
                        
                        WHEN EXISTS (
                        SELECT
                            id 
                        FROM
                            `TempSet` 
                        WHERE
                            `id` = `item_id` 
                            LIMIT 1 
                            ) THEN
                            1 ELSE 0 
                        END 
                        ) / count( 1 ) * 100 >= 30 
                        LIMIT 1 
                        ) < 1 THEN
                        RETURN ( SELECT GROUP_CONCAT( id SEPARATOR '-' ) FROM `TempSet` );
                    
                END IF;
                
                SET tries := tries - 1;
                IF
                    tries = 0 THEN
                        RETURN NULL;
                    
                END IF;
                
            END LOOP iterat;
        
END

When I test it, even when newly generated combination's elements doesn't exist in any other previously generated combination, it returns null as a result.

My question is, what am I doing wrong?

Answer 1

My question is, what am I doing wrong?

You don't have any data in your SetItem table.

Edit: You commented that this is wrong; you do have 300k rows in SetItem.

---

I got an example working. It appears that you can't use a scalar subquery like you're doing. I got it working this way:

DROP FUNCTION IF EXISTS gen_uniq_perm_by_kw;
DELIMITER ;;
CREATE DEFINER = `root` @`localhost` FUNCTION `gen_uniq_perm_by_kw` (comboSize INT, tries INT) RETURNS text CHARSET utf8 SQL SECURITY INVOKER
BEGIN
        iterat :
        LOOP
                DELETE FROM `TempSet`;

                INSERT INTO `TempSet` (`id`)
                SELECT `i`.`id` FROM `Item` AS `i` ORDER BY RAND() LIMIT comboSize;

                IF EXISTS(
                        SELECT set_id,
                                SUM(CASE WHEN EXISTS (SELECT id FROM `TempSet` WHERE `id` = `item_id` LIMIT 1) THEN 1 ELSE 0 END) AS group_sum,
                                COUNT(*) AS group_count
                        FROM `SetItem`
                        GROUP BY `set_id`
                        HAVING group_sum * 10 / group_count < 3
                ) THEN
                        RETURN (SELECT GROUP_CONCAT(id SEPARATOR '-') FROM `TempSet`);
                END IF;

                SET tries = tries - 1;

                IF tries = 0 THEN
                        RETURN NULL;
                END IF;
        END LOOP iterat;
END

I also got it working in a simpler way, without using the SUM and extra subquery:

DROP FUNCTION IF EXISTS gen_uniq_perm_by_kw;
DELIMITER ;;
CREATE DEFINER = `root` @`localhost` FUNCTION `gen_uniq_perm_by_kw` (comboSize INT, tries INT) RETURNS text CHARSET utf8 SQL SECURITY INVOKER
BEGIN
        iterat :
        LOOP
                DELETE FROM `TempSet`;

                INSERT INTO `TempSet` (`id`)
                SELECT `i`.`id` FROM `Item` AS `i` ORDER BY RAND() LIMIT comboSize;

                IF EXISTS(
                        SELECT s.set_id,
                                COUNT(t.id) AS group_matches,
                                COUNT(*) AS group_count
                        FROM SetItem AS s LEFT OUTER JOIN TempSet AS t ON t.id = s.item_id
                        GROUP BY s.set_id
                        HAVING group_matches * 10 / group_count < 3
                ) THEN
                        RETURN (SELECT GROUP_CONCAT(id SEPARATOR '-') FROM `TempSet`);
                END IF;

                SET tries = tries - 1;

                IF tries = 0 THEN
                        RETURN NULL;
                END IF;
        END LOOP iterat;
END