[MDEV-29231] View returns wrong value with SQL_MODE 'NO_BACKSLASH_ESCAPES' Created: 2022-08-02  Updated: 2023-11-28

Status: Stalled
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Lena Startseva Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: view-protocol

Issue Links:
PartOf
is part of MDEV-27691 make working view-protocol Open

 Description   

Expected that "select from table" and "select from view" give the same result:
Test:

SET @@SQL_MODE='NO_BACKSLASH_ESCAPES';
 
show local variables like 'SQL_MODE';
 
 
 
CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p));
 
INSERT t1 (a) VALUES 
('\\'),
 
('\n'),
 
('\b'),
 
('\r');
 
 
 
select 
  masks.p,
 
  masks.a as mask,
 
  examples.a as example
 
from
 
            t1 as masks
 
  left join t1 as examples on examples.a LIKE masks.a
 
order by masks.p, example;
 
 
 
create view v1 as select 
  masks.p,
 
  masks.a as mask,
 
  examples.a as example
 
from
 
            t1 as masks
 
  left join t1 as examples on examples.a LIKE masks.a
 
order by masks.p, example;
 
select * from v1;
 
 
 
drop view v1;
 
drop table t1;

Actual result:

select 
masks.p,
 
masks.a as mask,
 
examples.a as example
 
from
 
t1 as masks
 
left join t1 as examples on examples.a LIKE masks.a
 
order by masks.p, example;
 
p	mask	example
 
1	\\	\\
 
2	\n	\n
 
3	\b	\b
 
4	\r	\r
 
create view v1 as select 
masks.p,
 
masks.a as mask,
 
examples.a as example
 
from
 
t1 as masks
 
left join t1 as examples on examples.a LIKE masks.a
 
order by masks.p, example;
 
select * from v1;
 
p	mask	example
 
1	\\	NULL
 
2	\n	NULL
 
3	\b	NULL
 
4	\r	NULL

Expected result:

select 
masks.p,
 
masks.a as mask,
 
examples.a as example
 
from
 
t1 as masks
 
left join t1 as examples on examples.a LIKE masks.a
 
order by masks.p, example;
 
p	mask	example
 
1	\\	\\
 
2	\n	\n
 
3	\b	\b
 
4	\r	\r
 
create view v1 as select 
masks.p,
 
masks.a as mask,
 
examples.a as example
 
from
 
t1 as masks
 
left join t1 as examples on examples.a LIKE masks.a
 
order by masks.p, example;
 
select * from v1;
 
p	mask	example
 
1	\\	\\
 
2	\n	\n
 
3	\b	\b
 
4	\r	\r



 Comments   
Comment by Oleksandr Byelkin [ 2022-12-07 ]

I made it critical, because the fix it add workaround of the bug which can lead to making brocken .frm for a view with ".. like ... escape ..."

Comment by Oleksandr Byelkin [ 2022-12-07 ]

Branch bb-10.3-MDEV-29231

commit 92ff948d021ab706a1d534b186bec1a8f8263966
 
Author: Oleksandr Byelkin <sanja@mariadb.com>
 
Date:   Mon Dec 5 12:47:07 2022 +0100
 
 
 
    MDEV-29231 View returns wrong value with SQL_MODE 'NO_BACKSLASH_ESCAPES'
 
    
    1. make view printing sql_mode independent
 
      1.1. Print always current escape character
 
      1.2. Allow escape character usage independent of sql_mode
 
    2. Add workaround for parsing "like 3 in (0,1) escape 3" problem
 
     (alwaysuse parances in case of printing ESCAPE)

Generated at Thu Feb 08 10:06:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.