odd problem with select as statement

--_000_0385417EB350D84BAE0D5103C372C65E0791F27BITSNT440iowau io_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I am having the hardest time getting a select as statement right. Here is =
the full query:

select
SUM(IF(image=3D'EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A3=
17E5FA9899C48C19,
SUM(IF(image=3D'235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7C=
EBF56FBDA2BF7815,
SUM(IF(image=3D'96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7F=
E789A18E09BC5889,
SUM(IF(image=3D'D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408=
391132F451AE724A,
SUM(IF(image=3D'018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2B=
EB040D241739B784,
SUM(IF(image=3D'98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6=
E0FEA25BAB0177FE,
SUM(IF(image=3D'4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B=
482EA7AA67853D13, <--offending line
SUM(IF(image=3D'FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B2756=
05BD6B69F444700C
from dsrssfeed

If I remove that one line, the query works fine. If I do:

select SUM(IF(image=3D'4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from =
dsrssfeed ;

it works. But these fail:

select SUM(IF(image=3D'4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E566473=
6F400E8B482EA7AA67853D13 from dsrssfeed ;
select SUM(IF(image=3D'4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from=
dsrssfeed ;

It can't be field name length, since even 4E5 fails, the field name can sta=
rt with a number since 4E succeeds.

Any ideas?

The goal is to see what arbitrary images have information associated with t=
hem. The table has two fields: image is a UID that is the primary key, an=
d caption which is a varchar(255) that has information about the image. Im=
ages 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 w=
ith and have no control over. An array of image UIDs is sent to the php sc=
ript 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 o=
ne query. Then I iterate through the array again and check the value in th=
e field. 1 means the UID has an entry, 0 means it doesn't. I thought doin=
g 1 mysql call would be more efficient than lots of calls as I iterate thro=
ugh 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.

Thanks,

Bob





--_000_0385417EB350D84BAE0D5103C372C65E0791F27BITSNT440iowau io_--

Re: odd problem with select as statement

--00163628523ca888d30497db27f8
Content-Type: text/plain; charset=ISO-8859-1

I can't tell you 'why' it is occurring when the field name begins with 4E5,
but you can solve your problem by enclosing all your field names
in backticks ( ` ).

IE:

SELECT field AS `4E5664736F400E8B482EA7AA67853D13`



On Mon, Dec 20, 2010 at 11:43 AM, Ramsey, Robert L
wrote:

> I am having the hardest time getting a select as statement right. Here is
> the full query:
>
> select
> 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, <--offending line
> SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as
> FEB810A43A1B275605BD6B69F444700C
> from dsrssfeed
>
> 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.
>
> Any ideas?
>
> 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.
>
> Thanks,
>
> Bob
>
>
>
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--00163628523ca888d30497db27f8--

Re: odd problem with select as statement

--0016363101fb0febbc0497db29ed
Content-Type: text/plain; charset=ISO-8859-1

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.

-Hank


On Mon, Dec 20, 2010 at 12:43 PM, Ramsey, Robert L
wrote:

> I am having the hardest time getting a select as statement right. Here is
> the full query:
>
> select
> 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, <--offending line
> SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as
> FEB810A43A1B275605BD6B69F444700C
> from dsrssfeed
>
> 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.
>
> Any ideas?
>
> 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.
>
> Thanks,
>
> Bob
>
>
>
>
>

--0016363101fb0febbc0497db29ed--

Re: odd problem with select as statement

--0016363101fbd03a8f0497db2ef5
Content-Type: text/plain; charset=ISO-8859-1

i.e. just try this:

mysql> select 4E5664736F400E8B482EA7AA67853D13;
ERROR 1367 (22007): Illegal double '4E5664736' value found during parsing

-Hank


On Mon, Dec 20, 2010 at 12:50 PM, Hank wrote:

>
> 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.
>
> -Hank
>
>
> On Mon, Dec 20, 2010 at 12:43 PM, Ramsey, Robert L <
> robert-ramsey@uiowa.edu> wrote:
>
>> I am having the hardest time getting a select as statement right. Here is
>> the full query:
>>
>> select
>> 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, <--offending line
>> SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as
>> FEB810A43A1B275605BD6B69F444700C
>> from dsrssfeed
>>
>> 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.
>>
>> Any ideas?
>>
>> 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.
>>
>> Thanks,
>>
>> Bob
>>
>>
>>
>>
>>
>

--0016363101fbd03a8f0497db2ef5--

RE: odd problem with select as statement

--_000_0385417EB350D84BAE0D5103C372C65E0791F3A3ITSNT440iowau io_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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 yo=
ur sql."

Thank you!

Bob

From: Hank [mailto:heskin@gmail.com]
Sent: Monday, December 20, 2010 11:52 AM
To: Ramsey, Robert L
Cc: mysql@lists.mysql.com
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

-Hank

On Mon, Dec 20, 2010 at 12:50 PM, Hank l.com>> wrote:

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.

-Hank


On Mon, Dec 20, 2010 at 12:43 PM, Ramsey, Robert L > wrote:
I am having the hardest time getting a select as statement right. Here is =
the full query:

select
SUM(IF(image=3D'EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A3=
17E5FA9899C48C19,
SUM(IF(image=3D'235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7C=
EBF56FBDA2BF7815,
SUM(IF(image=3D'96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7F=
E789A18E09BC5889,
SUM(IF(image=3D'D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408=
391132F451AE724A,
SUM(IF(image=3D'018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2B=
EB040D241739B784,
SUM(IF(image=3D'98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6=
E0FEA25BAB0177FE,
SUM(IF(image=3D'4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B=
482EA7AA67853D13, <--offending line
SUM(IF(image=3D'FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B2756=
05BD6B69F444700C
from dsrssfeed

If I remove that one line, the query works fine. If I do:

select SUM(IF(image=3D'4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from =
dsrssfeed ;

it works. But these fail:

select SUM(IF(image=3D'4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E566473=
6F400E8B482EA7AA67853D13 from dsrssfeed ;
select SUM(IF(image=3D'4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from=
dsrssfeed ;

It can't be field name length, since even 4E5 fails, the field name can sta=
rt with a number since 4E succeeds.

Any ideas?

The goal is to see what arbitrary images have information associated with t=
hem. The table has two fields: image is a UID that is the primary key, an=
d caption which is a varchar(255) that has information about the image. Im=
ages 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 w=
ith and have no control over. An array of image UIDs is sent to the php sc=
ript 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 o=
ne query. Then I iterate through the array again and check the value in th=
e field. 1 means the UID has an entry, 0 means it doesn't. I thought doin=
g 1 mysql call would be more efficient than lots of calls as I iterate thro=
ugh 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.

Thanks,

Bob






--_000_0385417EB350D84BAE0D5103C372C65E0791F3A3ITSNT440iowau io_--