[MDEV-16420] View stop working after upgrade from 10.1.15 to 10.3.7 Created: 2018-06-07  Updated: 2018-07-21  Resolved: 2018-07-21

Status: Closed
Project: MariaDB Server
Component/s: Views
Affects Version/s: 10.3.7, 10.3
Fix Version/s: 10.3.8

Type: Bug Priority: Major
Reporter: Zakharov Sergey Assignee: Igor Babaev
Resolution: Fixed Votes: 1
Labels: None

Attachments: File 16420.sql    
Issue Links:
Duplicate
is duplicated by MDEV-16472 column aliases no longer available in... Closed

 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.



 Comments   
Comment by Elena Stepanova [ 2018-06-07 ]

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.

Comment by Zakharov Sergey [ 2018-06-07 ]

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

Comment by Alice Sherepa [ 2018-06-08 ]

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

Comment by Zakharov Sergey [ 2018-06-08 ]

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)

Comment by Alice Sherepa [ 2018-06-08 ]

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!

Comment by Zakharov Sergey [ 2018-06-08 ]

_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();

Comment by Alice Sherepa [ 2018-06-08 ]

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)

Comment by Zakharov Sergey [ 2018-06-08 ]

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.

Comment by Alice Sherepa [ 2018-06-11 ]

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'

Comment by Zakharov Sergey [ 2018-06-12 ]

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

Comment by Alice Sherepa [ 2018-06-12 ]

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;

Comment by Zakharov Sergey [ 2018-06-12 ]

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.

Comment by Alice Sherepa [ 2018-06-13 ]

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'

Comment by Igor Babaev [ 2018-06-14 ]

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;

Comment by Oleksandr Byelkin [ 2018-06-15 ]

OK to push

Comment by Igor Babaev [ 2018-07-21 ]

A fix for this bug was pushed into 10.3 tree.

Generated at Thu Feb 08 08:28:47 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.