MySQL bug

[DrDoc]

When hardcoding numbers in a SELECT statement:

SELECT 'foo', 'bar', 'baz', '1'

the results will be skewed. The above query would return:

foo 1 baz 1

when the expected result is:

foo bar baz 1

Simply giving the number an alias solves the problem:

SELECT 'foo', 'bar', 'baz', '1' AS widget

[coopster]

Which version? Works fine in both 3.23.58 as well as 4.1.1.

mysql> select 'foo', 'bar', 'baz', '1'; 
+-----+-----+-----+---+ 
¦ foo ¦ bar ¦ baz ¦ 1 ¦ 
+-----+-----+-----+---+ 
¦ foo ¦ bar ¦ baz ¦ 1 ¦ 
+-----+-----+-----+---+ 
1 row in set (0.00 sec)

[DrDoc]

4.0.x

So it must've been broken in 4.0, and fixed in 4.1 :)

[coopster]

No, hold on DrDoc, I'm with you. I ran that first test from the command line. Running it from a script returns things a bit differently. Check it out...(running against the same MySQL versions again, except this time through a PHP script):

$row = mysql_query("SELECT 'foo', 'bar', 'baz', '1'"); 
print_r(mysql_fetch_row($row)); 
mysql_data_seek($row, 0); 
print_r(mysql_fetch_assoc($row)); 
mysql_data_seek($row, 0); 
print_r(mysql_fetch_array($row)); 
// 
// Returns: 
// 
Array 
( 
 [0] => foo 
 [1] => bar 
 [2] => baz 
 [3] => 1 
) 
Array 
( 
 [foo] => foo 
 [bar] => bar 
 [baz] => baz 
 [1] => 1 
) 
Array 
( 
 [0] => foo 
 [foo] => foo 
 [1] => 1 
 [bar] => bar 
 [2] => baz 
 [baz] => baz 
 [3] => 1 
)

Explanation of what is happening:

mysql_fetch_array [php.net]

mysql_fetch_array() is an extended version of mysql_fetch_row(). In addition to storing the data in the numeric indices of the result array, it also stores the data in associative indices, using the field names as keys.

If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you must use the numeric index of the column or make an alias for the column. For aliased columns, you cannot access the contents with the original column name (by using 'field' in this example).