On 8 May 2003 at 3:12, Nitin Nanivadekar wrote:
> 1. the max () keyword reports 'different' resultsets
> when following queries are sent to it,
> a. select max(id) from table1;
> b. select max(id) from table1 where id2=100;
> in any condition (like the low resources etc.) i
> should not get different results from seemingly
> identical queries.
But those queries aren't identical. If you think they are, the
"serious trouble" is with your understanding, not with MySQL.
> 2. i will also point one observation when i was
> hacking the max () function. when there is an empty
> set max() reports Null. this makes max() limited to
> arithmatic ops only. i am using max() to find the next
> incremental primary key index for some other table.
> i am now seriously considering NOT using max() for
> that reason after 1,. and 2. above.
NULL is the value you should expect to get in that situation.
What other value could MAX() return if there are no records?
The normal way of handling getting the value of an autoincrement key
is to insert the record and then use LAST_INSERT_ID() to get the
value of the key, which you can then use for creating records in the
other table. Your way is trickier, since you would have to avoid the
possibility of having another process insert a record after you've
gotten MAX(id).
--
Keith C. Ivey <keith@stripped>
Tobacco Documents Online
http://tobaccodocuments.org
