Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.7, 10.3(EOL)
-
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
- 16420.sql
- 8 kB
- Alice Sherepa
Issue Links
- is duplicated by
-
MDEV-16472 column aliases no longer available in joins
-
- Closed
-
Activity
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
|
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)
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();
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.
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.
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; |
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, 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;
|
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.