Date: 2017-12-20 18:34:07 +0100
From: Manuel <>
To: SQL devs <>
Version: 11.27.11 (Jul2017-SP3)
CC: @njnes, @drstmane

Last updated: 2018-02-12 16:12:06 +0100

Comment 26035

Date: 2017-12-20 18:34:07 +0100
From: Manuel <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.84 Safari/537.36
Build Identifier:

The query reported below returns nothing, not even an empty table.
Removing one of the limit clauses fixes this issue.
I will attach the sql to generate the table referenced in the query.

I am aware that in this case the query could be simplified as to use only limit clause, but it might not be always the case
in a different query, and maybe this is a generic issue.

Hope this helps

SELECT "t4jdu"."c4jdd_store_phone_number_8958366756",
"t4jdu"."c4jde_line_cost__singleton__3098294",
"t4jdu"."c4jdf_yyyymmdd__mean__3928221090907",
"t4jdu"."c4jdg_line_price__sum__145489358291",
"t4jdu"."c4jdh_month_begin_date__min__838817",
"t4jdu"."c4jdi_customer_state__max__67562250",
"t4jdu"."c4jdo_mode_product_family" ,
"t4jdu"."c4jdp_product_subcategory__unique_v"
FROM (
SELECT "t4jds"."c4jdd_store_phone_number_8958366756",
"t4jds"."c4jde_line_cost__singleton__3098294",
"t4jds"."c4jdf_yyyymmdd__mean__3928221090907",
"t4jds"."c4jdg_line_price__sum__145489358291",
"t4jds"."c4jdh_month_begin_date__min__838817",
"t4jds"."c4jdi_customer_state__max__67562250",
"t4jds"."c4jdo_mode_product_family" ,
"t4jds"."c4jdp_product_subcategory__unique_v"
FROM (
SELECT "t4jdq"."c4jdd_store_phone_number_8958366756",
"t4jdq"."c4jde_line_cost__singleton__3098294",
"t4jdq"."c4jdf_yyyymmdd__mean__3928221090907",
"t4jdq"."c4jdg_line_price__sum__145489358291",
"t4jdq"."c4jdh_month_begin_date__min__838817",
"t4jdq"."c4jdi_customer_state__max__67562250",
"t4jdr"."c4jdo_mode_product_family" ,
"t4jdq"."c4jdp_product_subcategory__unique_v"
FROM (
SELECT "t4jdc"."c4jcu_store_phone_number" AS "c4jdd_store_phone_number_8958366756",
CASE
WHEN (
Count("t4jdc"."c4jd7_line_cost") = Count()
AND Min("t4jdc"."c4jd7_line_cost") = Max("t4jdc"."c4jd7_line_cost")) THEN Min("t4jdc"."c4jd7_line_cost")
END AS "c4jde_line_cost__singleton__3098294",
(Avg("c4jdb_m_yyyymmdd_yyyymm") /
CASE
WHEN Avg("t4jdc"."c4jd3_yyyymm") = 0.0 THEN NULL
ELSE Avg("t4jdc"."c4jd3_yyyymm")
END) AS "c4jdf_yyyymmdd__mean__3928221090907",
Sum("t4jdc"."c4jd6_line_price") AS "c4jdg_line_price__sum__145489358291",
Min("t4jdc"."c4jd2_month_begin_date") AS "c4jdh_month_begin_date__min__838817",
Max("t4jdc"."c4jcg_customer_state") AS "c4jdi_customer_state__max__67562250",
Count(DISTINCT "t4jdc"."c4jcm_product_subcategory") AS "c4jdp_product_subcategory__unique_v"
FROM (
SELECT "t4jcc"."Customer_Gender" AS "c4jcd_customer_gender" ,
"t4jcc"."Customer_Region" AS "c4jce_customer_region" ,
"t4jcc"."Customer_Country" AS "c4jcf_customer_country" ,
"t4jcc"."Customer_State" AS "c4jcg_customer_state" ,
"t4jcc"."Customer_City" AS "c4jch_customer_city" ,
"t4jcc"."Customer_Date_Of_Birth" AS "c4jci_customer_date_of_birth",
"t4jcc"."Customer_Id" AS "c4jcj_customer_id" ,
"t4jcc"."Product_Family" AS "c4jck_product_family" ,
"t4jcc"."Product_Category" AS "c4jcl_product_category" ,
"t4jcc"."Product_Subcategory" AS "c4jcm_product_subcategory" ,
"t4jcc"."Product_Name" AS "c4jcn_product_name" ,
"t4jcc"."Product_Id" AS "c4jco_product_id" ,
"t4jcc"."Store_Id" AS "c4jcp_store_id" ,
"t4jcc"."Store_Longitude" AS "c4jcq_store_longitude" ,
"t4jcc"."Store_Latitude" AS "c4jcr_store_latitude" ,
"t4jcc"."Store_Name" AS "c4jcs_store_name" ,
"t4jcc"."Store_Manager" AS "c4jct_store_manager" ,
"t4jcc"."Store_Phone_Number" AS "c4jcu_store_phone_number" ,
"t4jcc"."Store_Region" AS "c4jcv_store_region" ,
"t4jcc"."Store_Country" AS "c4jcw_store_country" ,
"t4jcc"."Store_State" AS "c4jcx_store_state" ,
"t4jcc"."Store_City" AS "c4jcy_store_city" ,
"t4jcc"."Order_Date" AS "c4jcz_order_date" ,
"t4jcc"."Year_Begin_Date" AS "c4jd0_year_begin_date" ,
"t4jcc"."Quarter_Begin_Date" AS "c4jd1_quarter_begin_date" ,
"t4jcc"."Month_Begin_Date" AS "c4jd2_month_begin_date" ,
"t4jcc"."Yyyymm" AS "c4jd3_yyyymm" ,
"t4jcc"."Yyyymmdd" AS "c4jd4_yyyymmdd" ,
"t4jcc"."Ddmonyyyy" AS "c4jd5_ddmonyyyy" ,
"t4jcc"."Line_Price" AS "c4jd6_line_price" ,
"t4jcc"."Line_Cost" AS "c4jd7_line_cost" ,
"t4jcc"."Line_Margin" AS "c4jd8_line_margin" ,
"t4jcc"."Line_Margin_Percent" AS "c4jd9_line_margin_percent" ,
"t4jcc"."visokio_row_id" AS "c4jda__visokio_row_id_" ,
("t4jcc"."Yyyymm" * "t4jcc"."Yyyymmdd") AS "c4jdb_m_yyyymmdd_yyyymm"
FROM (
SELECT *
FROM "lineitem_denormalized_first1k_sanitised" AS "t4jcb") AS "t4jcc") AS "t4jdc"
GROUP BY "t4jdc"."c4jcu_store_phone_number") AS "t4jdq"
LEFT OUTER JOIN
(
SELECT "t4jdn"."c4jdj_modeg_c4jcu_store_phone_numbe",
"t4jdn"."c4jdl_modec_product_family" AS "c4jdo_mode_product_family"
FROM (
SELECT "t4jdc"."c4jcu_store_phone_number" AS "c4jdj_modeg_c4jcu_store_phone_numbe",
"t4jdc"."c4jck_product_family" AS "c4jdl_modec_product_family" ,
Count() AS "c4jdk_mode_count" ,
Row_number() OVER ( partition BY "t4jdc"."c4jcu_store_phone_number" ORDER BY Count(*) DESC, "t4jdc"."c4jck_product_family" DESC) AS "c4jdm_mode_rank"
FROM (
SELECT "t4jcc"."Customer_Gender" AS "c4jcd_customer_gender" ,
"t4jcc"."Customer_Region" AS "c4jce_customer_region" ,
"t4jcc"."Customer_Country" AS "c4jcf_customer_country" ,
"t4jcc"."Customer_State" AS "c4jcg_customer_state" ,
"t4jcc"."Customer_City" AS "c4jch_customer_city" ,
"t4jcc"."Customer_Date_Of_Birth" AS "c4jci_customer_date_of_birth",
"t4jcc"."Customer_Id" AS "c4jcj_customer_id" ,
"t4jcc"."Product_Family" AS "c4jck_product_family" ,
"t4jcc"."Product_Category" AS "c4jcl_product_category" ,
"t4jcc"."Product_Subcategory" AS "c4jcm_product_subcategory" ,
"t4jcc"."Product_Name" AS "c4jcn_product_name" ,
"t4jcc"."Product_Id" AS "c4jco_product_id" ,
"t4jcc"."Store_Id" AS "c4jcp_store_id" ,
"t4jcc"."Store_Longitude" AS "c4jcq_store_longitude" ,
"t4jcc"."Store_Latitude" AS "c4jcr_store_latitude" ,
"t4jcc"."Store_Name" AS "c4jcs_store_name" ,
"t4jcc"."Store_Manager" AS "c4jct_store_manager" ,
"t4jcc"."Store_Phone_Number" AS "c4jcu_store_phone_number" ,
"t4jcc"."Store_Region" AS "c4jcv_store_region" ,
"t4jcc"."Store_Country" AS "c4jcw_store_country" ,
"t4jcc"."Store_State" AS "c4jcx_store_state" ,
"t4jcc"."Store_City" AS "c4jcy_store_city" ,
"t4jcc"."Order_Date" AS "c4jcz_order_date" ,
"t4jcc"."Year_Begin_Date" AS "c4jd0_year_begin_date" ,
"t4jcc"."Quarter_Begin_Date" AS "c4jd1_quarter_begin_date" ,
"t4jcc"."Month_Begin_Date" AS "c4jd2_month_begin_date" ,
"t4jcc"."Yyyymm" AS "c4jd3_yyyymm" ,
"t4jcc"."Yyyymmdd" AS "c4jd4_yyyymmdd" ,
"t4jcc"."Ddmonyyyy" AS "c4jd5_ddmonyyyy" ,
"t4jcc"."Line_Price" AS "c4jd6_line_price" ,
"t4jcc"."Line_Cost" AS "c4jd7_line_cost" ,
"t4jcc"."Line_Margin" AS "c4jd8_line_margin" ,
"t4jcc"."Line_Margin_Percent" AS "c4jd9_line_margin_percent" ,
"t4jcc"."visokio_row_id" AS "c4jda__visokio_row_id_" ,
("t4jcc"."Yyyymm" * "t4jcc"."Yyyymmdd") AS "c4jdb_m_yyyymmdd_yyyymm"
FROM (
SELECT *
FROM "lineitem_denormalized_first1k_sanitised" AS "t4jcb") AS "t4jcc") AS "t4jdc"
WHERE "t4jdc"."c4jck_product_family" IS NOT NULL
GROUP BY "t4jdc"."c4jcu_store_phone_number",
"t4jdc"."c4jck_product_family") AS "t4jdn"
WHERE (
"t4jdn"."c4jdm_mode_rank" = 1
AND "t4jdn"."c4jdk_mode_count" > 1)) AS "t4jdr"
ON (
"t4jdq"."c4jdd_store_phone_number_8958366756" = "t4jdr"."c4jdj_modeg_c4jcu_store_phone_numbe"
OR (
"t4jdq"."c4jdd_store_phone_number_8958366756" IS NULL
AND "t4jdr"."c4jdj_modeg_c4jcu_store_phone_numbe" IS NULL))) AS "t4jds"
ORDER BY "c4jdd_store_phone_number_8958366756" ASC limit 1000) AS "t4jdu" limit 125000

Reproducible: Always

Steps to Reproduce:

1. Import the table "lineitem_denormalized_first1k_sanitised" as per attachment
2. Execute the query reported in details

Actual Results:

Nothing, not even an empty result set

Expected Results:

The result of the query

Comment 26036

Date: 2017-12-20 18:34:41 +0100
From: Manuel <>

Created attachment 584
the sql to generate the table and import the records to reproduce the issue

Attached file: lineitem_denormalized_first1k_sanitised.sql (application/octet-stream, 885584 bytes)
Description: the sql to generate the table and import the records to reproduce the issue

Comment 26038

Date: 2017-12-21 11:32:33 +0100
From: @njnes

what encoding does your 1k file use? its not utf8 as one expects..

Comment 26039

Date: 2017-12-21 11:42:20 +0100
From: Manuel <>

Created attachment 585
the sql to generate the table and import the records to reproduce the issue in UTF8

Same sql file with utf-8 encoding

Attached file: lineitem_denormalized_first1k_sanitised_monetdb.sql (text/plain, 545931 bytes)
Description: the sql to generate the table and import the records to reproduce the issue in UTF8

Comment 26040

Date: 2017-12-21 11:43:52 +0100
From: Manuel <>

(In reply to Niels Nes from comment 2)

what encoding does your 1k file use? its not utf8 as one expects..

Apologies for the previous file, unfortunately when using exporting tables using windows power shell, and redirect the output to a file, something goes wrong....

I updated the a new file (for the same table) in UTF-8

thanks,

Manuel

Comment 26044

Date: 2017-12-22 11:14:07 +0100
From: @drstmane

The query "returns nothing" because it crashes the server:

could not find t4jds.c4jdd_store_phone_number_8958366756
t4jdu.c4jdd_store_phone_number_8958366756
t4jdu.c4jde_line_cost__singleton__3098294
t4jdu.c4jdf_yyyymmdd__mean__3928221090907
t4jdu.c4jdg_line_price__sum__145489358291
t4jdu.c4jdh_month_begin_date__min__838817
t4jdu.c4jdi_customer_state__max__67562250
t4jdu.c4jdo_mode_product_family
t4jdu.c4jdp_product_subcategory__unique_v
t4jdu.c4jdd_store_phone_number_8958366756
t4jdu.c4jde_line_cost__singleton__3098294
t4jdu.c4jdf_yyyymmdd__mean__3928221090907
t4jdu.c4jdg_line_price__sum__145489358291
t4jdu.c4jdh_month_begin_date__min__838817
t4jdu.c4jdi_customer_state__max__67562250
t4jdu.c4jdo_mode_product_family
t4jdu.c4jdp_product_subcategory__unique_v
*** Error in `mserver5': double free or corruption (!prev): 0x00007fc4c897cd50 ***
Aborted (core dumped)

Comment 26046

Date: 2017-12-23 15:13:16 +0100
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=e075b1f804db

Changeset description:

fix for bug #6502, ie use a wrapping project instead of inplace renaming. This
is needed as the order by columns my refer to the projections columns, which
will not work if they are inplace renamed.