Mysql not returning all matching rows

>Description:
Where clause does not return all matching rows. In a left join between
two tables various numbers of rows are returned depending on how mysql optimized
the query

Example

select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
order by s desc limit 100; #returns 2 rows

select count(s) from t2 left join t1 on t2.id=t1.id where t1.name='green' order
by s desc limit 100; #reports 15 rows

select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
and t1.id=10007 order by s desc limit 100; #returns 2 rows

select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
and t2.id=10007 order by s desc limit 100; #returns 15 rows




>How-To-Repeat:
Download and import database tables from
"https://argus.gw.utexas.edu/~spindler/spindlerdb"
or "ftp://support.mysql.com/pub/mysql/secret/spindlerdb.gz"

Perform above queries

>Fix:
Remove limit or remove order by from selects

>Submitter-Id:
>Originator: spindler
>Organization:
University of Texas at Austin
>MySQL support: none
>Synopsis: Select not returning all matching rows
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.4-beta (yes)

>Environment:

System: FreeBSD test-ar3.gw.utexas.edu 4.6.2-RELEASE FreeBSD 4.6.2-RELEASE #4:
Fri Oct 18 15:06:23 CDT 2002 spindler@test-ar3.gw
..utexas.edu:/usr/obj/usr/src/sys/AUTHKERNEL i386


Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.3 20010315 (release) [FreeBSD]
Compilation info: CC='gcc' CFLAGS='-DUSE_OLD_FUNCTIONS' CXX='g++' CXXFLAGS=''
LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1223390 Aug 14 14:19 /usr/lib/libc.a
lrwxrwxrwx 1 root wheel 9 Aug 23 04:49 /usr/lib/libc.so -> libc.so.4
-r--r--r-- 1 root wheel 580520 Aug 14 14:19 /usr/lib/libc.so.4
Configure command: ./configure --prefix=/usr/local/mysql-new --without-bench
--without-docs --without-debug --with-mysqld-user=root
--with-unix-socket-path=/var/db/mysql/mysql.sock --enable-local-infile
--with-comment --with-mysqld-ldflags=-rdynamic CFLAGS=-DUSE_O
LD_FUNCTIONS CFLAGS=-DUSE_OLD_FUNCTIONS


--
David Spindler
TIS-UTnet/THEnet
512-475-9299
512-415-1434
UNCLASSIFIED


------------------------------------------------------------ ---------
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-thread12821@lists.mysql.com
To unsubscribe, e-mail

Re: Mysql not returning all matching rows

On Wednesday 23 October 2002 01:26, David Spindler wrote:
> >Description:
> Where clause does not return all matching rows. In a left join=20
between=20
> two tables various numbers of rows are returned depending on how mysql=20
optimized=20
> the query


Unfortunately you do not provide full example so I can't quickly make sur=
e if=20
this is something already fixed. You have only queries but we also need=
=20
table structure and content to run them.


This really looks like a bug in 4.0.4 which is already fixed in our BK tr=
ee,
and so will be in the next release as well.

You may wish to compile current version from BinKeeper tree to see if it =
works=20
for you. If it does not - just provide complete information and we'll fix=
it.=20

>=20
> Example
>=20
> select s from t2 left join t1 on t2.id=3Dt1.id where t1.name=3D'green'=
=20
> order by s desc limit 100; #returns 2 rows
>=20
> select count(s) from t2 left join t1 on t2.id=3Dt1.id where t1.name=3D=
'green'=20
order=20
> by s desc limit 100; #reports 15 rows
>=20
> select s from t2 left join t1 on t2.id=3Dt1.id where t1.name=3D'green'
> and t1.id=3D10007 order by s desc limit 100; #returns 2 rows
>=20
> select s from t2 left join t1 on t2.id=3Dt1.id where t1.name=3D'green'
> and t2.id=3D10007 order by s desc limit 100; #returns 15 rows
>=20
>=20
>=20
>=20
> >How-To-Repeat:
> =09Download and import database tables from=20
> =09"https://argus.gw.utexas.edu/~spindler/spindlerdb"=20
> =09or "ftp://support.mysql.com/pub/mysql/secret/spindlerdb.gz"
>=20
> =09Perform above queries
>=20
> >Fix:
> =09Remove limit or remove order by from selects=20
>=20
> >Submitter-Id:
> >Originator: spindler
> >Organization:
> =09University of Texas at Austin
> >MySQL support: none
> >Synopsis: Select not returning all matching rows
> >Severity: serious
> >Priority: medium
> >Category: mysql
> >Class: sw-bug
> >Release: mysql-4.0.4-beta (yes)
>=20
> >Environment:
>
> System: FreeBSD test-ar3.gw.utexas.edu 4.6.2-RELEASE FreeBSD 4.6.2-RELE=
ASE=20
#4:=20
> Fri Oct 18 15:06:23 CDT 2002 spindler@test-ar3.gw
> .utexas.edu:/usr/obj/usr/src/sys/AUTHKERNEL i386
>=20
>=20
> Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/=
gcc=20
> /usr/bin/cc
> GCC: Using builtin specs.
> gcc version 2.95.3 20010315 (release) [FreeBSD]
> Compilation info: CC=3D'gcc' CFLAGS=3D'-DUSE_OLD_FUNCTIONS' CXX=3D'g+=
+' =20
CXXFLAGS=3D'' =20
> LDFLAGS=3D''
> LIBC:
> -r--r--r-- 1 root wheel 1223390 Aug 14 14:19 /usr/lib/libc.a
> lrwxrwxrwx 1 root wheel 9 Aug 23 04:49 /usr/lib/libc.so -> libc.so.4
> -r--r--r-- 1 root wheel 580520 Aug 14 14:19 /usr/lib/libc.so.4
> Configure command: ./configure --prefix=3D/usr/local/mysql-new --withou=
t-bench=20
> --without-docs --without-debug --with-mysqld-user=3Droot
> --with-unix-socket-path=3D/var/db/mysql/mysql.sock --enable-local-infil=
e=20
> --with-comment --with-mysqld-ldflags=3D-rdynamic CFLAGS=3D-DUSE_O
> LD_FUNCTIONS CFLAGS=3D-DUSE_OLD_FUNCTIONS
>=20
>=20
> --=20
> David Spindler
> TIS-UTnet/THEnet
> 512-475-9299
> 512-415-1434
> UNCLASSIFIED
>=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-thread12821@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-thread12824@lists.mysql.com
To unsubscribe, e-mail

Re: Mysql not returning all matching rows

Hello,
On Wed, 2002-10-23 at 00:26, David Spindler wrote:
> >Description:
> Where clause does not return all matching rows. In a left join between
> two tables various numbers of rows are returned depending on how mysql optimized
> the query
>
> Example
>

With your tables as found in spindlerdb.gz...

> select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
> order by s desc limit 100; #returns 2 rows

....this query returns 15 rows for me.
How happened you get only 2?

> select count(s) from t2 left join t1 on t2.id=t1.id where t1.name='green' order
> by s desc limit 100; #reports 15 rows

You must keep in mind that this query can return different number than
number of rows. In other words:

select count(column) as num_rows from tbl;

num_rows is not necesarrliy equal to number of rows returned by:

select column from tbl;

Reason is that count() skips NULLs

> select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
> and t1.id=10007 order by s desc limit 100; #returns 2 rows

Again it returns 15 rows for me.

> select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
> and t2.id=10007 order by s desc limit 100; #returns 15 rows

Note that above queries has quite different WHERE clauses and you should
expect different results. First one refers Right table where all columns
can be filled with NULLs because of LEFT JOIN.

btw. you have redundant key in your table t2

PRIMARY KEY (`id`),
KEY `authid` (`id`)
^^^^^^^^^^^^^^
This key only waste space as it is same as PK


--
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com M: +359 88 231668



------------------------------------------------------------ ---------
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-thread12825@lists.mysql.com
To unsubscribe, e-mail

Re: Mysql not returning all matching rows

Hello,

Even I get the correct results. I am able to get 15 rows for your query
where you
get 2 rows. What version of MySQL are you using?

-- Ram

----- Original Message -----
From: "Alexander Keremidarski"
To: "David Spindler"
Cc:
Sent: Wednesday, October 23, 2002 1:27 PM
Subject: Re: Mysql not returning all matching rows


> Hello,
> On Wed, 2002-10-23 at 00:26, David Spindler wrote:
> > >Description:
> > Where clause does not return all matching rows. In a left join
between
> > two tables various numbers of rows are returned depending on how mysql
optimized
> > the query
> >
> > Example
> >
>
> With your tables as found in spindlerdb.gz...
>
> > select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
> > order by s desc limit 100; #returns 2 rows
>
> ...this query returns 15 rows for me.
> How happened you get only 2?
>
> > select count(s) from t2 left join t1 on t2.id=t1.id where
t1.name='green' order
> > by s desc limit 100; #reports 15 rows
>
> You must keep in mind that this query can return different number than
> number of rows. In other words:
>
> select count(column) as num_rows from tbl;
>
> num_rows is not necesarrliy equal to number of rows returned by:
>
> select column from tbl;
>
> Reason is that count() skips NULLs
>
> > select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
> > and t1.id=10007 order by s desc limit 100; #returns 2 rows
>
> Again it returns 15 rows for me.
>
> > select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
> > and t2.id=10007 order by s desc limit 100; #returns 15 rows
>
> Note that above queries has quite different WHERE clauses and you should
> expect different results. First one refers Right table where all columns
> can be filled with NULLs because of LEFT JOIN.
>
> btw. you have redundant key in your table t2
>
> PRIMARY KEY (`id`),
> KEY `authid` (`id`)
> ^^^^^^^^^^^^^^
> This key only waste space as it is same as PK
>
>
> --
> For technical support contracts, visit https://order.mysql.com/?ref=msal
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Alexander
Keremidarski
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> <___/ www.mysql.com M: +359 88 231668
>
>
>
> ------------------------------------------------------------ ---------
> 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-thread12825@lists.mysql.com
> To unsubscribe, e-mail


------------------------------------------------------------ ---------
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-thread12826@lists.mysql.com
To unsubscribe, e-mail

Re: *****SPAM***** Re: Mysql not returning all matching rows

> Hello,
>
> Even I get the correct results. I am able to get 15 rows for your query
> where you
> get 2 rows. What version of MySQL are you using?
>
> -- Ram
>
> ----- Original Message -----
> From: "Alexander Keremidarski"
> To: "David Spindler"
> Cc:
> Sent: Wednesday, October 23, 2002 1:27 PM
> Subject: Re: Mysql not returning all matching rows
>
>
> > Hello,
> > On Wed, 2002-10-23 at 00:26, David Spindler wrote:
> > > >Description:
> > > Where clause does not return all matching rows. In a left join
> between
> > > two tables various numbers of rows are returned depending on how mysql
> optimized
> > > the query
> > >
> > > Example
> > >
> >
> > With your tables as found in spindlerdb.gz...
> >
> > > select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
> > > order by s desc limit 100; #returns 2 rows
Are you using the latest Version 4.0.4 (mysql-4.0.4-beta.tar) version of mysql
and running all four queries? With the data provided they should all
report/return 15 rows.
> > ...this query returns 15 rows for me.
> > How happened you get only 2?



> > > select count(s) from t2 left join t1 on t2.id=t1.id where
> t1.name='green' order
> > > by s desc limit 100; #reports 15 rows

> > You must keep in mind that this query can return different number than
> > number of rows. In other words:
> >
> > select count(column) as num_rows from tbl;
> > num_rows is not necesarrliy equal to number of rows returned by:
> > select column from tbl;
> > Reason is that count() skips NULLs
Understood, in this case their are no NULLS.

> > > select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
> > > and t1.id=10007 order by s desc limit 100; #returns 2 row
> > Again it returns 15 rows for mem
> > > select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
> > > and t2.id=10007 order by s desc limit 100; #returns 15 rows

> > Note that above queries has quite different WHERE clauses and you should
> > expect different results. First one refers Right table where all columns
> > can be filled with NULLs because of LEFT JOIN.
True but the top query with just "where t1.name='green' is the most general and
should return all 15 rows, not just 2 rows.


> > btw. you have redundant key in your table t2
> >
> > PRIMARY KEY (`id`),
> > KEY `authid` (`id`)
> > ^^^^^^^^^^^^^^
> > This key only waste space as it is same as PK
Yes, These tables used to be much larger. I've dropped a bunch of columns so the
those keys are now redundant.

I haven't downloaded the newest bin which might have the bug fix yet, but
as soon as you come out with the source for 4.0.5 I'll compile and see if
fixes the bug. I noticed that Sinisa pointed out this looks like a previous bug
"Fixed some ORDER BY ... DESC problems with InnoDB tables.", but these tables
are not InnodDB tables




In the meantime I've set up an open mysql server with the problem at
128.83.118.11
Username :root/no password
database test.

The following queries should all return 15 rows, but do not.

select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
order by s desc limit 100; #returns 2 rows

select count(s) from t2 left join t1 on t2.id=t1.id where t1.name='green' order

by s desc limit 100; #reports 15 rows

select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
and t1.id=10007 order by s desc limit 100; #returns 2 rows

select s from t2 left join t1 on t2.id=t1.id where t1.name='green'
and t2.id=10007 order by s desc limit 100; #returns 15 rows




------------------------------------------------------------ ---------
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-thread12837@lists.mysql.com
To unsubscribe, e-mail