alias problem and odd warnings

Using 5.1.41/Ubuntu

I have 2 tables, members & countries. The former has a column,
country_id (tinyint(3) unsigned). The countries id column is the same
(although auto_increment). I'm trying to select just those countries for
which there is a member. So I decided this query should do the trick:

SELECT c.id, c.name FROM countries AS c INNER JOIN members AS m ON
m.country_id = c.id GROUP BY c.id;

And, indeed, it works like a charm. However, I'm using the CakePHP
framework, which creates a query like:

SELECT `Country`.`id`, `Country`.`name` FROM `countries` AS `Country`
INNER JOIN members AS `Member` ON `Member`.`country_id` = 'Country.id'
WHERE 1 = 1 GROUP BY `Country`.`id`;

This not only gives an empty set, but also throws 171 warnings (more on
that below). I've remove both the WHERE and GROUP BY clauses with no
success. I've been staring at this for an hour now and can't see what
the trouble is. Can any of you?


As for the warnings:

Warning | 1292 | Truncated incorrect DOUBLE value: 'Country.id'

I've searched online a bit without success. There was a submitted bug:

http://bugs.mysql.com/bug.php?id=46641

.... but I haven't been able to figure anything out based on that (except
to agree that this error "message" is definitely not very enlightening).
Can anyone shed some light?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: alias problem and odd warnings

On 10-06-14 09:13 PM, brian wrote:
> Using 5.1.41/Ubuntu
>
> I have 2 tables, members & countries. The former has a column,
> country_id (tinyint(3) unsigned). The countries id column is the same
> (although auto_increment). I'm trying to select just those countries for
> which there is a member. So I decided this query should do the trick:
>
> SELECT c.id, c.name FROM countries AS c INNER JOIN members AS m ON
> m.country_id = c.id GROUP BY c.id;
>
> And, indeed, it works like a charm. However, I'm using the CakePHP
> framework, which creates a query like:
>
> SELECT `Country`.`id`, `Country`.`name` FROM `countries` AS `Country`
> INNER JOIN members AS `Member` ON `Member`.`country_id` = 'Country.id'
> WHERE 1 = 1 GROUP BY `Country`.`id`;
>
> This not only gives an empty set, but also throws 171 warnings (more on
> that below). I've remove both the WHERE and GROUP BY clauses with no
> success. I've been staring at this for an hour now and can't see what
> the trouble is. Can any of you?

Solved. I just noticed the quoting difference here:

ON `Member`.`country_id` = 'Country.id'

Country.id is entirely wrapped in single quotes rather than the alias
and column being separately wrapped with back-ticks. I've changed my
code so that the query is created properly.

I'm still curious about the strange warning, though.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: alias problem and odd warnings

Hi Brian, all!


brian wrote:
> On 10-06-14 09:13 PM, brian wrote:
>> [[...]]
>>
>> This not only gives an empty set, but also throws 171 warnings (mo=
re on
>> that below). I've remove both the WHERE and GROUP BY clauses with =
no
>> success. I've been staring at this for an hour now and can't see w=
hat
>> the trouble is. Can any of you?
>=20
> Solved. I just noticed the quoting difference here:
>=20
> ON `Member`.`country_id` =3D 'Country.id'
>=20
> Country.id is entirely wrapped in single quotes rather than the ali=
as
> and column being separately wrapped with back-ticks. I've changed m=
y
> code so that the query is created properly.

Quote inserted by Jörg:
>> As for the warnings:
>>=20
>> Warning | 1292 | Truncated incorrect DOUBLE value: 'Country.id'

>=20
> I'm still curious about the strange warning, though.

AIUI, the wrong quotes in your statement made the parser take Country=
..id
as a string, which was then to be compared to an integer (the country=
_id
column). This comparison forced a conversion from string to number, a=
nd
the string contained a dot which was assumed to be a decimal point,
hence a floating point number.

If 171 is the number of rows in your countries table, then it is also
the number of times this comparison is done.

The warnings should be gone, I assume, now that you fixed the quoting=
..


Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg