Bug #54434 LEFT JOIN on federated view always results in NULL
Submitted: 11 Jun 2010 15:55 Modified: 11 Jun 2010 17:55
Reporter: Rob Hoelz Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S2 (Serious)
Version:5.1.47 OS:Linux (CentOS 5.5)
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[11 Jun 2010 15:55] Rob Hoelz
Description:
If you have a table on a server (let's call it sql-1) that uses a federated table to access a remote view (on sql-2), left joining against that view results in all columns in the view yielding NULL.

Example:

On sql-2:

CREATE TABLE TestView (
  value INT
);

On sql-1:

CREATE TABLE FedView (
  value INT
) ENGINE=Federated CONNECTION='sql-2/TestView';

SELECT fv.value FROM SomeTable AS st LEFT JOIN FedView AS fv ON fv.value = st.id;
-- fv.value will be NULL, even if the join matched a row

How to repeat:
Create a temporary directory, and run this script within it:

#!/usr/bin/perl 

use strict;
use warnings;

use Cwd qw(getcwd);
use DBI;
use Fatal qw(mkdir open);
use File::Spec::Functions qw(catfile devnull);

########## Perl black magic for portable process killing and colors ############
###################### Not relevant to the bug in MySQL! #######################
BEGIN {
    eval {
        require POSIX;
        *send_term = sub {
            kill &POSIX::SIGTERM, $_[0];
        };

        if(-t STDOUT) {
            # This function assumes that if you're on a POSIX system, you're running an
            # ANSI-compatiable terminal
            *yellow = sub {
                return "\033[33m$_[0]\033[0m";
            };
        } else {
            *yellow = sub {
                return $_[0];
            };
        }
    };
    if($@) {
        eval {
            require Win32::Process;
            Win32::Process->import;
            *send_term = sub {
                Win32::Process::KillProcess($_[0], 1);
            };
            # I don't know enough about Win32 to implement colors here...
            *yellow = sub {
                return $_[0];
            };
        };
        if($@) {
            die "Neither the POSIX nor the Win32::Process module could be found; exiting\n";
        }
    }
}

my $cwd = getcwd;
my $null = devnull;

my $data_location_primary = catfile($cwd, 'data_primary');
my $socket_location_primary = catfile($cwd, 'mysqld_primary.sock');
my $pid_location_primary = catfile($cwd, 'primary.pid');

my $data_location_secondary = catfile($cwd, 'data_secondary');
my $socket_location_secondary = catfile($cwd, 'mysqld_secondary.sock');
my $pid_location_secondary = catfile($cwd, 'secondary.pid');

sub unslurp {
    my ( $filename, $contents ) = @_;

    my $fh;
    open $fh, '>', $filename;
    print $fh $contents;
    close $fh;
}

sub slurp {
    my ( $filename ) = @_;

    local $/;

    my $fh;
    open $fh, '<', $filename;
    my $content = <$fh>;
    close $fh;
    
    return $content;
}

sub wipe_old_files {
    system 'rm', '-rf', $data_location_primary, $data_location_secondary, 'my_primary.cnf', 'my_secondary.cnf';
}

sub install_mysql_files {
    mkdir 'data_primary';
    mkdir 'data_secondary';

    system "mysql_install_db --datadir=$data_location_primary >/dev/null 2>&1";
    system "mysql_install_db --datadir=$data_location_secondary >/dev/null 2>&1";
}

sub write_configuration {
    unslurp 'my_primary.cnf', <<MY_CNF;
[client]
socket = $socket_location_primary

[mysqld]
socket = $socket_location_primary
datadir = $data_location_primary
pid-file = $pid_location_primary
skip-networking
federated
MY_CNF

    unslurp 'my_secondary.cnf', <<MY_CNF;
[client]
socket = $socket_location_secondary

[mysqld]
socket = $socket_location_secondary
datadir = $data_location_secondary
pid-file = $pid_location_secondary
skip-networking
federated
MY_CNF
}

sub start_servers {
    system "mysqld_safe --defaults-file=my_primary.cnf >$null 2>&1&";
    system "mysqld_safe --defaults-file=my_secondary.cnf >$null 2>&1&";

    print STDERR "servers started; waiting 10 seconds for them to get settled\n";
    sleep 10;
}

sub create_tables {
    my $dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_primary.sock', 'root', '', {
        PrintError => 0,
        RaiseError => 1,
    });
    my $sql = <<SQL;
CREATE DATABASE testdb;

CREATE SERVER secondary FOREIGN DATA WRAPPER mysql
OPTIONS (DATABASE 'testdb', USER 'root', SOCKET '$socket_location_secondary');

CREATE TABLE testdb.TestTable (
    value INT
);

CREATE TABLE testdb.FedTable (
    value INT
) ENGINE=FEDERATED CONNECTION='secondary/TestTable';

CREATE TABLE testdb.FedView (
    value INT
) ENGINE=FEDERATED CONNECTION='secondary/TestView';
SQL

    print yellow("Setting up tables on primary:\n");
    print $sql, "\n";
    my @sql = split ';', $sql;
    foreach $sql (@sql) {
	next unless $sql =~ /\S/;
        $dbh->do($sql);
    }

    $dbh->disconnect;

    $dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_secondary.sock', 'root', '', {
        PrintError => 0,
        RaiseError => 1,
    });

    $sql = <<SQL;
CREATE DATABASE testdb;

CREATE TABLE testdb.TestTable (
    value INT
);

CREATE VIEW testdb.TestView AS SELECT * FROM testdb.TestTable;
SQL

    print yellow("Setting up tables on secondary:\n");
    print $sql, "\n";
    @sql = split /;/, $sql;
    foreach $sql (@sql) {
	next unless $sql =~ /\S/;
        $dbh->do($sql);
    }

    $dbh->disconnect;
}

sub insert_fake_data {
    my $dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_primary.sock:database=testdb', 'root', '', {
        PrintError => 0,
        RaiseError => 1,
    });

    my $sth = $dbh->prepare('INSERT INTO TestTable VALUES (?)');
    for(my $i = 1; $i <= 9; $i += 2) {
        $sth->execute($i);
    }
    for(my $i = 15; $i <= 20; $i++) {
        $sth->execute($i);
    }

    undef $sth;
    $dbh->disconnect;

    $dbh = DBI->connect('dbi:mysql:mysql_socket=mysqld_secondary.sock:database=testdb', 'root', '', {
        PrintError => 0,
        RaiseError => 1,
    });
    $sth = $dbh->prepare('INSERT INTO TestTable VALUES (?)');
    for(my $i = 2; $i <= 10; $i += 2) {
        $sth->execute($i);
    }
    for(my $i = 15; $i <= 20; $i++) {
        $sth->execute($i);
    }
    undef $sth;
    $dbh->disconnect;
}

sub kill_servers {
    my $pid = slurp $pid_location_primary;
    chomp $pid;
    send_term $pid;
    $pid = slurp $pid_location_secondary;
    chomp $pid;
    send_term $pid;
}

sub dump_query {
    my ( $instance, $sql ) = @_;

    my $dbh = DBI->connect("dbi:mysql:mysql_socket=mysqld_$instance.sock:database=testdb", 'root', '', {
        PrintError => 0,
        RaiseError => 1,
    });
    my $text = sprintf("Running SQL on %s: %s", $instance, yellow($sql));
    print $text, "\n";
    print '-' x length($text), "\n";
    print '', "\n";

    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my @columns = @{$sth->{NAME}};
    my @max_lengths = map { length } @columns;
    my $rows = $sth->fetchall_arrayref;

    foreach my $row (@$rows) {
        @$row = map { defined() ? $_ : 'NULL' } @$row;
        for(my $i = 0; $i < @$row; $i++) {
            my $max_len = $max_lengths[$i];
            my $len = length($row->[$i]);
            $max_lengths[$i] = $len if $len > $max_len;
        }
    }
    my $format = join(' | ', map { "%${_}s" } @max_lengths) . "\n";

    printf $format, @columns;
    printf $format, map { '-' x $_ } @max_lengths;
    printf $format, @$_ foreach @$rows;

    $sth->finish;
    undef $sth;

    $dbh->disconnect;
}

wipe_old_files;
install_mysql_files;
write_configuration;
start_servers;
eval {
    create_tables;
    insert_fake_data;

    dump_query 'primary', 'SELECT * FROM TestTable';
    dump_query 'secondary', 'SELECT * FROM TestTable';
    dump_query 'secondary', 'SELECT * FROM TestView';
    dump_query 'primary', 'SELECT tt.value AS local, ft.value AS remote FROM TestTable AS tt LEFT JOIN FedTable as ft ON tt.value = ft.value';
    dump_query 'primary', 'SELECT tt.value AS local, fv.value AS remote FROM TestTable AS tt LEFT JOIN FedView as fv ON tt.value = fv.value';
};
if($@) {
    warn $@;
}
kill_servers;
[11 Jun 2010 17:55] Sveta Smirnova
Thank you for the report.

Verified as described.
[17 Jun 2010 9:23] He yunfei
Verified as described on 5.1.40 too, 

when to resolove this bug .
[24 Aug 2016 15:30] Andreas Schnederle-Wagner
Any News on this? As this is a show stopper right now.