[MDEV-19383] Query crashes server Created: 2019-05-02  Updated: 2023-10-12  Resolved: 2023-10-12

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.3.14, 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Aria Stewart Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: None
Environment:

3.10.0-693.5.2.el7.x86_64

Issue Links:
Relates
relates to MDEV-18502 Server crash in find_field_in_tables ... Closed

 Description    

May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: 190502 14:28:29 [ERROR] mysqld got signal 11 ;
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: This could be because you hit a bug. It is also possible that this binary
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: or one of the libraries it was linked against is corrupt, improperly built,
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: or misconfigured. This error can also be caused by malfunctioning hardware.
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: We will try our best to scrape up some info that will hopefully help
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: diagnose the problem, but since we have already crashed,
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: something is definitely wrong and this may fail.
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: Server version: 10.3.14-MariaDB
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: key_buffer_size=134217728
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: read_buffer_size=131072
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: max_used_connections=11
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: max_threads=153
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: thread_count=22
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: It is possible that mysqld could use up to
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467390 K  bytes of memory
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: Hope that's ok; if not, decrease some variables in the equation.
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: Thread pointer: 0x7efefc0008c8
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: Attempting backtrace. You can use the following information to find out
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: where mysqld died. If you see no messages after this, something went
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: terribly wrong...
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: stack_bottom = 0x7effc1a63d00 thread_stack 0x49000
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x56445dcabdfe]
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(handle_fatal_signal+0x357)[0x56445d74bcc7]
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: /lib64/libpthread.so.0(+0xf5d0)[0x7f0a5ee4d5d0]
 
May 02 14:28:29 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z20find_field_in_tablesP3THDP10Item_identP10TABLE_LISTS4_PP4Item27find_item_error_report_typebb+0x622)[0x56445d518862]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z11setup_groupP3THD20Bounds_checked_arrayIP4ItemEP10TABLE_LISTR4ListIS2_ES9_P8st_orderPbb+0x2d8)[0x56445d5b8d38]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z13setup_windowsP3THD20Bounds_checked_arrayIP4ItemEP10TABLE_LISTR4ListIS2_ES9_RS7_I11Window_specERS7_I16Item_window_funcE+0x1aa)[0x56445d6a7b9a]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_ZN4JOIN7prepareEP10TABLE_LISTjP4ItemjP8st_orderbS5_S3_S5_P13st_select_lexP18st_select_lex_unit+0x929)[0x56445d5bb5e9]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_ZN18st_select_lex_unit12prepare_joinEP3THDP13st_select_lexP13select_resultmb+0x113)[0x56445d60b673]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_ZN18st_select_lex_unit7prepareEP10TABLE_LISTP13select_resultm+0x10c1)[0x56445d60f241]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z21mysql_derived_prepareP3THDP3LEXP10TABLE_LIST+0x263)[0x56445d538a43]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xfc)[0x56445d53979c]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z20mysql_prepare_insertP3THDP10TABLE_LISTP5TABLER4ListI4ItemEPS7_S8_S8_15enum_duplicatesPPS6_b+0xba)[0x56445d53fc9a]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z27mysql_insert_select_prepareP3THD+0x62)[0x56445d543d22]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x887d)[0x56445d5753bd]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x36)[0x56445d4db886]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr+0x99)[0x56445d4e3319]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x64c)[0x56445d4e3d6c]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_ZN7sp_head7executeEP3THDb+0x860)[0x56445d4df280]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x74d)[0x56445d4e049d]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(+0x59c819)[0x56445d565819]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(+0x59dd76)[0x56445d566d76]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_ZN12Sql_cmd_call7executeEP3THD+0x90)[0x56445d567590]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x3952)[0x56445d570492]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x22b)[0x56445d575cab]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1d3e)[0x56445d578a2e]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z10do_commandP3THD+0x13e)[0x56445d57988e]
 
May 02 14:28:30 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1aa)[0x56445d64fb6a]
 
May 02 14:28:31 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(handle_one_connection+0x3d)[0x56445d64fc8d]
 
May 02 14:28:31 ip-10-0-192-142.ec2.internal mysqld[28907]: /usr/sbin/mysqld(+0xc9748d)[0x56445dc6048d]
 
May 02 14:28:31 ip-10-0-192-142.ec2.internal mysqld[28907]: /lib64/libpthread.so.0(+0x7dd5)[0x7f0a5ee45dd5]
 
May 02 14:28:31 ip-10-0-192-142.ec2.internal mysqld[28907]: /lib64/libc.so.6(clone+0x6d)[0x7f0a5d1e7ead]
 
May 02 14:28:31 ip-10-0-192-142.ec2.internal mysqld[28907]: Trying to get some variables.
 
May 02 14:28:31 ip-10-0-192-142.ec2.internal mysqld[28907]: Some pointers may be invalid and cause the dump to abort.
 
May 02 14:28:31 ip-10-0-192-142.ec2.internal mysqld[28907]: Query (0x7efefc13e1c0): REPLACE INTO  abc_by_quarter_75_v2(     quarter,     sku_prefix,     brand_name,     6_month_rev,     6_month_abc,     12_month_rev,     12_month_abc,     refreshed_at)      WITH 12_month as (  SELECT  oiam.sku_prefix,             m.brand_name,             @quarter as `quarter`,             SUM(CASE WHEN DATE(oiam.created_at) BETWEEN @start_date_12_month AND @end_date THEN oiam.product_amount_ordered ELSE NULL END) as 12_month_rev #12 month product revenue by maker for most recently completed quarter          FROM  `bi-test`.mv_order_item_as_merchandised oiam          LEFT JOIN `bi-test`.mv_maker m USING(sku_prefix)          WHERE  oiam.sku_prefix != '1'          GROUP BY  oiam.sku_prefix, m.brand_name          HAVING  12_month_rev > 0)    #Query SELECT  a.quarter,     a.sku_prefix,    a.brand_name,    b.6_month_rev,    b.6_month_abc,    a.12_month_rev,    a.12_month_abc,    NOW() as refreshed_at  FROM  (SELECT  s.*,       SUM(s.percent) over (partition by s.quarter order by s.12_month_rev DESC) as running_total,       CASE WHEN SUM(s.percent) over (partition by s.quarter order by s.12_month_rev DESC) <= .75 THEN "A" WHEN SUM(s.percent) over (partition by s.quarter order by s.12_month_rev DESC) >= .95 THEN "C" ELSE "B" END as 12_month_abc    FROM  (SELECT  m.quarter,          m.sku_prefix,          m.brand_name,          m.12_month_rev,          y.total_12_month_rev,          m.12_month_rev / y.total_12_month_rev as percent       FROM  12_month m  #joining total sales for the specified time period in order to calculate the % of rev by maker of the total sales - mimics subquery above but is total sales by quarter RIGHT JOIN (SELECT  f.quarter,       SUM(f.12_month_rev) as total_12_month_rev    FROM  12_month f    GROUP BY f.quarter) y ON y.quarter = m.quarter) s GROUP BY s.quarter, s.sku_prefix ORDER BY s.quarter ASC, s.12_month_rev DESC) a  LEFT JOIN  #same queries as above but for 6 months rather than 12 months - so the first query is for 6 month sales by maker and the second query is tot
 
May 02 14:28:31 ip-10-0-192-142.ec2.internal mysqld[28907]: al 6 month sales for that time period (SELECT  s.*,    SUM(s.percent) over (partition by s.quarter order by s.6_month_rev DESC) as running_total,    CASE WHEN SUM(s.percent) over (partition by s.quarter order by s.6_month_rev DESC) <= .75 THEN "A" WHEN SUM(s.percent) over (partition by s.quarter order by s.6_month_rev DESC) >= .95 THEN "C" ELSE "B" END as 6_month_abc FROM  (SELECT  m.quarter,       m.sku_prefix,       m.brand_name,       m.6_month_rev,       y.total_6_month_rev,       m.6_month_rev / y.total_6_month_rev as percent    FROM  (SELECT  oiam.sku_prefix,          m.brand_name,          @quarter as `quarter`,          SUM(CASE WHEN DATE(oiam.created_at) BETWEEN @start_date_6_month AND @end_date THEN oiam.product_amount_ordered ELSE NULL END) as 6_month_rev #6 month product revenue by maker for most recently completed quarter       FROM  `bi-test`.mv_order_item_as_merchandised oiam       LEFT JOIN `bi-test`.mv_maker m USING(sku_prefix)       WHERE  oiam.sku_prefix != '1'       GROUP BY  oiam.sku_prefix, m.brand_name) m  #total 6 month sales RIGHT JOIN (SELECT  f.quarter,       SUM(f.6_month_rev) as total_6_month_rev    FROM  (SELECT  oiam.sku_prefix,          m.brand_name,          @quarter as `quarter`,          SUM(CASE WHEN DATE(oiam.created_at) BETWEEN @start_date_6_month AND @end_date THEN oiam.product_amount_ordered ELSE NULL END) as 6_month_rev #6 month product revenue by maker for most recently completed quarter       FROM  `bi-test`.mv_order_item_as_merchandised oiam       LEFT JOIN `bi-test`.mv_maker m USING(sku_prefix)       WHERE  oiam.sku_prefix != '1'      GROUP BY   oiam.sku_prefix, m.brand_name) f   GROUP BY f.quarter) y ON y.quarter = m.quarter) s GROUP BY s.quarter, s.sku_prefix) b ON b.quarter = a.quarter AND b.sku_prefix = a.sku_prefix WHERE  a.12_month_rev > 0
 
May 02 14:28:31 ip-10-0-192-142.ec2.internal mysqld[28907]: Connection ID (thread ID): 16
 
May 02 14:28:31 ip-10-0-192-142.ec2.internal mysqld[28907]: Status: NOT_KILLED
 
May 02 14:28:31 ip-10-0-192-142.ec2.internal mysqld[28907]: Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on

Table schemas below.

CREATE TABLE `mv_order_item_as_merchandised` (
 
  `item_id` int(10) unsigned NOT NULL COMMENT 'Discrete Dimension. Internal row ID',
 
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Continuous Dimension. Time of order creation',
 
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Continuous Dimension. Time order was last updated',
 
  `order_id` int(10) unsigned NOT NULL COMMENT 'Discrete Dimension. Internal row ID of order',
 
  `order_increment_id` varchar(50) DEFAULT NULL COMMENT 'Discrete Dimension. Order ID as visible in Magento',
 
  `order_date` date DEFAULT NULL COMMENT 'Continuous Dimension. Date order was placed',
 
  `order_state` varchar(32) DEFAULT NULL COMMENT 'Discrete Dimension. Stage in order processing',
 
  `store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Discrete Dimension. Magento store ID',
 
  `customer_email` varchar(255) DEFAULT NULL COMMENT 'Discrete Dimension. Customer email',
 
  `email_hash` varchar(128) DEFAULT NULL COMMENT 'Discrete Dimension. Customer email hash',
 
  `sku` varchar(255) DEFAULT NULL COMMENT 'Discrete Dimension. SKU ordered',
 
  `sku_prefix` varchar(64) GENERATED ALWAYS AS (substr(`sku`,1,locate('-',`sku`) - 1)) STORED COMMENT 'Discrete Dimension. SKU Prefix.',
 
  `shipwire_vendor_id` int(11) DEFAULT NULL COMMENT 'Discrete Dimension. Link to shipwire vendor item was shipped from',
 
  `shipwire_warehouse_id` int(11) DEFAULT NULL COMMENT 'Discrete Dimension. Link to shipwire warehouse item was shipped from',
 
  `product_id` int(10) unsigned DEFAULT NULL COMMENT 'Discrete Dimension. Link to product ordered.',
 
  `product_type` varchar(255) DEFAULT NULL COMMENT 'Discrete Dimension. Product type',
 
  `name` varchar(255) DEFAULT NULL COMMENT 'Discrete Dimension. Name of product ordered',
 
  `free_shipping` tinyint(1) NOT NULL COMMENT 'Discrete Dimension. Whether or not the item had free shipping',
 
  `product_unit_price` decimal(12,2) DEFAULT NULL COMMENT 'Discrete Dimension. Unit price of the item at the time it was ordered',
 
  `product_unit_cost` decimal(12,2) DEFAULT NULL COMMENT 'Discrete Dimension. Unit cost of the item at the time it was ordered',
 
  `product_cost_ordered` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Total cost of the items ordered',
 
  `product_cost_canceled` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Total cost of items canceled (not charged to customers)',
 
  `product_cost_refunded` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Total cost of items refunded (charged and refunded to customers)',
 
  `product_cost_returned` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Total cost of items returned (charged, refunded and RMA issued)',
 
  `product_cost_liable` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Total cost of items owed to customer',
 
  `product_cost_net` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Total cost of items actually finally sold',
 
  `qty_backordered` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Quantity of items backordered',
 
  `qty_virtual` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Quantity of items that are virtually delivered (not shipped)',
 
  `qty_canceled` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Quantity of items canceled (not charged to customers)',
 
  `qty_invoiced` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Quantity of items invoiced (charged to customers)',
 
  `qty_ordered` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Quantity of items ordered',
 
  `qty_refunded` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Quantity of items refunded (charged and refunded to customers)',
 
  `qty_shipped` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Quantity of items shipped',
 
  `qty_returned` decimal(12,2) NOT NULL COMMENT 'Metric. Quantity of items returned (charged, refunded and RMA issued)',
 
  `qty_liable` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Quantity of items owed to customer',
 
  `qty_net` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Quantity of items actually processed and sold',
 
  `margin_product` decimal(12,2) GENERATED ALWAYS AS (1 / (nullif(`product_amount_ordered` - `product_amount_canceled` - `product_amount_refunded`,0) / nullif(`product_cost_net`,0))) VIRTUAL COMMENT 'Metric. Margin of items sold',
 
  `margin_sales` decimal(12,2) GENERATED ALWAYS AS (1 / (nullif(`product_net`,0) / nullif(`product_cost_net`,0))) VIRTUAL COMMENT 'Metric. Margin of items sold',
 
  `margin` decimal(12,2) GENERATED ALWAYS AS (1 / (nullif(`product_net`,0) / nullif(`product_cost_net`,0))) VIRTUAL COMMENT 'Metric. Margin of items sold',
 
  `product_amount_ordered` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount of ordered products.',
 
  `product_amount_discount` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount discounted from product price',
 
  `product_amount_canceled` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount canceled (not charged to customer) for product',
 
  `product_amount_refunded` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount of product refunded (charged and refunded to customer)',
 
  `product_amount_shipped` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount of product shipped',
 
  `product_amount_liable` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount of product owed to the customer',
 
  `product_net` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Net amount from product after cancelation, refund and discounts',
 
  `perks_amount_ordered` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount of ordered perks.',
 
  `perks_amount_discount` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount discounted from perks price.',
 
  `perks_amount_canceled` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount canceled (not charged to customer) for perks',
 
  `perks_amount_refunded` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount of perks refunded (charged and refunded to customer)',
 
  `perks_net` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Net amount from perks after cancelation, refund and discounts',
 
  `giftcard_amount_ordered` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount of ordered gift cards',
 
  `giftcard_amount_discount` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount discounted from gift card price',
 
  `giftcard_amount_canceled` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount canceled (not charged to customer) for gift cards',
 
  `giftcard_amount_refunded` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount of gift cards refunded (charged and refunded to customer)',
 
  `giftcard_amount_liable` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount of gift card we still owe the customer',
 
  `giftcard_net` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Net amount from gift cards after cancelation, refund and discounts',
 
  `item_tax_ordered` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Tax on ordered items',
 
  `item_tax_canceled` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount of tax on row canceled (not charged to customer)',
 
  `item_tax_refunded` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Amount of tax refunded (charged and refunded to customer)',
 
  `item_tax_net` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Net amount of tax after cancelation, refund and discounts',
 
  `total_ordered` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Total amount ordered.',
 
  `total_discount` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Total amount discounted from price.',
 
  `total_canceled` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Total amount canceled (not charged to customer)',
 
  `total_refunded` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Total amount refunded (charged and refunded to customer)',
 
  `total_liable` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Total amount owed to customer',
 
  `revenue_operational` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Revenue without allowances',
 
  `revenue_kpi` decimal(12,2) DEFAULT NULL COMMENT 'Metric. Revenue with 3% allowance.',
 
  `valid` tinyint(1) NOT NULL COMMENT 'Is this row self-consistent?',
 
  `refreshed_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  PRIMARY KEY (`item_id`),
 
  KEY `order_id` (`order_id`),
 
  KEY `order_increment_id` (`order_increment_id`),
 
  KEY `product_id` (`product_id`),
 
  KEY `sku` (`sku`),
 
  KEY `created_at` (`created_at`),
 
  KEY `refreshed_at` (`refreshed_at`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
 CREATE TABLE `mv_maker` (
 
  `maker_id` bigint(10) NOT NULL,
 
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  `maker_sku` varchar(64) NOT NULL,
 
  `sku_prefix` varchar(192) NOT NULL,
 
  `brand_name` varchar(255) NOT NULL,
 
  `short_description` text DEFAULT NULL,
 
  `is_visible` tinyint(1) NOT NULL,
 
  `live_on_site_date` date DEFAULT NULL,
 
  `homepage_launch_date` date DEFAULT NULL,
 
  `is_discontinued` int(11) DEFAULT NULL,
 
  `is_enabled` tinyint(1) NOT NULL,
 
  `is_dropship` tinyint(1) NOT NULL,
 
  `is_fulfillment` tinyint(1) NOT NULL,
 
  `allows_returns` tinyint(1) DEFAULT NULL COMMENT 'Null is N/A: for drop-ship makers',
 
  `allows_returns_invalid` varchar(64) DEFAULT NULL COMMENT 'Value is reason',
 
  `free_freight` tinyint(1) DEFAULT NULL COMMENT 'Null is N/A: for drop-ship makers',
 
  `url_key` varchar(500) DEFAULT NULL,
 
  `terms_retail_media_allowance` decimal(12,4) NOT NULL COMMENT 'Fraction',
 
  `terms_return_discount_allowance` decimal(12,4) NOT NULL COMMENT 'Fraction',
 
  `terms_early_discount` decimal(12,4) NOT NULL COMMENT 'Fraction',
 
  `terms_retail_media_fee` decimal(12,4) NOT NULL COMMENT 'Dollars',
 
  `user_id` int(11) DEFAULT NULL,
 
  `refreshed_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 
  PRIMARY KEY (`maker_id`),
 
  UNIQUE KEY `sku_prefix` (`sku_prefix`),
 
  KEY `url_key_idx` (`url_key`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING
 
 PARTITION BY SYSTEM_TIME
 
(PARTITION `p_hist` HISTORY ENGINE = InnoDB,
 
 PARTITION `p_cur` CURRENT ENGINE = InnoDB);



 Comments   
Comment by Alice Sherepa [ 2019-05-13 ]

Thanks for the report!
Reproduced on 10.2-10.4:

create table t1 (i1 int, i2 varchar(50)) ;
 
insert into t1 values (1,"foo"),(2,"bar"); 
 
 
create table t2 ( i2 varchar(50) not null) ;
 
insert into t2 values ('foo'), ('foo'),('bar');
 
 
 
delimiter $$;
 
create procedure pr()
 
begin
 
with cte as (select 2 as q, sum(t1.i1) as sm from t1 join t2 m using(i2)) 
select 1 from
 
  (select s.*, sum(s.p) over (partition by s.q), case when sum(s.p) over (partition by s.q) < 0.75 then "a" else "b" end
 
   from (select m.q,  m.sm / y.ts as p
 
      from cte m 
      join (select f.q, sum(f.sm) as ts from cte f) y on y.q = m.q) s)a;
 
end$$
 
delimiter ;$$
 
 
call pr();
 
call pr();
 
call pr();

10.2 8ce702aa90c174566f4a

 
#0 0x5578b6b7d6f0 in find_field_in_tables(THD*, Item_ident*, TABLE_LIST*, TABLE_LIST*, Item**, find_item_error_report_type, bool, bool) /10.2/sql/sql_base.cc:5945
 
    #1 0x5578b6d9ad92 in find_order_in_list /10.2/sql/sql_select.cc:22455
 
    #2 0x5578b6d9c110 in setup_group(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<Item>&, List<Item>&, st_order*, bool*, bool) /10.2/sql/sql_select.cc:22633
 
    #3 0x5578b709f7fa in setup_windows(THD*, Bounds_checked_array<Item*>, TABLE_LIST*, List<Item>&, List<Item>&, List<Window_spec>&, List<Item_window_func>&) /10.2/sql/sql_window.cc:208
 
    #4 0x5578b6d05a33 in setup_without_group /10.2/sql/sql_select.cc:664
 
    #5 0x5578b6d071e8 in JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) /10.2/sql/sql_select.cc:819
 
    #6 0x5578b6ea93a0 in st_select_lex_unit::prepare(THD*, select_result*, unsigned long) /10.2/sql/sql_union.cc:586
 
    #7 0x5578b6c04dd1 in mysql_derived_prepare(THD*, LEX*, TABLE_LIST*) /10.2/sql/sql_derived.cc:741
 
    #8 0x5578b6c021b1 in mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int) /10.2/sql/sql_derived.cc:198
 
    #9 0x5578b6f10b10 in TABLE_LIST::handle_derived(LEX*, unsigned int) /10.2/sql/table.cc:7988
 
    #10 0x5578b6c3a4fd in LEX::handle_list_of_derived(TABLE_LIST*, unsigned int) /10.2/sql/sql_lex.h:3185
 
    #11 0x5578b6c56d6e in st_select_lex::handle_derived(LEX*, unsigned int) /10.2/sql/sql_lex.cc:3918
 
    #12 0x5578b6d0600a in JOIN::prepare(TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) /10.2/sql/sql_select.cc:716
 
    #13 0x5578b6d23d2f in mysql_select(THD*, 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*) /10.2/sql/sql_select.cc:3798
 
    #14 0x5578b6d0363a in handle_select(THD*, LEX*, select_result*, unsigned long) /10.2/sql/sql_select.cc:376
 
    #15 0x5578b6c8b0d6 in execute_sqlcom_select /10.2/sql/sql_parse.cc:6486
 
    #16 0x5578b6c784cb in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:3534
 
    #17 0x5578b75d9bf1 in sp_instr_stmt::exec_core(THD*, unsigned int*) /10.2/sql/sp_head.cc:3248
 
    #18 0x5578b75d8874 in sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*) /10.2/sql/sp_head.cc:3011
 
    #19 0x5578b75d94ce in sp_instr_stmt::execute(THD*, unsigned int*) /10.2/sql/sp_head.cc:3164
 
    #20 0x5578b75cee57 in sp_head::execute(THD*, bool) /10.2/sql/sp_head.cc:1329
 
    #21 0x5578b75d2cbe in sp_head::execute_procedure(THD*, List<Item>*) /10.2/sql/sp_head.cc:2118
 
    #22 0x5578b6c74a15 in do_execute_sp /10.2/sql/sql_parse.cc:2956
 
    #23 0x5578b6c86571 in mysql_execute_command(THD*) /10.2/sql/sql_parse.cc:5834
 
    #24 0x5578b6c9383a in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /10.2/sql/sql_parse.cc:8020
 
    #25 0x5578b6c6eec4 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /10.2/sql/sql_parse.cc:1833
 
    #26 0x5578b6c6c041 in do_command(THD*) /10.2/sql/sql_parse.cc:1387
 
    #27 0x5578b6f95689 in do_handle_one_connection(CONNECT*) /10.2/sql/sql_connect.cc:1335
 
    #28 0x5578b6f95091 in handle_one_connection /10.2/sql/sql_connect.cc:1241
 
    #29 0x5578b8143ac5 in pfs_spawn_thread /10.2/storage/perfschema/pfs.cc:1862
 
    #30 0x7f22508656b9 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x76b9)
 
    #31 0x7f224fcfa41c in clone (/lib/x86_64-linux-gnu/libc.so.6+0x10741c)

Comment by Alice Sherepa [ 2023-10-12 ]

not reproducible on current 10.4 0c7af6a2a19343cb9d4fedbd7165b8f73bc4cf96 -11.2

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