[MCOL-1753] Two logically same query - one successful, another errors out on columnstore Created: 2018-09-27  Updated: 2022-11-05  Resolved: 2022-11-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: N/A
Affects Version/s: 1.1.6
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Dipti Joshi (Inactive) Assignee: Unassigned
Resolution: Won't Do Votes: 0
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)



 Comments   
Comment by Dipti Joshi (Inactive) [ 2018-09-27 ]

Example of another query that gives the same error - This time using IN instead of BETWEEN 

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_Capacity_Code='A'  AND area_Code=305  AND Mail_Score IN ('M1','M2','P2A') AND Credit_Score IN (1,99);   
                  
ERROR 1815 (HY000): Internal error: combineJobStepsByTable failed.

Comment by Todd Stoffel (Inactive) [ 2022-11-05 ]

Item is out of date. Closing due to inactivity. If you feel this was done in error please open a new ticket.

Generated at Thu Feb 08 02:31:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.