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

View stop working after upgrade from 10.1.15 to 10.3.7

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.7, 10.3(EOL)
    • 10.3.8
    • Views
    • None

    Description

      After upgrade MariaDB from 10.1.15 to 10.3.7 one view stop working:
      CREATE OR REPLACE VIEW first_view AS SELECT o.order_pk FROM second_view AS o LEFT JOIN third_view AS d ON o.order_pk=d.order_pk;
      Now return
      Unknown column 'd.order_pk' in 'on clause'

      If i change LEFT JOIN to RIGHT JOIN no errors.

      I have 10.1.15 in the same cluster and this view works fine.

      Attachments

        Issue Links

          Activity

            cyber_neo Zakharov Sergey added a comment - - edited

            Strange, maybe that's happened because your select more simple that my view?
            My full view looks like this:

            CREATE OR REPLACE VIEW third_view AS
            SELECT
            o.PK AS order_pk,

            MAX(IF(omc.ostatus = 'SHIPPED', omc.p_timestamp, null)) AS shipped_date,
            MAX(IF(omc.ostatus = 'REALIZATION', omc.p_timestamp, null)) AS realization_date,
            MAX(IF(omc.ostatus = 'RETURN', omc.p_timestamp, null)) AS return_date,
            MAX(IF(omc.ostatus = 'RETURN_AND_REALIZATION', omc.p_timestamp, null)) AS return_and_realization_date,
            MAX(IF(omc.ostatus = 'APPROVED', omc.p_timestamp, null)) AS approved_date,
            MAX(IF(omc.ostatus = 'READY_TO_PACKING', omc.p_timestamp, null)) AS ready_to_packing_date,
            MAX(IF(omc.ostatus = 'PACKED', omc.p_timestamp, null)) AS packed_date,
            MAX(IF(omc.ostatus = 'READY_FOR_ISSUANCE', omc.p_timestamp, null)) AS ready_for_issurance_date,
            MAX(IF(omc.ostatus = 'IN_STORE_PARTIALLY_COMPLETED', omc.p_timestamp, null)) AS in_store_partially_completed_date,
            MAX(IF(omc.ostatus = 'DELIVERED', omc.p_timestamp, null)) AS delivered_date,
            MAX(IF(omc.ostatus = 'CANCELLED', omc.p_timestamp, null)) AS cancelled_date,
            MAX(IF(omc.ostatus = 'RETURNED_FOR_REVISION', omc.p_timestamp, null)) AS returned_for_revision_date,
            MAX(IF(omc.ostatus = 'MOVED_TO_RETAIL', omc.p_timestamp, null)) AS moved_to_retail_date,
            MAX(IF(omc.ostatus = 'PROCESSED', omc.p_timestamp, null)) AS processed_date,
            MAX(IF(omc.ostatus = 'WAITING', omc.p_timestamp, null)) AS waiting_date,
            MAX(IF(omc.ostatus = 'UNCALL', omc.p_timestamp, null)) AS uncall_date,
            MAX(IF(omc.ostatus = 'PARTIALLY_READY_FOR_ISSUANCE', omc.p_timestamp, null)) AS partially_ready_for_issuance_date,
            MAX(IF(omc.ostatus = 'PARTIAL_REALIZATION', omc.p_timestamp, null)) AS partial_realization_date,
            MAX(IF(omc.ostatus = 'REALIZATION_AND_RETURN', omc.p_timestamp, null)) AS realization_and_return_date

            FROM main_view AS o
            JOIN ordermodifirecords AS omr
            ON o.PK = omr.p_order
            JOIN ordermodifirecentrs AS omc
            ON omc.p_modificationrecord = omr.PK
            WHERE omc.p_status IS NOT NULL
            GROUP BY o.PK
            ORDER BY NULL;

            If i test your example, disable optimization option works good.

            cyber_neo Zakharov Sergey added a comment - - edited Strange, maybe that's happened because your select more simple that my view? My full view looks like this: CREATE OR REPLACE VIEW third_view AS SELECT o.PK AS order_pk, MAX(IF(omc.ostatus = 'SHIPPED', omc.p_timestamp, null)) AS shipped_date, MAX(IF(omc.ostatus = 'REALIZATION', omc.p_timestamp, null)) AS realization_date, MAX(IF(omc.ostatus = 'RETURN', omc.p_timestamp, null)) AS return_date, MAX(IF(omc.ostatus = 'RETURN_AND_REALIZATION', omc.p_timestamp, null)) AS return_and_realization_date, MAX(IF(omc.ostatus = 'APPROVED', omc.p_timestamp, null)) AS approved_date, MAX(IF(omc.ostatus = 'READY_TO_PACKING', omc.p_timestamp, null)) AS ready_to_packing_date, MAX(IF(omc.ostatus = 'PACKED', omc.p_timestamp, null)) AS packed_date, MAX(IF(omc.ostatus = 'READY_FOR_ISSUANCE', omc.p_timestamp, null)) AS ready_for_issurance_date, MAX(IF(omc.ostatus = 'IN_STORE_PARTIALLY_COMPLETED', omc.p_timestamp, null)) AS in_store_partially_completed_date, MAX(IF(omc.ostatus = 'DELIVERED', omc.p_timestamp, null)) AS delivered_date, MAX(IF(omc.ostatus = 'CANCELLED', omc.p_timestamp, null)) AS cancelled_date, MAX(IF(omc.ostatus = 'RETURNED_FOR_REVISION', omc.p_timestamp, null)) AS returned_for_revision_date, MAX(IF(omc.ostatus = 'MOVED_TO_RETAIL', omc.p_timestamp, null)) AS moved_to_retail_date, MAX(IF(omc.ostatus = 'PROCESSED', omc.p_timestamp, null)) AS processed_date, MAX(IF(omc.ostatus = 'WAITING', omc.p_timestamp, null)) AS waiting_date, MAX(IF(omc.ostatus = 'UNCALL', omc.p_timestamp, null)) AS uncall_date, MAX(IF(omc.ostatus = 'PARTIALLY_READY_FOR_ISSUANCE', omc.p_timestamp, null)) AS partially_ready_for_issuance_date, MAX(IF(omc.ostatus = 'PARTIAL_REALIZATION', omc.p_timestamp, null)) AS partial_realization_date, MAX(IF(omc.ostatus = 'REALIZATION_AND_RETURN', omc.p_timestamp, null)) AS realization_and_return_date FROM main_view AS o JOIN ordermodifirecords AS omr ON o.PK = omr.p_order JOIN ordermodifirecentrs AS omc ON omc.p_modificationrecord = omr.PK WHERE omc.p_status IS NOT NULL GROUP BY o.PK ORDER BY NULL; If i test your example, disable optimization option works good.
            alice Alice Sherepa added a comment -

            cyber_neo, thanks for adding the whole view, it is good to check after the bug will be fixed, that it solves the initial problem, not only the oversimplified version.

            CREATE TABLE t1 (id int, PRIMARY KEY (id));
             
            WITH cte AS 
            (SELECT o.id bb FROM (SELECT * FROM t1) AS o GROUP BY o.id)
            SELECT * FROM t1 
            LEFT JOIN cte ON t1.id = cte.bb;
            #ERROR 1054 (42S22): Unknown column 'cte.bb' in 'on clause'
             
            SELECT * FROM t1 
            LEFT JOIN (SELECT o.id bb FROM (SELECT * FROM t1) AS o GROUP BY o.id) t2 ON t1.id = t2.bb;
            #ERROR 1054 (42S22): Unknown column 't2.bb' in 'on clause'
            

            alice Alice Sherepa added a comment - cyber_neo , thanks for adding the whole view, it is good to check after the bug will be fixed, that it solves the initial problem, not only the oversimplified version. CREATE TABLE t1 (id int , PRIMARY KEY (id));   WITH cte AS ( SELECT o.id bb FROM ( SELECT * FROM t1) AS o GROUP BY o.id) SELECT * FROM t1 LEFT JOIN cte ON t1.id = cte.bb; #ERROR 1054 (42S22): Unknown column 'cte.bb' in 'on clause'   SELECT * FROM t1 LEFT JOIN ( SELECT o.id bb FROM ( SELECT * FROM t1) AS o GROUP BY o.id) t2 ON t1.id = t2.bb; #ERROR 1054 (42S22): Unknown column 't2.bb' in 'on clause'

            Here's a test case demonstrating the same problem that uses only views:

            CREATE TABLE t1 (id int, PRIMARY KEY (id));
            INSERT INTO t1 VALUES (2), (3), (7), (1);
            CREATE VIEW v1 AS SELECT * FROM t1;
            CREATE  VIEW v2 AS SELECT v1.id AS order_pk FROM v1 GROUP BY v1.id;
            SELECT * FROM t1  LEFT JOIN v2  ON t1.id=v2.order_pk;
            

            igor Igor Babaev (Inactive) added a comment - Here's a test case demonstrating the same problem that uses only views: CREATE TABLE t1 (id int, PRIMARY KEY (id)); INSERT INTO t1 VALUES (2), (3), (7), (1); CREATE VIEW v1 AS SELECT * FROM t1; CREATE VIEW v2 AS SELECT v1.id AS order_pk FROM v1 GROUP BY v1.id; SELECT * FROM t1 LEFT JOIN v2 ON t1.id=v2.order_pk;

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push

            A fix for this bug was pushed into 10.3 tree.

            igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 10.3 tree.

            People

              igor Igor Babaev (Inactive)
              cyber_neo Zakharov Sergey
              Votes:
              1 Vote for this issue
              Watchers:
              8 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.