Yes! The "illegal double" error only happens if you do the select like you did. The only
error I was getting was the generic "there's an error in your sql."
From: Hank [mailto:heskin@stripped]
Sent: Monday, December 20, 2010 11:52 AM
To: Ramsey, Robert L
Subject: Re: odd problem with select as statement
i.e. just try this:
mysql> select 4E5664736F400E8B482EA7AA67853D13;
ERROR 1367 (22007): Illegal double '4E5664736' value found during parsing
On Mon, Dec 20, 2010 at 12:50 PM, Hank
Here's my 5 second guess..
4E5664736... is being interpreted as a number in scientific notation .. i.e.
4*10^5664736 and the parser doesn't like that as a field name.
On Mon, Dec 20, 2010 at 12:43 PM, Ramsey, Robert L
I am having the hardest time getting a select as statement right. Here is the full query:
SUM(IF(image='EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A317E5FA9899C48C19,
SUM(IF(image='235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7CEBF56FBDA2BF7815,
SUM(IF(image='96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7FE789A18E09BC5889,
SUM(IF(image='D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408391132F451AE724A,
SUM(IF(image='018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2BEB040D241739B784,
SUM(IF(image='98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6E0FEA25BAB0177FE,
SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13,
SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B275605BD6B69F444700C
If I remove that one line, the query works fine. If I do:
select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from dsrssfeed ;
it works. But these fail:
select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as
4E5664736F400E8B482EA7AA67853D13 from dsrssfeed ;
select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from dsrssfeed ;
It can't be field name length, since even 4E5 fails, the field name can start with a
number since 4E succeeds.
The goal is to see what arbitrary images have information associated with them. The table
has two fields: image is a UID that is the primary key, and caption which is a
varchar(255) that has information about the image. Images are added and deleted from the
table as they are changed on a web page. The UID is generated by a third party program
that I have to interface with and have no control over. An array of image UIDs is sent
to the php script and the script needs to determine which UIDs are present in the table.
Rather than make N number of individual queries as I iterate through the array, I iterate
through the array and build the query on the fly to make one query. Then I iterate
through the array again and check the value in the field. 1 means the UID has an entry,
0 means it doesn't. I thought doing 1 mysql call would be more efficient than lots of
calls as I iterate through the array. But since there will probably never be more than
100 images in the table at any one time, it may not make any difference. But now I'm
just curious as to why this is happening.