Different behavior when using aliases in 4.1.21 vs. 4.1.22 version

Hi All,

These are the commands I'm using to create the table and insert 6
records into it:
create table `my_test`.`numbers` ( `number` int (10) NULL )
insert into `numbers` (`number`) values ( '1')
insert into `numbers` (`number`) values ( '2')
insert into `numbers` (`number`) values ( '3')
insert into `numbers` (`number`) values ( '1')
insert into `numbers` (`number`) values ( '2')
insert into `numbers` (`number`) values ( '3')

This is command A:
SELECT number from numbers
GROUP by number;

This is command B:
SELECT `my number`.`number` as '[my numbers].number'
FROM numbers as `my number`
GROUP BY '[my numbers].number';

The difference between these two commands is only the usage of
aliases.

When running command A or command B on MYSQL 4.1.21 we get the
following result (this is the expected result):
1
2
3

When running command A on MYSQL 4.1.22 we also get the above expected
result.

On the contrary, when running command B on MYSQL 4.1.22 we get the
following result (which is wrong):
1

I found that when changing the suffix of command B to: GROUP BY `[my
numbers].number`; than command B will also work correctly on MYSQL
4.1.22

What i don't understand is when should i use `` and when '' when using
aliases?
E.g.: why does the expression: as '[my numbers].number'
Works with '', while the expression: GROUP BY '[my numbers].number';
Must have `` to work correct?

In the MYSQL documentation, it's not clear what are the exact rules
for defining and using aliases, for both columns selections and
tables.

Thanks a lot

CS student wrote:

> What i don't understand is when should i use `` and when '' when using
> aliases?
> E.g.: why does the expression: as '[my numbers].number'
> Works with '', while the expression: GROUP BY '[my numbers].number';
> Must have `` to work correct?
> In the MYSQL documentation, it's not clear what are the exact rules
> for defining and using aliases, for both columns selections and
> tables.

As I have understood, '' is used for "strings", as when you define a new
alias, it's an string and you use `` for enclose column/alias/table names that
you use them.



--

//Aho

If I understood you correctly, than I can write the following:
FROM numbers as 'my number'
But unfortunately it will yell that it's a syntax MYSQL error
Thus, I must write: FROM numbers as `my number`
On the other hand, i can write: SELECT `my number`.`number` as '[my
numbers].number'
I.e., it's not clear when the alias definition (the part that comes
after the AS keyword) requires ' ' and when does it require ` `?
Thanks a lot


J.O. Aho wrote:
> CS student wrote:
>
> > What i don't understand is when should i use `` and when '' when using
> > aliases?
> > E.g.: why does the expression: as '[my numbers].number'
> > Works with '', while the expression: GROUP BY '[my numbers].number';
> > Must have `` to work correct?
> > In the MYSQL documentation, it's not clear what are the exact rules
> > for defining and using aliases, for both columns selections and
> > tables.
>
> As I have understood, '' is used for "strings", as when you define a new
> alias, it's an string and you use `` for enclose column/alias/table names that
> you use them.
>
>
>
> --
>
> //Aho

CS student wrote:
> Hi All,
>
> These are the commands I'm using to create the table and insert 6
> records into it:
> create table `my_test`.`numbers` ( `number` int (10) NULL )
> insert into `numbers` (`number`) values ( '1')
> insert into `numbers` (`number`) values ( '2')
> insert into `numbers` (`number`) values ( '3')
> insert into `numbers` (`number`) values ( '1')
> insert into `numbers` (`number`) values ( '2')
> insert into `numbers` (`number`) values ( '3')
>
> This is command A:
> SELECT number from numbers
> GROUP by number;
>
> This is command B:
> SELECT `my number`.`number` as '[my numbers].number'
> FROM numbers as `my number`
> GROUP BY '[my numbers].number';
>
> The difference between these two commands is only the usage of
> aliases.
>
> When running command A or command B on MYSQL 4.1.21 we get the
> following result (this is the expected result):
> 1
> 2
> 3
>
> When running command A on MYSQL 4.1.22 we also get the above expected
> result.
>
> On the contrary, when running command B on MYSQL 4.1.22 we get the
> following result (which is wrong):
> 1
>
> I found that when changing the suffix of command B to: GROUP BY `[my
> numbers].number`; than command B will also work correctly on MYSQL
> 4.1.22
>
> What i don't understand is when should i use `` and when '' when using
> aliases?
> E.g.: why does the expression: as '[my numbers].number'
> Works with '', while the expression: GROUP BY '[my numbers].number';
> Must have `` to work correct?
>
> In the MYSQL documentation, it's not clear what are the exact rules
> for defining and using aliases, for both columns selections and
> tables.
>
> Thanks a lot
>

Strings are required to be enclosed with single quotes. This is part of
the SQL standard. You use them in places like the WHERE clause of a
SELECT or UPDATE statement, or data in INSERT or UPDATE statements. You
do not use them in table or column names or aliases.

The use of a back-tickey is a MySQL extension that I do not recommend
using. They are only required when you have a table or column name
which is the same as a keyword (i.e. GROUP, KEY, etc.). It's not a good
idea to use keywords for table/column names, and is not transportable to
other databases.

My recommendation is to stay away from the back-tickey all together and
not use reserved words. It will make you life a whole lot easier.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Thanks for your reply.
Can you please tell how should i rewrite the following expression in
case i will avoid using back ticks:
SELECT `my number`.`number` as '[my numbers].number'
FROM numbers as `my number`
GROUP BY '[my numbers].number';

I'm asking since without the back ticks, the following sub-expression:
`my number`.`number`
isn't valid in MYSQL.


Jerry Stuckle wrote:
> CS student wrote:
> > Hi All,
> >
> > These are the commands I'm using to create the table and insert 6
> > records into it:
> > create table `my_test`.`numbers` ( `number` int (10) NULL )
> > insert into `numbers` (`number`) values ( '1')
> > insert into `numbers` (`number`) values ( '2')
> > insert into `numbers` (`number`) values ( '3')
> > insert into `numbers` (`number`) values ( '1')
> > insert into `numbers` (`number`) values ( '2')
> > insert into `numbers` (`number`) values ( '3')
> >
> > This is command A:
> > SELECT number from numbers
> > GROUP by number;
> >
> > This is command B:
> > SELECT `my number`.`number` as '[my numbers].number'
> > FROM numbers as `my number`
> > GROUP BY '[my numbers].number';
> >
> > The difference between these two commands is only the usage of
> > aliases.
> >
> > When running command A or command B on MYSQL 4.1.21 we get the
> > following result (this is the expected result):
> > 1
> > 2
> > 3
> >
> > When running command A on MYSQL 4.1.22 we also get the above expected
> > result.
> >
> > On the contrary, when running command B on MYSQL 4.1.22 we get the
> > following result (which is wrong):
> > 1
> >
> > I found that when changing the suffix of command B to: GROUP BY `[my
> > numbers].number`; than command B will also work correctly on MYSQL
> > 4.1.22
> >
> > What i don't understand is when should i use `` and when '' when using
> > aliases?
> > E.g.: why does the expression: as '[my numbers].number'
> > Works with '', while the expression: GROUP BY '[my numbers].number';
> > Must have `` to work correct?
> >
> > In the MYSQL documentation, it's not clear what are the exact rules
> > for defining and using aliases, for both columns selections and
> > tables.
> >
> > Thanks a lot
> >
>
> Strings are required to be enclosed with single quotes. This is part of
> the SQL standard. You use them in places like the WHERE clause of a
> SELECT or UPDATE statement, or data in INSERT or UPDATE statements. You
> do not use them in table or column names or aliases.
>
> The use of a back-tickey is a MySQL extension that I do not recommend
> using. They are only required when you have a table or column name
> which is the same as a keyword (i.e. GROUP, KEY, etc.). It's not a good
> idea to use keywords for table/column names, and is not transportable to
> other databases.
>
> My recommendation is to stay away from the back-tickey all together and
> not use reserved words. It will make you life a whole lot easier.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================

On Sun, 09 Sep 2007 08:27:59 -0700, CS student wrote:
> Thanks for your reply.
> Can you please tell how should i rewrite the following expression in
> case i will avoid using back ticks:
> SELECT `my number`.`number` as '[my numbers].number'
> FROM numbers as `my number`
> GROUP BY '[my numbers].number';
>
> I'm asking since without the back ticks, the following sub-expression:
> `my number`.`number`
> isn't valid in MYSQL.

Can't have spaces in table names, generally. I'm not sure most DBMSs
would even consider allowing it. MySQL lets you do it, by jumping
through hoops, but it's still not advised.

Why in the name of Zeus would you even want to do that? Removing all the
extraneous bits leaves:

SELECT number
FROM numbers
GROUP BY number;

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord

CS student wrote:
> Jerry Stuckle wrote:
>> CS student wrote:
>>> Hi All,
>>>
>>> These are the commands I'm using to create the table and insert 6
>>> records into it:
>>> create table `my_test`.`numbers` ( `number` int (10) NULL )
>>> insert into `numbers` (`number`) values ( '1')
>>> insert into `numbers` (`number`) values ( '2')
>>> insert into `numbers` (`number`) values ( '3')
>>> insert into `numbers` (`number`) values ( '1')
>>> insert into `numbers` (`number`) values ( '2')
>>> insert into `numbers` (`number`) values ( '3')
>>>
>>> This is command A:
>>> SELECT number from numbers
>>> GROUP by number;
>>>
>>> This is command B:
>>> SELECT `my number`.`number` as '[my numbers].number'
>>> FROM numbers as `my number`
>>> GROUP BY '[my numbers].number';
>>>
>>> The difference between these two commands is only the usage of
>>> aliases.
>>>
>>> When running command A or command B on MYSQL 4.1.21 we get the
>>> following result (this is the expected result):
>>> 1
>>> 2
>>> 3
>>>
>>> When running command A on MYSQL 4.1.22 we also get the above expected
>>> result.
>>>
>>> On the contrary, when running command B on MYSQL 4.1.22 we get the
>>> following result (which is wrong):
>>> 1
>>>
>>> I found that when changing the suffix of command B to: GROUP BY `[my
>>> numbers].number`; than command B will also work correctly on MYSQL
>>> 4.1.22
>>>
>>> What i don't understand is when should i use `` and when '' when using
>>> aliases?
>>> E.g.: why does the expression: as '[my numbers].number'
>>> Works with '', while the expression: GROUP BY '[my numbers].number';
>>> Must have `` to work correct?
>>>
>>> In the MYSQL documentation, it's not clear what are the exact rules
>>> for defining and using aliases, for both columns selections and
>>> tables.
>>>
>>> Thanks a lot
>>>
>> Strings are required to be enclosed with single quotes. This is part of
>> the SQL standard. You use them in places like the WHERE clause of a
>> SELECT or UPDATE statement, or data in INSERT or UPDATE statements. You
>> do not use them in table or column names or aliases.
>>
>> The use of a back-tickey is a MySQL extension that I do not recommend
>> using. They are only required when you have a table or column name
>> which is the same as a keyword (i.e. GROUP, KEY, etc.). It's not a good
>> idea to use keywords for table/column names, and is not transportable to
>> other databases.
>>
>> My recommendation is to stay away from the back-tickey all together and
>> not use reserved words. It will make you life a whole lot easier.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstucklex@attglobal.net
>> ==================
>
> Thanks for your reply.
> Can you please tell how should i rewrite the following expression in
> case i will avoid using back ticks:
> SELECT `my number`.`number` as '[my numbers].number'
> FROM numbers as `my number`
> GROUP BY '[my numbers].number';
>
> I'm asking since without the back ticks, the following sub-expression:
> `my number`.`number`
> isn't valid in MYSQL.
>
>

(Top posting fixed)

That's right - don't use spaces square brackets or other
non-alphanumeric characters in your table or column names or aliases.
They are not valid in strict SQL.

Instead, use something like:

SELECT MyNumber from Numbers Group By MyNumber

Of course, your table and column names need to reflect this.

You don't need an alias for the column name if the name is unique in
your returned columns (i.e. you don't have a MyNumber in two different
tables being joined). And the same is true for the table name - it
doesn't have to have an alias.

Also, in a case like this, your SQL is confusing because you're
selecting the column `my number` then giving the table the alias of `my
number`. Not a good idea.

Keep things simple. It's much easier to follow, and later when you need
to modify or troubleshoot a problem you'll appreciate it.

P.S. Please don't top post. Thanks.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================