[MDEV-8433] Make field<'broken-string' use indexes - Jira

Details

Type: Task
Status: In Review (View Workflow)
Priority: Major
Resolution: Unresolved
Fix Version/s: None
Component/s: Character Sets, Optimizer
Labels:
- performance

Sprint:
10.1.7-2, 10.1.8-1

Description

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

SET NAMES 'utf8';

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (

  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',

  KEY(a)

) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO t1 (a) VALUES ('admin');

INSERT INTO t1 (a) VALUES ('admin;');

INSERT INTO t1 (a) VALUES ('adminx');

EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';

returns

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

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

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

|    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL |    3 | Using where |

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

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences somehow for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:

Add a new parameter to Field::store()
Add a new method, say Field::store_for_search()
Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to handle bad bytes differently (on search).
Add new classes Field_string_key, Field_varstring_key, Field_blob_key and overwrite their store() methods

As soon as we're able to store bad values into the Field buffer in a way suitable for search (instead of just replacing to '?'), the collation should be able to do index scan in exactly the same way with a non-indexed search.

Attachments

Issue Links

is blocked by

MDEV-8036 Fix all collations to compare broken bytes as "greater than any non-broken character"

Closed

relates to

MDEV-8420 UCA: compare broken bytes as "greater than any non-broken character"

Open

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2015-07-07 10:05

Alexander Barkov made changes - 2015-07-07 10:08

Field	Original Value	New Value
Link		This issue is blocked by ~~MDEV-8036~~ [ ~~MDEV-8036~~ ]

Alexander Barkov made changes - 2015-07-07 10:22

Link

This issue relates to MDEV-8420 [ MDEV-8420 ]

Alexander Barkov made changes - 2015-08-20 11:34

Rank

Ranked higher

Alexander Barkov made changes - 2015-08-20 11:34

Rank

Ranked higher

Alexander Barkov made changes - 2015-08-20 11:37

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2015-08-20 11:39

Sprint

10.1.7-2 [ 12 ]

Alexander Barkov made changes - 2015-08-20 11:39

Rank

Ranked higher

Alexander Barkov made changes - 2015-08-20 12:02

Assignee	Alexander Barkov [ bar ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Alexander Barkov made changes - 2015-08-20 12:03

Description

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ (almost) done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to preserve bad bytes as is (on search).

As soon as we're able to store bad values into the Field buffer, the collation should be able to do index scan in exactly the same way with a non-indexed search.

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to preserve bad bytes as is (on search).

As soon as we're able to store bad values into the Field buffer, the collation should be able to do index scan in exactly the same way with a non-indexed search.

Alexander Barkov made changes - 2015-08-20 12:04

Description

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to preserve bad bytes as is (on search).

As soon as we're able to store bad values into the Field buffer, the collation should be able to do index scan in exactly the same way with a non-indexed search.

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences somehow for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to preserve bad bytes as is (on search).

As soon as we're able to store bad values into the Field buffer, the collation should be able to do index scan in exactly the same way with a non-indexed search.

Alexander Barkov made changes - 2015-08-20 12:05

Description

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences somehow for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to preserve bad bytes as is (on search).

As soon as we're able to store bad values into the Field buffer, the collation should be able to do index scan in exactly the same way with a non-indexed search.

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences somehow for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to preserve bad bytes as is (on search).
- Add new classes Field_string_key, Field_varstring_key, Field_blob_key and overwrite their store() methods

As soon as we're able to store bad values into the Field buffer, the collation should be able to do index scan in exactly the same way with a non-indexed search.

Alexander Barkov made changes - 2015-08-20 12:06

Description

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences somehow for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to preserve bad bytes as is (on search).
- Add new classes Field_string_key, Field_varstring_key, Field_blob_key and overwrite their store() methods

As soon as we're able to store bad values into the Field buffer, the collation should be able to do index scan in exactly the same way with a non-indexed search.

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences somehow for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to handle bad bytes differently (on search).
- Add new classes Field_string_key, Field_varstring_key, Field_blob_key and overwrite their store() methods

As soon as we're able to store bad values into the Field buffer, the collation should be able to do index scan in exactly the same way with a non-indexed search.

Alexander Barkov made changes - 2015-08-20 12:07

Description

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences somehow for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to handle bad bytes differently (on search).
- Add new classes Field_string_key, Field_varstring_key, Field_blob_key and overwrite their store() methods

As soon as we're able to store bad values into the Field buffer, the collation should be able to do index scan in exactly the same way with a non-indexed search.

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences somehow for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to handle bad bytes differently (on search).
- Add new classes Field_string_key, Field_varstring_key, Field_blob_key and overwrite their store() methods

As soon as we're able to store bad values into the Field buffer in a way suitable for search, the collation should be able to do index scan in exactly the same way with a non-indexed search.

Alexander Barkov made changes - 2015-08-20 12:07

Description

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences somehow for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to handle bad bytes differently (on search).
- Add new classes Field_string_key, Field_varstring_key, Field_blob_key and overwrite their store() methods

As soon as we're able to store bad values into the Field buffer in a way suitable for search, the collation should be able to do index scan in exactly the same way with a non-indexed search.

Under terms of ~~MDEV-7649~~ we disabled using indexes when comparing a field to a broken character string when processing non-equality operations <, >, <=, =>, <> , for example:

{code}
SET NAMES 'utf8';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a varchar(60) COLLATE utf8_general_ci NOT NULL DEFAULT '',
  KEY(a)
) ENGINE=MyISAM CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO t1 (a) VALUES ('admin');
INSERT INTO t1 (a) VALUES ('admin;');
INSERT INTO t1 (a) VALUES ('adminx');
EXPLAIN SELECT * FROM t1 WHERE a < 'admin��';
{code}
returns
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | ALL | a | NULL | NULL | NULL | 3 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
{noformat}

Notice, '��' is a broken utf8 character (it's a valid utf8mb4 character).

Using indexes was disabled to have consistent result set with and without index.

Now with ~~MDEV-8036~~ done we can enable using indexes again.

get_mm_leaf() calls Field::store() to put the value to search into the Field buffer.
This task will need changes in Fields, which should be able to store broken byte sequences somehow for index search purposes, as the normal behavior of Field::store() to replace bad bytes to question marks won't work for search optimization purposes.

Possible solutions:
- Add a new parameter to Field::store()
- Add a new method, say Field::store_for_search()
- Add a new flag into Field::flags and make Field::store() check this flag to decide whether to replace bad bytes to question marks (on INSERT/UPDATE), or to handle bad bytes differently (on search).
- Add new classes Field_string_key, Field_varstring_key, Field_blob_key and overwrite their store() methods

As soon as we're able to store bad values into the Field buffer in a way suitable for search (instead of just replacing to '?'), the collation should be able to do index scan in exactly the same way with a non-indexed search.

Sergei Golubchik made changes - 2015-08-26 14:06

Rank

Ranked higher

Rasmus Johansson (Inactive) made changes - 2015-09-09 10:52

Sprint

10.1.7-2 [ 12 ]

10.1.7-2, 10.1.8-1 [ 12, 13 ]

Sergei Golubchik made changes - 2015-09-16 00:22

Sprint

10.1.7-2, 10.1.8-1 [ 12, 13 ]

10.1.7-2, 10.1.8-1, 10.1.8-2 [ 12, 13, 14 ]

Sergei Golubchik made changes - 2015-09-16 00:22

Rank

Ranked higher

Rasmus Johansson (Inactive) made changes - 2015-09-23 12:59

Sprint

10.1.7-2, 10.1.8-1, 10.1.8-2 [ 12, 13, 14 ]

10.1.7-2, 10.1.8-1, 10.1.8-3 [ 12, 13, 15 ]

Rasmus Johansson (Inactive) made changes - 2015-09-23 12:59

Rank

Ranked lower

Sergei Golubchik made changes - 2015-09-24 16:31

Sprint

10.1.7-2, 10.1.8-1, 10.1.8-3 [ 12, 13, 15 ]

10.1.7-2, 10.1.8-1 [ 12, 13 ]

Sergei Golubchik made changes - 2015-12-15 22:31

Sprint

10.1.7-2, 10.1.8-1 [ 12, 13 ]

10.1.7-2, 10.1.8-1, 10.1.10 [ 12, 13, 24 ]

Sergei Golubchik made changes - 2015-12-15 22:31

Sprint

10.1.7-2, 10.1.8-1, 10.1.10 [ 12, 13, 24 ]

10.1.7-2, 10.1.8-1 [ 12, 13 ]

Alexander Barkov made changes - 2016-01-11 05:50

Labels

performance

Julien Fritsch made changes - 2020-08-25 08:33

Assignee

Sergei Golubchik [ serg ]

Alexander Barkov [ bar ]

Julien Fritsch made changes - 2021-04-30 14:12

Assignee

Alexander Barkov [ bar ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2021-12-06 21:22

Workflow

MariaDB v3 [ 70510 ]

MariaDB v4 [ 131762 ]

Ralf Gebhardt made changes - 2023-10-12 11:02

Fix Version/s		11.4 [ 29301 ]
Fix Version/s	10.1 [ 16100 ]

Julien Fritsch made changes - 2023-11-06 10:47

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Golubchik made changes - 2023-12-22 17:45

Fix Version/s		11.5 [ 29506 ]
Fix Version/s	11.4 [ 29301 ]

Sergei Golubchik made changes - 2024-03-19 18:32

Fix Version/s		11.6 [ 29515 ]
Fix Version/s	11.5 [ 29506 ]

Sergei Golubchik made changes - 2024-06-04 15:39

Fix Version/s		11.7 [ 29815 ]
Fix Version/s	11.6 [ 29515 ]

Sergei Golubchik made changes - 2024-08-22 11:17

Priority

Critical [ 2 ]

Major [ 3 ]

Sergei Golubchik made changes - 2024-09-24 13:54

Fix Version/s		11.8 [ 29921 ]
Fix Version/s	11.7 [ 29815 ]

Sergei Golubchik made changes - 2024-11-21 11:03

Fix Version/s

11.8 [ 29921 ]

People

Assignee:: Sergei Golubchik

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2015-07-07 10:05

Updated:: 2024-11-21 11:03

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration