bug in explain

>Description:
trying the same query ( with different rows but the same order )
"explain" some times
says it will use the primary and some others a filesort

>How-To-Repeat:
create table categorias_sitios_ordenada(
Estado TINYINT(1) not null,
Posicion BigInt(20) unsigned not null,
Nivel TINYINT unsigned,
Nombre varchar(64),
Descripcion varchar(255),
Internal_Code BigInt(20),
AceptaInscripciones enum ( 'S', 'N' ),
Primary Key ( Estado, Posicion )
);

INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre,
Descripcion, Internal_Code, AceptaInscripciones) values ( 0 ,2 ,0
,"Computacion" ,"S\
itios relacionados con computacion y no necesariamente internet", 3, "N")
INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre,
Descripcion, Internal_Code, AceptaInscripciones) values ( 1 ,2 ,1
,"Hardware" ,"Siti\
os relacionados con hardware de computadores", 4, "S")
INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre,
Descripcion, Internal_Code, AceptaInscripciones) values ( 2 ,2 ,1
,"Software" ,"Siti\
os relacionados con software, porgramas, sistemas operativos, cursos,
juegos", 5, "N")
INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre,
Descripcion, Internal_Code, AceptaInscripciones) values ( 3 ,2 ,2 ,"Juegos"
,"Sitios\
relacionados con juegos ( demos, trucos, club de fans)", 7, "S")
INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre,
Descripcion, Internal_Code, AceptaInscripciones) values ( 4 ,2 ,2
,"Programacion" ,"\
Sitios relacionados con lenguajes y tecnicas de programacion", 6, "S")
INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre,
Descripcion, Internal_Code, AceptaInscripciones) values ( 5 ,2 ,0
,"Internet" ,"Siti\
os relacionados con directamente internet, ej: buscadores, intercambios de
links", 0, "N")
INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre,
Descripcion, Internal_Code, AceptaInscripciones) values ( 6 ,2 ,1
,"Buscadores" ,"Si\
tios que permiten buscar informacion sean o no especializados", 1, "S")
INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre,
Descripcion, Internal_Code, AceptaInscripciones) values ( 7 ,2 ,1
,"Rankings" ,"Rank\
ing de paginas, ej: Top 100", 2, "S")

explain SELECt Posicion from categorias_sitios_ordenada WHERE Estado = 1
ORDER BY Posicion, Estado;
explain SELECt Posicion, Nombre from categorias_sitios_ordenada WHERE
Estado = 1 ORDER BY Posicion, Estado;
explain SELECt Posicion, Estado, Nombre from categorias_sitios_ordenada
WHERE Estado = 1 ORDER BY Posicion, Estado;
>Fix:
Dont know

>Submitter-Id: Juan Manuel Doren
>Originator: jmdoren@ok.cl
>Organization:
Juan Manuel Doren
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis:
>Severity: <[ non-critical | serious | critical ] (one line)>
>Priority: <[ low | medium | high ] (one line)>
>Category: mysql
>Class: <[ sw-bug | doc-bug | change-request | support ] (one line)>
>Release: mysql-3.23.52 (Official MySQL RPM)

>Environment:

System: Linux kikolina.ok.cl 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 i686
unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-112)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer
-mpentium' CXX='gcc' CXXFLAGS='-O6
-fno-omit-frame-pointer -felide-constructor\
s -fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Aug 16 03:31 /lib/libc.so.6 ->
libc-2.2.5.so
-rwxr-xr-x 2 root root 1260480 Aug 7 13:09 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2312410 Aug 7 12:46 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Aug 7 12:36 /usr/lib/libc.so
lrwxrwxrwx 1 root root 10 Jul 13 01:37
/usr/lib/libc-client.a -> c-client.a
Configure command: ./configure --disable-shared
--with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static
--with-other-libc=/usr/local/mysql-glibc \
--without-berkeley-db --without-innodb --enable-assembler
--enable-local-infile --with-mysqld-user=mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --\
prefix=/ --with-extra-charsets=complex --exec-prefix=/usr
--libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share
--localstatedir=/var/lib/mysql --info\
dir=/usr/share/info --includedir=/usr/include --mandir=/usr/share/man
'--with-comment=Official MySQL RPM' CC=gcc 'CFLAGS=-O6
-fno-omit-frame-pointer -mpentiu\
m' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' CXX=gcc



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12935@lists.mysql.com
To unsubscribe, e-mail

Re: bug in explain

On Sunday 10 November 2002 07:39, Juan Manuel Doren wrote:

Dear Juan,


I've checked your case with MySQL 3.23.53 but I was unable to repeat the=20
behaviour you refer to.

Are you sure the problem happened without exactly such test case ?
It is not even completely correct (";" missing between queries)

If you just see such behaviour on production "live" system it could be no=
rmal=20
if your data is in the boundary state where just slight modification make=
=20
MySQL to select a different execution path.

If you feel like this really the bug - could you please try recent MySQL=20
3.23.53 and try to provide bug report which works in all cases - it is al=
so=20
possible problem is trigered with some specific options you have in my.cn=
f
so provide this file as well.

> >Description:
> trying the same query ( with different rows but the same order=
)=20
> "explain" some times
> says it will use the primary and some others a filesort
>=20
> >How-To-Repeat:
> create table categorias_sitios_ordenada(
> Estado TINYINT(1) not null,
> Posicion BigInt(20) unsigned not null,
> Nivel TINYINT unsigned,
> Nombre varchar(64),
> Descripcion varchar(255),
> Internal_Code BigInt(20),
> AceptaInscripciones enum ( 'S', 'N' ),
> Primary Key ( Estado, Posicion )
> );
>=20
> INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre=
,=20
> Descripcion, Internal_Code, AceptaInscripciones) values ( 0 ,2 ,0=20
> ,"Computacion" ,"S\
> itios relacionados con computacion y no necesariamente internet", 3, "N=
")
> INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre=
,=20
> Descripcion, Internal_Code, AceptaInscripciones) values ( 1 ,2 ,1=20
> ,"Hardware" ,"Siti\
> os relacionados con hardware de computadores", 4, "S")
> INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre=
,=20
> Descripcion, Internal_Code, AceptaInscripciones) values ( 2 ,2 ,1=20
> ,"Software" ,"Siti\
> os relacionados con software, porgramas, sistemas operativos, cursos,=20
> juegos", 5, "N")
> INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre=
,=20
> Descripcion, Internal_Code, AceptaInscripciones) values ( 3 ,2 ,2 ,"Jue=
gos"=20
> ,"Sitios\
> relacionados con juegos ( demos, trucos, club de fans)", 7, "S")
> INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre=
,=20
> Descripcion, Internal_Code, AceptaInscripciones) values ( 4 ,2 ,2=20
> ,"Programacion" ,"\
> Sitios relacionados con lenguajes y tecnicas de programacion", 6, "S")
> INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre=
,=20
> Descripcion, Internal_Code, AceptaInscripciones) values ( 5 ,2 ,0=20
> ,"Internet" ,"Siti\
> os relacionados con directamente internet, ej: buscadores, intercambios=
de=20
> links", 0, "N")
> INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre=
,=20
> Descripcion, Internal_Code, AceptaInscripciones) values ( 6 ,2 ,1=20
> ,"Buscadores" ,"Si\
> tios que permiten buscar informacion sean o no especializados", 1, "S")
> INSERT INTO categorias_sitios_ordenada (Posicion, Estado, Nivel, Nombre=
,=20
> Descripcion, Internal_Code, AceptaInscripciones) values ( 7 ,2 ,1=20
> ,"Rankings" ,"Rank\
> ing de paginas, ej: Top 100", 2, "S")
>=20
> explain SELECt Posicion from categorias_sitios_ordenada WHERE Estado =3D=
1=20
> ORDER BY Posicion, Estado;
> explain SELECt Posicion, Nombre from categorias_sitios_ordenada WHERE=20
> Estado =3D 1 ORDER BY Posicion, Estado;
> explain SELECt Posicion, Estado, Nombre from categorias_sitios_ordenada=
=20
> WHERE Estado =3D 1 ORDER BY Posicion, Estado;
> >Fix:
> Dont know
>=20
> >Submitter-Id: Juan Manuel Doren
> >Originator: jmdoren@ok.cl
> >Organization:
> Juan Manuel Doren
> >MySQL support: [none | licence | email support | extended email suppo=
rt ]
> >Synopsis:
> >Severity: <[ non-critical | serious | critical ] (one line)>
> >Priority: <[ low | medium | high ] (one line)>
> >Category: mysql
> >Class: <[ sw-bug | doc-bug | change-request | support ] (one=20
line)>
> >Release: mysql-3.23.52 (Official MySQL RPM)
>=20
> >Environment:
>
> System: Linux kikolina.ok.cl 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 i=
686=20
> unknown
> Architecture: i686
>=20
> Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc=20
> /usr/bin/cc
> GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
> gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-112)
> Compilation info: CC=3D'gcc' CFLAGS=3D'-O6 -fno-omit-frame-pointer=20
> -mpentium' CXX=3D'gcc' CXXFLAGS=3D'-O6=20
> -fno-omit-frame-pointer -felide-constructor\
> s -fno-exceptions -fno-rtti -mpentium' LDFLAGS=3D''
> LIBC:
> lrwxrwxrwx 1 root root 13 Aug 16 03:31 /lib/libc.so.6 =
->=20
> libc-2.2.5.so
> -rwxr-xr-x 2 root root 1260480 Aug 7 13:09 /lib/libc-2.2.5=
so
> -rw-r--r-- 1 root root 2312410 Aug 7 12:46 /usr/lib/libc.a
> -rw-r--r-- 1 root root 178 Aug 7 12:36 /usr/lib/libc.s=
o
> lrwxrwxrwx 1 root root 10 Jul 13 01:37=20
> /usr/lib/libc-client.a -> c-client.a
> Configure command: ./configure --disable-shared=20
> --with-mysqld-ldflags=3D-all-static --with-client-ldflags=3D-all-static=
=20
> --with-other-libc=3D/usr/local/mysql-glibc \
> --without-berkeley-db --without-innodb --enable-assembler=20
> --enable-local-infile --with-mysqld-user=3Dmysql=20
> --with-unix-socket-path=3D/var/lib/mysql/mysql.sock --\
> prefix=3D/ --with-extra-charsets=3Dcomplex --exec-prefix=3D/usr=20
> --libexecdir=3D/usr/sbin --sysconfdir=3D/etc --datadir=3D/usr/share=20
> --localstatedir=3D/var/lib/mysql --info\
> dir=3D/usr/share/info --includedir=3D/usr/include --mandir=3D/usr/share=
/man=20
> '--with-comment=3DOfficial MySQL RPM' CC=3Dgcc 'CFLAGS=3D-O6=20
> -fno-omit-frame-pointer -mpentiu\
> m' 'CXXFLAGS=3D-O6 -fno-omit-frame-pointer -felide-constru=
ctors=20
> -fno-exceptions -fno-rtti -mpentium' CXX=3Dgcc
>=20
>=20
>=20
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>=20
> To request this thread, e-mail bugs-thread12935@lists.mysql.com
> To unsubscribe, e-mail
>=20
>=20

--=20
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ www.mysql.com M: +7 095 725 4955


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12936@lists.mysql.com
To unsubscribe, e-mail