[MDEV-20011] Virtual columns contains grarbage Created: 2019-07-10  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.2.25, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Mykhailo Pashchenko Assignee: Nikita Malyavin
Resolution: Unresolved Votes: 1
Labels: None


 Description   

There is a garbage in virutal column in big tables with utf8 encoding.
Table:

CREATE TABLE clients_copy4 (
 
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
    col1 VARCHAR(255) NOT NULL DEFAULT '',
 
    col2 VARCHAR(255) NOT NULL DEFAULT '',
 
    col3 VARCHAR(32) NULL DEFAULT NULL,
 
    col_virtual VARCHAR(255) AS (concat(col1,' ',col2,' (',ifnull(col3,''),')')) VIRTUAL
 
)ENGINE=InnoDB
 
 
 
for($i=0; $i < 14000; $i++){
 
    ENV::$dbi->insert("INSERT INTO clients_copy4(col1, col2, col3) VALUES ('мама', 'мыла', 'раму')");
 
}

Query:

SELECT id, col_virtual  FROM clients_copy4 ORDER BY col_virtual;

Result:
http://prntscr.com/od0vcs

If table contains less than 14k rows - it works ok, only big table has this bug.

 Comments   
Comment by Alice Sherepa [ 2019-07-10 ]

Thanks a lot!
Reproduced as described, on 10.2-10.4 with InnoDB and MyIsam, when the query has "ORDER BY" virtual column
Not reproducible on 10.1

--source include/have_sequence.inc
 
 
 
CREATE TABLE t1 (
 
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
    col1 VARCHAR(255) NOT NULL DEFAULT '',
 
    col2 VARCHAR(255) NOT NULL DEFAULT '',
 
    col3 VARCHAR(32) NULL DEFAULT NULL,
 
    col_virtual VARCHAR(255) AS (concat(col1,' ',col2,' (',ifnull(col3,''),')')) VIRTUAL
 
);
 
 
INSERT INTO t1(col1, col2, col3) select 'мама', 'мыла', 'раму' from seq_1_to_14000;
 
SELECT id, col_virtual  FROM t1 ORDER BY col_virtual;

Comment by Nikita Malyavin [ 2022-06-27 ]

man i like garbage

Generated at Thu Feb 08 08:56:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.