[MDEV-27172] Prefix indices on Spider tables may lead to wrong query results Created: 2021-12-06  Updated: 2023-11-27  Resolved: 2022-09-01

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - Spider
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.3.37, 10.4.27, 10.5.18, 10.6.10, 10.7.6, 10.8.5, 10.9.3, 10.10.2

Type: Bug Priority: Critical
Reporter: Daniel YE Assignee: Nayuta Yanagisawa (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-26287 Refactor spider_db_append_key_where_i... Stalled

 Description   

How to reproduce

Create a table with a prefix index on a TEXT field, given a small prefix length (in my example, 5). And then, insert a record which contain characters more than the given prefix length.

MariaDB [mytest]> show create table test_table\G
 
*************************** 1. row ***************************
 
       Table: test_table
 
Create Table: CREATE TABLE `test_table` (
 
  `id` int(11) NOT NULL,
 
  `t` text DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `k_t` (`t`(5))
 
) ENGINE=SPIDER DEFAULT CHARSET=latin1
 
 PARTITION BY LIST (`id` MOD 2)
 
(PARTITION `pt0` VALUES IN (0) COMMENT = 'database "mytest_2", table "test_table", server "SPT2"' ENGINE = SPIDER,
 
 PARTITION `pt1` VALUES IN (1) COMMENT = 'database "mytest_3", table "test_table", server "SPT3"' ENGINE = SPIDER)
 
1 row in set (0.00 sec)
 
 
 
MariaDB [mytest]> insert into test_table values(1, "aloha"), (2, "hello world");
 
Query OK, 2 rows affected (0.01 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [mytest]> select * from test_table;
 
+----+-------------+
 
| id | t           |
 
+----+-------------+
 
|  2 | hello world |
 
|  1 | aloha       |
 
+----+-------------+
 
2 rows in set (0.00 sec)

Then we get this:

MariaDB [mytest]> select * from test_table where t="aloha";
 
+----+-------+
 
| id | t     |
 
+----+-------+
 
|  1 | aloha |
 
+----+-------+
 
1 row in set (0.01 sec)
 
 
 
MariaDB [mytest]> select * from test_table where t="hello world";
 
Empty set (0.00 sec)

The general log output can give us some clues, indicating that the string value is truncated to the length of 5 when performing an index read:

select `id`,`t` from `mytest_2`.`test_table` where `t` = 'hello' and (`t` = 'hello world')
 
select `id`,`t` from `mytest_3`.`test_table` where `t` = 'hello' and (`t` = 'hello world')

It also affects JOIN operations

Create a similar table, having the TEXT field values equal to the one above.

MariaDB [mytest]> show create table another_test_table\G
 
*************************** 1. row ***************************
 
       Table: another_test_table
 
Create Table: CREATE TABLE `another_test_table` (
 
  `id` int(11) NOT NULL,
 
  `t` text DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `k_t` (`t`(5))
 
) ENGINE=SPIDER DEFAULT CHARSET=latin1
 
 PARTITION BY LIST (`id` MOD 2)
 
(PARTITION `pt0` VALUES IN (0) COMMENT = 'database "mytest_2", table "another_test_table", server "SPT2"' ENGINE = SPIDER,
 
 PARTITION `pt1` VALUES IN (1) COMMENT = 'database "mytest_3", table "another_test_table", server "SPT3"' ENGINE = SPIDER)
 
1 row in set (0.00 sec)
 
 
 
MariaDB [mytest]> select * from another_test_table;
 
+----+-------------+
 
| id | t           |
 
+----+-------------+
 
| 44 | hello world |
 
| 33 | aloha       |
 
+----+-------------+
 
2 rows in set (0.01 sec)

Perform a JOIN between these two tables, on the TEXT field. The result set has only 1 row, instead of 2 as expected, obviously for the same reason.

MariaDB [mytest]> select * from test_table a straight_join another_test_table b on a.t=b.t;
 
+----+-------+----+-------+
 
| id | t     | id | t     |
 
+----+-------+----+-------+
 
|  1 | aloha | 33 | aloha |
 
+----+-------+----+-------+
 
1 row in set (0.00 sec)

Suggested fix

If we consider this a bug, instead of an "expected behavior", I have some humble opinion on the possible fix.

After looking into the code, I found the code where Spider prints the truncated value into the queries resides in spider_db_mysql_util::append_column_value, where the value passed by the server layer is already truncated to the length of the prefix key. Seems like there is not a lot we can do to fix this without changing the server's behavior.

So, I suggest we can treat prefix indices differently in the "append key where" stage, when an EQUAL matching is performed on a prefix index. That is, in the queries sent to backends, replace "<field_name>='<truncated_str_val>'" with "<field_name> LIKE '<truncated_str_val>%'". In my example above, "t='hello'" should be replaced with "t LIKE 'hello%'". This way, the desired records could be matched.

There are also some cons I can think of for this hack. First is the performance issue, since an EQUAL reference is turned into a RANGE operation. Second is the backends possibly returning more results than desired. For this matter, we do not need to worry about the correctness of results returned to the client, since the server does the evaluation of records itself. However, the issue of more records needing to process is also a performance influencer though.

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

DanielYe133 Thank you for your report. In which version have you found the above behavior? Possibly, you've tested on TSpider based on MariaDB 10.3?

Comment by Daniel YE [ 2021-12-06 ]

nayuta-yanagisawa It was initially found on TSpider based on MariaDB 10.3.7. Then I reproduced it on MariaDB 10.6.4.

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

DanielYe133 Thank you. I will check it soon.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-01-12 ]

Roel Could you please verify the bug?

Comment by Roel Van de Paar [ 2022-01-14 ]

Partially verified on 10.8 as described. Tested 10.4 to 10.8, same result. Likely also present in earlier versions.

INSTALL PLUGIN spider SONAME 'ha_spider.so';
 
SET GLOBAL spider_same_server_link=ON;
 
SET SESSION spider_same_server_link=ON;
 
CREATE TABLE tb (c1 INT, c2 TEXT) ENGINE=InnoDB;
 
CREATE TABLE t (c1 INT, c2 TEXT, KEY k (c2(5))) ENGINE=SPIDER COMMENT "HOST '127.0.0.1', PORT '29125', USER 'root', PASSWORD '', DATABASE 'test', TABLE 'tb'" PARTITION BY LIST (c1 MOD 2) (PARTITION pt0 VALUES IN (0), PARTITION pt1 VALUES IN (1)) ;
 
INSERT INTO t VALUES (1, "aloha"),(2, "hello world");
 
SELECT * FROM t;
 
SELECT * FROM t WHERE c2="aloha";
 
SELECT * FROM t WHERE c2="hello world";
 
SELECT c1,c2 FROM t WHERE c2='hello' AND (c2='hello world');
 
SELECT * FROM t a STRAIGHT_JOIN t b ON a.c1=b.c1;

Leads to:

10.8.0 347f6d01e3b570dce49aa1ab42cb83021905a14d (Debug)

 
10.8.0-dbg>INSERT INTO t VALUES (1, "aloha"),(2, "hello world");
 
Query OK, 2 rows affected (0.016 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
10.8.0-dbg>SELECT * FROM t;
 
+------+-------------+
 
| c1   | c2          |
 
+------+-------------+
 
|    2 | hello world |
 
|    1 | aloha       |
 
|    2 | hello world |
 
|    1 | aloha       |
 
+------+-------------+
 
4 rows in set (0.003 sec)
 
 
 
10.8.0-dbg>SELECT * FROM t WHERE c2="aloha";
 
+------+-------+
 
| c1   | c2    |
 
+------+-------+
 
|    1 | aloha |
 
|    1 | aloha |
 
+------+-------+
 
2 rows in set (0.004 sec)
 
 
 
10.8.0-dbg>SELECT * FROM t WHERE c2="hello world";
 
Empty set (0.003 sec)
 
 
 
10.8.0-dbg>SELECT c1,c2 FROM t WHERE c2='hello' AND (c2='hello world');
 
Empty set (0.000 sec)
 
 
 
10.8.0-dbg>SELECT * FROM t a STRAIGHT_JOIN t b ON a.c1=b.c1;
 
+------+-------------+------+-------------+
 
| c1   | c2          | c1   | c2          |
 
+------+-------------+------+-------------+
 
|    2 | hello world |    2 | hello world |
 
|    2 | hello world |    2 | hello world |
 
|    1 | aloha       |    1 | aloha       |
 
|    1 | aloha       |    1 | aloha       |
 
|    2 | hello world |    2 | hello world |
 
|    2 | hello world |    2 | hello world |
 
|    1 | aloha       |    1 | aloha       |
 
|    1 | aloha       |    1 | aloha       |
 
+------+-------------+------+-------------+
 
8 rows in set (0.005 sec)

Besides the issue reported, why is there double output? This looks like a serious bug, as a similar but simpler testcase does not produce such double output:

INSTALL PLUGIN spider SONAME 'ha_spider.so';
 
SET GLOBAL spider_same_server_link=ON;
 
SET SESSION spider_same_server_link=ON;
 
CREATE TABLE t_base (c INT) ENGINE=InnoDB;
 
CREATE TABLE t_link (c INT) ENGINE=SPIDER COMMENT "HOST '127.0.0.1', PORT '29125', USER 'root', PASSWORD '', DATABASE 'test', TABLE 't_base'";
 
INSERT INTO t_link VALUES (1);
 
SELECT * FROM t_link;

Comment by Roel Van de Paar [ 2022-01-14 ]

For completeness, InnoDB seems to work correctly for the same testcase:

CREATE TABLE t (c1 INT, c2 TEXT, KEY k (c2(5))) ENGINE=InnoDB PARTITION BY LIST (c1 MOD 2) (PARTITION pt0 VALUES IN (0), PARTITION pt1 VALUES IN (1)) ;
 
INSERT INTO t VALUES (1, "aloha"),(2, "hello world");
 
SELECT * FROM t;
 
SELECT * FROM t WHERE c2="aloha";
 
SELECT * FROM t WHERE c2="hello world";
 
SELECT c1,c2 FROM t WHERE c2='hello' AND (c2='hello world');
 
SELECT * FROM t a STRAIGHT_JOIN t b ON a.c1=b.c1;

Leads to:

10.8.0 347f6d01e3b570dce49aa1ab42cb83021905a14d (Debug)

 
10.8.0-dbg>INSERT INTO t VALUES (1, "aloha"),(2, "hello world");
 
Query OK, 2 rows affected (0.006 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
10.8.0-dbg>SELECT * FROM t;
 
+------+-------------+
 
| c1   | c2          |
 
+------+-------------+
 
|    2 | hello world |
 
|    1 | aloha       |
 
+------+-------------+
 
2 rows in set (0.001 sec)
 
 
 
10.8.0-dbg>SELECT * FROM t WHERE c2="aloha";
 
+------+-------+
 
| c1   | c2    |
 
+------+-------+
 
|    1 | aloha |
 
+------+-------+
 
1 row in set (0.001 sec)
 
 
 
10.8.0-dbg>SELECT * FROM t WHERE c2="hello world";
 
+------+-------------+
 
| c1   | c2          |
 
+------+-------------+
 
|    2 | hello world |
 
+------+-------------+
 
1 row in set (0.001 sec)
 
 
 
10.8.0-dbg>SELECT c1,c2 FROM t WHERE c2='hello' AND (c2='hello world');
 
Empty set (0.000 sec)
 
 
 
10.8.0-dbg>SELECT * FROM t a STRAIGHT_JOIN t b ON a.c1=b.c1;
 
+------+-------------+------+-------------+
 
| c1   | c2          | c1   | c2          |
 
+------+-------------+------+-------------+
 
|    2 | hello world |    2 | hello world |
 
|    1 | aloha       |    1 | aloha       |
 
+------+-------------+------+-------------+
 
2 rows in set (0.001 sec)

Comment by Nayuta Yanagisawa (Inactive) [ 2022-02-16 ]

Besides the issue reported, why is there double output?

This is possibly because of MRR, which is disabled in TSpider, but I will look at it further.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-02-24 ]

This is possibly because of MRR, which is disabled in TSpider, but I will look at it further.

Roel This was not true. You associated the single remote table with different partitions of the Spider table. This will surely double the result set from a single table. For STRAIGHT JOIN, the results set from the outer and the inner table are both doubled, and then the whole result set is quadrupled (2*2 = 4).

Comment by Nayuta Yanagisawa (Inactive) [ 2022-02-24 ]

I too cannot reproduce the bug on STRAIGHT JOIN on 10.3. Spider uses id as the JOIN key and thus the problem doesn't come to visible. The difference is possibly due to the difference in parameters or the difference in the code (TSpider has original patches). However, I believe that, if we fixed the single table case, the potential bug of JOINs would be also fixed.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-03-01 ]

The bug is reproducible on 10.2.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-03-01 ]

Spider converts HA_READ_KEY_EXACT to the equality (=) but the conversion is not necessarily correct. HA_READ_KEY_EXACT rather means "find first record else error". Thus, converting = to HA_READ_KEY_EXACT is always correct but not vice versa.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-03-01 ]

This is just an idea but importing the implementation of ha_federatedx::create_where_from_key() possibly fixes the issue.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-03-15 ]

I pushed a fix for the bug. I believe it works but it affects wrappers other than mysql/mariadb. So, I will think some more about how it should be fixed. https://github.com/MariaDB/server/commit/d01959f78cc7f2b1b3f30d00f774c99e368702c9

Comment by Nayuta Yanagisawa (Inactive) [ 2022-05-17 ]

The patch converts string key equalities to LIKE conditions but the conversion is unnecessary or even harmful if a key is non-prefixed.

Tencent reported that they tried the above patch and suffered from significant performance degradation. The problem is raised in the case where Spider table's primary key is on VARCHAR field and the table is partitioned.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-06-29 ]

holyfoot Please review: https://github.com/MariaDB/server/commit/da5aff18d6321ffc24677573e47277f0d7a25a79

Comment by Alexey Botchkov [ 2022-08-15 ]

ok to push with one proposed correction (see the patch comment).

Comment by Marko Mäkelä [ 2022-09-06 ]

This caused several conflicts on an attempted merge to 10.4 due to MDEV-16248 in 10.4. I tried to fix most of them (and accidentally added bool append_column_value in two declarations instead of bool is_like). One test is failing:

spider/bugfix.delete_with_float_column_mariadb [ fail ]
 
        Test ended at 2022-09-06 16:08:07
 
 
 
CURRENT_TEST: spider/bugfix.delete_with_float_column_mariadb
 
analyze: sync_with_master
 
mysqltest: In included file "/mariadb/10.4/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column.inc": 
included from /mariadb/10.4/storage/spider/mysql-test/spider/bugfix/t/delete_with_float_column_mariadb.test at line 3:
 
At line 75: sync_with_master failed: 'select master_pos_wait('master-bin.000001', 2402, 300, '')' returned NULL indicating slave SQL thread failure

Next time, please test if cherry-picking the change would cause trouble in later versions, and provide additional branches that would address those troubles, to avoid blocking a merge.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-09-06 ]

marko Sorry for the trouble. I will check if there are problematic conflicts.

I think the following fixup fixes your merge branch: https://github.com/MariaDB/server/commit/ca57a6176787dad7bdd9878a73bf1154348e8d7f

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