Details
-
New Feature
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
None
-
None
-
Production
Description
UPDATE from LinuxJedi: The problem is lower_case_table_names=0 which allows for mixed case names. This causes problems for ColumnStore as everything is parsed as lower case so cross engine joins request a lower case table name instead of mixed case.
Let me give you some input. we ran a query having three tables with left join, where one table is columnstore engine while others in innodb.
Table_Schema:
1. SMSMemberGrowthAgg having records ~ 542878 which in future will increased
CREATE TABLE `SMSMemberGrowthAgg` (
|
`AggregatedOn` datetime DEFAULT NULL,
|
`StoreCode` varchar(100) COLLATE DEFAULT NULL,
|
`ListId` bigint(20) DEFAULT NULL,
|
`InputSource` varchar(100) COLLATE DEFAULT NULL,
|
`BeginningCount` int(11) DEFAULT NULL,
|
`NewMemberCount` int(11) DEFAULT NULL,
|
`OptedOutCount` int(11) DEFAULT NULL,
|
`SixMonthCount` int(11) DEFAULT NULL
|
) ENGINE=Columnstore;
|
2. List having records ~313 records
CREATE TABLE `List` (
|
`ListId` BIGINT(20) NOT NULL AUTO_INCREMENT,
|
`TenantId` INT(11) NOT NULL,
|
`Name` VARCHAR(200) NOT NULL,
|
`Description` LONGTEXT NULL DEFAULT NULL,
|
`ChannelTypeId` INT(11) NULL DEFAULT NULL,
|
`DefaultFromName` VARCHAR(2000) NULL DEFAULT NULL,
|
`DefaultFromAddress` VARCHAR(1000) NULL DEFAULT NULL,
|
`DefaultReplyTo` VARCHAR(1000) NULL DEFAULT NULL,
|
`DefaultSubject` VARCHAR(2000) NULL DEFAULT NULL,
|
`LastUpdated` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
|
`Created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
|
`DisplayName` BIT(1) NOT NULL DEFAULT b'1',
|
`LockDefaultFromName` BIT(1) NOT NULL DEFAULT b'0',
|
`LockDefaultFromAddress` BIT(1) NOT NULL DEFAULT b'0',
|
`LockDefaultReplyTo` BIT(1) NOT NULL DEFAULT b'0',
|
`LockDefaultSubject` BIT(1) NOT NULL DEFAULT b'0',
|
`SubscribedByDefault` BIT(1) NOT NULL DEFAULT b'1',
|
`Active` BIT(1) NOT NULL DEFAULT b'1',
|
`DataEntryListID` VARCHAR(20) NULL DEFAULT '',
|
`LastPushedCount` INT(11) NULL DEFAULT NULL,
|
`DisplayDescription` BIT(1) NOT NULL DEFAULT b'1',
|
`DisplayPublic` BIT(1) NULL DEFAULT NULL,
|
`LastActivity` DATETIME NULL DEFAULT NULL,
|
`CreatedBy` VARCHAR(250) NULL DEFAULT '',
|
`Deleted` BIT(1) NOT NULL DEFAULT b'0',
|
PRIMARY KEY (`ListId`),
|
INDEX `IX_ChannelTypeId` (`ChannelTypeId`, `Active`, `Deleted`),
|
INDEX `IX_Name` (`Name`(20))
|
)
|
COLLATE='utf8_general_ci'
|
ENGINE=InnoDB;
|
3. Group having records ~ 127
CREATE TABLE `Group` (
|
`GroupId` INT(11) NOT NULL AUTO_INCREMENT,
|
`TenantId` INT(11) NOT NULL,
|
`CategoryId` INT(11) NOT NULL,
|
`GroupName` VARCHAR(80) NULL DEFAULT '',
|
`Created` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
|
`LastUpdated` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`GroupId`),
|
INDEX `Fk_Group_Category` (`CategoryId`),
|
CONSTRAINT `Fk_Group_Category` FOREIGN KEY (`CategoryId`) REFERENCES `Category` (`CategoryId`)
|
)
|
COLLATE='utf8_general_ci'
|
ENGINE=InnoDB;
|
--------------------------------------------------------------------------------------------------------
When we run this query -
set infinidb_vtable_mode = 1;
|
SELECT
|
"UNID" AS GroupName,
|
S.StoreCode,
|
S.StoreName,
|
IFNULL(L.Name, 'No List') AS ListName,
|
Agg.InputSource,
|
SUM(
|
IFNULL(
|
CASE
|
WHEN DATE(Agg.AggregatedOn) = DATE('2018-01-01 00:00:00.000')
|
THEN Agg.BeginningCount
|
ELSE 0
|
END,
|
0
|
)
|
) AS BeginningCount,
|
SUM(IFNULL(Agg.NewMemberCount, 0)) AS NewMemberCount,
|
SUM(IFNULL(Agg.OptedOutCount, 0)) AS OptedOutCount,
|
SUM(
|
IFNULL(
|
CASE
|
WHEN DATE(Agg.AggregatedOn) = DATE('2018-01-01 00:00:00.000')
|
THEN Agg.BeginningCount
|
ELSE 0
|
END,
|
0
|
)
|
) + SUM(IFNULL(Agg.NewMemberCount, 0)) - SUM(IFNULL(Agg.OptedOutCount, 0)) AS EndingMemberCount,
|
ROUND(
|
(
|
(
|
SUM(
|
IFNULL(
|
CASE
|
WHEN DATE(Agg.AggregatedOn) = DATE('2018-01-01 00:00:00.000')
|
THEN Agg.BeginningCount
|
ELSE 0
|
END,
|
0
|
)
|
) + SUM(IFNULL(Agg.NewMemberCount, 0)) - SUM(IFNULL(Agg.OptedOutCount, 0)) - SUM(IFNULL(Agg.SixMonthCount, 0))
|
) / (SUM(IFNULL(Agg.SixMonthCount, 0)))
|
) * 100,
|
2
|
) AS SixMonthGrowth,
|
ROUND(
|
(
|
(
|
SUM(IFNULL(Agg.NewMemberCount, 0)) - SUM(IFNULL(Agg.OptedOutCount, 0))
|
) / (
|
SUM(
|
IFNULL(
|
CASE
|
WHEN DATE(Agg.AggregatedOn) = DATE('2018-01-01 00:00:00.000')
|
THEN Agg.BeginningCount
|
ELSE 0
|
END,
|
0
|
)
|
)
|
)
|
) * 100,
|
2
|
) AS PeriodGrowth
|
FROM
|
SMSMemberGrowthAgg Agg -- force index(IX_AggregatedOn)
|
LEFT JOIN `List` L
|
ON L.ListId = Agg.ListId
|
LEFT JOIN Store S
|
ON Agg.StoreCode = S.StoreCode
|
WHERE Agg.AggregatedOn BETWEEN '2018-01-01 00:00:00.000'
|
AND '2018-04-24 00:00:00.000'
|
GROUP BY S.StoreCode,S.StoreName,ListName,Agg.InputSource;
|
-----------------------------------------------------------------------------------------------------------------
We are getting an error:
/* SQL Error (1815): Internal error: fatal error reading result from crossengine client lib(4294967295)(null pointer) */
|
Attachments
Issue Links
- relates to
-
MCOL-882 ColumnStore Tables can't join with InnoDB tables that contain uppercase characters
-
- Closed
-