Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29231

View returns wrong value with SQL_MODE 'NO_BACKSLASH_ESCAPES'

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
    • 10.4, 10.5, 10.6, 10.11
    • None

    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
      

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              lstartseva Lena Startseva
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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