Select all possible unique combinations of values, including variable lengths

[hessodreamy]

Say that a column in a result set has unique values 1,2 and 3, how would I return all the possible combinations of these values, including lists of different lengths? eg
1
2
3
1,2
1,3
2,3
1,2,3

A sample query: SELECT attribute_item.item_id, attribute_item.attribute_id FROM item_listing JOIN attribute_item ON item_listing.item_id = attribute_item.item_id WHERE item_listing.listing_id = 250592

gives me:

item_id  attribute_id  
-------  --------------
 250021               1
 250021               2
 250021               3
 250022               1
 250022               2
 250022               3

but using GROUP_CONCAT on attribute_id just gives me 1,2,3.

I did attempt to do this in a procedure with dynamically-generated sql, but as I’m trying to use it in a trigger mysql (MariaDB 5.5.47) won’t allow it.

[DarthGuido]

I think you’ll have to do this in your scripting language (PHP or whatever) ?

[Mittineague]

@DarthGuido

I think you’ll have to do this in your scripting language (PHP or whatever) ?

I think so.

I don’t know of any database that can give all permutations of possible values as a query result.

For that matter I don’t know of any language that has a native function that produces permutations from an array either.

I think this will require a custom recursive script.

[hessodreamy]

Recursive is fine, as I can do that in a procedure, it’s the dynamic sql that’s the barrier.

[DarthGuido]

What problem are you having with the dynamic sql? Both of the queries you posted give a result that you can use to elaborate and get the permutations, don’t they?

[hessodreamy]

The issue was that dynamic is not allowed in triggers, or in procedures called by triggers.
But I did manage to come up with a recursive solution which didn’t require dynamic Sql:

DROP PROCEDURE IF EXISTS createListCombos;
DELIMITER ;;
CREATE PROCEDURE createListCombos(IN numbers VARCHAR(512))
BEGIN
	DECLARE done TINYINT DEFAULT 0;
	DECLARE attributeListEntry VARCHAR(512) DEFAULT "";
	DECLARE attributeListCursor CURSOR FOR SELECT combo FROM attributeCombos;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

	DROP TEMPORARY TABLE IF EXISTS attributeCombos;
	CREATE TEMPORARY TABLE attributeCombos
	(
		`combo` VARCHAR(512) PRIMARY KEY
	);

	SET @delimiter = ",";
	SET @fieldCount = (CHAR_LENGTH(numbers) - CHAR_LENGTH(REPLACE(numbers,@delimiter,"")))+1;
	SET @currentDepth=0;
	
	
	depthloop: LOOP
		SET @currentDepth = @currentDepth+1;
		IF @currentDepth > @fieldCount THEN LEAVE depthloop; END IF;
		SET @numbersindex = 0;
		numbersloop: LOOP
			SET @numbersindex = @numbersindex+1;
			
			IF @numbersindex > @fieldCount THEN LEAVE numbersloop; END IF;
			SET @output = CONCAT(@output ,"\n@number index = ",@numbersindex);
			#get the number from the index of the delimited list
			SET @n = SUBSTRING_INDEX(SUBSTRING_INDEX(numbers,",",@numbersindex),@delimiter,-1);
			IF @currentDepth = 1 THEN 
				INSERT IGNORE INTO attributeCombos VALUES(@n);
			ELSE
				#foreach output append this number up TO a MAX of maxDepth
				OPEN attributeListCursor;
				SET done=0;
				attributeListLoop: LOOP
					FETCH attributeListCursor INTO attributeListEntry;
					IF done THEN
						LEAVE attributeListLoop;
					END IF;
					#get LAST digit
					SET @lastNo = SUBSTRING_INDEX(attributeListEntry,@delimiter,-1);
					
					IF CAST(@n AS UNSIGNED) > CAST(@lastNo AS UNSIGNED)
					THEN
						
						SET @thisEntry = CONCAT(attributeListEntry,@delimiter,@n);
						INSERT IGNORE INTO attributeCombos VALUES(@thisEntry);
						SET @myDepth = CHAR_LENGTH(@thisEntry) - CHAR_LENGTH(REPLACE(@thisEntry,@delimiter,""));
						IF @myDepth>@currentDepth THEN ITERATE attributeListLoop; END IF;#???
					END IF;
				END LOOP;# end attributeListLoop
				CLOSE attributeListCursor;
			END IF;
		
		END LOOP; #end numbersloop
	END LOOP; #end depthloop

END ;;
DELIMITER ;

So now CALL createListCombos(“1,2,4,3”) gives me 1 1,2 1,2,3 1,2,3,4 1,2,4 1,3 1,3,4 1,4 2 2,3 2,3,4 2,4 3 3,4 4

Me 1, MySql 0