>Description:
The following silly query (silly because it should return no
records since it is impossible for a.type to have two different
values simultaneously), returns two records when it should
return none with the enclosed test data.
select a.*, b.* from dns_rec a left join dns_rec b
on (a.zone = b.zone and
a.rightside = b.rightside and
a.type = 'A' and b.type = 'A' and
a.leftside = '' and b.leftside != '')
where b.zone is null and a.type = 'MX'
;
>How-To-Repeat:
Restore following dump, containing 19 records. Run above query.
If you drop the index right_idx, you get the correct result, but
this makes the REAL intended query (the above query without the
"and a.type = 'MX'" part) run much MUCH slower than is tolerable
(I've never had time to let it finish) for the full database
(about 200,000 records). You get the same wrong answer if
right_idx is an index on rightside only.
This data is from a DNS database. The fields zone and rightside
have been replaced with a hash, but the problem is still
reproducable with the hash. The intent of the query is to
find all the top-level (name is the same as name of the zone,
e.g. foobar.com) A records which do not have a non-top-level
A record pointing at the same place (e.g. www.foobar.com).
I get back almost all the records.
I have tried checking indexes and dropping the table and reloading.
Corrupted tables or indexes do not seem to be an issue.
-- MySQL dump 8.22
--
-- Host: mysql.burditt.org Database: p1
---------------------------------------------------------
-- Server version 3.23.54-log
--
-- Table structure for table 'dns_rec'
--
CREATE TABLE dns_rec (
zone char(191) NOT NULL default '',
leftside char(63) NOT NULL default '',
class char(2) NOT NULL default 'IN',
ttl int(11) default NULL,
type char(6) NOT NULL default '',
mxpri int(11) default NULL,
rightside char(255) NOT NULL default '',
mod_user char(8) default NULL,
mod_time datetime default NULL,
id int(11) NOT NULL auto_increment,
dorev enum('Y','N') NOT NULL default 'Y',
PRIMARY KEY (id),
KEY zone_key (zone,leftside),
KEY right_idx (rightside,leftside)
) TYPE=MyISAM;
--
-- Dumping data for table 'dns_rec'
--
INSERT INTO dns_rec VALUES
('b718178a4a43a90edc77c32edc357c41','www','IN',86400,'A',NULL,'78668248f0026f1dee9f3d3615c6a3c1','gordonb','2003-01-20
17:12:25',20000,'Y');
INSERT INTO dns_rec VALUES
('76649f82176bede3d63adda4b15b3f6c','216-118-17-94','IN',86400,'A',NULL,'b5272e80685c0d44a2efdcca42915e23','gordonb','2003-01-20
17:12:25',30000,'Y');
INSERT INTO dns_rec VALUES
('c7ad2afab901a4b7725cc9236f612186','','IN',43200,'MX',10,'b83a886a5c437ccd9ac15473fd6f1788','gordonb','2003-01-20
17:12:25',1000000,'Y');
INSERT INTO dns_rec VALUES
('76649f82176bede3d63adda4b15b3f6c','216-118-35-215','IN',86400,'A',NULL,'1cd28f7acc17907519923f290fa8bd90','gordonb','2003-01-20
17:12:25',40000,'Y');
INSERT INTO dns_rec VALUES
('cccc10f95129f591c33d569825d519ac','87','IN',86400,'PTR',NULL,'cac23788d6426b77981900cccfa97344','gordonb','2003-01-20
17:12:25',50000,'Y');
INSERT INTO dns_rec VALUES
('93b473baaea76836872b456eccf133f6','98','IN',NULL,'A',NULL,'b620fc599397b9bcf3197f8b5720c786','gordonb','2003-01-20
17:12:25',3080000,'Y');
INSERT INTO dns_rec VALUES
('ecf4d945c6e819af75f4612609b0f1de','wrangler','IN',86400,'CNAME',NULL,'678fac0aa4714c742c6341d07ee6a347','gordonb','2003-01-20
17:12:25',100000,'Y');
INSERT INTO dns_rec VALUES
('287c097c2069bc00305c93f6a0cc214c','mail','IN',86400,'A',NULL,'83fcc28f1c5857ba69d7c5e055714d7e','gordonb','2003-01-20
17:12:25',110000,'Y');
INSERT INTO dns_rec VALUES
('ba1c0f8033b7700750fcd44e99258442','cf9e7efe','IN',NULL,'A',NULL,'8ef3484a73acce6c80c2cd9d8be20ec1','gordonb','2003-01-20
17:12:25',980000,'Y');
INSERT INTO dns_rec VALUES
('1ca3c3722a33ec06c7ab889e2308634c','97','IN',NULL,'A',NULL,'5ebcd38d80575ab790f0e86c92a7af23','gordonb','2003-01-20
17:12:25',1060000,'Y');
INSERT INTO dns_rec VALUES
('6f065a6b44888e4f43f2ec31411b2f49','pppte04-422','IN',NULL,'A',NULL,'4133269be2eb34a5c82d964a64ddbefb','gordonb','2003-01-20
17:12:25',2850000,'Y');
INSERT INTO dns_rec VALUES
('b36b250e8c66b2b3b133219b80dbe079','','IN',NULL,'MX',10,'d59ceba889986c4fdfbe27c3c8a919d6','gordonb','2003-01-20
17:12:25',2010000,'Y');
INSERT INTO dns_rec VALUES
('e2777c044daf17127184f8033c751249','28','IN',NULL,'A',NULL,'bc35afd157ffa6c8729557a6fb62810d','gordonb','2003-01-20
17:12:25',2340000,'Y');
INSERT INTO dns_rec VALUES
('00105a3bcac286c9f9487dfe896ad220','0b','IN',NULL,'A',NULL,'d982c79ede90bc888c2ec9757f143e9d','gordonb','2003-01-20
17:12:25',2900000,'Y');
INSERT INTO dns_rec VALUES
('eb74f350c3ac84314caca073ec778f2a','54','IN',NULL,'A',NULL,'72ccd8a4558c99fa55fca52f96585d51','gordonb','2003-01-20
17:12:25',2970000,'Y');
INSERT INTO dns_rec VALUES
('9eb0667cd82601f86b1e5c1bfac272b5','3b','IN',NULL,'A',NULL,'072dda1bea720f07d2a262ed7a976ff6','gordonb','2003-01-20
17:12:25',2980000,'Y');
INSERT INTO dns_rec VALUES
('355d0d6199452a39e70d44353337a777','d8','IN',NULL,'A',NULL,'df835ba0b73348a43db8cf1845bdb431','gordonb','2003-01-20
17:12:25',3020000,'Y');
INSERT INTO dns_rec VALUES
('3aceea99953c7feadfb076ad1f69f401','82','IN',NULL,'A',NULL,'7ae650b7b7483c4377ca0f48f28e821d','gordonb','2003-01-20
17:12:25',3030000,'Y');
INSERT INTO dns_rec VALUES
('f65782e0966b230cac422fdb73d377cd','2a','IN',NULL,'A',NULL,'89977656fc335b5d3cdb3387a3ce9d23','gordonb','2003-01-20
17:12:25',3040000,'Y');
>Fix:
Dropping right_idx fixes the query for the test data but it makes
it impossibly slow for the full database.
>Submitter-Id: <submitter ID>
>Originator: Gordon Burditt
>Organization:
Internet America
>MySQL support: none [none | licence | email support | extended email support ]
>Synopsis: Left join returns records it shouldn't
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.54 (FreeBSD port: mysql-server-3.23.54)
>Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.54, for portbld-freebsd4.7
>on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.54-log
Protocol version 10
Connection mysql.burditt.org via TCP/IP
TCP port 3306
Uptime: 35 days 23 hours 2 min 29 sec
Threads: 1 Questions: 1736390 Slow queries: 1517 Opens: 445 Flush tables: 1 Open
tables: 64 Queries per second avg: 0.559
>Environment:
System: FreeBSD hammy.lonestar.org 4.7-RELEASE FreeBSD 4.7-RELEASE #0: Tue Dec 3
22:49:06 CST 2002 [EMAIL PROTECTED]:/scratch5/i386-obj/usr/src/sys/HAMMY
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.4 20020320 [FreeBSD]
Compilation info: CC='cc' CFLAGS='-O -pipe ' CXX='cc' CXXFLAGS='-O -pipe
-felide-constructors -fno-rtti -fno-exceptions' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1218496 Dec 3 23:29 /usr/lib/libc.a
lrwxrwxr-x 1 root wheel 9 Dec 3 23:47 /usr/lib/libc.so -> libc.so.4
-r--r--r-- 1 root wheel 574916 Dec 3 23:47 /usr/lib/libc.so.4
Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-perl'
'--without-debug' '--without-readline' '--without-bench' '--with-mit-threads=no'
'--with-libwrap' '--with-low-memory' '--with-comment=FreeBSD port:
mysql-server-3.23.54' '--program-prefix=' '--with-innodb' '--enable-assembler'
'--with-berkeley-db' '--prefix=/usr/local' 'i386-portbld-freebsd4.7' 'CFLAGS=-O -pipe
' 'CXX=cc' 'host_alias=i386-portbld-freebsd4.7' 'build_alias=i386-portbld-freebsd4.7'
'target_alias=i386-portbld-freebsd4.7' 'CC=cc' 'CXXFLAGS=-O -pipe
-felide-constructors -fno-rtti -fno-exceptions'
Perl: This is perl, version 5.005_03 built for i386-freebsd
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
