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:
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:MySQL 5.0.13-rc-nt OS:Microsoft Windows (Windows Xp)
Assigned to: Bugs System

[21 Nov 2005 7:38] Barak Mery
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 : 
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.

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:

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

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

  DECLARE FilterEndPosition INTEGER;
  DECLARE ConditionEndPosition INTEGER;
  DECLARE ConditionStr VARCHAR(5000)  DEFAULT '';
  DECLARE ConditionCount INTEGER;
  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');


              SET ConditionType = 2;
              SET SelectStr = '';

            END IF;

           WHEN 2 THEN

            IF ConditionType=1 THEN

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


              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;


      SET CheckFilterState=0;

    END IF;

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

UNTIL CheckFilterState=0 END REPEAT;

SELECT FinalQuery;

END $$


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

Suggested fix:
I have'nt found no workaround
[21 Nov 2005 17:37] Valerii Kravchuk
Thank you for a problem report. Let me check your procedure... UNION cause implicit sorting, by the way.
[21 Nov 2005 18:14] Barak Mery
whether i have a problem or not in my sp, there should be other error message.
[25 Nov 2005 14:23] Valerii Kravchuk
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.
[31 Jan 2006 15:53] Per-Erik Martin
This has been fixed, the error message is now the less misleading:

ERROR 1109 (42S02): Unknown table 'c' in field list
[31 Jan 2006 16:00] Bugs System
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:

[1 Feb 2006 14:53] Per-Erik Martin
Pushed to bk 5.0.19
[2 Feb 2006 18:20] Mike Hillyer
Documented in 5.0.19 changelog:

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