MySQL Bugs: #10906: SELECT with NOT EXISTS fails

Bug #10906	SELECT with NOT EXISTS fails
Submitted:	27 May 2005 8:44	Modified:	25 Jul 2005 3:41
Reporter:	pedro SANCHEZ	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S1 (Critical)
Version:	4.1	OS:	Windows (xp)
Assigned to:		CPU Architecture:	Any

Description:
I have discovered that a SELECT with NOT EXISTS returns a bad result distinct from expected: 

select * from e where not exists (select * from p where e.code=p.codeE) 

IF THIS SELECT IS REWRITTEN AS 

select * from e where not exists (select codeE from p where e.code=p.codeE) 

THEN IT WORKS WELL !! 

It is, when putting a only column in the inner select the result is OK and if I put the '*' the result is NOT OK.

Why is difference in the result ?

(I have been working with SQL during 15 years)

WHY ?

How to repeat:
I can send the database with the data and the select detailed, if needed.

I forgot to comment that there is no null values managed in any conditions of the SELECT.

I enclose the complete situation to be repeated if needed:

-- Create table

create table especialidades(codigo int not null, descripcion varchar(30), constraint cp_espe PRIMARY key (codigo)) type=InnoDB;

create table personalSanitario(codigo int not null, dni char(10), nombre char(30), numColegiado int, 
esJefe bool not null, anyoTitulo tinyInt not null, esMedico bool not null DEFAULT 'false', 
codigoSuEspecialidad int not null, codigoTurno int not null, codigoEspe int not null,
constraint cp_personalSanitario primary key(codigo),
constraint unps_dni unique(dni), index(codigoSuEspecialidad),
constraint ajps_espe foreign key (codigoSuEspecialidad) references especialidades(codigo), index(codigoTurno, codigoEspe),
constraint ajps_turnespe foreign key (codigoTurno, codigoEspe) references turnosEspecialidad(codTurno, codEspe)) type=InnoDB;

create table turnosEspecialidad (codTurno int not null, codEspe int not null,
constraint cp_turnosEspe primary key (codTurno, codEspe), index(codTurno),
constraint caj_turno foreign key (codTurno) references turnos(codigo), index(codEspe),
constraint cal_espe foreign key (codEspe) references especialidades(codigo)) type=InnoDB;

create table turnos(codigo int not null, descripcion varchar(30), constraint cp_turnos primary key(codigo)) type=InnoDB;

-- INSERT DATA

INSERT INTO especialidades (codigo, descripcion) VALUES("1", "pediatría");
INSERT INTO especialidades (codigo, descripcion) VALUES("2", "traumatología");
INSERT INTO especialidades (codigo, descripcion) VALUES("3", "dermatología");
INSERT INTO especialidades (codigo, descripcion) VALUES("4", "ginecología");
INSERT INTO especialidades (codigo, descripcion) VALUES("5", "cardiología");
INSERT INTO especialidades (codigo, descripcion) VALUES("6", "gerontología");
INSERT INTO especialidades (codigo, descripcion) VALUES("7", "medico de familia");
INSERT INTO especialidades (codigo, descripcion) VALUES("8", "oncología");
INSERT INTO especialidades (codigo, descripcion) VALUES("9", "oftalmología");
INSERT INTO especialidades (codigo, descripcion) VALUES("10", "estomatología");
INSERT INTO especialidades (codigo, descripcion) VALUES("11", "otorrinilarongología");
INSERT INTO especialidades (codigo, descripcion) VALUES("12", "hematología");
INSERT INTO especialidades (codigo, descripcion) VALUES("13", "neurología");
INSERT INTO especialidades (codigo, descripcion) VALUES("14", "psiquiatría");

INSERT INTO turnos (codigo, descripcion) VALUES("1", "mañana");
INSERT INTO turnos (codigo, descripcion) VALUES("2", "tarde");
INSERT INTO turnos (codigo, descripcion) VALUES("3", "noche");

INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("1", "1");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("1", "2");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("1", "5");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("1", "6");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("1", "8");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("1", "9");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("1", "12");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("2", "2");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("2", "5");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("2", "10");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("2", "12");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("2", "13");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("3", "3");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("3", "4");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("3", "6");
INSERT INTO turnosespecialidad (codTurno, codEspe) VALUES("3", "7");

INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("1", "54445588", "Benito Sanchez", "145", "1", "66", "1", "1", "1", "1");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("2", "111444848", "García Lorca", "456", "0", "69", "1", "4", "2", "2");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("3", "84447782", "Pedro de Lorca", "451", "0", "52", "1", "2", "2", "2");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("4", "74954325", "María Saura", "4551", "0", "78", "1", "3", "3", "4");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("5", "56466620", "Mercedes Milá", "748", "0", "98", "0", "5", "2", "5");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("6", "12121211", "Pedro Guerra", "4587", "1", "67", "1", "6", "1", "6");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("7", "5555525", "Alfonso Faz", "7489", "0", "88", "1", "7", "2", "2");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("8", "88888888", "Javier del Mar", "478", "0", "65", "1", "12", "1", "12");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("9", "4441144", "Lanjarón del Valle", "450", "0", "88", "0", "9", "2", "13");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("10", "4441005", "Estefanía de Mónaco", "745", "0", "96", "1", "9", "1", "9");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("11", "2222222", "Alfonso Guerra", "895", "0", "63", "1", "8", "3", "7");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("12", "1100225", "Eduardo VilaPlana", "859", "1", "56", "1", "5", "2", "5");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("13", "0222002", "Juan Sin Miedo", "663", "0", "66", "1", "5", "2", "5");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("14", "2366525", "Javier Mendez", "120", "0", "90", "1", "3", "3", "4");
INSERT INTO personalsanitario (codigo, dni, nombre, numColegiado, esJefe, anyoTitulo, esMedico, codigoSuEspecialidad, codigoTurno, codigoEspe) VALUES("15", "78448456", "Florencio Martínez", "787", "1", "55", "1", "13", "2", "13");

-- THE SELECT WITH PROBLEMS

select * FROM especialidades e where not exists
(select * from personalsanitario p where esMedico=1 and p.codigoEspe = e.codigo)

-- RESULT

codigo descripcion 
2 traumatología 
3 dermatología 
6 gerontología 
8 oncología 
10 estomatología 
11 otorrinilarongología 
14 psiquiatría 

-- THE ERROR: 

Why ?

The row (2, traumatología) of the result is wrong. (there are others more).

Which server release version are you using, with the today release from your
query sample I got:

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.12-nt |
+-----------+
1 row in set (0.03 sec)

mysql> select * FROM especialidades e where not exists
    -> (select * from personalsanitario p where esMedico=1 and p.codigoEspe =
    -> e.codigo);
+--------+----------------------+
| codigo | descripcion          |
+--------+----------------------+
|      3 | dermatología         |
|      8 | oncología            |
|     10 | estomatología        |
|     11 | otorrinilarongología |
|     14 | psiquiatría          |
+--------+----------------------+
5 rows in set (0.00 sec)

mysql>

The version is 4.1.10-nt

very close to the yours, isnt it?

many thanks for your time.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Pedro, which collate do you use for tables?

Dear Sir,

Excuse me but I do not understand the terms "collate". The tables are InnoDB as the script can shows.

Please, give more data about your question

Thank you very much for your answer.

Pedro

I was unable to reproduce this bug using 4.1.13:

mysql> select * from especialidades e where not exists (select * from personalSanitario p where e.codigo=p.codigo);
+--------+---------------------+
| codigo | descripcion         |
+--------+---------------------+
|      1 | pediatra            |
|      2 | traumatologa        |
|      3 | dermatologa         |
|      4 | ginecologa          |
|      5 | cardiologa          |
|      6 | gerontologa         |
|      7 | medico de
familia   |
|      8 | oncologa            |
|      9 | oftalmologa         |
|     10 | estomatologa        |
|     11 | otorrinilarongologa |
|     12 | hematologa          |
|     13 | neurologa           |
|     14 | psiquiatra          |
+--------+---------------------+
14 rows in set (0.00 sec)