Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.45, 10.2.32, 10.3.23, 10.4.13, 10.5.4, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
-
Platforms:
- Windows 10 Pro - version 2004
- CloudLinux 7.8
Description
Use this schema as example:
CREATE TABLE `products` ( |
`product_id` INT, |
`code` VARCHAR(24), |
`price` DECIMAL(10,2), |
`avgOrderQuantity` INT |
);
|
|
CREATE TABLE `orders` ( |
`order_id` INT, |
`date` DATETIME DEFAULT CURRENT_TIMESTAMP, |
`state` ENUM('pending','confirmed','cancelled','completed') |
);
|
|
CREATE TABLE `order_products` ( |
`order_id` INT, |
`product_id` INT, |
`quantity` INT, |
`price` DECIMAL(10,2) |
);
|
|
CREATE TABLE `invoices` ( |
`invoice_id` INT, |
`order_id` INT, |
`date` DATETIME DEFAULT CURRENT_TIMESTAMP |
);
|
|
CREATE TABLE `invoice_products` ( |
`invoice_id` INT, |
`product_id` INT, |
`quantity` INT, |
`price` DECIMAL(10,2) |
);
|
|
--
|
|
INSERT INTO `products` (`product_id`, `code`, `price`, `avgOrderQuantity`) VALUES |
(1, 'COD1', 12, 3), (2, 'COD2', 11, 1); |
|
INSERT INTO `orders` (`order_id`, `state`) VALUES |
(1, 'confirmed'), |
(2, 'confirmed'), |
(3, 'pending'); |
|
INSERT INTO `order_products` (`order_id`, `product_id`, `quantity`, `price`) VALUES |
(1, 1, 30, 10.2),
|
(2, 1, 10, 12), (2, 2, 2, 11),
|
(3, 1, 10, 12);
|
|
INSERT INTO `invoices` (`invoice_id`, `order_id`) VALUES |
(1, 1),
|
(2, 2),
|
(3, 1);
|
|
INSERT INTO `invoice_products` (`invoice_id`, `product_id`, `quantity`, `price`) VALUES |
(1, 1, 10, 10.2),
|
(2, 1, 20, 12), (2, 2, 2, 11),
|
(3, 1, 1, 10.2);
|
If we run this query:
SELECT |
P.code,
|
O.state,
|
OP.quantity AS ordered, |
IFNULL((
|
SELECT SUM(IP.quantity) |
FROM invoices AS I |
LEFT JOIN invoice_products AS IP |
ON IP.invoice_id = I.invoice_id |
WHERE I.order_id = O.order_id |
AND IP.product_id = OP.product_id |
), 0) AS invoiced, |
IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold, |
P.avgOrderQuantity AS avgOrdQty, |
IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold |
FROM order_products AS OP |
INNER JOIN orders AS O |
ON O.order_id = OP.order_id |
INNER JOIN products AS P |
ON P.product_id = OP.product_id |
WHERE O.state != 'pending'; |
It returns:
code | state | ordered | invoiced | sold | avgOrdQty | avgSold |
---|---|---|---|---|---|---|
COD1 | confirmed | 30 | 11 | 30 | 3 | 3 |
COD1 | confirmed | 10 | 20 | 10 | 3 | 3 |
COD2 | confirmed | 2 | 2 | 2 | 1 | 1 |
Now If move this into a derived table to get the sumarized `avgSold` grouped by `code` like this
SELECT T.code, SUM(T.avgSold) |
FROM ( |
SELECT |
P.code,
|
O.state,
|
OP.quantity AS ordered, |
IFNULL((
|
SELECT SUM(IP.quantity) |
FROM invoices AS I |
LEFT JOIN invoice_products AS IP |
ON IP.invoice_id = I.invoice_id |
WHERE I.order_id = O.order_id |
AND IP.product_id = OP.product_id |
), 0) AS invoiced, |
IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold, |
P.avgOrderQuantity AS avgOrdQty, |
IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold |
FROM order_products AS OP |
INNER JOIN orders AS O |
ON O.order_id = OP.order_id |
INNER JOIN products AS P |
ON P.product_id = OP.product_id |
WHERE O.state != 'pending' |
) AS T |
GROUP BY T.code |
It returns:
code | SUM(T.avgSold) |
---|---|
COD1 | null |
COD2 | null |
This is NOT the expected result.
If I add a numeric column (YES it MUST be a numeric column) like this:
SELECT T.code, T.sold, SUM(T.avgSold) |
FROM ( |
SELECT |
P.code,
|
O.state,
|
OP.quantity AS ordered, |
IFNULL((
|
SELECT SUM(IP.quantity) |
FROM invoices AS I |
LEFT JOIN invoice_products AS IP |
ON IP.invoice_id = I.invoice_id |
WHERE I.order_id = O.order_id |
AND IP.product_id = OP.product_id |
), 0) AS invoiced, |
IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold, |
P.avgOrderQuantity AS avgOrdQty, |
IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold |
FROM order_products AS OP |
INNER JOIN orders AS O |
ON O.order_id = OP.order_id |
INNER JOIN products AS P |
ON P.product_id = OP.product_id |
WHERE O.state != 'pending' |
) AS T |
GROUP BY T.code |
It returns:
code | sold | SUM(T.avgSold) |
---|---|---|
COD1 | 30 | 6 |
COD2 | 2 | 1 |
NOW this is the expected result
Attachments
Issue Links
- relates to
-
MDEV-19680 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index) || (!(ptr >= table->record[0] && ptr < table->record[0] + table->s->reclength)))' or alike failed upon SELECT with mix of functions from simple view
-
- Stalled
-
-
MDEV-19984 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index) || (!(ptr >= table->record[0] && ptr < table->record[0] + table->s->reclength)))' failed in JOIN_CACHE::generate_full_extensions
-
- Confirmed
-
Thanks a lot!
I repeated as described. On debug versions on 10.0-10.5 test fails with the assertion
`!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))'
`!table || (!table->read_set || bitmap_is_set(table->read_set, field_index) || (!(ptr >= table->record[0] && ptr < table->record[0] + table->s->reclength)))'
`marked_for_read()'
5.5 returns correct results.
10.0 c2a7dffc573f311029ed4696f
Version: '10.0.40-4-MariaDB-debug'
mysqld: /10.0/sql/field.cc:3798: virtual longlong Field_long::val_int(): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed.
200727 9:21:02 [ERROR] mysqld got signal 6 ;
Server version: 10.0.40-4-MariaDB-debug
linux/raise.c:51(__GI_raise)[0x7f54491a67bb]
stdlib/abort.c:81(__GI_abort)[0x7f5449191535]
intl/loadmsgcat.c:1177(_nl_load_domain)[0x7f544919140f]
/lib/x86_64-linux-gnu/libc.so.6(+0x30102)[0x7f544919f102]
sql/field.cc:3801(Field_long::val_int())[0x556f7e2a44ec]
sql/item.cc:2607(Item_field::val_int())[0x556f7e2df94e]
sql/item.cc:7446(Item_direct_ref::val_int())[0x556f7e2ec313]
sql/item.cc:7222(Item_ref::val_int_result())[0x556f7e2eb6e0]
sql/item.cc:8844(Item_cache_int::cache_value())[0x556f7e2f00bf]
sql/item_subselect.cc:1125(Item_singlerow_subselect::store(unsigned int, Item*))[0x556f7e362628]
sql/sql_class.cc:3069(select_singlerow_subselect::send_data(List<Item>&))[0x556f7e0a03a5]
sql/sql_select.cc:2496(JOIN::exec_inner())[0x556f7e113426]
sql/sql_select.cc:2413(JOIN::exec())[0x556f7e112e40]
sql/item_subselect.cc:3774(subselect_single_select_engine::exec())[0x556f7e36a990]
sql/item_subselect.cc:678(Item_subselect::exec())[0x556f7e36142a]
sql/item_subselect.cc:1298(Item_singlerow_subselect::val_decimal(my_decimal*))[0x556f7e362eb5]
sql/item_cmpfunc.cc:2875(Item_func_if::decimal_op(my_decimal*))[0x556f7e3022a0]
sql/item_func.h:453(Item_func_hybrid_result_type::decimal_op_with_null_check(my_decimal*))[0x556f7e33cf2a]
sql/item_func.cc:1040(Item_func_hybrid_result_type::val_decimal(my_decimal*))[0x556f7e32926a]
sql/item.cc:7462(Item_direct_ref::val_decimal(my_decimal*))[0x556f7e2ec3e2]
sql/item.cc:7247(Item_ref::val_decimal_result(my_decimal*))[0x556f7e2eb856]
sql/item.cc:9081(Item_cache_decimal::cache_value())[0x556f7e2f0ccc]
sql/item_subselect.cc:1125(Item_singlerow_subselect::store(unsigned int, Item*))[0x556f7e362628]
sql/sql_class.cc:3069(select_singlerow_subselect::send_data(List<Item>&))[0x556f7e0a03a5]
sql/sql_select.cc:2496(JOIN::exec_inner())[0x556f7e113426]
sql/sql_select.cc:2413(JOIN::exec())[0x556f7e112e40]
sql/item_subselect.cc:3774(subselect_single_select_engine::exec())[0x556f7e36a990]
sql/item_subselect.cc:678(Item_subselect::exec())[0x556f7e36142a]
sql/item_subselect.cc:1298(Item_singlerow_subselect::val_decimal(my_decimal*))[0x556f7e362eb5]
sql/item_cmpfunc.cc:1125(Arg_comparator::compare_decimal())[0x556f7e2fc0d5]
sql/item_cmpfunc.h:80(Arg_comparator::compare())[0x556f7e30d7e8]
sql/item_cmpfunc.cc:2052(Item_func_gt::val_int())[0x556f7e2fedc7]
sql/item.cc:213(Item::val_bool())[0x556f7e2d93c0]
sql/item_cmpfunc.cc:2874(Item_func_if::decimal_op(my_decimal*))[0x556f7e302258]
sql/item_func.h:453(Item_func_hybrid_result_type::decimal_op_with_null_check(my_decimal*))[0x556f7e33cf2a]
sql/item_func.cc:1040(Item_func_hybrid_result_type::val_decimal(my_decimal*))[0x556f7e32926a]
sql/item.cc:7462(Item_direct_ref::val_decimal(my_decimal*))[0x556f7e2ec3e2]
sql/item.h:3849(Item_direct_view_ref::val_decimal(my_decimal*))[0x556f7e2f6210]
sql/item_sum.cc:2336(Item_sum_sum::reset_field())[0x556f7e378aa4]
sql/sql_select.cc:22858(init_tmptable_sum_functions(Item_sum**))[0x556f7e145958]
sql/sql_select.cc:19507(end_update(JOIN*, st_join_table*, bool))[0x556f7e13d82a]
sql/sql_join_cache.cc:2409(JOIN_CACHE::generate_full_extensions(unsigned char*))[0x556f7e22d76c]
sql/sql_join_cache.cc:2301(JOIN_CACHE::join_matching_records(bool))[0x556f7e22d4fe]
sql/sql_join_cache.cc:2097(JOIN_CACHE::join_records(bool))[0x556f7e22ceb9]
sql/sql_join_cache.cc:2143(JOIN_CACHE::join_records(bool))[0x556f7e22d059]
sql/sql_select.cc:17850(sub_select_cache(JOIN*, st_join_table*, bool))[0x556f7e139944]
sql/sql_select.cc:18020(sub_select(JOIN*, st_join_table*, bool))[0x556f7e139b50]
sql/sql_select.cc:17731(do_select(JOIN*, List<Item>*, TABLE*, Procedure*))[0x556f7e139639]
sql/sql_select.cc:2665(JOIN::exec_inner())[0x556f7e113d73]
sql/sql_select.cc:2413(JOIN::exec())[0x556f7e112e40]
sql/sql_select.cc:3352(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x556f7e1161bd]
sql/sql_select.cc:366(handle_select(THD*, LEX*, select_result*, unsigned long))[0x556f7e10c2ac]
sql/sql_parse.cc:5312(execute_sqlcom_select(THD*, TABLE_LIST*))[0x556f7e0e0038]
sql/sql_parse.cc:2554(mysql_execute_command(THD*))[0x556f7e0d838d]
sql/sql_parse.cc:6648(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x556f7e0e2e6b]
sql/sql_parse.cc:1303(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x556f7e0d5635]
sql/sql_parse.cc:1003(do_command(THD*))[0x556f7e0d495f]
sql/sql_connect.cc:1377(do_handle_one_connection(THD*))[0x556f7e1f7358]
sql/sql_connect.cc:1293(handle_one_connection)[0x556f7e1f70b6]
perfschema/pfs.cc:1863(pfs_spawn_thread)[0x556f7e5c826f]
nptl/pthread_create.c:487(start_thread)[0x7f5449b7bfa3]
x86_64/clone.S:97(clone)[0x7f54492684cf]
Query (0x7f54409f0088): SELECT T.code, SUM(T.avgSold) FROM ( SELECT P.code, O.state, OP.quantity AS ordered, IFNULL(( SELECT SUM(IP.quantity) FROM invoices AS I LEFT JOIN invoice_products AS IP ON IP.invoice_id = I.invoice_id WHERE I.order_id = O.order_id AND IP.product_id = OP.product_id ), 0) AS invoiced, IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold, P.avgOrderQuantity AS avgOrdQty, IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold FROM order_products AS OP INNER JOIN orders AS O ON O.order_id = OP.order_id INNER JOIN products AS P ON P.product_id = OP.product_id WHERE O.state != 'pending' ) AS T GROUP BY T.code