Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-2770

LP:671901 - No rows returned with (flat, BNLH join) in maria-5.3-mwl128

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: None
        • Fix Version/s: None
        • Component/s: None
        • Labels:

          Description

          The following query

          SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key;

          returns no rows when executed with (flat, BNLH join) , but returns rows when executed with full table scan and classical nested loop join.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                philipstoev Philip Stoev added a comment -

                Re: No rows returned with (flat, BNLH join) in maria-5.3-mwl128
                Test case:

                --disable_warnings
                DROP TABLE /*! IF EXISTS */ t1;
                DROP TABLE /*! IF EXISTS */ t2;
                --enable_warnings

                CREATE TABLE t1 (
                col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
                col_int_key int(11) DEFAULT NULL,
                col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
                KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
                KEY col_int_key (col_int_key),
                KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(255))
                ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
                INSERT INTO t1 VALUES ('did',5,'abcdefjhjkl'),('was',-1631322112,'no'),('are',3,'abcdefjhjkl'),('abcdefjhjkl',3,'w'),('abcdefjhjkl',4,'x'),('tell',-824573952,'abcdefjhjkl'),('t',0,'r'),('v',-1711013888,'the'),('abcdefjhjkl',1015414784,'x'),('or',4,'o'),('now',0,'have'),('abcdefjhjkl',-32702464,'h'),('abcdefjhjkl',4,'abcdefjhjkl'),('time',1078394880,'abcdefjhjkl'),('f',4,'j'),('m',-1845559296,'abcdefjhjkl'),('abcdefjhjkl',-1074397184,'something');

                CREATE TABLE t2 (
                col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
                col_int_key int(11) DEFAULT NULL,
                col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
                KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
                KEY col_int_key (col_int_key),
                KEY col_varcohar_1024_utf8_key (col_varchar_1024_utf8_key(333))
                ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
                INSERT INTO t2 VALUES ('abcdefjhjkl',8,'k'),('abcdefjhjkl',-575340544,'abcdefjhjkl'),('some',2,'because'),('of',-517472256,'f'),('h',5,'abcdefjhjkl'),('mean',7,'z');

                SET SESSION join_cache_level = 6;
                SET SESSION join_buffer_size = 1024;
                SET SESSION optimizer_switch='join_cache_bka=off';

                SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key ;
                EXPLAIN SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key ;

                SET SESSION join_cache_level=0;
                ALTER TABLE t1 DISABLE KEYS;
                ALTER TABLE t2 DISABLE KEYS;
                SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key;
                EXPLAIN SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key;

                DROP TABLE t1;
                DROP TABLE t2;

                Show
                philipstoev Philip Stoev added a comment - Re: No rows returned with (flat, BNLH join) in maria-5.3-mwl128 Test case: --disable_warnings DROP TABLE /*! IF EXISTS */ t1; DROP TABLE /*! IF EXISTS */ t2; --enable_warnings CREATE TABLE t1 ( col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, col_int_key int(11) DEFAULT NULL, col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key), KEY col_int_key (col_int_key), KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(255)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES ('did',5,'abcdefjhjkl'),('was',-1631322112,'no'),('are',3,'abcdefjhjkl'),('abcdefjhjkl',3,'w'),('abcdefjhjkl',4,'x'),('tell',-824573952,'abcdefjhjkl'),('t',0,'r'),('v',-1711013888,'the'),('abcdefjhjkl',1015414784,'x'),('or',4,'o'),('now',0,'have'),('abcdefjhjkl',-32702464,'h'),('abcdefjhjkl',4,'abcdefjhjkl'),('time',1078394880,'abcdefjhjkl'),('f',4,'j'),('m',-1845559296,'abcdefjhjkl'),('abcdefjhjkl',-1074397184,'something'); CREATE TABLE t2 ( col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, col_int_key int(11) DEFAULT NULL, col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key), KEY col_int_key (col_int_key), KEY col_varcohar_1024_utf8_key (col_varchar_1024_utf8_key(333)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO t2 VALUES ('abcdefjhjkl',8,'k'),('abcdefjhjkl',-575340544,'abcdefjhjkl'),('some',2,'because'),('of',-517472256,'f'),('h',5,'abcdefjhjkl'),('mean',7,'z'); SET SESSION join_cache_level = 6; SET SESSION join_buffer_size = 1024; SET SESSION optimizer_switch='join_cache_bka=off'; SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key ; EXPLAIN SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key ; SET SESSION join_cache_level=0; ALTER TABLE t1 DISABLE KEYS; ALTER TABLE t2 DISABLE KEYS; SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key; EXPLAIN SELECT table1.col_int_key FROM t1 AS table1 JOIN t2 AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_1024_utf8_key; DROP TABLE t1; DROP TABLE t2;
                Hide
                Permalink
                ratzpo Rasmus Johansson added a comment -

                Launchpad bug id: 671901

                Show
                ratzpo Rasmus Johansson added a comment - Launchpad bug id: 671901

                  People

                  • Assignee:
                    igor Igor Babaev
                    Reporter:
                    philipstoev Philip Stoev
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    0 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: