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

Wrong result with CTEs (name resolution?)

    XMLWordPrintable

Details

    Description

      I am trying an example with CTEs, using branch bb-10.2-mdev9864, tip cset

      commit 8c6a9aa30f9e74388aaf923ac8e3b19ca0f86188
      Author: Igor Babaev <igor@askmonty.org>
      Date:   Thu Jun 30 15:13:12 2016 -0700
       
          Added a proper check for acceptable mutually recursive CTE.
      

      create table ten(a int primary key);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table one_k(a int primary key);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
       
      create view v1 as select * from ten;
      

      mysql> select * from v1;
      +------+
      | a    |
      +------+
      |    0 |
      |    1 |
      |    2 |
      |    3 |
      |    4 |
      |    5 |
      |    6 |
      |    7 |
      |    8 |
      |    9 |
      +------+
      10 rows in set (0.01 sec)
       
      mysql> with ten as (select  a from one_k where a<20) select * from v1;
      +------+
      | a    |
      +------+
      |    0 |
      |    1 |
      |    2 |
      |    3 |
      |    4 |
      |    5 |
      |    6 |
      |    7 |
      |    8 |
      |    9 |
      |   10 |
      |   11 |
      |   12 |
      |   13 |
      |   14 |
      |   15 |
      |   16 |
      |   17 |
      |   18 |
      |   19 |
      +------+
      20 rows in set (0.06 sec)
      

      I think, the last query should produce 10 rows, not 20.

      Attachments

        Activity

          People

            igor Igor Babaev
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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