Bug #27069 set with identical elements are created
Submitted: 13 Mar 2007 8:20 Modified: 16 Apr 2007 0:55
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.37, 4.1, 5.1, 5.2 OS:Linux (Linux, Win XP - probably any)
Assigned to: Sergei Glukhov CPU Architecture:Any

[13 Mar 2007 8:20] Peter Laursen
Description:
set with identical elements are both created and not created (?) when specified in the create statement.

SHOW CREATE tells there are more identical elements, SELECT that there is only one.

How to repeat:
use zzz;
drop table if exists settest ;
create table settest (id integer, s set('a','a')  NOT NULL);
insert into settest values (1,'a,a');
select * from settest;
/* returns "1,'a' "*/
show create table settest
/* returns
CREATE TABLE `settest` (                
           `id` int(11) default NULL,            
           `s` set('a','a') NOT NULL  -- why not only "`s` set('a') NOT NULL"         
         ) ENGINE=InnoDB DEFAULT CHARSET=latin1
*/

Now are there 1 or 2 elements is this set?

Suggested fix:
Why does not the server see that the set('a','a') 
is a UNION of identical elements and is plain equal to set('a')
(in common mathematics), so that the create statement would reflect that?

Problem is programmatically that when parsing the CREATE STATEMENT you will expect two elements returned from the SELECT ... what could easily create crashes in applications when trying to adress a NULL value (and other issues as well)
[13 Mar 2007 8:52] Sveta Smirnova
Thank you for the report.

Verified as described.
[27 Mar 2007 9:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22990

ChangeSet@1.2416, 2007-03-27 14:54:37+05:00, gluh@mysql.com +4 -0
  Bug#27069 set with identical elements are created
  added the check for unique elements count in SET
[29 Mar 2007 7:55] Alexander Barkov
Patch http://lists.mysql.com/commits/22990 looks ok to push.

Consider adding "SHOW CREATE TABLE" into the test.
[30 Mar 2007 13:43] Timour Katchaounov
Approved.

Please also add a comment above the condition:
  if (sql_field->interval->count -  dup_val_count > sizeof(longlong)*8)
what does it test.

As we discussed also explain to the users why the original report is
not a bug.
[2 Apr 2007 9:58] Sergei Glukhov
set('a','a') is legal in 'create table' statement.
You get a warning in this case in non strict mysqld mode
and an error in strict mode. 'show create' shows data
from 'create' as it was specified in the statement.
The bug in this case is that 
'set' does not allow to have 64 different members.
(see the comment from Sveta).
[2 Apr 2007 10:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/23523

ChangeSet@1.2431, 2007-04-02 15:01:19+05:00, gluh@mysql.com +4 -0
  Bug#27069 set with identical elements are created
  added the check for unique elements count in SET
[9 Apr 2007 12:42] Bugs System
Pushed into 5.1.18-beta
[9 Apr 2007 12:43] Bugs System
Pushed into 5.0.40
[10 Apr 2007 10:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24145

ChangeSet@1.2441, 2007-04-10 15:01:04+05:00, gluh@mysql.com +3 -0
  Bug#27069 set with identical elements are created(additional fix)
  issue an error in strict mode
  if enum|set column has duplicates members in 'create table'
[15 Apr 2007 16:48] Bugs System
Pushed into 5.1.18-beta
[15 Apr 2007 16:53] Bugs System
Pushed into 5.0.40
[16 Apr 2007 0:55] Paul DuBois
Noted in 5.0.40, 5.1.18 changelogs.

Duplicate members in SET definitions were not detected. Now they
result in a warning; if strict SQL mode is enabled, an error occurs 
instead.
[21 May 2008 20:28] Paul DuBois
The bugfix also applies to ENUM.