[#MDEV-25985] Spider handle ">=" as ">" in some cases

[MDEV-25985] Spider handle ">=" as ">" in some cases Created: 2021-06-22 Updated: 2021-09-17 Resolved: 2021-07-14
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - Spider
Affects Version/s:	10.5.9, 10.6.2, 10.2.39, 10.3.30, 10.4.20
Fix Version/s:	10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.4

Type:

Bug

Priority:

Major

Reporter:

Richard Stracke

Assignee:

Nayuta Yanagisawa (Inactive)

Resolution:

Fixed

Votes:

Labels:

None

Attachments:

MDEV-25985_mysqld.1_partial_99f700a820ef90b5b36ef765fb1532145ab3e907.trace

Description

Testcase to reproduce:

SET @@session.spider_same_server_link = ON;

GRANT ALL PRIVILEGES ON *.* TO 'spinne'@'127.0.0.1'  IDENTIFIED BY 'Widow2021!';

DROP SERVER data1;

CREATE SERVER IF NOT EXISTS data1

FOREIGN DATA WRAPPER mysql

OPTIONS(

HOST '127.0.0.1',

DATABASE 'test',

USER 'spinne',

PORT 3307,

PASSWORD 'Widow2021!'

);

DROP TABLE IF EXISTS `test`.`spider_data_tab2`;

CREATE TABLE `test`.`spider_data_tab2` (

`col0` varchar(10) NOT NULL,

`col1` varchar(10) NOT NULL,

`col2` varchar(10) NOT NULL,

`col3` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',

`col4` int,

PRIMARY KEY (`col1`,`col2`,`col3`),

KEY `IX_1` (`col3`,`col4`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

PARTITION BY RANGE COLUMNS(`col3`)

(PARTITION `pmax` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

DROP TABLE IF EXISTS `test`.`spider_tab2`;

CREATE TABLE IF NOT EXISTS `test`.`spider_tab2` (

`col0` varchar(10) NOT NULL,

`col1` varchar(10) NOT NULL,

`col2` varchar(17) NOT NULL,

`col3` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',

`col4` int,

PRIMARY KEY (`col0`,`col1`,`col2`,`col3`)

,KEY `IX_1` (`col3`,`col4`)

) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mariadb", table "spider_data_tab2"'

PARTITION BY LIST COLUMNS(`col1`)

(PARTITION `ptdef` DEFAULT COMMENT = 'srv "data1"' ENGINE = SPIDER);

DROP TABLE IF EXISTS `test`.`local_tab`;

CREATE TABLE IF NOT EXISTS `test`.`local_tab` (

`col1` varchar(10) NOT NULL,

`col2` varchar(17) NOT NULL,

`col3` varchar(100) NOT NULL,

PRIMARY KEY (`col1`,`col2`,`col3`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

INSERT INTO test.spider_data_tab2 VALUES

("8522d5bd69","00174","037ec5b849","2021-03-31 00:00:00",3),

("8522d5bd70","00174","04b11de592","2021-04-01 00:00:00",3),

("8522d5bd71","00174","0f29529250","2021-04-02 00:00:00",1),

("8522d5bd72","00174","1bb3fecc59","2021-04-02 00:00:00",3),

("8522d5bd70","00174","293f5f916a","2021-04-03 00:00:00",3),

("8522d5bd70","00174","3ddba35681","2021-04-04 00:00:00",3),

("8522d5bd70","00166","024009786c","2021-03-31 00:00:00",3),

("8522d5bd69","00166","037ec5b849","2021-04-01 00:00:00",3),

("8522d5bd73","00166","04b11de592","2021-04-02 00:00:00",1),

("8522d5bd72","00166","04f62d1dbf","2021-04-02 00:00:00",3),

("8522d5bd71","00166","0ba4eacc85","2021-04-03 00:00:00",3),

("8522d5bd66","00166","1104ef747c","2021-04-04 00:00:00",3);

INSERT INTO test.local_tab VALUES

("00166", "024009786c", "d1cde678-cd97-11eb-9ff8-020017000a63"),

("00166", "037ec5b849", "d60e4c51-cd97-11eb-9ff8-020017000a63"),

("00166", "04b11de592", "d007ede3-cd97-11eb-9ff8-020017000a63"),

("00166", "04f62d1dbf", "d0fd469c-cd97-11eb-9ff8-020017000a63"),

("00166", "0ba4eacc85", "cfaf324d-cd97-11eb-9ff8-020017000a63"),

("00166", "1104ef747c", "cceafd90-cd97-11eb-9ff8-020017000a63"),

("00174", "037ec5b849", "d60e6dd5-cd97-11eb-9ff8-020017000a63"),

("00174", "04b11de592", "d007f3f3-cd97-11eb-9ff8-020017000a63"),

("00174", "0f29529250", "d0fd4d7e-cd97-11eb-9ff8-020017000a63"),

("00174", "1bb3fecc59", "cceb03e5-cd97-11eb-9ff8-020017000a63"),

("00174", "293f5f916a", "d419c420-cd97-11eb-9ff8-020017000a63");

SELECT DISTINCT A.col1, A.col2, B.col3 FROM test.local_tab A INNER JOIN test.spider_data_tab2 B ON A.col1 = B.col1 AND A.col2 = B.col2 AND A.col1 IN ('00166' , '00174') AND B.col4 <> 1 AND B.col3 >= '2021-04-01 00:00:00' AND B.col3 <= '2021-04-03 00:00:00';

SELECT DISTINCT A.col1, A.col2, B.col3 FROM test.local_tab A INNER JOIN test.spider_tab2 B ON A.col1 = B.col1 AND A.col2 = B.col2 AND A.col1 IN ('00166' , '00174') AND B.col4 <> 1 AND B.col3 >= '2021-04-01 00:00:00' AND B.col3 <= '2021-04-03 00:00:00';

Happens on Spider 3.3.15 and 3.4

Comments

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-02 ]

I've tested on 10.4.20 (why not 10.5.9? Because I've not constructed a test environment for 10.5. I will soon test on 10.5.). The bug reproduced.

SELECT DISTINCT A.col1, A.col2, B.col3 FROM test.local_tab A INNER JOIN test.spider_tab2 B ON A.col1 = B.col1 AND A.col2 = B.col2 AND A.col1 IN ('00166' , '00174') AND B.col4 <> 1 AND B.col3 >= '2021-04-01 00:00:00' AND B.col3 <= '2021-04-03 00:00:00';

When I executed the above query on the Spider node, the following corresponding query was executed on the data node. The clause c `col3` > _latin1'2021-04-01 00:00:00' seems to be the source of the problem.

select `col1`,`col2`,`col3`,`col4` from `test`.`spider_data_tab2`

    where

        `col3` > _latin1'2021-04-01 00:00:00' and /* HERE！ */

        `col3` <= _latin1'2021-04-03 00:00:00' and

        ((`col1` in( '00166' , '00174')) and

        (`col4` <> 1) and

        (`col3` >= _latin1'2021-04-01 00:00:00') and

        (`col3` <= _latin1'2021-04-03 00:00:00'))

    order by `col3`,`col4`

Comment by Richard Stracke [ 2021-07-02 ]

Interesting point here is, that adding keyword limit with random number gives the correct result

SELECT DISTINCT A.col1, A.col2, B.col3 FROM test.local_tab A INNER JOIN test.spider_data_tab2 B ON A.col1 = B.col1 AND A.col2 = B.col2 AND A.col1 IN ('00166' , '00174') AND B.col4 <> 1 AND B.col3 >= '2021-04-01 00:00:00' AND B.col3 <= '2021-04-03 00:00:00' limit 5555;

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-02 ]

Richard I'm afraid that you may be querying the data node because spider_data_tab2 is on the data node.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-03 ]

The bug is reproducible even on 10.2.39 and 10.3.30.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-04 ]

I generated, on 10.2 HEAD(99f700a820ef90b5b36ef765fb1532145ab3e907), the debug trace of the query that is causing the problem. A partial trace is attached to the present issue.

By combining the debug trace and GDB debugging on 10.2 HEAD, I found that the wrong condition (`col3` > _latin1'2021-04-01 00:00:00') is added by the function spider_db_append_key_where_internal(), especially by spd_db_conn.cc: L1867-L1925.

          case HA_READ_AFTER_KEY:

            if (sql_kind == SPIDER_SQL_KIND_SQL)

...

              str->q_append(SPIDER_SQL_GT_STR, SPIDER_SQL_GT_LEN);

              if (spider_dbton[dbton_id].db_util->

                append_column_value(spider, str, field, ptr,

                  share->access_charset))

...

...

            break;

My insight is supported by the fact that the following modified version of the query (B.col4 <> 1 is removed) works as expected. The Spider also calls the function spider_db_append_key_where_internal() for the modified query but does not goes into the problematic part, L1867-L1925.

SELECT DISTINCT A.col1, A.col2, B.col3 FROM test.local_tab A INNER JOIN test.spider_tab2 B ON A.col1 = B.col1 AND A.col2 = B.col2 AND A.col1 IN ('00166' , '00174') AND B.col3 >= '2021-04-01 00:00:00' AND B.col3 <= '2021-04-03 00:00:00';

Comment by Allen Lee (Inactive) [ 2021-07-04 ]

nayuta-yanagisawa is there any reason you've removed condition (B.col4 <> 1)? I thought this is the key problem causing different result?
Also, do you also know why `_latin1` is added to query execution though both spider and data node uses utf8?

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-05 ]

> Nayuta Yanagisawa is there any reason you've removed condition (B.col4 <> 1)?

I tested the modified query just because it was suggested as a workaround in the support ticket. I need more time to detect the root cause.

> Also, do you also know why `_latin1` is added to query execution though both spider and data node uses utf8?

I've not found why `_latin1` is added. I will investigate it later.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-06 ]

The bug is reproducible, on 10.2 HEAD, without the local table, while the table definitions and data inserted are the same as the ones in the issue description.

On data node:

MariaDB [(none)]> SELECT DISTINCT * FROM test.spider_data_tab2 B WHERE B.col4 > 1 AND B.col3 >= '2021-04-01 00:00:00' AND B.col3 <= '2021-04-03 00:00:00';

+------------+-------+------------+---------------------+------+

| col0       | col1  | col2       | col3                | col4 |

+------------+-------+------------+---------------------+------+

| 8522d5bd69 | 00166 | 037ec5b849 | 2021-04-01 00:00:00 |    3 |

| 8522d5bd72 | 00166 | 04f62d1dbf | 2021-04-02 00:00:00 |    3 |

| 8522d5bd71 | 00166 | 0ba4eacc85 | 2021-04-03 00:00:00 |    3 |

| 8522d5bd70 | 00174 | 04b11de592 | 2021-04-01 00:00:00 |    3 |

| 8522d5bd72 | 00174 | 1bb3fecc59 | 2021-04-02 00:00:00 |    3 |

| 8522d5bd70 | 00174 | 293f5f916a | 2021-04-03 00:00:00 |    3 |

+------------+-------+------------+---------------------+------+

6 rows in set (0.01 sec)

On Spider node:

MariaDB [(none)]> SELECT DISTINCT * FROM test.spider_tab2 B WHERE B.col4 > 1 AND B.col3 >= '2021-04-01 00:00:00' AND B.col3 <= '2021-04-03 00:00:00';

+------------+-------+------------+---------------------+------+

| col0       | col1  | col2       | col3                | col4 |

+------------+-------+------------+---------------------+------+

| 8522d5bd72 | 00166 | 04f62d1dbf | 2021-04-02 00:00:00 |    3 |

| 8522d5bd71 | 00166 | 0ba4eacc85 | 2021-04-03 00:00:00 |    3 |

| 8522d5bd72 | 00174 | 1bb3fecc59 | 2021-04-02 00:00:00 |    3 |

| 8522d5bd70 | 00174 | 293f5f916a | 2021-04-03 00:00:00 |    3 |

+------------+-------+------------+---------------------+------+

The corresponding query executed on the data node is the following:

 select distinct `col0`,`col1`,`col2`,`col3`,`col4` from `test`.`spider_data_tab2` where `col3` > '2021-04-01 00:00:00' and `col3` <= '2021-04-03 00:00:00' and ((`col4` > 1) and (`col3` >= '2021-04-01 00:00:00') and (`col3` <= '2021-04-03 00:00:00'))

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-06 ]

I took a deeper look at the bug.

The function spider_db_append_key_where_internal() converts HA_READ_AFTER_KEY to > (see the code block above).The conversion seems to be correct for single-column indexes because HA_READ_AFTER_KEY means read the key after the provided value. However, how about multi-column indexes?

Assume that there is a multi-column index on c1 and c2 and we would like to search with WHERE c1 >= 100 AND c2 > 200. I think that the key_range.flag corresponds to the search condition could be HA_READ_AFTER_KEY . In such a case, we could not simply convert HA_READ_AFTER_KEY to >. I think that the bug reproduction without the local table is probably a real world example of the case.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-06 ]

This seems to be also possible for HA_READ_BEFORE_KEY. See the following two queries on the spider node and the data node (tested on 10.2 HEAD):

MariaDB [(none)]>  SELECT * FROM test.spider_tab2 B WHERE B.col4 < 2 AND B.col3 <= '2021-04-03 00:00:00';

Empty set (0.03 sec)

MariaDB [(none)]> SELECT * FROM test.spider_data_tab2 B WHERE B.col4 < 2 AND B.col3 <= '2021-04-03 00:00:00';

+------------+-------+------------+---------------------+------+

| col0       | col1  | col2       | col3                | col4 |

+------------+-------+------------+---------------------+------+

| 8522d5bd73 | 00166 | 04b11de592 | 2021-04-02 00:00:00 |    1 |

| 8522d5bd71 | 00174 | 0f29529250 | 2021-04-02 00:00:00 |    1 |

+------------+-------+------------+---------------------+------+

EDIT: This seems to be a bug, but I noticed that this may be another bug. That is because, when executing the first query on the Spider node, the server didn't go into case HA_READ_BEFORE_KEY: and the corresponding query executed on the data node is the following:

 select `col0`,`col1`,`col2`,`col3`,`col4` from `test`.`spider_data_tab2` where `col3` = '2021-04-03 00:00:00' and `col4` < 2 and ((`col4` < 2) and (`col3` <= '2021-04-03 00:00:00'))

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-06 ]

Simply replacing SPIDER_SQL_LT_XX by SPIDER_SQL_LTEQUAL_XX does not work. That result in failures of existing tests, say spider.direct_aggregate and spider.direct_aggregate_part.

diff --git a/storage/spider/spd_db_conn.cc b/storage/spider/spd_db_conn.cc

index 8d4e9acda07..6195ad5658d 100644

--- a/storage/spider/spd_db_conn.cc

+++ b/storage/spider/spd_db_conn.cc

@@ -1869,10 +1869,10 @@ int spider_db_append_key_where_internal(

               if (str->reserve(store_length + key_name_length +

                 /* SPIDER_SQL_NAME_QUOTE_LEN */ 2 +

-                SPIDER_SQL_GT_LEN))

+                SPIDER_SQL_GTEQUAL_LEN))

                 DBUG_RETURN(HA_ERR_OUT_OF_MEM);

               dbton_share->append_column_name(str, field->field_index);

-              str->q_append(SPIDER_SQL_GT_STR, SPIDER_SQL_GT_LEN);

+              str->q_append(SPIDER_SQL_GTEQUAL_STR, SPIDER_SQL_GTEQUAL_LEN);

               if (spider_dbton[dbton_id].db_util->

                 append_column_value(spider, str, field, ptr,

                   share->access_charset))

@@ -1929,10 +1929,10 @@ int spider_db_append_key_where_internal(

               if (str->reserve(store_length + key_name_length +

                 /* SPIDER_SQL_NAME_QUOTE_LEN */ 2 +

-                SPIDER_SQL_LT_LEN))

+                SPIDER_SQL_LTEQUAL_LEN))

                 DBUG_RETURN(HA_ERR_OUT_OF_MEM);

               dbton_share->append_column_name(str, field->field_index);

-              str->q_append(SPIDER_SQL_LT_STR, SPIDER_SQL_LT_LEN);

+              str->q_append(SPIDER_SQL_LTEQUAL_STR, SPIDER_SQL_LTEQUAL_LEN);

               if (spider_dbton[dbton_id].db_util->

                 append_column_value(spider, str, field, ptr,

                   share->access_charset))

Output of spider.direct_aggregate:

CURRENT_TEST: spider.direct_aggregate

--- /home/vagrant/repo/mariadb-server/storage/spider/mysql-test/spider/r/direct_aggregate.result        2021-07-06 08:36:54.467722477 +0000

+++ /home/vagrant/repo/mariadb-server/storage/spider/mysql-test/spider/r/direct_aggregate.reject        2021-07-06 14:51:59.518386327 +0000

@@ -69,13 +69,13 @@

 Spider_direct_aggregate        0

 SELECT MAX(a) FROM ta_l WHERE a < 5;

 MAX(a)

-4

+5                                                                                                                                                                                         SHOW STATUS LIKE 'Spider_direct_aggregate';

 Variable_name  Value

 Spider_direct_aggregate        0

 SELECT MIN(a) FROM ta_l WHERE a > 1;

 MIN(a)

-2

+1

 SHOW STATUS LIKE 'Spider_direct_aggregate';

 Variable_name  Value

 Spider_direct_aggregate        0

mysqltest: Result content mismatch

 - saving '/home/vagrant/repo/mariadb-server/bld/mysql-test/var/5/log/spider.direct_aggregate/' to '/home/vagrant/repo/mariadb-server/bld/mysql-test/var/log/spider.direct_aggregate/'

spider.direct_aggregate_part             w3 [ fail ]

        Test ended at 2021-07-06 14:51:59

NOTE: The output of spider.direct_aggregate_part is almost the same as one of spider.direct_aggregate.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-07 ]

serg I've got stuck now. Do you have any idea about why the above fix (comment-193668) doesn't work?

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-07 ]

I just asked a question, but I think I found the answer by myself.

The fix is based on the assumption that MariaDB's executor has the capability to filter out rows that do not match search conditions. However, while this is just a guess from the test name, the Spider SE seems to push down some aggregation functions. In such cases, we have to push down all exact search conditions.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-08 ]

I got a bit confused in a weird way while I've already got to the heart of the issue. In conclusion, the correct conversion seems to be to convert HA_READ_AFTER_KEY to '>' only for the last column in tgt_key_part_map and to convert HA_READ_AFTER_KEY to '>=' for the other column.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-08 ]

I minimized the test case:

CREATE TABLE `test`.`td` (

`a` int,

`b` int,

`c` int,

PRIMARY KEY (`a`),

KEY (`b`,`c`)

) ENGINE=InnoDB ;

CREATE TABLE `test`.`ts` (

`a` int,

`b` int,

`c` int,

PRIMARY KEY (`a`),

KEY (`b`,`c`)

) ENGINE=SPIDER COMMENT='table "td"'

PARTITION BY LIST COLUMNS(`a`)

(PARTITION `ptdef` DEFAULT COMMENT = 'srv "data1"' ENGINE = SPIDER);

INSERT INTO test.td VALUES (1,1,1), (2,2,1);

MariaDB [(none)]> SELECT * FROM test.td B WHERE B.c > 0 AND B.b >= 1 AND B.b <= 2;

+---+------+------+

| a | b    | c    |

+---+------+------+

| 1 |    1 |    1 |

| 2 |    2 |    1 |

+---+------+------+

2 rows in set (0.01 sec)

MariaDB [(none)]>  SELECT * FROM test.ts B WHERE B.c > 0 AND B.b >= 1 AND B.b <= 2;

+---+------+------+

| a | b    | c    |

+---+------+------+

| 2 |    2 |    1 |

+---+------+------+

1 row in set (0.05 sec)

The corresponding query executed on the data node:

 select `a`,`b`,`c` from `test`.`td` where `b` > 1 and `b` <= 2 and ((`c` > 0) and (`b` >= 1) and (`b` <= 2))

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-08 ]

I created another JIRA issue related to the present issue: MDEV-26111

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-08 ]

Another buggy behavior here, while the table difinitions and data are the same as ones in comment-193869.

MariaDB [(none)]> SELECT * FROM test.td B WHERE B.c < 3 AND B.b <= 2;

+---+------+------+

| a | b    | c    |

+---+------+------+

| 1 |    1 |    1 |

| 2 |    2 |    1 |

+---+------+------+

2 rows in set (0.00 sec)

MariaDB [(none)]> SELECT * FROM test.ts B WHERE B.c < 3 AND B.b <= 2;

+---+------+------+

| a | b    | c    |

+---+------+------+

| 1 |    1 |    1 |

+---+------+------+

1 row in set (3 min 46.20 sec)

The corresponding query executed on the data node:

select `a`,`b`,`c` from `test`.`td` where `b` is not null and `b` < 2 and ((`c` < 3) and (`b` <= 2))

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-08 ]

I pushed a patch for the present issue: https://github.com/MariaDB/server/commit/da70f190279738804b7ddbc15a71e1c3cf088dd0

Most of the files in `storage/spider/mysql-test/spider/bugfix` is copies of these on 10.3 HEAD. So, the fundamental differences are in:

storage/spider/spd_db_conn.cc
storage/spider/mysql-test/spider/bugfix/t/mdev_25985.test

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-13 ]

serg According to your review comment, I merged the test case for the issue into the existing test for partitioned tables. The code is as is, only the test is updated. https://github.com/MariaDB/server/commit/737ced02f897ce757fd66330fd6843c5b2e5b834

Comment by Sergei Golubchik [ 2021-07-14 ]

737ced02f89 is ok to push

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-14 ]

Merged https://github.com/MariaDB/server/commit/0f6e170c34700a2964556839c676c1b7768f3ffb

Generated at Thu Feb 08 09:41:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-25985] Spider handle ">=" as ">" in some cases Created: 2021-06-22 Updated: 2021-09-17 Resolved: 2021-07-14