Description:
Strange bug with a rather complex SELECT query (see "how to repeat" section)using BIT_OR
groupby function.
The query raises a 1062 error : "Duplicate entry '21717_3912-capc-55-21717-Utilisateur
CAPC-3158-21717-NULL-1525-2' for key 1".
If you remove the BIT_OR field from the query, it works.
May be there is something wrong with my query.
BTW, it works perfectly fine with MySQL Server 4.1.10.
Regards,
François MASUREL
Bordeaux, FRANCE
How to repeat:
/*
Server version 5.0.3-beta-nt
*/
create database if not exists `sqlbug`;
use `sqlbug`;
/*
Table structure for file
*/
drop table if exists `file`;
CREATE TABLE `file` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_object` int(10) unsigned NOT NULL default '0',
`id_version` int(10) unsigned NOT NULL default '1',
`file_type` varchar(20) default NULL,
`file_name` varchar(50) default NULL,
`file_ext` varchar(5) default NULL,
`file_size` int(10) unsigned default '0',
`file_index` text,
PRIMARY KEY (`id`),
UNIQUE KEY `id_object` (`id_object`),
KEY `id_version` (`id_version`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*
Table data for sqlbug.file
*/
INSERT INTO `file` VALUES
(1,3255,392,'image/pjpeg','3255','jpg',0,NULL),
(2,3253,31,NULL,NULL,NULL,0,NULL),
(4,3278,98,NULL,NULL,NULL,0,NULL),
(33,3382,18,'image/pjpeg','3382','jpg',0,NULL),
(77,21717,44,'image/pjpeg','21717','jpg',0,NULL),
(175,27438,2,NULL,NULL,NULL,0,NULL),
(259,29819,4,NULL,NULL,NULL,0,NULL),
(260,29829,2,NULL,NULL,NULL,0,NULL),
(261,29839,1,NULL,NULL,NULL,0,NULL),
(262,29972,2,NULL,NULL,NULL,0,NULL),
(283,30041,2,NULL,NULL,NULL,0,NULL);
/*
Table structure for identities
*/
drop table if exists `identities`;
CREATE TABLE `identities` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_object` int(10) unsigned default NULL,
`id_version` int(10) unsigned NOT NULL default '1',
`address` varchar(255) default NULL,
`zipcode` varchar(5) default NULL,
`city` varchar(50) default NULL,
`id_country` int(10) unsigned default NULL,
`phone` varchar(20) default NULL,
`fax` varchar(20) default NULL,
`email` varchar(150) default NULL,
`web` varchar(150) default NULL,
`id_zone_old` int(10) unsigned NOT NULL default '1',
`id_zone` int(10) unsigned default '0',
`id_environment` int(10) unsigned NOT NULL default '1',
`file_type` varchar(20) default NULL,
`file_name` varchar(50) default NULL,
`file_ext` varchar(5) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `object_version` (`id_object`,`id_version`),
KEY `id_object` (`id_object`),
KEY `id_version` (`id_version`),
KEY `zipcode` (`zipcode`),
KEY `id_environment` (`id_environment`),
KEY `id_zone` (`id_zone`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*
Table data for sqlbug.identities
*/
INSERT INTO `identities` VALUES
(1435,3253,31,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,10866,1,'',NULL,''),
(1436,3255,392,NULL,NULL,NULL,NULL,NULL,NULL,'f.masurel@mairie-bordeaux.fr',NULL,1,10866,1,NULL,NULL,NULL),
(1438,3278,98,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,10866,1,'',NULL,''),
(1467,3382,18,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,10866,1,'',NULL,''),
(1525,21717,44,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,NULL,NULL,NULL),
(1559,27438,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,NULL,NULL,NULL),
(2680,29819,4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,NULL,NULL,NULL),
(2681,29829,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,NULL,NULL,NULL),
(2682,29839,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,NULL,NULL,NULL),
(2683,29972,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,NULL,NULL,NULL),
(2703,30041,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,NULL,NULL,NULL);
/*
Table structure for label
*/
drop table if exists `label`;
CREATE TABLE `label` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_object` int(10) unsigned NOT NULL default '0',
`id_version` int(10) unsigned NOT NULL default '1',
`label` varchar(100) default NULL,
`description` text,
PRIMARY KEY (`id`),
KEY `id_object` (`id_object`),
KEY `id_version` (`id_version`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*
Table data for sqlbug.label
*/
INSERT INTO `label` VALUES
(2,3253,31,'Administrateur',NULL),
(3,3255,392,'MASUREL François',NULL),
(5,3278,98,'Anonymous',NULL),
(6,3382,18,'Test',NULL),
(3158,21717,44,'Utilisateur CAPC',NULL),
(3655,27438,2,'Achats et Marchés Administrateur',NULL),
(4784,29819,4,'Mairie Bordeaux Intranaute',NULL),
(4785,29829,2,'Conseil Municipal Administrateur',NULL),
(4787,29839,1,'Conseil Municipal Contributeur',NULL),
(4811,29972,2,'Mairie de Bordeaux Administrateur',NULL),
(4836,30041,2,'Cabinet du Maire',NULL);
/*
Table structure for object_acl
*/
drop table if exists `object_acl`;
CREATE TABLE `object_acl` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_object` int(10) unsigned NOT NULL default '0',
`id_obj_acl` int(10) unsigned NOT NULL default '0',
`id_group` int(10) unsigned NOT NULL default '0',
`obj_state` int(10) unsigned NOT NULL default '0',
`rights` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `object_group` (`id_obj_acl`,`id_group`),
KEY `rights` (`rights`),
KEY `state` (`obj_state`),
KEY `id_obj_acl` (`id_obj_acl`),
KEY `id_object` (`id_object`),
KEY `id_group` (`id_group`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*
Table data for sqlbug.object_acl
*/
INSERT INTO `object_acl` VALUES
(6671,0,3253,3276,0,15),
(6675,0,3255,3276,0,15),
(6742,0,3278,3276,0,15),
(7025,0,3382,3276,0,15),
(7026,0,3382,3382,0,15),
(33818,0,21717,3276,0,15),
(33819,0,21717,3255,0,15),
(40931,0,27438,3276,0,15),
(40932,0,27438,3255,0,15),
(43456,0,29819,3276,0,15),
(43457,0,29819,29819,0,15),
(43480,0,29829,3276,0,15),
(43481,0,29829,29829,0,15),
(43502,0,29839,3276,0,15),
(43503,0,29839,29839,0,15),
(43773,0,29972,3276,0,15),
(43774,0,29972,29972,0,15),
(43849,30000,29819,29971,0,15),
(43852,30001,27438,29971,0,15),
(43863,30005,29829,29971,0,15),
(43866,30006,29839,29971,0,15),
(43885,30012,29972,29971,0,1),
(44062,0,30041,3276,0,15),
(44063,0,30041,3255,0,15),
(44064,0,30041,3275,0,2),
(44065,0,30041,30041,0,15);
/*
Table structure for objects
*/
drop table if exists `objects`;
CREATE TABLE `objects` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_tree` int(10) unsigned default NULL,
`id_context` int(10) unsigned NOT NULL default '1',
`id_root` int(10) unsigned NOT NULL default '1',
`id_parent` int(10) unsigned default NULL,
`id_application` int(10) unsigned NOT NULL default '0',
`id_owner` int(10) unsigned NOT NULL default '0',
`id_version` int(10) unsigned NOT NULL default '1',
`id_newversion` int(10) unsigned NOT NULL default '1',
`state` int(10) unsigned NOT NULL default '0',
`id_export` int(10) unsigned default NULL,
`guid` varchar(21) NOT NULL default '',
`creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `id_parent` (`id_parent`),
KEY `id_version` (`id_version`),
KEY `id_newversion` (`id_newversion`),
KEY `state` (`state`),
KEY `id_export` (`id_export`),
KEY `id_tree` (`id_tree`),
KEY `id_root` (`id_root`),
KEY `id_context` (`id_context`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*
Table data for sqlbug.objects
*/
INSERT INTO `objects` VALUES
(3253,3912,1,3253,1,1,3253,31,31,0,NULL,'3253_3912','2005-02-18 00:21:59'),
(3255,3912,1,3255,1,1,3253,392,392,0,NULL,'3255_3912','2005-03-23 10:38:01'),
(3278,3912,1,3278,1,1,3253,98,98,0,NULL,'3278_3912','2005-02-18 00:21:26'),
(3382,3912,1,3382,1,1,3382,18,18,0,NULL,'3382_3912','2005-02-18 00:22:09'),
(21717,3912,1,14039,14039,0,3255,44,44,0,NULL,'21717_3912','2004-11-23 12:00:00'),
(27438,3912,1,14039,14039,0,3255,2,2,0,NULL,'27438_3912','2005-02-18 00:16:47'),
(29819,3912,1,14039,14039,0,29819,4,4,0,NULL,'29819_3912','2005-02-22 18:40:03'),
(29829,3912,1,14039,14039,0,29829,2,2,0,NULL,'29829_3912','2005-02-18 00:16:05'),
(29839,3912,1,14039,14039,0,29839,1,1,0,NULL,'29839_3912','2005-02-18 00:15:16'),
(29972,3912,1,14039,14039,0,29972,2,2,0,NULL,'29972_3912','2005-02-22 16:15:39'),
(30041,3912,1,14039,14039,0,30041,2,2,0,NULL,'30041_3912','2005-02-23 19:22:54');
/*
Table structure for person
*/
drop table if exists `person`;
CREATE TABLE `person` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_object` int(10) unsigned NOT NULL default '0',
`id_version` int(10) unsigned default NULL,
`name` varchar(50) default NULL,
`firstname` varchar(50) default NULL,
`civilite` int(10) default NULL,
`birthdate` date default NULL,
PRIMARY KEY (`id`),
KEY `id_object` (`id_object`),
KEY `id_version` (`id_version`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*
Table data for sqlbug.person
*/
INSERT INTO `person` VALUES
(1,3253,31,'Administrateur',NULL,NULL,NULL),
(2,3255,392,'MASUREL','François',228,NULL),
(4,3278,98,'Anonymous',NULL,NULL,NULL),
(33,3382,18,'Test',NULL,NULL,NULL),
(63,21717,44,'Utilisateur CAPC',NULL,NULL,NULL),
(194,27438,2,'Achats et Marchés Administrateur',NULL,NULL,NULL),
(325,29819,4,'Mairie Bordeaux Intranaute',NULL,NULL,NULL),
(326,29829,2,'Conseil Municipal Administrateur',NULL,NULL,NULL),
(327,29839,1,'Conseil Municipal Contributeur',NULL,NULL,NULL),
(328,29972,2,'Mairie de Bordeaux Administrateur',NULL,NULL,NULL),
(348,30041,2,'Cabinet du Maire',NULL,NULL,NULL);
/*
Table structure for user
*/
drop table if exists `user`;
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_object` int(10) unsigned default NULL,
`login` varchar(20) NOT NULL default '',
`password` varchar(20) default NULL,
`creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `login` (`login`),
UNIQUE KEY `id_object` (`id_object`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*
Table data for sqlbug.user
*/
INSERT INTO `user` VALUES
(1,3253,'admin','1974','2002-11-13 16:47:17'),
(5,3278,'anonymous','anonymous','2002-11-13 18:08:35'),
(6,3255,'f.masurel','zzzz','2005-02-17 17:16:07'),
(36,3382,'test','test','2002-11-13 19:21:21'),
(55,21717,'capc','capc','2004-04-03 02:02:00'),
(58,27438,'dam.admin','dam.admin','2005-02-18 00:16:47'),
(61,29819,'mbx','mbx','2005-02-18 00:18:32'),
(62,29829,'cm.admin','cm.admin','2005-02-18 00:16:05'),
(63,29839,'cm.contrib','cm.contrib','2005-02-18 00:15:16'),
(64,29972,'mbx.admin','mbx.admin','2005-02-22 16:14:09'),
(84,30041,'cab','cab','2005-02-23 19:22:54');
/*
Table structure for usergroup
*/
drop table if exists `usergroup`;
CREATE TABLE `usergroup` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_object` int(10) unsigned NOT NULL default '0',
`label` varchar(50) NOT NULL default '',
`type` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `id_object` (`id_object`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*
Table data for sqlbug.usergroup
*/
INSERT INTO `usergroup` VALUES
(1,3275,'Tout le monde',1),
(2,3276,'Administrateurs',1),
(17,3304,'Groupe Test',1),
(18,3253,'Administrateur',0),
(19,3255,'MASUREL François',0),
(21,3278,'Anonymous',0),
(49,3382,'Test',0),
(53,21693,'fr.mbx.capc',1),
(68,21717,'Utilisateur CAPC',0),
(70,27434,'fr.mbx.dam',1),
(72,27438,'Achats et Marchés Administrateur',0),
(75,29808,'fr.mbx',1),
(76,29819,'Mairie Bordeaux Intranaute',0),
(77,29827,'fr.mbx.sc.admin',1),
(78,29828,'fr.mbx.sc.contrib',1),
(79,29829,'Conseil Municipal Administrateur',0),
(80,29839,'Conseil Municipal Contributeur',0),
(81,29971,'fr.mbx.admin',1),
(82,29972,'Mairie de Bordeaux Administrateur',0),
(103,30041,'Cabinet du Maire',0),
(104,30044,'fr.mbx.cab',1),
(144,30311,'fr.mbx.sc',1);
/*
Table structure for usergroup_tree
*/
drop table if exists `usergroup_tree`;
CREATE TABLE `usergroup_tree` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_parent` int(10) unsigned NOT NULL default '0',
`id_child` int(10) unsigned NOT NULL default '0',
`distance` int(10) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `parent_child` (`id_parent`,`id_child`),
KEY `id_child` (`id_child`),
KEY `id_parent` (`id_parent`),
KEY `distance` (`distance`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*
Table data for sqlbug.usergroup_tree
*/
INSERT INTO `usergroup_tree` VALUES
(28,3275,3275,0),
(29,29808,29808,0),
(32,3304,3304,0),
(39,3275,29808,1),
(40,29971,29971,0),
(41,29808,29971,1),
(42,3275,29971,2),
(43,30044,30044,0),
(44,29808,30044,1),
(45,3275,30044,2),
(46,21693,21693,0),
(47,29808,21693,1),
(48,3275,21693,2),
(49,27434,27434,0),
(50,29808,27434,1),
(51,3275,27434,2),
(52,3275,3304,1),
(53,3276,3276,0),
(54,3275,3276,1),
(55,30311,30311,0),
(56,29808,30311,1),
(57,3275,30311,2),
(58,29827,29827,0),
(59,30311,29827,1),
(60,29808,29827,2),
(61,3275,29827,3),
(62,29828,29828,0),
(63,30311,29828,1),
(64,29808,29828,2),
(65,3275,29828,3),
(66,3255,3255,0),
(68,3275,3255,2),
(69,3253,3253,0),
(70,3276,3253,1),
(71,3275,3253,2),
(72,3278,3278,0),
(73,3275,3278,1),
(74,30041,30041,0),
(75,30044,30041,1),
(76,29808,30041,2),
(77,3275,30041,3),
(78,21717,21717,0),
(79,21693,21717,1),
(80,29808,21717,2),
(81,3275,21717,3),
(82,29829,29829,0),
(83,29827,29829,1),
(84,30311,29829,2),
(85,29808,29829,3),
(86,3275,29829,4),
(87,29839,29839,0),
(88,29828,29839,1),
(89,30311,29839,2),
(90,29808,29839,3),
(91,3275,29839,4),
(92,27438,27438,0),
(93,27434,27438,1),
(94,29808,27438,2),
(95,3275,27438,3),
(96,3276,3255,1),
(97,29819,29819,0),
(98,29808,29819,1),
(99,3275,29819,2),
(100,29972,29972,0),
(101,29971,29972,1),
(102,29808,29972,2),
(103,3275,29972,3),
(104,3382,3382,0),
(105,3304,3382,1),
(106,3275,3382,2);
SELECT object.guid AS object_guid, user.login AS user_login, label.label AS label_label,
ident.email AS ident_email, photo.file_type AS photo_file_type, photo.file_name AS
photo_file_name, photo.file_ext AS photo_file_ext, user.id AS user_id, user.id_object AS
user_id_object, person.id AS person_id, person.id_object AS person_id_object, ident.id AS
ident_id, ident.id_object AS ident_id_object, photo.id AS photo_id, photo.id_object AS
photo_id_object, usergroup.id AS usergroup_id, usergroup.id_object AS
usergroup_id_object, label.id AS label_id, label.id_object AS label_id_object,
BIT_OR(object_acl.rights) AS object_acl_rights FROM objects AS object INNER JOIN user AS
user ON (user.id_object = object.id) INNER JOIN label AS label ON (label.id_object =
object.id AND label.id_version = object.id_version) INNER JOIN identities AS ident ON
(ident.id_object = object.id AND ident.id_version = object.id_version) INNER JOIN file AS
photo ON (photo.id_object = object.id AND photo.id_version = object.id_version) INNER JOIN
person AS person ON (person.id_object = object.id AND person.id_version =
object.id_version) INNER JOIN usergroup AS usergroup ON (usergroup.id_object = object.id)
INNER JOIN object_acl ON object_acl.id_obj_acl = object.id INNER JOIN usergroup_tree AS
usergrouptree ON usergrouptree.id_parent = object_acl.id_group WHERE (object.id_tree =
3912 AND ((usergrouptree.id_child = 3255 AND (object_acl.rights & 1) > 0))) GROUP BY
object_guid, user_login, user_id, user_id_object, label_label, label_id, label_id_object,
ident_email, ident_id, ident_id_object, photo_file_type, photo_file_name, photo_file_ext,
photo_id, photo_id_object, person_id, person_id_object, usergroup_id, usergroup_id_object
ORDER BY user_login ASC;
