Anomaly in resultset

[Karam Chand]

Greetings

I have a table with the following table structure -

Field Type Collation Null Key
Default Extra
------- ------------- ----------------- ------
------ ------- ------
email char(75) latin1_swedish_ci

is_sent enum('Y','N') latin1_swedish_ci YES
N
id int(11) binary PRI
0

I store information about people email whom i need to
send mails and whether a mail has been sent to them or
not.

When I execute a query something like this -

select id/1024,
concat(sum(conv(substring(md5(email),1,8),16,10)),
sum(conv(substring(md5(email),9,16),16,10))) as b from
email_table_copy group by id/1024 order by 1 limit 5;

I get a rsultset -

id/1024 b
------- -------------------------------
0.00 909388403840711996371492430000
0.01 2205732187095504887683337060000
0.02 1674463121499308443382963454000
0.03 1770590858478526561626311655000
0.04 3376981218189278277222690357000

When I execute a query like -

select id/1024, @a:=md5(email),
concat(sum(conv(substring(@a,1,8),16,10)),
sum(conv(substring(@a,9,16),16,10))) as b from
email_table_copy group by id/1024 order by 1 limit 5;

I get a resultset -

id/1024 @a:=md5(email) b

------- --------------------------------
--------------------------------
0.00 a0cb77a048bcafa0c122a97c48c4dc2f
1322364373425669539519654994000
0.01 3d3de1a6987b54fa31c9290a0eaa987d
11944879734104128479516410860000
0.02 0b84e00a70f4d0b2cfc38449e5ab8b4b
276678297684241708186218316000
0.03 025ab61135c8ed4a329dd5670801e866
54875101143019835333537694000
0.04 f53e627643b62f9bda102b0612f28ec3
4118456286152667004309559280000

Shouldnt be the value of `b` be same both the times.
By executing the second query i belive i do md5
conversion only once which should significantly boost
the result on a big table.

Am I doing something wrong?

Also, what is the best way to remove the md5 key
column from the resultset as I dont need that.

Thanks in advance

Karam

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw