List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 24 2001 10:13pm
Subject:Re: Request for feature: making SELECT FROM ... IS NULL mirror LAST_INSERT_ID
View as plain text  
In the last episode (May 24), Michael Widenius said:
>  -----  Message from "Joshua J. Kugler" <isd@stripped> -----
> 
> Joshua> So, I can "SELECT id FROM table_name WHERE id IS NULL" as many times as I
> 
> Joshua> need/want to, and it will return the proper value.  Very nice.
> 
> Joshua> So, that was my problem, and the solution.  Could these be changed in the
> 
> Joshua> official distribution?  Or at least a compile time option?  I see no
> reason 
> Joshua> why the SELECT NULL behavior cannot mirror the behavior of LAST_INSERT_ID.
> 
> 
> The reason for the above is mainly a security thing. I think the
> above usage of id IS NULL is very dangerous as a general feature as
> it could have strange side effects for applications that are not
> aware of this.
> 
> To minimize the side effects, we clear the flag after one search to
> not affect the rest of the application.  I don't know if this is the
> best thing to do, but this is the first time we hear about any
> problems regarding this.  The question is of we should add a separate
> option for to get both behavour's or just remove the 'clear for next
> request'.

I had the same problem last November, and worked around it by editing
the source and removing the OPTION_AUTO_IS_NULL flag from
thd_startup_options.  Joshua's solution is better, though, as it allows
"IS NULL" selects and "last_insert_id()" selects to coexist.

Considering that there is no other reason to do an IS NULL query on an
autonumber field, and considering that Access and MS SQL both use this
behaviour, I don't think there is much risk in applying Joshua's patch.

-- 
	Dan Nelson
	dnelson@stripped

Attachment: [message/rfc822]
Attachment: [message/rfc822]
Thread
Request for feature: making SELECT FROM ... IS NULL mirror LAST_INSERT_IDMichael Widenius24 May
  • Re: Request for feature: making SELECT FROM ... IS NULL mirror LAST_INSERT_IDJoshua J. Kugler24 May
  • Re: Request for feature: making SELECT FROM ... IS NULL mirror LAST_INSERT_IDDan Nelson25 May
    • My mySQL doesn't start...Victor SpĂ„ng Arthursson25 May
Re: My mySQL doesn't start...Rolf Hopkins25 May
Re: My mySQL doesn't start...Zachary Burnham25 May