Re: query with NULL

Hi!

Thanks!
I created a bugreport for this:

http://bugs.mysql.com/4458

you may use this url to track the progress of the issue

On Jul 08, npe-fast-it-net wrote:
> OS: Windows XP German
>  
> "Willkommen bei phpMyAdmin 2.5.6
>  
> Verbunden mit MySQL 4.0.18-nt-log auf localhost als root@localhost"
>  
> Following query won't be correctly executed, if table alias acf has not NULL
> (= admin_access_special) in the first found row. Unfortunately, all rows of
> admins' group are deleted in that case even not contenting NULL-condition.
> If I did not anything wrong, then in my intention all rows of selected
> conditions in tables should be deleted. A bug?
>  
> German: Folgenden Abfrage wird nicht richtig ausgef?hrt, wenn die Tabelle
> mit dem Alias acf keine erste Zeile mit NULL ( =
> admin_access_special)findet. Leider werden in diesem Fall alle Zeilen der
> Admingruppe gel?scht auch wenn die NULL-Bedingung nicht erf?llt ist. Falls
> ich keine Fehler begang, sollten nur Zeilen in beiden Tabellen gel?scht
> werden, die diesen Bedingungen entsprechen. Ein Bug?
>  
>  
> DELETE  FROM admin_access_files,
> admin USING admin_access_files acf,
> admin a WHERE a.admin_id = acf.admin_id AND a.admin_groups_id =5 AND
> acf.admin_access_special IS NULL;
>  
>  Datenbank vatansesi  - Tabelle admin  auf localhost
>  
> # phpMyAdmin SQL Dump
> # version 2.5.6
> # http://www.phpmyadmin.net
> #
> # Host: localhost
> # Erstellungszeit: 07. Juli 2004 um 23:51
> # Server Version: 4.0.18
> # PHP-Version: 4.3.6
> # 
> # Datenbank: `vatansesi`
> # 
>  
> # --------------------------------------------------------
>  
> #
> # Tabellenstruktur f?r Tabelle `admin`
> #
>  
> DROP TABLE IF EXISTS `admin`;
> CREATE TABLE `admin` (
>   `admin_id` int(11) NOT NULL auto_increment,
>   `admin_groups_id` int(11) default NULL,
>   `admin_firstname` varchar(32) NOT NULL default '',
>   `admin_lastname` varchar(32) default NULL,
>   `admin_email_address` varchar(96) NOT NULL default '',
>   `admin_email_notification_level` tinyint(4) NOT NULL default '0',
>   `admin_password` varchar(40) NOT NULL default '',
>   `admin_created` datetime default NULL,
>   `admin_access_status` tinyint(4) NOT NULL default '1',
>   `admin_scheduled` datetime default NULL,
>   `admin_access_until` datetime default NULL,
>   `admin_modified` datetime default NULL,
>   `admin_logdate` datetime default NULL,
>   `admin_lognum` int(11) NOT NULL default '0',
>   PRIMARY KEY  (`admin_id`),
>   UNIQUE KEY `admin_email_address` (`admin_email_address`),
>   KEY `admin_stop_email_notification` (`admin_email_notification_level`),
>   KEY `admin_access_until` (`admin_access_until`),
>   KEY `admin_access_status` (`admin_access_status`),
>   KEY `admin_sheduled` (`admin_scheduled`)
> ) TYPE=MyISAM AUTO_INCREMENT=38 ;
>  
> #
> # Daten f?r Tabelle `admin`
> #
>  
> INSERT  INTO  `admin` (  `admin_id` ,  `admin_groups_id` ,
> `admin_firstname` ,  `admin_lastname` ,  `admin_email_address` ,
> `admin_email_notification_level` ,  `admin_password` ,  `admin_created` ,
> `admin_access_status` ,  `admin_scheduled` ,  `admin_access_until` ,
> `admin_modified` ,  `admin_logdate` ,  `admin_lognum`  ) 
> VALUES ( 1, 1,  'N.....',  'E....t',  '[email protected]', 5,
> '7497194c8bed6:d4',  '2004-06-14 14:07:00', 1,  NULL ,  '0000-00-00
> 00:00:00',  '0000-00-00 00:00:00',  '2004-07-07 11:31:17', 163  ) , ( 3, 5,
> 'Ht',  'E...',  '[email protected]', 1,  '064df98b185ff:ed',  '2004-06-14 19:13:02',
> 1,  NULL ,  '0000-00-00 00:00:00',  '0000-00-00 00:00:00',  NULL , 0  ) , (
> 4, 5,   'Ht',  'E.dfdfdf..',  '[email protected]', 1,  '064df98b185ff:ed',
> '2004-06-14 19:13:02', 1,  NULL ,  '0000-00-00 00:00:00',  '0000-00-00
> 00:00:00',  NULL , 0  );
>  
>  
>  
>  
>  
> # --------------------------------------------------------
>  
> #
> # Tabellenstruktur f?r Tabelle `admin_access_files`
> #
>  
> DROP TABLE IF EXISTS `admin_access_files`;
> CREATE TABLE `admin_access_files` (
>   `file_access_id` int(4) NOT NULL auto_increment,
>   `admin_files_id` int(11) NOT NULL default '0',
>   `admin_id` int(11) NOT NULL default '1',
>   `admin_access_values` int(11) NOT NULL default '1',
>   `admin_access_special` int(1) default NULL,
>   PRIMARY KEY  (`file_access_id`),
>   UNIQUE KEY `file_access_id` (`file_access_id`),
>   KEY `admin_access_special` (`admin_access_special`)
> ) TYPE=MyISAM AUTO_INCREMENT=393 ;
>  
> #
> # Daten f?r Tabelle `admin_access_files`
> #
>  
> INSERT INTO `admin_access_files` (`file_access_id`, `admin_files_id`,
> `admin_id`, `admin_access_values`, `admin_access_special`) VALUES (4, 6, 1,
> 2, 1),
> (5, 5, 1, 2, NULL),
> (6, 4, 1, 2, 1),
> (7, 7, 1, 2, NULL),
> (8, 8, 1, 2, 1),
> (9, 9, 3, 2, 1),
> (10, 10, 3, 2, NULL),
> (11, 11, 1, 2, 1),
> (12, 12, 5, 2, 1),
> (13, 13, 6, 2, NULL),
> (14, 14, 1, 2, NULL),
> (15, 15, 1, 2, 1);
Regards,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <[email protected]>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/  www.mysql.com

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]