List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:April 28 2000 8:21pm
Subject:Re: ORDER BY converts text to integer???
View as plain text  
jackmc-mysql@stripped wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
>
>     I have a query that works if ORDER BY is left off.  If I add ORDER BY,
> then one of the columns (NOT mentioned in the ORDER BY) gets converted to
> an integer.  If it does not begin with a digit, it is 0, otherwise the
> initial integer is returned.
>     Here is an example of how to recreate it (I am using 3.22.26a, and
> cannot upgrade to any higher version because of library incompatibilities
> introduced in 3.22.27).  The table example has three columns.  The first
> column identifies an entry.  The second column represents the type of
> entry, and the third is an index into another table where data is found.
> The second column determines which table is used.  If it is 1, then example1
> should be used.  If it is 2, then example 2 should be used.
>     The first SELECT below works just fine.  The second SELECT is identical
> to the first, except an ORDER BY is added.  When this is done, the text fields
> become integers...
>
> CREATE TABLE example
>  (
>   a          int unsigned       NOT NULL,
>   b          int unsigned       NOT NULL,
>   c          int unsigned       NOT NULL,
>   UNIQUE(a),
>   INDEX(b),
>   INDEX(c)
>  );
>
> CREATE TABLE example1
>  (
>   c          int unsigned       NOT NULL,
>   i          int unsigned       NOT NULL,
>   INDEX(c)
>  );
>
> CREATE TABLE example2
>  (
>   c          int unsigned       NOT NULL,
>   v          varchar(64),
>   INDEX(c)
>  );
>
> INSERT INTO example VALUES (1,1,1);
> INSERT INTO example VALUES (2,1,2);
> INSERT INTO example VALUES (3,2,1);
> INSERT INTO example VALUES (4,2,2);
>
> INSERT INTO example1 VALUES (1,50);
> INSERT INTO example1 VALUES (2,25);
>
> INSERT INTO example2 VALUES (1,'123 Park Place');
> INSERT INTO example2 VALUES (2,'453 Boardwalk');
>
> SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
> FROM      example
> LEFT JOIN example1 USING(c)
> LEFT JOIN example2 ON example2.c = example.c;
>
> SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
> FROM      example
> LEFT JOIN example1 USING(c)
> LEFT JOIN example2 ON example2.c = example.c
> ORDER BY a;
>

The result set is a table.
A column in a table can be only one type, in this case int or char.
I suspect the first generated row would determine the type for the rest of the
rows.


Thread
ORDER BY converts text to integer???jackmc-mysql28 Apr
  • Re: ORDER BY converts text to integer???Gerald Clark29 Apr
    • Re: ORDER BY converts text to integer???jackmc-mysql29 Apr
      • Re: ORDER BY converts text to integer???Benjamin Pflugmann30 Apr
        • Re: ORDER BY converts text to integer???jackmc-mysql30 Apr
          • Re: ORDER BY converts text to integer???Thimble Smith1 May