[MDEV-19383] Query crashes server - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Cannot Reproduce
Affects Version/s: 10.3.14, 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: N/A
Component/s: Optimizer - Window functions
Labels:
None
Environment:
3.10.0-693.5.2.el7.x86_64

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

Attachments

Issue Links

relates to

MDEV-18502 Server crash in find_field_in_tables upon 2nd execution of SP which causes ER_WRONG_GROUP_FIELD

Closed

Activity

Ascending order - Click to sort in descending order

Aria Stewart created issue - 2019-05-02 14:41

Elena Stepanova made changes - 2019-05-06 23:00

Field	Original Value	New Value
Assignee		Alice Sherepa [ alice ]

Alice Sherepa added a comment - 2019-05-13 11:31

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)

Alice Sherepa added a comment - 2019-05-13 11:31 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)

Alice Sherepa made changes - 2019-05-13 11:31

Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]

Alice Sherepa made changes - 2019-05-13 11:31

Fix Version/s		10.2 [ 14601 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]

Alice Sherepa made changes - 2019-05-13 11:31

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alice Sherepa made changes - 2019-05-13 11:32

Assignee

Alice Sherepa [ alice ]

Varun Gupta [ varun ]

Alice Sherepa made changes - 2019-05-13 11:33

Link

This issue relates to ~~MDEV-18502~~ [ ~~MDEV-18502~~ ]

Julien Fritsch made changes - 2021-03-19 14:17

Assignee

Varun Gupta [ varun ]

Sergei Petrunia [ psergey ]

Sergei Golubchik made changes - 2021-12-06 21:36

Workflow

MariaDB v3 [ 96537 ]

MariaDB v4 [ 144138 ]

Ralf Gebhardt made changes - 2022-08-04 08:40

Fix Version/s

10.2 [ 14601 ]

Julien Fritsch made changes - 2023-04-27 14:22

Fix Version/s

10.3 [ 22126 ]

Alice Sherepa added a comment - 2023-10-12 09:45

not reproducible on current 10.4 0c7af6a2a19343cb9d4fedbd7165b8f73bc4cf96 -11.2

Alice Sherepa added a comment - 2023-10-12 09:45 not reproducible on current 10.4 0c7af6a2a19343cb9d4fedbd7165b8f73bc4cf96 -11.2

Alice Sherepa made changes - 2023-10-12 09:45

Component/s		Optimizer - Window functions [ 13502 ]
Component/s	Data Manipulation - Subquery [ 10107 ]
Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.4 [ 22408 ]
Assignee	Sergei Petrunia [ psergey ]
Resolution		Cannot Reproduce [ 5 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

People

Assignee:: Unassigned

Reporter:: Aria Stewart

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2019-05-02 14:41

Updated:: 2023-10-12 09:45

Resolved:: 2023-10-12 09:45

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration