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

            Does it throw the error when you are trying to create the view, or when you are trying to use it?
            Please provide the underlying structures (second_view, third_view and the tables/views which they use), and your cnf file(s).
            Thanks.

            elenst Elena Stepanova added a comment - Does it throw the error when you are trying to create the view, or when you are trying to use it? Please provide the underlying structures ( second_view , third_view and the tables/views which they use), and your cnf file(s). Thanks.
            cyber_neo Zakharov Sergey added a comment - - edited

            This error occure when i try to select * from this view

            Full schema:
            Main view:

            CREATE OR REPLACE VIEW main_view AS
              SELECT
                *
              FROM table AS o
              WHERE o.coll BETWEEN report_from_order_creation_date() AND report_until_order_creation_date();
            

            second_view:

            CREATE OR REPLACE VIEW second_view AS
              SELECT
                o.PK AS order_pk
            FROM main_view AS o
            

            third_view:

            CREATE OR REPLACE VIEW third_view AS
              SELECT
                o.PK AS order_pk
            FROM main_view AS o
            

            my.cnf the same on 10.1.15 and 1.3.7:

            [mysqld]
             
            # GENERAL #
            user                           = mysql
            default-storage-engine         = InnoDB
            socket                         = /db/mysql/mysql.sock
            pid-file                       = /db/mysql/mysql.pid
            open_files_limit               = 102400
             
            # MyISAM #
            key-buffer-size                = 32M
            myisam-recover                 = FORCE,BACKUP
             
            # SAFETY #
            max-allowed-packet             = 16M
            max-connect-errors             = 1000000
            innodb_lock_wait_timeout       = 180
            skip-name-resolve
             
            # DATA STORAGE #
            datadir                        = /db/mysql/
            tmpdir                         = /dev/shm
             
            # BINARY LOGGING #
            log-bin                        = /db/mysql/mysql-bin
            expire-logs-days               = 1
            sync-binlog                    = 0
             
            read-only                      = 1
            binlog_format                  = ROW
            binlog-checksum                = crc32
            server-id                      = 5
            log-slave-updates                = false
            slave_parallel_mode             = optimistic
            slave_parallel_threads          = 6
            slave-skip-errors = 1032 1062 1054
             
             
            # CACHES AND LIMITS #
            sort_buffer_size               = 1G
            tmp-table-size                 = 1G
            max-heap-table-size            = 1G
            query-cache-type               = 0
            query_cache_limit              = 2M
            query_cache_wlock_invalidate   = ON
            max-connections                = 2048
            thread-cache-size              = 2048
            open-files-limit               = 65535
            table-definition-cache         = 8192
            table-open-cache               = 8192
             
            # INNODB #
            innodb-flush-method            = O_DIRECT
            innodb_log_file_size           = 4G
            innodb_log_files_in_group      = 4
            innodb_log_buffer_size         = 16M
            innodb-flush-log-at-trx-commit = 2
            innodb-file-per-table          = 1
            innodb-buffer-pool-size        = 120G 
            innodb_buffer_pool_instances   = 8
            innodb_write_io_threads        = 64
            innodb_read_io_threads         = 64
            innodb_use_native_aio          = 0
            innodb_thread_concurrency      = 0
             
            # LOGGING #
            log-error                      = /db/mysql/mysql-error.log
            log-queries-not-using-indexes  = 0
            slow-query-log                 = 1 
            slow-query-log-file            = /db/mysql/mysql-slow.log
             
            max_allowed_packet             = 200M
            event_scheduler                = ON
             
            optimizer-switch               = condition_pushdown_for_derived=off
            

            cyber_neo Zakharov Sergey added a comment - - edited This error occure when i try to select * from this view Full schema: Main view: CREATE OR REPLACE VIEW main_view AS SELECT * FROM table AS o WHERE o.coll BETWEEN report_from_order_creation_date() AND report_until_order_creation_date(); second_view: CREATE OR REPLACE VIEW second_view AS SELECT o.PK AS order_pk FROM main_view AS o third_view: CREATE OR REPLACE VIEW third_view AS SELECT o.PK AS order_pk FROM main_view AS o my.cnf the same on 10.1.15 and 1.3.7: [mysqld]   # GENERAL # user = mysql default-storage-engine = InnoDB socket = /db/mysql/mysql.sock pid-file = /db/mysql/mysql.pid open_files_limit = 102400   # MyISAM # key-buffer-size = 32M myisam-recover = FORCE,BACKUP   # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 innodb_lock_wait_timeout = 180 skip-name-resolve   # DATA STORAGE # datadir = /db/mysql/ tmpdir = /dev/shm   # BINARY LOGGING # log-bin = /db/mysql/mysql-bin expire-logs-days = 1 sync-binlog = 0   read-only = 1 binlog_format = ROW binlog-checksum = crc32 server-id = 5 log-slave-updates = false slave_parallel_mode = optimistic slave_parallel_threads = 6 slave-skip-errors = 1032 1062 1054     # CACHES AND LIMITS # sort_buffer_size = 1G tmp-table-size = 1G max-heap-table-size = 1G query-cache-type = 0 query_cache_limit = 2M query_cache_wlock_invalidate = ON max-connections = 2048 thread-cache-size = 2048 open-files-limit = 65535 table-definition-cache = 8192 table-open-cache = 8192   # INNODB # innodb-flush-method = O_DIRECT innodb_log_file_size = 4G innodb_log_files_in_group = 4 innodb_log_buffer_size = 16M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 120G innodb_buffer_pool_instances = 8 innodb_write_io_threads = 64 innodb_read_io_threads = 64 innodb_use_native_aio = 0 innodb_thread_concurrency = 0   # LOGGING # log-error = /db/mysql/mysql-error.log log-queries-not-using-indexes = 0 slow-query-log = 1 slow-query-log-file = /db/mysql/mysql-slow.log   max_allowed_packet = 200M event_scheduler = ON   optimizer-switch = condition_pushdown_for_derived=off
            alice Alice Sherepa added a comment -

            Please add the output from
            SHOW CREATE TABLE `table`;

            alice Alice Sherepa added a comment - Please add the output from SHOW CREATE TABLE `table`;

            CREATE TABLE `orders` (
            `hjmpTS` bigint(20) DEFAULT NULL,
            `createdTS` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
            `modifiedTS` datetime(6) DEFAULT NULL,
            `TypePkString` bigint(20) DEFAULT NULL,
            `OwnerPkString` bigint(20) DEFAULT NULL,
            `PK` bigint(20) NOT NULL DEFAULT 0,
            `calculatedflag` tinyint(1) DEFAULT NULL,
            `code` varchar(255) COLLATE utf8_bin DEFAULT NULL,
            `currencypk` bigint(20) DEFAULT NULL,
            `deliveryaddresspk` bigint(20) DEFAULT NULL,
            `deliverycost` decimal(30,8) DEFAULT NULL,
            `deliverymodepk` bigint(20) DEFAULT NULL,
            `deliverystatuspk` bigint(20) DEFAULT NULL,
            `globaldiscountvalues` text COLLATE utf8_bin DEFAULT NULL,
            `netflag` tinyint(1) DEFAULT NULL,
            `paymentaddresspk` bigint(20) DEFAULT NULL,
            `paymentcost` decimal(30,8) DEFAULT NULL,
            `paymentinfopk` bigint(20) DEFAULT NULL,
            `paymentmodepk` bigint(20) DEFAULT NULL,
            `paymentstatuspk` bigint(20) DEFAULT NULL,
            `statuspk` bigint(20) DEFAULT NULL,
            `p_exportstatus` bigint(20) DEFAULT NULL,
            `statusinfo` varchar(255) COLLATE utf8_bin DEFAULT NULL,
            `totalprice` decimal(30,8) DEFAULT NULL,
            `totaldiscounts` decimal(30,8) DEFAULT NULL,
            `totaltax` decimal(30,8) DEFAULT NULL,
            `totaltaxvalues` text COLLATE utf8_bin DEFAULT NULL,
            `userpk` bigint(20) DEFAULT NULL,
            `subtotal` decimal(30,8) DEFAULT NULL,
            `discountondelivery` tinyint(1) DEFAULT NULL,
            `discountonpayment` tinyint(1) DEFAULT NULL,
            `p_previousdeliverymode` bigint(20) DEFAULT NULL,
            `p_site` bigint(20) DEFAULT NULL,
            `p_store` bigint(20) DEFAULT NULL,
            `p_guid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
            `p_yoyosum` bigint(20) DEFAULT NULL,
            `p_transportcompany` bigint(20) DEFAULT NULL,
            `p_freevouchers` longblob DEFAULT NULL,
            `p_pointofservice` bigint(20) DEFAULT NULL,
            `p_deliveryzonelist` text COLLATE utf8_bin DEFAULT NULL,
            `p_tariffzonelist` text COLLATE utf8_bin DEFAULT NULL,
            `p_ordersubstatus` bigint(20) DEFAULT NULL,
            `p_deletelines` longblob DEFAULT NULL,
            `p_weight` decimal(30,8) DEFAULT NULL,
            `p_volume` decimal(30,8) DEFAULT NULL,
            `p_weightbulky` decimal(30,8) DEFAULT NULL,
            `p_volumebulky` decimal(30,8) DEFAULT NULL,
            `p_paywithgift` tinyint(1) DEFAULT NULL,
            `p_paywithyoyo` tinyint(1) DEFAULT NULL,
            `p_deletedfreegoodsmap` longblob DEFAULT NULL,
            `p_versionid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
            `p_originalversion` bigint(20) DEFAULT NULL,
            `p_fraudulent` tinyint(1) DEFAULT NULL,
            `p_potentiallyfraudulent` tinyint(1) DEFAULT NULL,
            `p_salesapplication` bigint(20) DEFAULT NULL,
            `p_language` bigint(20) DEFAULT NULL,
            `p_deliverydate` datetime(6) DEFAULT NULL,
            `p_deliverytime` varchar(255) COLLATE utf8_bin DEFAULT NULL,
            `p_shippingdate` datetime(6) DEFAULT NULL,
            `p_informingmethod` bigint(20) DEFAULT NULL,
            `p_requesteddeliverydate` datetime(6) DEFAULT NULL,
            `p_sendsmswhenstatuschanged` tinyint(1) DEFAULT NULL,
            `p_sapapprovedate` datetime(6) DEFAULT NULL,
            `p_sapcalculatedate` datetime(6) DEFAULT NULL,
            `p_sapreservedate` datetime(6) DEFAULT NULL,
            `p_ordertype` bigint(20) DEFAULT NULL,
            `p_promotionsdate` datetime(6) DEFAULT NULL,
            `p_operator` bigint(20) DEFAULT NULL,
            `p_buyerpaymentresulterror` tinyint(1) DEFAULT NULL,
            `p_parentorder` bigint(20) DEFAULT NULL,
            `p_cslockdate` datetime(6) DEFAULT NULL,
            `p_cslockoperator` bigint(20) DEFAULT NULL,
            `p_ismigrated` tinyint(1) DEFAULT NULL,
            `p_partlypaidsum` decimal(30,8) DEFAULT NULL,
            `p_deliveryinterval` bigint(20) DEFAULT NULL,
            `p_refunddate` datetime(6) DEFAULT NULL,
            `p_yoyoawardedpoints` decimal(30,8) DEFAULT NULL,
            `p_yoyodebitedpoints` decimal(30,8) DEFAULT NULL,
            `p_needrefundyoyo` tinyint(1) DEFAULT NULL,
            `p_giftusedforyoyoaddpoints` decimal(30,8) DEFAULT NULL,
            `p_processedtime` datetime(6) DEFAULT NULL,
            `aCLTS` bigint(20) DEFAULT 0,
            `propTS` bigint(20) DEFAULT 0,
            `p_fulfilmentstatus` bigint(20) DEFAULT NULL,
            `p_notes` varchar(255) COLLATE utf8_bin DEFAULT NULL,
            `p_computerid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
            `p_ordercreator` bigint(20) DEFAULT NULL,
            `p_fiascode` varchar(255) COLLATE utf8_bin DEFAULT NULL,
            `originaldeliverycost` decimal(30,8) DEFAULT NULL,
            `p_ordernotificationstatus` bigint(20) DEFAULT NULL,
            `p_chequeassigned` tinyint(1) DEFAULT NULL,
            `refundeddeliverycost` decimal(30,8) DEFAULT NULL,
            `p_remindsmscount` int(11) DEFAULT NULL,
            `p_checkoutsuccess` tinyint(1) DEFAULT NULL,
            `p_modified` tinyint(1) DEFAULT NULL,
            `p_dateofstatusupdate` datetime(6) DEFAULT NULL,
            `p_instorecompleted` tinyint(1) DEFAULT NULL,
            PRIMARY KEY (`PK`,`createdTS`),
            KEY `OrderCode_45` (`code`),
            KEY `OrderUser_45` (`userpk`),
            KEY `guidIndex_45` (`p_guid`),
            KEY `currencyPK_idx_45` (`currencypk`),
            KEY `paymentAddressPK_idx_45` (`paymentaddresspk`),
            KEY `ars_report_idx_orders_create` (`createdTS`) USING BTREE,
            KEY `ars_report_idx_orders_modify` (`modifiedTS`) USING BTREE,
            KEY `ars_report_idx_orders_deliveryaddresspk` (`deliveryaddresspk`) USING BTREE,
            KEY `ars_report_idx_orders_delivery_dates` (`p_deliverydate`) USING BTREE,
            KEY `ars_report_idx_orders_req_delivery_dates` (`p_requesteddeliverydate`) USING BTREE,
            KEY `ars_report_idx_orders_deliverymodepk` (`deliverymodepk`) USING BTREE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
            PARTITION BY RANGE COLUMNS(`createdTS`)
            (PARTITION `p0` VALUES LESS THAN ('2017-03-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p1` VALUES LESS THAN ('2017-04-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p2` VALUES LESS THAN ('2017-05-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p3` VALUES LESS THAN ('2017-06-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p4` VALUES LESS THAN ('2017-07-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p5` VALUES LESS THAN ('2017-08-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p6` VALUES LESS THAN ('2017-09-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p7` VALUES LESS THAN ('2017-10-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p8` VALUES LESS THAN ('2017-11-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p9` VALUES LESS THAN ('2017-12-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p10` VALUES LESS THAN ('2018-01-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p11` VALUES LESS THAN ('2018-02-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p12` VALUES LESS THAN ('2018-03-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p13` VALUES LESS THAN ('2018-04-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p14` VALUES LESS THAN ('2018-05-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p15` VALUES LESS THAN ('2018-06-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p16` VALUES LESS THAN ('2018-07-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p17` VALUES LESS THAN ('2018-08-01 00:00:00.000000') ENGINE = InnoDB,
            PARTITION `p18` VALUES LESS THAN ('2018-09-01 00:00:00.000000') ENGINE = InnoDB)

            cyber_neo Zakharov Sergey added a comment - CREATE TABLE `orders` ( `hjmpTS` bigint(20) DEFAULT NULL, `createdTS` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000', `modifiedTS` datetime(6) DEFAULT NULL, `TypePkString` bigint(20) DEFAULT NULL, `OwnerPkString` bigint(20) DEFAULT NULL, `PK` bigint(20) NOT NULL DEFAULT 0, `calculatedflag` tinyint(1) DEFAULT NULL, `code` varchar(255) COLLATE utf8_bin DEFAULT NULL, `currencypk` bigint(20) DEFAULT NULL, `deliveryaddresspk` bigint(20) DEFAULT NULL, `deliverycost` decimal(30,8) DEFAULT NULL, `deliverymodepk` bigint(20) DEFAULT NULL, `deliverystatuspk` bigint(20) DEFAULT NULL, `globaldiscountvalues` text COLLATE utf8_bin DEFAULT NULL, `netflag` tinyint(1) DEFAULT NULL, `paymentaddresspk` bigint(20) DEFAULT NULL, `paymentcost` decimal(30,8) DEFAULT NULL, `paymentinfopk` bigint(20) DEFAULT NULL, `paymentmodepk` bigint(20) DEFAULT NULL, `paymentstatuspk` bigint(20) DEFAULT NULL, `statuspk` bigint(20) DEFAULT NULL, `p_exportstatus` bigint(20) DEFAULT NULL, `statusinfo` varchar(255) COLLATE utf8_bin DEFAULT NULL, `totalprice` decimal(30,8) DEFAULT NULL, `totaldiscounts` decimal(30,8) DEFAULT NULL, `totaltax` decimal(30,8) DEFAULT NULL, `totaltaxvalues` text COLLATE utf8_bin DEFAULT NULL, `userpk` bigint(20) DEFAULT NULL, `subtotal` decimal(30,8) DEFAULT NULL, `discountondelivery` tinyint(1) DEFAULT NULL, `discountonpayment` tinyint(1) DEFAULT NULL, `p_previousdeliverymode` bigint(20) DEFAULT NULL, `p_site` bigint(20) DEFAULT NULL, `p_store` bigint(20) DEFAULT NULL, `p_guid` varchar(255) COLLATE utf8_bin DEFAULT NULL, `p_yoyosum` bigint(20) DEFAULT NULL, `p_transportcompany` bigint(20) DEFAULT NULL, `p_freevouchers` longblob DEFAULT NULL, `p_pointofservice` bigint(20) DEFAULT NULL, `p_deliveryzonelist` text COLLATE utf8_bin DEFAULT NULL, `p_tariffzonelist` text COLLATE utf8_bin DEFAULT NULL, `p_ordersubstatus` bigint(20) DEFAULT NULL, `p_deletelines` longblob DEFAULT NULL, `p_weight` decimal(30,8) DEFAULT NULL, `p_volume` decimal(30,8) DEFAULT NULL, `p_weightbulky` decimal(30,8) DEFAULT NULL, `p_volumebulky` decimal(30,8) DEFAULT NULL, `p_paywithgift` tinyint(1) DEFAULT NULL, `p_paywithyoyo` tinyint(1) DEFAULT NULL, `p_deletedfreegoodsmap` longblob DEFAULT NULL, `p_versionid` varchar(255) COLLATE utf8_bin DEFAULT NULL, `p_originalversion` bigint(20) DEFAULT NULL, `p_fraudulent` tinyint(1) DEFAULT NULL, `p_potentiallyfraudulent` tinyint(1) DEFAULT NULL, `p_salesapplication` bigint(20) DEFAULT NULL, `p_language` bigint(20) DEFAULT NULL, `p_deliverydate` datetime(6) DEFAULT NULL, `p_deliverytime` varchar(255) COLLATE utf8_bin DEFAULT NULL, `p_shippingdate` datetime(6) DEFAULT NULL, `p_informingmethod` bigint(20) DEFAULT NULL, `p_requesteddeliverydate` datetime(6) DEFAULT NULL, `p_sendsmswhenstatuschanged` tinyint(1) DEFAULT NULL, `p_sapapprovedate` datetime(6) DEFAULT NULL, `p_sapcalculatedate` datetime(6) DEFAULT NULL, `p_sapreservedate` datetime(6) DEFAULT NULL, `p_ordertype` bigint(20) DEFAULT NULL, `p_promotionsdate` datetime(6) DEFAULT NULL, `p_operator` bigint(20) DEFAULT NULL, `p_buyerpaymentresulterror` tinyint(1) DEFAULT NULL, `p_parentorder` bigint(20) DEFAULT NULL, `p_cslockdate` datetime(6) DEFAULT NULL, `p_cslockoperator` bigint(20) DEFAULT NULL, `p_ismigrated` tinyint(1) DEFAULT NULL, `p_partlypaidsum` decimal(30,8) DEFAULT NULL, `p_deliveryinterval` bigint(20) DEFAULT NULL, `p_refunddate` datetime(6) DEFAULT NULL, `p_yoyoawardedpoints` decimal(30,8) DEFAULT NULL, `p_yoyodebitedpoints` decimal(30,8) DEFAULT NULL, `p_needrefundyoyo` tinyint(1) DEFAULT NULL, `p_giftusedforyoyoaddpoints` decimal(30,8) DEFAULT NULL, `p_processedtime` datetime(6) DEFAULT NULL, `aCLTS` bigint(20) DEFAULT 0, `propTS` bigint(20) DEFAULT 0, `p_fulfilmentstatus` bigint(20) DEFAULT NULL, `p_notes` varchar(255) COLLATE utf8_bin DEFAULT NULL, `p_computerid` varchar(255) COLLATE utf8_bin DEFAULT NULL, `p_ordercreator` bigint(20) DEFAULT NULL, `p_fiascode` varchar(255) COLLATE utf8_bin DEFAULT NULL, `originaldeliverycost` decimal(30,8) DEFAULT NULL, `p_ordernotificationstatus` bigint(20) DEFAULT NULL, `p_chequeassigned` tinyint(1) DEFAULT NULL, `refundeddeliverycost` decimal(30,8) DEFAULT NULL, `p_remindsmscount` int(11) DEFAULT NULL, `p_checkoutsuccess` tinyint(1) DEFAULT NULL, `p_modified` tinyint(1) DEFAULT NULL, `p_dateofstatusupdate` datetime(6) DEFAULT NULL, `p_instorecompleted` tinyint(1) DEFAULT NULL, PRIMARY KEY (`PK`,`createdTS`), KEY `OrderCode_45` (`code`), KEY `OrderUser_45` (`userpk`), KEY `guidIndex_45` (`p_guid`), KEY `currencyPK_idx_45` (`currencypk`), KEY `paymentAddressPK_idx_45` (`paymentaddresspk`), KEY `ars_report_idx_orders_create` (`createdTS`) USING BTREE, KEY `ars_report_idx_orders_modify` (`modifiedTS`) USING BTREE, KEY `ars_report_idx_orders_deliveryaddresspk` (`deliveryaddresspk`) USING BTREE, KEY `ars_report_idx_orders_delivery_dates` (`p_deliverydate`) USING BTREE, KEY `ars_report_idx_orders_req_delivery_dates` (`p_requesteddeliverydate`) USING BTREE, KEY `ars_report_idx_orders_deliverymodepk` (`deliverymodepk`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin PARTITION BY RANGE COLUMNS(`createdTS`) (PARTITION `p0` VALUES LESS THAN ('2017-03-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p1` VALUES LESS THAN ('2017-04-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p2` VALUES LESS THAN ('2017-05-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p3` VALUES LESS THAN ('2017-06-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p4` VALUES LESS THAN ('2017-07-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p5` VALUES LESS THAN ('2017-08-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p6` VALUES LESS THAN ('2017-09-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p7` VALUES LESS THAN ('2017-10-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p8` VALUES LESS THAN ('2017-11-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p9` VALUES LESS THAN ('2017-12-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p10` VALUES LESS THAN ('2018-01-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p11` VALUES LESS THAN ('2018-02-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p12` VALUES LESS THAN ('2018-03-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p13` VALUES LESS THAN ('2018-04-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p14` VALUES LESS THAN ('2018-05-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p15` VALUES LESS THAN ('2018-06-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p16` VALUES LESS THAN ('2018-07-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p17` VALUES LESS THAN ('2018-08-01 00:00:00.000000') ENGINE = InnoDB, PARTITION `p18` VALUES LESS THAN ('2018-09-01 00:00:00.000000') ENGINE = InnoDB)
            alice Alice Sherepa added a comment -

            am I right that o.coll is orders.createdTS? And second_view and third_view are the same?
            Please provide also information about report_from_order_creation_date() and report_until_order_creation_date().
            Thanks!

            alice Alice Sherepa added a comment - am I right that o.coll is orders.createdTS? And second_view and third_view are the same? Please provide also information about report_from_order_creation_date() and report_until_order_creation_date(). Thanks!

            _am I right that o.coll is orders.createdTS? _
            Yes, strange change after post to JIRA

            And second_view and third_view are the same?
            Yes, i change it to become simple

            CREATE FUNCTION report_from_order_creation_date()
            RETURNS DATETIME DETERMINISTIC
            RETURN @from_order_creation_date;

            CREATE FUNCTION report_until_order_creation_date()
            RETURNS DATETIME DETERMINISTIC
            RETURN @until_order_creation_date;

            SET @from_order_creation_date = DATE_SUB(NOW(), INTERVAL 1 HOUR);
            SET @until_order_creation_date = NOW();

            cyber_neo Zakharov Sergey added a comment - _am I right that o.coll is orders.createdTS? _ Yes, strange change after post to JIRA And second_view and third_view are the same? Yes, i change it to become simple CREATE FUNCTION report_from_order_creation_date() RETURNS DATETIME DETERMINISTIC RETURN @from_order_creation_date; CREATE FUNCTION report_until_order_creation_date() RETURNS DATETIME DETERMINISTIC RETURN @until_order_creation_date; SET @from_order_creation_date = DATE_SUB(NOW(), INTERVAL 1 HOUR); SET @until_order_creation_date = NOW();
            alice Alice Sherepa added a comment -

            I can not reproduce it as for now. I get this plan (running as 16420.sql)

            MariaDB [test]> explain extended select * from first_view;
            +------+-------------+-------+-------+--------------------------------------+------------------------------+---------+-----------+------+----------+--------------------------+
            | id   | select_type | table | type  | possible_keys                        | key                          | key_len | ref       | rows | filtered | Extra                    |
            +------+-------------+-------+-------+--------------------------------------+------------------------------+---------+-----------+------+----------+--------------------------+
            |    1 | SIMPLE      | o     | index | ars_report_idx_orders_create         | ars_report_idx_orders_create | 8       | NULL      |  300 |   100.00 | Using where; Using index |
            |    1 | SIMPLE      | o     | ref   | PRIMARY,ars_report_idx_orders_create | PRIMARY                      | 8       | test.o.PK |   50 |   100.00 | Using where; Using index |
            +------+-------------+-------+-------+--------------------------------------+------------------------------+---------+-----------+------+----------+--------------------------+
            2 rows in set, 1 warning (0.001 sec)
             
            Note (Code 1003): select `o`.`PK` AS `order_pk` from `test`.`orders` `o` left join (`test`.`orders` `o`) on(`o`.`PK` = `o`.`PK` and `o`.`createdTS` between <cache>(`report_from_order_creation_date`()) and <cache>(`report_until_order_creation_date`())) where `o`.`createdTS` between <cache>(`report_from_order_creation_date`()) and <cache>(`report_until_order_creation_date`())
             
            MariaDB [test]> select version();
            +----------------+
            | version()      |
            +----------------+
            | 10.3.7-MariaDB |
            +----------------+
            1 row in set (0.000 sec)
            

            alice Alice Sherepa added a comment - I can not reproduce it as for now. I get this plan (running as 16420.sql) MariaDB [test]> explain extended select * from first_view; +------+-------------+-------+-------+--------------------------------------+------------------------------+---------+-----------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+-------+--------------------------------------+------------------------------+---------+-----------+------+----------+--------------------------+ | 1 | SIMPLE | o | index | ars_report_idx_orders_create | ars_report_idx_orders_create | 8 | NULL | 300 | 100.00 | Using where; Using index | | 1 | SIMPLE | o | ref | PRIMARY,ars_report_idx_orders_create | PRIMARY | 8 | test.o.PK | 50 | 100.00 | Using where; Using index | +------+-------------+-------+-------+--------------------------------------+------------------------------+---------+-----------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.001 sec)   Note (Code 1003): select `o`.`PK` AS `order_pk` from `test`.`orders` `o` left join (`test`.`orders` `o`) on(`o`.`PK` = `o`.`PK` and `o`.`createdTS` between <cache>(`report_from_order_creation_date`()) and <cache>(`report_until_order_creation_date`())) where `o`.`createdTS` between <cache>(`report_from_order_creation_date`()) and <cache>(`report_until_order_creation_date`())   MariaDB [test]> select version(); +----------------+ | version() | +----------------+ | 10.3.7-MariaDB | +----------------+ 1 row in set (0.000 sec)

            I found part of view where error occure:
            third_view should look like this:

            CREATE OR REPLACE VIEW third_view AS
            -> SELECT
            -> o.PK AS order_pk
            -> FROM main_view AS o
            -> GROUP BY o.PK
            -> ORDER BY NULL;

            If i delete GROUP BY o.PK from this view all work fine.

            cyber_neo Zakharov Sergey added a comment - I found part of view where error occure: third_view should look like this: CREATE OR REPLACE VIEW third_view AS -> SELECT -> o.PK AS order_pk -> FROM main_view AS o -> GROUP BY o.PK -> ORDER BY NULL; If i delete GROUP BY o.PK from this view all work fine.
            alice Alice Sherepa added a comment - - edited

            Thanks for the report! Reproducible on Maridb 10.3
            As a temporary workaround please use SET optimizer_switch='derived_merge=off';

            testcase:

             
            CREATE TABLE t1 (dt int , id int , PRIMARY KEY (id,dt));
             
            CREATE VIEW v1 AS
            SELECT o.id AS order_pk
            FROM (SELECT * FROM t1) AS o ;
             
            CREATE  VIEW v2 AS
            SELECT o.id AS order_pk
            FROM (SELECT * FROM t1) AS o GROUP BY o.id;
             
            explain extended 
            SELECT * FROM v1 
            LEFT JOIN v2 
            ON v1.order_pk=v2.order_pk;
            

            mysqltest: At line 12: query 'explain extended 
            SELECT * FROM v1 
            LEFT JOIN v2 
            ON v1.order_pk=v2.order_pk' failed: 1054: Unknown column 'v2.order_pk' in 'on clause'
            

            alice Alice Sherepa added a comment - - edited Thanks for the report! Reproducible on Maridb 10.3 As a temporary workaround please use SET optimizer_switch='derived_merge=off'; testcase:   CREATE TABLE t1 (dt int , id int , PRIMARY KEY (id,dt));   CREATE VIEW v1 AS SELECT o.id AS order_pk FROM (SELECT * FROM t1) AS o ;   CREATE VIEW v2 AS SELECT o.id AS order_pk FROM (SELECT * FROM t1) AS o GROUP BY o.id;   explain extended SELECT * FROM v1 LEFT JOIN v2 ON v1.order_pk=v2.order_pk; mysqltest: At line 12: query 'explain extended SELECT * FROM v1 LEFT JOIN v2 ON v1.order_pk=v2.order_pk' failed: 1054: Unknown column 'v2.order_pk' in 'on clause'

            Thank you, but SET optimizer_switch='derived_merge=off'; i get same error.

            cyber_neo Zakharov Sergey added a comment - Thank you, but SET optimizer_switch='derived_merge=off'; i get same error.
            alice Alice Sherepa added a comment -

            Strange, SET @@optimizer_switch='derived_merge=off'; worked for me.

            simplified test, without views.

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

            alice Alice Sherepa added a comment - Strange, SET @@optimizer_switch='derived_merge=off'; worked for me. simplified test, without views. CREATE TABLE t1 (dt int , id int , PRIMARY KEY (id,dt)); INSERT INTO t1 VALUES (1,1),(2,2);   SELECT * FROM ( SELECT o.id AS order_pk FROM ( SELECT * FROM t1) AS o) v1 LEFT JOIN ( SELECT o.id AS order_pk FROM ( SELECT * FROM t1) AS o GROUP BY o.id) v2 ON v1.order_pk=v2.order_pk;
            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.