MySQL Bugs: #15091: Sp Returns Unknown error in order clause....and there is no order by clause

Bug #15091	Sp Returns Unknown error in order clause....and there is no order by clause
Submitted:	21 Nov 2005 7:38	Modified:	2 Feb 2006 18:20
Reporter:	Barak Mery	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	MySQL 5.0.13-rc-nt	OS:	Windows (Windows Xp)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
I have A very Weird problem with some SP.

I have a sp that manipulate strings. 
The sp Get some data in the format of Rows and Fields. 
For exsample : 
'111|222|333|444@555|666|777|888' 
The @ seperates Rows (I call it filter). 
The | seperates Fields  (I call it Condition). 

Each row always contain 4 fields. 
The number of row is not known. 

The sp Manipulate the string and Create A string of a Query.
Each Row(filter) is a select clause combined with UNION STATEMENT with the other rows.

Im using two REPEAT STATEMENTS.
1. for Seperating Rows
2. for Seperating Fields

When I CALL string_manipulation('1|2,3,4|11,22,13|10,20,30')
I get error :
"Unknown table 'c' in Order clause"

The strange thing is that i dont have any order clause in my sp,
and i'm not running any real query.

I'm using mysql 5.0.13, innodb, windows xp

How to repeat:
DELIMITER $$

DROP PROCEDURE IF EXISTS `bcm`.`string_manipulation` $$
CREATE PROCEDURE `string_manipulation`(ExportList VARCHAR(5000))
BEGIN

  #CALL string_manipulation('1|2,3,4|11,22,13|10,20,30')

  DECLARE FilterEndPosition INTEGER;
  DECLARE FilterStr VARCHAR(5000) DEFAULT '';
  DECLARE TempStr VARCHAR(5000) DEFAULT '';
  DECLARE ConditionEndPosition INTEGER;
  DECLARE ConditionStr VARCHAR(5000)  DEFAULT '';
  DECLARE CheckFilterState INTEGER DEFAULT 1;
  DECLARE ConditionCount INTEGER;
  DECLARE FilterCount INTEGER DEFAULT 1;
  DECLARE SelectStr VARCHAR(6000) DEFAULT ' ';
  DECLARE ConditionType INTEGER;
  DECLARE FinalQuery VARCHAR(7000) DEFAULT ' ';

SET ExportList = CONCAT(ExportList,'@') ;

REPEAT  #rows Loop

    SET FilterEndPosition = INSTR(ExportList,'@'); #finds the First Row End Position

    SET FilterStr = LEFT(ExportList,FilterEndPosition-1); #Put the first Row String into variable

      SET ConditionCount = 1; #Initialize the field counter . the loop will run 4 times
                        #Ther are onlyqalways 4 fields

      SET FilterStr = CONCAT(FilterStr,'|');

      REPEAT   #fields Loop

        SET ConditionEndPosition = INSTR(FilterStr,'|');  #finds the First Field End Position

        SET ConditionStr = LEFT(FilterStr,ConditionEndPosition-1); #Put the first field String into variable

        CASE ConditionCount

          WHEN 1 THEN

            IF ConditionStr=1 THEN

              SET ConditionType = 1;
              SET SelectStr = CONCAT('SELECT ',FilterCount,' FilterCount, cg.ClientId, cg.ClientOperatorId FROM ClientsGroups cg LEFT OUTER JOIN Clients c ON c.Clientid=cg.ClientId');

            ELSE

              SET ConditionType = 2;
              SET SelectStr = '';

            END IF;

           WHEN 2 THEN

            IF ConditionType=1 THEN

              SET SelectStr = CONCAT(SelectStr,' WHERE cg.GroupId IN (',ConditionStr,')');

            ELSE

              SET SelectStr = CONCAT(SelectStr,'');

            END IF;

           WHEN 3 THEN

             IF LENGTH(ConditionStr) >0 THEN

               SET SelectStr = CONCAT(SelectStr,' AND cg.GroupId NOT IN (',ConditionStr,')');

             END IF;

           WHEN 4 THEN

             SET SelectStr = CONCAT(SelectStr,' AND ', c.OperatorId, 'IN (', ConditionStr, ')');

        END CASE;

        SET TempStr =  FilterStr;
        SET FilterStr =  '';
        SET FilterStr = RIGHT(TempStr,LENGTH(TempStr) - ConditionEndPosition);  # row string without the field already handled

        SET ConditionCount = ConditionCount + 1;

      UNTIL ConditionCount=5 END REPEAT;

   SET ExportList = RIGHT(ExportList,LENGTH(ExportList)-FilterEndPosition);  # Alldata string without the Row already handled

    IF INSTR(ExportList,'@') > 0 THEN

      SET CheckFilterState=1;
      SET FilterCount = FilterCount + 1;

    ELSE

      SET CheckFilterState=0;

    END IF;

    IF LENGTH(FinalQuery) > 0 THEN
      SET FinalQuery = CONCAT(' UNION ',FinalQuery,' ',SelectStr);
    ELSE
      SET FinalQuery = CONCAT(FinalQuery,' ',SelectStr);
    END IF;

UNTIL CheckFilterState=0 END REPEAT;

SELECT FinalQuery;

END $$

DELIMITER ;

CALL string_manipulation('1|2,3,4|11,22,13|10,20,30');

Suggested fix:
I have'nt found no workaround

Thank you for a problem report. Let me check your procedure... UNION cause implicit sorting, by the way.

whether i have a problem or not in my sp, there should be other error message.

The problem in your procedure is in the following lines:

SET SelectStr = CONCAT(SelectStr,' AND ', c.OperatorId, 'IN (',
ConditionStr, ')');

You have to change them to:

SET SelectStr = CONCAT(SelectStr,' AND c.OperatorId IN (',
ConditionStr, ')');

and it will work. 

But you are right, the error message we get (verified on 5.0.15-nt and 5.0.17-BK, ChangeSet@1.1991, 2005-11-22 16:01:04+01:00, on Linux):

mysql> CALL string_manipulation('1|2,3,4|11,22,13|10,20,30');
ERROR 1109 (42S02): Unknown table 'c' in order clause

is incorrect and misleading, because there is no ORDER clause anywhere in the text.

This has been fixed, the error message is now the less misleading:

ERROR 1109 (42S02): Unknown table 'c' in field list

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1951

Pushed to bk 5.0.19

Documented in 5.0.19 changelog:

      <listitem>
        <para>
          Message for error 1109 changed from <literal>Unknown table
            ... in order clause</literal> to <literal>Unknown table '...
              in field list</literal>. (Bug #15091)
        </para>
      </listitem>