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(substr ing(md5(email), 9,16),16,10))) as b from
email_table_cop y group by id/1024 order by 1 limit 5;

I get a rsultset -

id/1024 b
------- -------------------------------
0.00 909388403840711 996371492430000
0.01 220573218709550 488768333706000 0
0.02 167446312149930 844338296345400 0
0.03 177059085847852 656162631165500 0
0.04 337698121818927 827722269035700 0

When I execute a query like -

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

I get a resultset -

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

------- --------------------------------
--------------------------------
0.00 a0cb77a048bcafa 0c122a97c48c4dc 2f
132236437342566 953951965499400 0
0.01 3d3de1a6987b54f a31c9290a0eaa98 7d
119448797341041 284795164108600 00
0.02 0b84e00a70f4d0b 2cfc38449e5ab8b 4b
276678297684241 708186218316000
0.03 025ab61135c8ed4 a329dd5670801e8 66
548751011430198 35333537694000
0.04 f53e627643b62f9 bda102b0612f28e c3
411845628615266 700430955928000 0

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