MySQL Bugs: #64339: Query should not duplicate results

Bug #64339	Query should not duplicate results
Submitted:	15 Feb 2012 13:07	Modified:	16 Mar 2012 12:39
Reporter:	Roberto Caiola	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The following query should not repeat the results.

The @x assignment is being done to suppress the inner assignment but it duplicates the records.

How to repeat:
SET @ids = '';

SET @x := 0 IN (
	SELECT @ids := CONCAT(@ids,CAST(id_agency AS CHAR),',') as id
	FROM (
		SELECT 1 as id_agency
		UNION
		SELECT 2 as id_agency
		UNION
		SELECT 3 as id_agency
	) as agencies
);

SET @ids := LEFT(@ids,LENGTH(@ids)-1);

SELECT @ids as ids;

Query should not duplicate results

Here I can only remind you the manual (http://dev.mysql.com/doc/refman/5.1/en/user-variables.html):

"As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server."

Thank you for your comments but I guess I solved my problem! :)

After reading a lot of forums, help, manuals, etc..
I dont know how to do it with a SET instruction, but using "strange" SELECT and a SET, I ended up with the following statement:

SET @ids = '';

SET @x := (
	SELECT 0 FROM (
		SELECT @ids := CONCAT(@ids,CAST(id_agency AS CHAR),',') as id
		FROM (
			SELECT 1 as id_agency
			UNION
			SELECT 22 as id_agency
			UNION
			SELECT 3 as id_agency
		) as agencies
	) as t
	LIMIT 1
);

SET @ids := LEFT(@ids,LENGTH(@ids)-1);

SELECT @ids as ids;

This works as expected, I am using a CSV without a temporary table to select records in a CSV manner.

A really unique world wide solution ;)

Final product:

SET @ids = '';

SET @x := (
	SELECT 0 FROM (
		SELECT @ids := CONCAT(@ids,CAST(id_entity AS CHAR),',') as id
		FROM reports_id_entities
		WHERE datasource = 'rpt_QualityDaily_sub_Recap_dsTimekeepTractionsRegular'
	) as t
	LIMIT 1
);

SET @ids := LEFT(@ids,LENGTH(@ids)-1);

-- SELECT @ids as ids;

SELECT * FROM agencies WHERE FIND_IN_SET(id_agency,@ids) <> 0;

Works like a treat! :)

Again, not sure what exact new feature you ask to implement here, and why. Existing features allow to get the results you need.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".