Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

M5 breaks with huge SQL query. #2647

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

M5 breaks with huge SQL query. #2647

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2010-08-10 16:25:14 +0200
From: Manuel Mayr <>
To: SQL devs <>
Version: 2.40.1 (Oct2010) [obsolete]
CC: mayrm, @njnes

Last updated: 2011-03-28 17:31:38 +0200

Comment 14634

Date: 2010-08-10 16:25:14 +0200
From: Manuel Mayr <>

User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_4; en-us) AppleWebKit/533.17.8 (KHTML, like Gecko) Version/5.0.1 Safari/533.17.8
Build Identifier:

When I run the SQL query (listed in Steps to Reproduce) against M5 using the mclient-tool it breaks with the following error message:

MAPI = monetdb@localhost:50000
ACTION= read_line
QUERY = WITH
ERROR = !Connection terminated

I'm able to run the query partwise

Reproducible: Always

Steps to Reproduce:

  1. Create a database with the following tables and constraints:

DROP TABLE payments; -- dep: orders
DROP TABLE shipments; -- dep: orders
DROP TABLE line_items; -- dep: orders
DROP TABLE orders; -- dep: addresses
DROP TABLE addresses; -- dep: states, countries
DROP TABLE zone_members; -- dep: zones, countries
DROP TABLE countries; -- dep:
DROP TABLE variants; -- dep: products
DROP TABLE products; -- dep:
DROP TABLE schema_migrations; -- dep:
DROP TABLE shipping_methods; -- dep: zones
DROP TABLE states; -- dep: states
DROP TABLE tax_categories; -- dep:
DROP TABLE tax_rates; -- dep: zones
DROP TABLE zones; -- dep:

--
-- Table structure for table payments

CREATE TABLE payments (
id integer,
order_id integer default NULL,
created_at timestamp default NULL,
updated_at timestamp default NULL,
amount decimal(8,2) NOT NULL default '0.00',
creditcard_id integer default NULL,
paytype varchar(255) default NULL
);

--
-- Table structure for table addresses

CREATE TABLE addresses (
id integer,
firstname varchar(255) default NULL,
lastname varchar(255) default NULL,
address1 varchar(255) default NULL,
address2 varchar(255) default NULL,
city varchar(255) default NULL,
state_id integer default NULL,
zipcode varchar(255) default NULL,
country_id integer default NULL,
phone varchar(255) default NULL,
created_at timestamp default NULL,
updated_at timestamp default NULL,
state_name varchar(255) default NULL
);

--
-- Table structure for table countries

CREATE TABLE countries (
id integer,
iso_name varchar(255) default NULL,
iso varchar(255) default NULL,
name varchar(255) default NULL,
iso3 varchar(255) default NULL,
numcode integer default NULL
);

--
-- Table structure for table line_items

CREATE TABLE line_items (
id integer,
order_id integer default NULL,
variant_id integer default NULL,
quantity integer NOT NULL,
price decimal(8,2) NOT NULL,
created_at timestamp default NULL,
updated_at timestamp default NULL
);

--
-- Table structure for table orders

CREATE TABLE orders (
id integer default NULL,
user_id integer default NULL,
number varchar(255) default NULL,
ship_amount decimal(8,2) NOT NULL default '0.00',
tax_amount decimal(8,2) NOT NULL default '0.00',
item_total decimal(8,2) NOT NULL default '0.00',
total decimal(8,2) NOT NULL default '0.00',
ip_address varchar(255) default NULL,
special_instructions text,
created_at timestamp default NULL,
updated_at timestamp default NULL,
state varchar(255) default NULL,
checkout_complete integer default NULL,
token varchar(255) default NULL,
email varchar(255) default NULL,
bill_address_id integer default NULL,
ship_address_id integer default NULL
);

--
-- Table structure for table products

CREATE TABLE products (
id integer default NULL,
name varchar(255) NOT NULL default '',
description text,
master_price decimal(8,2) default NULL,
created_at timestamp default NULL,
updated_at timestamp default NULL,
permalink varchar(255) default NULL,
available_on timestamp default NULL,
tax_category_id integer default NULL,
shipping_category integer default NULL,
deleted_at timestamp default NULL,
meta_description varchar(255) default NULL,
meta_keywords varchar(255) default NULL
);

--
-- Table structure for table schema_migrations

CREATE TABLE schema_migrations (
version varchar(255) NOT NULL
);

--
-- Table structure for table shipments

CREATE TABLE shipments (
id integer,
order_id integer default NULL,
shipping_method_id integer default NULL,
tracking varchar(255) default NULL,
created_at timestamp default NULL,
updated_at timestamp default NULL,
number varchar(255) default NULL,
cost decimal(8,2) default NULL,
shipped_at timestamp default NULL,
address_id integer default NULL
);

--
-- Table structure for table shipping_methods

CREATE TABLE shipping_methods (
id integer,
zone_id integer default NULL,
shipping_calculator varchar(255) default NULL,
name varchar(255) default NULL,
created_at timestamp default NULL,
updated_at timestamp default NULL
);

--
-- Table structure for table states

CREATE TABLE states (
id integer,
name varchar(255) default NULL,
abbr varchar(255) default NULL,
country_id integer default NULL
);

--
-- Table structure for table tax_categories

CREATE TABLE tax_categories (
id integer,
name varchar(255) default NULL,
description varchar(255) default NULL,
created_at timestamp default NULL,
updated_at timestamp default NULL
);

--
-- Table structure for table tax_rates

CREATE TABLE tax_rates (
id integer default NULL,
zone_id integer default NULL,
amount decimal(8,4) default NULL,
created_at timestamp default NULL,
updated_at timestamp default NULL,
tax_type integer default NULL,
tax_category_id integer default NULL
);

--
-- Table structure for table variants

CREATE TABLE variants (
id integer default NULL,
product_id integer default NULL,
sku varchar(255) default NULL,
price decimal(8,2) NOT NULL,
weight decimal(8,2) default NULL,
height decimal(8,2) default NULL,
width decimal(8,2) default NULL,
depth decimal(8,2) default NULL,
deleted_at timestamp default NULL
);

--
-- Table structure for table zone_members

CREATE TABLE zone_members (
id integer default NULL,
zone_id integer default NULL,
country_id integer default NULL,
zoneable_type varchar(255) default NULL,
created_at timestamp default NULL,
updated_at timestamp default NULL
);

--
-- Table structure for table zones

CREATE TABLE zones (
id integer default NULL,
name varchar(255) default NULL,
description varchar(255) default NULL,
created_at timestamp default NULL,
updated_at timestamp default NULL
);

--
-- Constraints for table zones

ALTER TABLE zones
ADD CONSTRAINT prim_zones_id
PRIMARY KEY(id);
ALTER TABLE zones
ALTER COLUMN id SET NOT NULL;

--
-- Constraints for table orders

ALTER TABLE orders
ADD CONSTRAINT prim_orders_id
PRIMARY KEY(id);
ALTER TABLE orders
ALTER COLUMN id SET NOT NULL;

--
-- Constraints for table addresses

ALTER TABLE addresses
ADD CONSTRAINT prim_addresses_id
PRIMARY KEY(id);
ALTER TABLE addresses
ALTER COLUMN id SET NOT NULL;

--
-- Constraints for table countries

ALTER TABLE countries
ADD CONSTRAINT prim_countries_id
PRIMARY KEY(id);
ALTER TABLE countries
ALTER COLUMN id SET NOT NULL;

--
-- Constraints for table states

ALTER TABLE states
ADD CONSTRAINT prim_states_id
PRIMARY KEY(id);
ALTER TABLE states
ALTER COLUMN id SET NOT NULL;

ALTER TABLE states
ADD CONSTRAINT for_key_states_country_id
FOREIGN KEY (country_id) REFERENCES states(id);

--
-- Constraints for table payments

ALTER TABLE payments
ADD CONSTRAINT prim_payments_id
PRIMARY KEY(id);
ALTER TABLE payments
ALTER COLUMN id SET NOT NULL;

ALTER TABLE payments
ADD CONSTRAINT for_key_payments_order_id
FOREIGN KEY (order_id) REFERENCES orders(id);

--
-- Constraints for table products

ALTER TABLE products
ADD CONSTRAINT prim_products_id
PRIMARY KEY(id);
ALTER TABLE products
ALTER COLUMN id SET NOT NULL;

--
-- Constraints on table schema_migrations

ALTER TABLE schema_migrations
ADD CONSTRAINT prim_schema_migrations_id
PRIMARY KEY(version);
ALTER TABLE schema_migrations
ALTER COLUMN version SET NOT NULL;

--
-- Constraints for table shipping_methods

ALTER TABLE shipping_methods
ADD CONSTRAINT prim_shipping_methods_id
PRIMARY KEY(id);
ALTER TABLE shipping_methods
ALTER COLUMN id SET NOT NULL;

--
-- Constraints on table shipments

ALTER TABLE shipments
ADD CONSTRAINT prim_shipments_id
PRIMARY KEY(id);
ALTER TABLE shipments
ALTER COLUMN id SET NOT NULL;

ALTER TABLE shipments
ADD CONSTRAINT for_key_shipments_order_id
FOREIGN KEY (order_id) REFERENCES orders(id);
ALTER TABLE shipments
ADD CONSTRAINT for_key_shipments_shipping_method_id
FOREIGN KEY (shipping_method_id) REFERENCES shipping_methods(id);

--
-- Constraints for table tax_categories

ALTER TABLE tax_categories
ADD CONSTRAINT prim_tax_categories_id
PRIMARY KEY(id);
ALTER TABLE tax_categories
ALTER COLUMN id SET NOT NULL;

--
-- Constraints for table tax_rates

ALTER TABLE tax_rates
ADD CONSTRAINT prim_tax_rates_id
PRIMARY KEY(id);
ALTER TABLE tax_rates
ALTER COLUMN id SET NOT NULL;

ALTER TABLE tax_rates
ADD CONSTRAINT for_key_tax_rates_zone_id
FOREIGN KEY (zone_id) REFERENCES zones(id);

--
-- Constraints for table variants

ALTER TABLE variants
ADD CONSTRAINT prim_variants_id
PRIMARY KEY(id);
ALTER TABLE variants
ALTER COLUMN id SET NOT NULL;

ALTER TABLE variants
ADD CONSTRAINT for_key_variants_on_product_id
FOREIGN KEY (product_id) REFERENCES variants(id);

--
-- Constraints for table zone_members

ALTER TABLE zone_members
ADD CONSTRAINT prim_zone_members_id
PRIMARY KEY(id);
ALTER TABLE zone_members
ALTER COLUMN id SET NOT NULL;

ALTER TABLE zone_members
ADD CONSTRAINT for_key_zone_members_country_id
FOREIGN KEY (country_id) REFERENCES countries(id);
ALTER TABLE zone_members
ADD CONSTRAINT for_key_zone_members_zone_id
FOREIGN KEY (zone_id) REFERENCES zones(id);

--
-- Constraints for table line_items

ALTER TABLE line_items
ADD CONSTRAINT prim_line_items_id
PRIMARY KEY(id);
ALTER TABLE line_items
ALTER COLUMN id SET NOT NULL;

ALTER TABLE line_items
ADD CONSTRAINT for_key_line_items_on_order_id
FOREIGN KEY (order_id) REFERENCES orders(id);
ALTER TABLE line_items
ADD CONSTRAINT for_key_line_items_on_variant_id
FOREIGN KEY (variant_id) REFERENCES variants(id);

  1. Run the following query using mclient against the database:

WITH

yv(id, product_id, sku, price, weight,
height, width, depth, deleted_at,
line_item_id, order_id) AS
(SELECT v.id, v.product_id, v.sku,
v.price, v.weight, v.height,
v.width, v.depth, v.deleted_at, li.id, o.id
FROM Orders o, Line_Items li, Variants v
WHERE o.id = li.order_id
AND li.variant_id = v.id
AND o.user_id = 20),

Cheapest_Price(product_id, price) AS
(SELECT product_id, MIN(price)
FROM Variants v
WHERE v.product_id IN (SELECT product_id FROM yv)
GROUP BY product_id),

Cheapest_Variants(id, product_id, sku,
price, weight, height,
width, depth, deleted_at) AS
(SELECT v.id, v.product_id, v.sku, v.price,
v.weight, v.height, v.width, v.depth, v.deleted_at
FROM Variants v, Cheapest_Price cp
WHERE v.price = cp.price
AND v.product_id = cp.product_id),

Suggestions_(rid, id, product_id, sku,
price, weight, height,
width, depth, deleted_at) AS
(SELECT ROW_NUMBER() OVER (PARTITION BY cv.id) AS rid,
cv.id, cv.product_id, cv.sku, cv.price,
cv.weight, cv.height, cv.width, cv.depth, cv.deleted_at
FROM Cheapest_Variants cv),

Suggestions(sugg_id, sugg_product_id, sugg_sku, sugg_price,
sugg_weight, sugg_height, sugg_width, sugg_depth, sugg_deleted_at,
id, product_id, sku, price, weight, height, width, depth, deleted_at,
line_item_id, order_id) AS
(SELECT cv.id as sugg_id, cv.product_id as sugg_product_id, cv.sku as sugg_sku, cv.price as sugg_price,
cv.weight as sugg_weight, cv.height as sugg_height, cv.width as sugg_width, cv.depth as sugg_depth,
cv.deleted_at as sugg_deleted_at,
yv.id, yv.product_id, yv.sku, yv.price, yv.weight, yv.height, yv.width, yv.depth, yv.deleted_at,
yv.line_item_id, yv.order_id
FROM Suggestions_ cv, yv yv
WHERE cv.product_id = yv.product_id
AND rid = 1),

-- return only one variant in suggestions

Savings(order_id, amount) AS
(SELECT order_id, SUM(s.price - s.sugg_price) AS amount
FROM Suggestions s
GROUP BY order_id)

SELECT sugg.*, sav.amount
FROM suggestions sugg, savings sav
WHERE sugg.order_id = sav.order_id;

Comment 14635

Date: 2010-08-10 17:00:05 +0200
From: @grobian

Hint: you are allowed to use attachments for large pieces of SQL.

Comment 14747

Date: 2010-08-20 17:05:00 +0200
From: @sjoerdmullender

Changeset b633bd288199 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=b633bd288199

Changeset description:

Test for bug #2647.
Output needs to be provided once the bug is fixed.

Comment 14748

Date: 2010-08-20 17:11:03 +0200
From: @sjoerdmullender

Extract from gdb session:

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x7fffdf495710 (LWP 11076)]
0x00007fffe0344d30 in exps_mark_used (rel=0x7fffd481f118,
subrel=0x7fffd481f0c8)
at /ufs/sjoerd/src/MonetDB/stable/sql/src/server/rel_optimizer.mx:2818
2818 sql_exp *e = subrel->exps->h->data;

(gdb) p subrel->exps->h
$3 = (node *) 0x0

In other words, it crashes with a NULL pointer dereference.
I suspect it's an SQL bug, and it occurs in Jun2010-SP1.

Comment 14831

Date: 2010-08-30 09:22:00 +0200
From: @sjoerdmullender

The Jun2010-SP2 version has been released.

Comment 15075

Date: 2010-10-20 09:17:50 +0200
From: @njnes

Changeset 7a38d577f1c6 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=7a38d577f1c6

Changeset description:

fixed bug #2647. The columns of a 'with view' are now correctly named (ie
with relation and column names).

Comment 15266

Date: 2010-12-03 22:23:10 +0100
From: @njnes

fixed

Comment 15643

Date: 2011-03-28 17:31:38 +0200
From: @sjoerdmullender

The Mar2011 version has been released.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant