[MDEV-34305] Redundant truncation errors/warnings with optimizer_trace enabled - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.5, 11.5(EOL)
Fix Version/s: 10.5.26, 10.6.19, 10.11.9, 11.1.6, 11.2.5, 11.4.3, 11.5.2, 11.6.0
Component/s: Character Sets, Optimizer
Labels:
None

Description

The problem is repeatable with at least MyISAM and InnoDB.

SET @@optimizer_trace='enabled=on';

CREATE OR REPLACE TABLE t1 (

  a CHAR(2) NOT NULL PRIMARY KEY,

  b VARCHAR(20) NOT NULL,

  KEY (b)

) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE  OR REPLACE TABLE t2 (

  a CHAR(2) NOT NULL PRIMARY KEY,

  b VARCHAR(20) NOT NULL,

  KEY (b)

) ENGINE=InnoDB CHARSET=utf8mb4;

INSERT INTO t1 VALUES

('AB','MySQLAB'),

('JA','Sun Microsystems'),

('MS','Microsoft'),

('IB','IBM- Inc.'),

('GO','Google Inc.');

INSERT IGNORE INTO t2 VALUES

('AB','Sweden'),

('JA','USA'),

('MS','United States of America'),

('IB','North America'),

('GO','South America');

UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%';

ERROR 1406 (22001): Data too long for column 'b' at row 1

The above error is not expected.

Additionally, in some versions (at least 11.3 and higher), warnings are produced in a simpler script:

SET @@optimizer_trace='enabled=on';

CREATE OR REPLACE TABLE t1 (

  a CHAR(2) NOT NULL PRIMARY KEY,

  b VARCHAR(20) NOT NULL,

  KEY (b)

) ENGINE=InnoDB CHARSET=utf8mb4;

INSERT INTO t1 VALUES

('AB','MySQLAB'),

('JA','Sun Microsystems'),

('MS','Microsoft'),

('IB','IBM- Inc.'),

('GO','Google Inc.');

UPDATE t1 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%';

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

| Level   | Code | Message                                |

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

| Warning | 1265 | Data truncated for column 'b' at row 1 |

| Warning | 1265 | Data truncated for column 'b' at row 1 |

| Warning | 1265 | Data truncated for column 'b' at row 1 |

| Warning | 1265 | Data truncated for column 'b' at row 1 |

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

These warnings are not expected.

The problem seems to be in the fact that the call for like_range() in Item_func_like::get_mm_leaf() produces more characters than the size of the index.

The patch below fixes the problem, but the real patch should probably fix like_range() not to return more characters than the size of the index.

index 5793b19f695..108b4ac78ae 100644

--- a/sql/opt_range.cc

+++ b/sql/opt_range.cc

@@ -9174,6 +9174,19 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param,

                                    &min_length, &max_length))

     DBUG_RETURN(0);              // Can't optimize with LIKE

+  min_length= Well_formed_prefix(field->charset(),

+                                 (const char *) min_str + offset, min_length,

+                                 field->char_length()).length();

+  max_length= Well_formed_prefix(field->charset(),

+                                 (const char *) max_str + offset, max_length,

+                                 field->char_length()).length();

   if (offset != maybe_null)                    // BLOB or VARCHAR

     int2store(min_str + maybe_null, min_length);

Attachments

Issue Links

blocks

MDEV-19123 Change default charset from latin1 to utf8mb4

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2024-06-05 04:51

Alexander Barkov made changes - 2024-06-05 04:52

Field	Original Value	New Value
Description	{code:sql} SET @@optimizer_trace='enabled=on'; CREATE OR REPLACE TABLE t1 ( a CHAR(2) NOT NULL PRIMARY KEY, b VARCHAR(20) NOT NULL, KEY (b) ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE OR REPLACE TABLE t2 ( a CHAR(2) NOT NULL PRIMARY KEY, b VARCHAR(20) NOT NULL, KEY (b) ) ENGINE=InnoDB CHARSET=utf8mb4; INSERT INTO t1 VALUES ('AB','MySQLAB'), ('JA','Sun Microsystems'), ('MS','Microsoft'), ('IB','IBM- Inc.'), ('GO','Google Inc.'); INSERT IGNORE INTO t2 VALUES ('AB','Sweden'), ('JA','USA'), ('MS','United States of America'), ('IB','North America'), ('GO','South America'); UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%'; {code} {noformat} ERROR 1406 (22001): Data too long for column 'b' at row 1 {noformat} The above error is not expected. Additionally, in some versions (at least 11.3 and higher), warnings are produced in a simpler script: {code:sql} SET @@optimizer_trace='enabled=on'; CREATE OR REPLACE TABLE t1 ( a CHAR(2) NOT NULL PRIMARY KEY, b VARCHAR(20) NOT NULL, KEY (b) ) ENGINE=InnoDB CHARSET=utf8mb4; INSERT INTO t1 VALUES ('AB','MySQLAB'), ('JA','Sun Microsystems'), ('MS','Microsoft'), ('IB','IBM- Inc.'), ('GO','Google Inc.'); UPDATE t1 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%'; {code} {noformat} +---------+------+----------------------------------------+ \| Level \| Code \| Message \| +---------+------+----------------------------------------+ \| Warning \| 1265 \| Data truncated for column 'b' at row 1 \| \| Warning \| 1265 \| Data truncated for column 'b' at row 1 \| \| Warning \| 1265 \| Data truncated for column 'b' at row 1 \| \| Warning \| 1265 \| Data truncated for column 'b' at row 1 \| +---------+------+----------------------------------------+ {noformat} These warnings are not expected. The problem seems to be in the fact that the call for {{like_range()}} in {{Item_func_like::get_mm_leaf}} produces more characters than the size of the index. The patch below fixes the problem, but the real patch should probably fix {{like_range()}} not to return more characters than the size of the index. {code:cpp} index 5793b19f695..108b4ac78ae 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -9174,6 +9174,19 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM param, &min_length, &max_length)) DBUG_RETURN(0); // Can't optimize with LIKE + min_length= Well_formed_prefix(field->charset(), + (const char ) min_str + offset, min_length, + field->char_length()).length(); + max_length= Well_formed_prefix(field->charset(), + (const char *) max_str + offset, max_length, + field->char_length()).length(); if (offset != maybe_null) // BLOB or VARCHAR { int2store(min_str + maybe_null, min_length); {code}	{code:sql} SET @@optimizer_trace='enabled=on'; CREATE OR REPLACE TABLE t1 ( a CHAR(2) NOT NULL PRIMARY KEY, b VARCHAR(20) NOT NULL, KEY (b) ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE OR REPLACE TABLE t2 ( a CHAR(2) NOT NULL PRIMARY KEY, b VARCHAR(20) NOT NULL, KEY (b) ) ENGINE=InnoDB CHARSET=utf8mb4; INSERT INTO t1 VALUES ('AB','MySQLAB'), ('JA','Sun Microsystems'), ('MS','Microsoft'), ('IB','IBM- Inc.'), ('GO','Google Inc.'); INSERT IGNORE INTO t2 VALUES ('AB','Sweden'), ('JA','USA'), ('MS','United States of America'), ('IB','North America'), ('GO','South America'); UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%'; {code} {noformat} ERROR 1406 (22001): Data too long for column 'b' at row 1 {noformat} The above error is not expected. Additionally, in some versions (at least 11.3 and higher), warnings are produced in a simpler script: {code:sql} SET @@optimizer_trace='enabled=on'; CREATE OR REPLACE TABLE t1 ( a CHAR(2) NOT NULL PRIMARY KEY, b VARCHAR(20) NOT NULL, KEY (b) ) ENGINE=InnoDB CHARSET=utf8mb4; INSERT INTO t1 VALUES ('AB','MySQLAB'), ('JA','Sun Microsystems'), ('MS','Microsoft'), ('IB','IBM- Inc.'), ('GO','Google Inc.'); UPDATE t1 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%'; {code} {noformat} +---------+------+----------------------------------------+ \| Level \| Code \| Message \| +---------+------+----------------------------------------+ \| Warning \| 1265 \| Data truncated for column 'b' at row 1 \| \| Warning \| 1265 \| Data truncated for column 'b' at row 1 \| \| Warning \| 1265 \| Data truncated for column 'b' at row 1 \| \| Warning \| 1265 \| Data truncated for column 'b' at row 1 \| +---------+------+----------------------------------------+ {noformat} These warnings are not expected. The problem seems to be in the fact that the call for {{like_range()}} in {{Item_func_like::get_mm_leaf()}} produces more characters than the size of the index. The patch below fixes the problem, but the real patch should probably fix {{like_range()}} not to return more characters than the size of the index. {code:cpp} index 5793b19f695..108b4ac78ae 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -9174,6 +9174,19 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM param, &min_length, &max_length)) DBUG_RETURN(0); // Can't optimize with LIKE + min_length= Well_formed_prefix(field->charset(), + (const char ) min_str + offset, min_length, + field->char_length()).length(); + max_length= Well_formed_prefix(field->charset(), + (const char *) max_str + offset, max_length, + field->char_length()).length(); if (offset != maybe_null) // BLOB or VARCHAR { int2store(min_str + maybe_null, min_length); {code}

Alexander Barkov made changes - 2024-06-05 04:52

Link

This issue blocks ~~MDEV-34295~~ [ ~~MDEV-34295~~ ]

Alexander Barkov made changes - 2024-06-05 14:23

Priority

Major [ 3 ]

Critical [ 2 ]

Alexander Barkov made changes - 2024-06-06 04:10

Link

This issue blocks ~~MDEV-34295~~ [ ~~MDEV-34295~~ ]

Alexander Barkov made changes - 2024-06-06 04:10

Link

This issue blocks ~~MDEV-19123~~ [ ~~MDEV-19123~~ ]

Alexander Barkov made changes - 2024-06-11 09:35

Description

{code:sql}
SET @@optimizer_trace='enabled=on';

CREATE OR REPLACE TABLE t1 (
  a CHAR(2) NOT NULL PRIMARY KEY,
  b VARCHAR(20) NOT NULL,
  KEY (b)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE OR REPLACE TABLE t2 (
  a CHAR(2) NOT NULL PRIMARY KEY,
  b VARCHAR(20) NOT NULL,
  KEY (b)
) ENGINE=InnoDB CHARSET=utf8mb4;

INSERT INTO t1 VALUES
('AB','MySQLAB'),
('JA','Sun Microsystems'),
('MS','Microsoft'),
('IB','IBM- Inc.'),
('GO','Google Inc.');

INSERT IGNORE INTO t2 VALUES
('AB','Sweden'),
('JA','USA'),
('MS','United States of America'),
('IB','North America'),
('GO','South America');

UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%';
{code}
{noformat}
ERROR 1406 (22001): Data too long for column 'b' at row 1
{noformat}

The above error is not expected.

Additionally, in some versions (at least 11.3 and higher), warnings are produced in a simpler script:

{code:sql}
SET @@optimizer_trace='enabled=on';

CREATE OR REPLACE TABLE t1 (
  a CHAR(2) NOT NULL PRIMARY KEY,
  b VARCHAR(20) NOT NULL,
  KEY (b)
) ENGINE=InnoDB CHARSET=utf8mb4;

INSERT INTO t1 VALUES
('AB','MySQLAB'),
('JA','Sun Microsystems'),
('MS','Microsoft'),
('IB','IBM- Inc.'),
('GO','Google Inc.');

UPDATE t1 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%';
{code}
{noformat}
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'b' at row 1 |
| Warning | 1265 | Data truncated for column 'b' at row 1 |
| Warning | 1265 | Data truncated for column 'b' at row 1 |
| Warning | 1265 | Data truncated for column 'b' at row 1 |
+---------+------+----------------------------------------+
{noformat}
These warnings are not expected.

The problem seems to be in the fact that the call for *{{like_range()}}* in *{{Item_func_like::get_mm_leaf()}}* produces more characters than the size of the index.

The patch below fixes the problem, but the real patch should probably fix *{{like_range()}}* not to return more characters than the size of the index.

{code:cpp}
index 5793b19f695..108b4ac78ae 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -9174,6 +9174,19 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param,
                                    &min_length, &max_length))
     DBUG_RETURN(0); // Can't optimize with LIKE

+ min_length= Well_formed_prefix(field->charset(),
+ (const char *) min_str + offset, min_length,
+ field->char_length()).length();
+ max_length= Well_formed_prefix(field->charset(),
+ (const char *) max_str + offset, max_length,
+ field->char_length()).length();
   if (offset != maybe_null) // BLOB or VARCHAR
   {
     int2store(min_str + maybe_null, min_length);

{code}

The problem is repeatable with at least MyISAM and InnoDB.

{code:sql}
SET @@optimizer_trace='enabled=on';

CREATE OR REPLACE TABLE t1 (
  a CHAR(2) NOT NULL PRIMARY KEY,
  b VARCHAR(20) NOT NULL,
  KEY (b)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE OR REPLACE TABLE t2 (
  a CHAR(2) NOT NULL PRIMARY KEY,
  b VARCHAR(20) NOT NULL,
  KEY (b)
) ENGINE=InnoDB CHARSET=utf8mb4;

INSERT INTO t1 VALUES
('AB','MySQLAB'),
('JA','Sun Microsystems'),
('MS','Microsoft'),
('IB','IBM- Inc.'),
('GO','Google Inc.');

INSERT IGNORE INTO t2 VALUES
('AB','Sweden'),
('JA','USA'),
('MS','United States of America'),
('IB','North America'),
('GO','South America');

UPDATE t1,t2 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%';
{code}
{noformat}
ERROR 1406 (22001): Data too long for column 'b' at row 1
{noformat}

The above error is not expected.

Additionally, in some versions (at least 11.3 and higher), warnings are produced in a simpler script:

{code:sql}
SET @@optimizer_trace='enabled=on';

CREATE OR REPLACE TABLE t1 (
  a CHAR(2) NOT NULL PRIMARY KEY,
  b VARCHAR(20) NOT NULL,
  KEY (b)
) ENGINE=InnoDB CHARSET=utf8mb4;

INSERT INTO t1 VALUES
('AB','MySQLAB'),
('JA','Sun Microsystems'),
('MS','Microsoft'),
('IB','IBM- Inc.'),
('GO','Google Inc.');

UPDATE t1 SET t1.b=UPPER(t1.b) WHERE t1.b LIKE 'Unknown%';
{code}
{noformat}
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'b' at row 1 |
| Warning | 1265 | Data truncated for column 'b' at row 1 |
| Warning | 1265 | Data truncated for column 'b' at row 1 |
| Warning | 1265 | Data truncated for column 'b' at row 1 |
+---------+------+----------------------------------------+
{noformat}
These warnings are not expected.

The problem seems to be in the fact that the call for *{{like_range()}}* in *{{Item_func_like::get_mm_leaf()}}* produces more characters than the size of the index.

The patch below fixes the problem, but the real patch should probably fix *{{like_range()}}* not to return more characters than the size of the index.

{code:cpp}
index 5793b19f695..108b4ac78ae 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -9174,6 +9174,19 @@ Item_func_like::get_mm_leaf(RANGE_OPT_PARAM *param,
                                    &min_length, &max_length))
     DBUG_RETURN(0); // Can't optimize with LIKE

+ min_length= Well_formed_prefix(field->charset(),
+ (const char *) min_str + offset, min_length,
+ field->char_length()).length();
+ max_length= Well_formed_prefix(field->charset(),
+ (const char *) max_str + offset, max_length,
+ field->char_length()).length();
   if (offset != maybe_null) // BLOB or VARCHAR
   {
     int2store(min_str + maybe_null, min_length);

{code}

Alexander Barkov made changes - 2024-06-14 09:31

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2024-06-14 09:31

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

Alexander Barkov made changes - 2024-07-08 14:40

Assignee

Sergei Golubchik [ serg ]

Alexander Barkov [ bar ]

Alexander Barkov made changes - 2024-07-08 14:41

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Alexander Barkov made changes - 2024-07-08 14:41

Fix Version/s		10.5.26 [ 29832 ]
Fix Version/s	10.5 [ 23123 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Alexander Barkov made changes - 2024-07-08 14:41

Comment

[ [~serg], Please review a patch for 10.5:

https://github.com/MariaDB/server/commit/0104c7639c98fa2db9ca13a3ce47079917759ad3 ]

Alexander Barkov made changes - 2024-07-08 15:04

Fix Version/s		10.6.19 [ 29833 ]
Fix Version/s		10.11.9 [ 29834 ]
Fix Version/s		11.1.6 [ 29835 ]
Fix Version/s		11.2.5 [ 29836 ]
Fix Version/s		11.4.3 [ 29837 ]
Fix Version/s		11.5.2 [ 29838 ]
Fix Version/s		11.6.0 [ 29839 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2024-06-05 04:51

Updated:: 2024-07-08 15:04

Resolved:: 2024-07-08 14:41

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