[MCOL-1753] Two logically same query - one successful, another errors out on columnstore - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Do
Affects Version/s: 1.1.6
Fix Version/s: Icebox
Component/s: N/A
Labels:
None

Description

First create this table:

CREATE TABLE `business_listings` (

  `DBUSA_Business_ID` bigint(15) DEFAULT NULL,

  `DBUSA_Executive_ID` bigint(15) DEFAULT NULL,

  `Holding_Parent_ID` bigint(15) DEFAULT NULL,

  `Immediate_Parent_ID` bigint(15) DEFAULT NULL,

  `Ultimate_Parent_ID` bigint(15) DEFAULT NULL,

  `Ind_Frm_Indicator` varchar(1) DEFAULT NULL,

  `Company_Name` varchar(100) DEFAULT NULL,

  `Full_Name` varchar(85) DEFAULT NULL,

  `Prefix` varchar(2) DEFAULT NULL,

  `First_Name` varchar(30) DEFAULT NULL,

  `Middle_Initial` varchar(20) DEFAULT NULL,

  `Last_Name` varchar(30) DEFAULT NULL,

  `Suffix` varchar(3) DEFAULT NULL,

  `Gender` varchar(1) DEFAULT NULL,

  `Standardized_Title` varchar(50) DEFAULT NULL,

  `Source_Title` varchar(100) DEFAULT NULL,

  `Executive_Title_Rank` int(11) DEFAULT NULL,

  `Primary_Exec_Flag` int(11) DEFAULT NULL,

  `Exec_Type` varchar(1) DEFAULT NULL,

  `Executive_Department` varchar(45) DEFAULT NULL,

  `Executive_Level` varchar(25) DEFAULT NULL,

  `Physical_Address` varchar(75) DEFAULT NULL,

  `Physical_Address_City` varchar(40) DEFAULT NULL,

  `Physical_Address_State` varchar(2) DEFAULT NULL,

  `Physical_Address_Zip` varchar(5) DEFAULT NULL,

  `Physical_Address_Zip4` varchar(4) DEFAULT NULL,

  `Physical_Address_Carrier_Route` varchar(5) DEFAULT NULL,

  `Physical_Address_Delivery_Point` int(11) DEFAULT NULL,

  `Physical_Address_DP_Check_Digit` int(11) DEFAULT NULL,

  `Mailing_Address` varchar(75) DEFAULT NULL,

  `Mailing_Address_City` varchar(40) DEFAULT NULL,

  `Mailing_Address_State` varchar(2) DEFAULT NULL,

  `Mailing_Address_Zip` varchar(5) DEFAULT NULL,

  `Mailing_Address_Zip4` varchar(4) DEFAULT NULL,

  `Mailing_Address_Carrier_Route` varchar(5) DEFAULT NULL,

  `Mailing_Address_Delivery_Point` int(11) DEFAULT NULL,

  `Mailing_Address_DP_Check_Digit` int(11) DEFAULT NULL,

  `Mail_Score` varchar(3) DEFAULT NULL,

  `Mail_Score_Description` varchar(25) DEFAULT NULL,

  `Phone` varchar(10) DEFAULT NULL,

  `Area_Code` varchar(3) DEFAULT NULL,

  `Toll_Free` varchar(10) DEFAULT NULL,

  `Fax` varchar(10) DEFAULT NULL,

  `Email` varchar(100) DEFAULT NULL,

  `Email_Available_Indicator` int(11) DEFAULT NULL,

  `URL` varchar(200) DEFAULT NULL,

  `URL_facebook` varchar(200) DEFAULT NULL,

  `URL_google_Plus` varchar(200) DEFAULT NULL,

  `URL_instagram` varchar(200) DEFAULT NULL,

  `URL_linkedIN` varchar(200) DEFAULT NULL,

  `URL_twitter` varchar(200) DEFAULT NULL,

  `URL_youtube` varchar(200) DEFAULT NULL,

  `Business_Status_Code` int(11) DEFAULT NULL,

  `Business_Status_Description` varchar(12) DEFAULT NULL,

  `Franchise_Flag` int(11) DEFAULT NULL,

  `Franchise_Type` varchar(2) DEFAULT NULL,

  `Franchise_Description` varchar(100) DEFAULT NULL,

  `Ticker_Symbol` varchar(7) DEFAULT NULL,

  `Stock_Exchange` varchar(6) DEFAULT NULL,

  `Fortune_1000_Flag` bigint(20) DEFAULT NULL,

  `Fortune_1000_Rank` bigint(20) DEFAULT NULL,

  `Fort_1000_branches` bigint(20) DEFAULT NULL,

  `Num_Linked_Locations` varchar(5) DEFAULT NULL,

  `County_Code` varchar(75) DEFAULT NULL,

  `County_Description` varchar(50) DEFAULT NULL,

  `CBSA_Code` varchar(75) DEFAULT NULL,

  `CBSA_Description` varchar(50) DEFAULT NULL,

  `Geo_Match_Level` varchar(12) DEFAULT NULL,

  `Latitude` varchar(20) DEFAULT NULL,

  `Longitude` varchar(20) DEFAULT NULL,

  `SCF` varchar(3) DEFAULT NULL,

  `Time_Zone` varchar(15) DEFAULT NULL,

  `Census_Tract` varchar(10) DEFAULT NULL,

  `Census_Block` varchar(10) DEFAULT NULL,

  `Population_Code` varchar(1) DEFAULT NULL,

  `Population_Description` varchar(20) DEFAULT NULL,

  `Primary_SIC` varchar(7) DEFAULT NULL,

  `Primary_SIC_Description` varchar(100) DEFAULT NULL,

  `SIC02` varchar(7) DEFAULT NULL,

  `SIC02_Description` varchar(100) DEFAULT NULL,

  `SIC03` varchar(7) DEFAULT NULL,

  `SIC03_Description` varchar(100) DEFAULT NULL,

  `SIC04` varchar(7) DEFAULT NULL,

  `SIC04_Description` varchar(100) DEFAULT NULL,

  `SIC05` varchar(7) DEFAULT NULL,

  `SIC05_Description` varchar(100) DEFAULT NULL,

  `SIC06` varchar(7) DEFAULT NULL,

  `SIC06_Description` varchar(100) DEFAULT NULL,

  `Primary_SIC_2_Digit` varchar(2) DEFAULT NULL,

  `Primary_2_Digit_SIC_Description` varchar(100) DEFAULT NULL,

  `Primary_SIC_4_Digit` varchar(4) DEFAULT NULL,

  `Primary_4_Digit_SIC_Description` varchar(100) DEFAULT NULL,

  `NAICS01` varchar(6) DEFAULT NULL,

  `NAICS01_Description` varchar(100) DEFAULT NULL,

  `NAICS02` varchar(6) DEFAULT NULL,

  `NAICS02_Description` varchar(100) DEFAULT NULL,

  `NAICS03` varchar(6) DEFAULT NULL,

  `NAICS03_Description` varchar(100) DEFAULT NULL,

  `NAICS04` varchar(6) DEFAULT NULL,

  `NAICS04_Description` varchar(100) DEFAULT NULL,

  `NAICS05` varchar(6) DEFAULT NULL,

  `NAICS05_Description` varchar(100) DEFAULT NULL,

  `NAICS06` varchar(6) DEFAULT NULL,

  `NAICS06_Description` varchar(100) DEFAULT NULL,

  `Location_Employee_Total` varchar(32) DEFAULT NULL,

  `Location_Employee_Code` varchar(1) DEFAULT NULL,

  `Location_Employee_Description` varchar(15) DEFAULT NULL,

  `Location_Sales_Total` varchar(20) DEFAULT NULL,

  `Location_Sales_Code` varchar(1) DEFAULT NULL,

  `Location_Sales_Description` varchar(30) DEFAULT NULL,

  `Corporate_Employee_Total` varchar(32) DEFAULT NULL,

  `Corporate_Employee_Code` varchar(1) DEFAULT NULL,

  `Corporate_Employee_Description` varchar(15) DEFAULT NULL,

  `Year_Established` varchar(4) DEFAULT NULL,

  `Years_In_Business_Range` varchar(20) DEFAULT NULL,

  `Female_Owned` varchar(1) DEFAULT NULL,

  `Minority_Owned_Flag` varchar(1) DEFAULT NULL,

  `Minority_Type` varchar(25) DEFAULT NULL,

  `Home_Based_Indicator` varchar(1) DEFAULT NULL,

  `Small_Business_Indicator` varchar(1) DEFAULT NULL,

  `Import_Export` varchar(1) DEFAULT NULL,

  `Manufacturing_Location` varchar(1) DEFAULT NULL,

  `Public_Indicator` varchar(1) DEFAULT NULL,

  `EIN` varchar(10) DEFAULT NULL,

  `Non_Profit_Org` varchar(1) DEFAULT NULL,

  `Square_Footage` bigint(32) DEFAULT NULL,

  `Square_Footage_Code` varchar(1) DEFAULT NULL,

  `Square_Footage_Description` varchar(20) DEFAULT NULL,

  `Credit_Score` varchar(32) DEFAULT NULL,

  `Credit_Code` varchar(2) DEFAULT NULL,

  `Credit_Description` varchar(20) DEFAULT NULL,

  `Credit_Capacity` varchar(10) DEFAULT NULL,

  `Credit_Capacity_Code` varchar(1) DEFAULT NULL,

  `Credit_Capacity_Description` varchar(20) DEFAULT NULL,

  `Advertising_Expense_Code` varchar(1) DEFAULT NULL,

  `Advertising_Expense_Description` varchar(20) DEFAULT NULL,

  `Technology_Expense_Code` varchar(1) DEFAULT NULL,

  `Technology_Expense_Description` varchar(20) DEFAULT NULL,

  `Office_Equip_Expenses_Code` varchar(1) DEFAULT NULL,

  `Office_Equip_Expense_Description` varchar(20) DEFAULT NULL,

  `Rent_Expense_Code` varchar(1) DEFAULT NULL,

  `Rent_Expense_Description` varchar(20) DEFAULT NULL,

  `Telecom_Expense_Code` varchar(1) DEFAULT NULL,

  `Telecom_Expense_Description` varchar(20) DEFAULT NULL,

  `Accounting_Expense_Code` varchar(1) DEFAULT NULL,

  `Accounting_Expense_Description` varchar(20) DEFAULT NULL,

  `Business_Insurance_Expenses_Code` varchar(1) DEFAULT NULL,

  `Business_Insurance_Expense_Description` varchar(20) DEFAULT NULL,

  `Legal_Expenses_Code` varchar(1) DEFAULT NULL,

  `Legal_Expense_Description` varchar(20) DEFAULT NULL,

  `Utilities_Expenses_Code` varchar(1) DEFAULT NULL,

 `Utilities_Expense_Description` varchar(20) DEFAULT NULL,

  `Number_Of_PCs_code` varchar(1) DEFAULT NULL,

  `Number_Of_PCs_Description` varchar(15) DEFAULT NULL,

  `NB_Flag` varchar(1) DEFAULT NULL,

  `Holding_Parent_Company_Name` varchar(100) DEFAULT NULL,

  `Holding_Parent_City` varchar(40) DEFAULT NULL,

  `Holding_Parent_State` varchar(2) DEFAULT NULL,

  `Ultimate_Parent_Name` varchar(100) DEFAULT NULL,

  `Ultimate_Parent_City` varchar(40) DEFAULT NULL,

  `Ultimate_Parent_State` varchar(2) DEFAULT NULL,

  `Immediate_Parent_Name` varchar(100) DEFAULT NULL,

  `Immediate_Parent_City` varchar(40) DEFAULT NULL,

  `Immediate_Parent_State` varchar(2) DEFAULT NULL,

  `Domestic_Foreign_Owner_Flag` varchar(1) DEFAULT NULL,

  `Foreign_Parent_Company_Name` varchar(100) DEFAULT NULL,

  `Foreign_Parent_City` varchar(40) DEFAULT NULL,

  `Foreign_Parent_Country` varchar(30) DEFAULT NULL,

  `Hours_Mon` varchar(20) DEFAULT NULL,

  `Hours_Tues` varchar(20) DEFAULT NULL,

  `Hours_Wed` varchar(20) DEFAULT NULL,

  `Hours_Thu` varchar(20) DEFAULT NULL,

  `Hours_Fri` varchar(20) DEFAULT NULL,

  `Hours_Sat` varchar(20) DEFAULT NULL,

  `Hours_Sun` varchar(20) DEFAULT NULL,

  `Source_Data` varchar(11) DEFAULT NULL,

  `Production_Date` varchar(10) DEFAULT NULL

) ENGINE=Columnstore

Now execute following query

MariaDB [test]> SELECT

->

    -> company_name,

->

    -> physical_Address,

->

    -> mailing_Address

->

    -> FROM business_listings

->

    -> WHERE  mailing_Address_State='NY'

->

    -> AND Credit_Score BETWEEN 1 AND 99

->

    -> AND (physical_Address_Zip =mailing_Address_Zip

->

    -> AND physical_Address_State= mailing_Address_State

->

    -> AND physical_Address_City =mailing_Address_City )

->

    -> AND (LOWER(physical_Address)=LOWER(mailing_Address))

->

    -> AND credit_Capacity_Code='A'

->

    -> AND area_Code=305

->

    -> AND Mail_Score IN ('M1','M2','P2A');

Empty set, 1 warning (0.06 sec)

Now take the same query and change the order of where clause column sequence - logically both queries are same, but this second query returns error "combineJobStepsByTable failed."

MariaDB [test]> SELECT

->

    -> company_name,

->

    -> physical_Address,

->

    -> mailing_Address

->

    -> FROM business_listings

->

    -> WHERE  mailing_Address_State='NY'

->

    -> AND (physical_Address_Zip =mailing_Address_Zip

->

    -> AND physical_Address_State= mailing_Address_State

->

    -> AND physical_Address_City =mailing_Address_City )

->

    -> AND (LOWER(physical_Address)=LOWER(mailing_Address))

->

    -> AND Credit_Score BETWEEN 1 AND 99

->

    -> AND credit_Capacity_Code='A'

->

    -> AND area_Code=305

->

    -> AND Mail_Score IN ('M1','M2','P2A');

ERROR 1815 (HY000): Internal error: combineJobStepsByTable failed.

MariaDB [test]>

Ths above needs to be fixes

Further playing with this - following, it is the "Credit_Score BETWEEN 1 AND 99" that gives this problem. - So I rewrote the query again by doing a subquery and pulling this condition in outer query - and this one works

MariaDB [test]> SELECT * from (SELECT  company_name,  physical_Address,  mailing_Address, Credit_Score

    ->    FROM business_listings  WHERE  mailing_Address_State='NY'  AND (physical_Address_Zip =mailing_Address_Zip  AND physical_Address_State= mailing_Address_State  AND physical_Address_City =mailing_Address_City )  AND (LOWER(physical_Address)=LOWER(mailing_Address)) AND credit_Capacity_Code='A'  AND area_Code=305  AND Mail_Score IN ('M1','M2','P2A')) a  WHERE Credit_Score BETWEEN 1 AND 99;

Empty set, 1 warning (0.06 sec)

Attachments

Activity

People

Assignee:: Unassigned

Reporter:: Dipti Joshi (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2018-09-27 19:43

Updated:: 2022-11-05 04:18

Resolved:: 2022-11-05 04:18

Git Integration

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