[MDEV-25672] table alias from previous statement interferes later commands Created: 2021-05-14  Updated: 2021-06-23  Resolved: 2021-06-03

Status: Closed
Project: MariaDB Server
Component/s: Server, Virtual Columns
Affects Version/s: 10.3.29, 10.4.19, 10.5.10, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.39, 10.3.30, 10.4.20, 10.5.11

Type: Bug Priority: Blocker
Reporter: Soner Sayakci Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Blocks
Duplicate
duplicates MDEV-25673 MariaDB fail to execute CONVERT TO CH... Closed
duplicates MDEV-25676 Alias in a view on a table with virtu... Closed
duplicates MDEV-25699 Cannot create new column - check cons... Closed
duplicates MDEV-25732 Error while removing the constraint Closed
duplicates MDEV-25733 Select with table name alias prevents... Closed
duplicates MDEV-25763 optimize table : Unknown column Closed
duplicates MDEV-25868 CHECK constaint refers to old table n... Closed
duplicates MDEV-25879 Problem with modifing tables with STO... Closed
duplicates MDEV-25932 Error 1054 on DROP INDEX for tables h... Closed
is duplicated by MDEV-25997 ALTER TABLE command works on one tabl... Closed
Problem/Incident
is caused by MDEV-25091 CREATE TABLE: field references qualif... Closed
Relates
relates to MDEV-10355 Weird error message upon CREATE TABLE... Closed
relates to MDEV-16039 Crash when selecting virtual columns ... Closed
relates to MDEV-24176 Server crashes after insert in the ta... Closed
relates to MDEV-25794 vcol_info refix during lock_tables() ... Open

 Description   

When an Update Statement happens with an alias. The alias seems to replace the table name.

See below an example Query to reproduce the failure:

DROP TABLE IF EXISTS `test_table`;
 
 
 
CREATE TABLE `test_table` (
 
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
  `order_date` datetime NOT NULL,
 
  `language_id` BINARY(16) NULL 
);
 
 
 
ALTER TABLE `test_table` CHANGE `order_date` `order_date_time` DATETIME(3) NOT NULL;
 
 
 
ALTER TABLE `test_table` ADD COLUMN `order_date` DATE GENERATED ALWAYS AS (CONVERT(`order_date_time`, DATE)) STORED AFTER `order_date_time`;
 
 
 
UPDATE `test_table` as `x` SET order_date = NULL;
 
 
 
ALTER TABLE `test_table` MODIFY COLUMN `language_id` BINARY(16) NOT NULL;

Results in the following error on the ALTER TABLE statement:

Unknown column '`test`.`x`.`order_date_time`' in 'GENERATED ALWAYS'



 Comments   
Comment by Alice Sherepa [ 2021-05-14 ]

Thank you! Repeatable on 10.2-10.5

CREATE TABLE t1 ( a int, v_a int GENERATED always AS (a));
 
UPDATE t1 as x SET a = 1;
 
ALTER TABLE t1 force;


query 'ALTER TABLE t1 force' failed: 1054: Unknown column '`test`.`x`.`a`' in 'GENERATED ALWAYS'

Comment by Alice Sherepa [ 2021-05-14 ]

test case from MDEV-25676:

CREATE TABLE `RULE` (
 
   `DELETED` BIT(1),
 
   `UNIQ_CODE` VARCHAR(255) GENERATED ALWAYS AS (CASE DELETED WHEN 0 THEN 1 end) PERSISTENT
 
);
 
 
CREATE OR REPLACE VIEW `V_RULE` AS SELECT DELETED FROM RULE r;
 
 
CREATE INDEX `RULE_IDX01` ON `RULE` (DELETED);

Comment by Soner Sayakci [ 2021-05-17 ]

Hey @alice,

Can we expect a fix in the next patch version? Building and integrating a workaround is hard

Comment by Sergei Golubchik [ 2021-05-17 ]

Yes, it will be fixed in the next release.

As a workaround I'd think a FLUSH TABLES before ALTER should hide the bug.

Comment by Aleksey Midenkov [ 2021-05-31 ]

Please review 2 commits in bb-10.2-midenok

Comment by Nikita Malyavin [ 2021-06-01 ]

FYI git bisect points to
42f8548f MDEV-25091 CREATE TABLE: field references qualified by a wrong table name succeed

Comment by Aleksey Midenkov [ 2021-06-02 ]

nikitamalyavin just look at Issue Links caused by.

Comment by Aleksey Midenkov [ 2021-06-02 ]

ralf.gebhardt@mariadb.com serg If there is no time for this fix to be properly tested then MDEV-25091 should be reverted until the fix pushed.

Comment by Sergei Golubchik [ 2021-06-02 ]

I'd rather push a one-liner, as discussed on slack. I'll fix the bug without reopening MDEV-25091.

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