[MCOL-1372] Support lower_case_table_names=0 for cross engine joins Created: 2018-04-28  Updated: 2023-03-07  Resolved: 2020-11-09

Status: Closed
Project: MariaDB ColumnStore
Component/s: MDB Plugin
Affects Version/s: None
Fix Version/s: 5.5.1

Type: New Feature Priority: Minor
Reporter: Mukesh Prasad Assignee: Todd Stoffel (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Production

Issue Links:
Relates
relates to MCOL-882 ColumnStore Tables can't join with In... Closed
Epic Link: ColumnStore Compatibility Improvements

 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) */



 Comments   
Comment by Andrew Hutchings (Inactive) [ 2018-04-28 ]

Can you please let us know which version of ColumnStore you are using? The cross engine library is very different between 1.0 and 1.1.

Comment by Mukesh Prasad [ 2018-04-29 ]

Hi Andrew,

Currently we are using version Columnstore 1.1.3-1 and mariadb version 10.2.13-MariaDB-log.

We have recently upgrade columstore from 1.1.2-1 to 1.1.3-1

Thanks
Mukesh

Comment by Mukesh Prasad [ 2018-04-30 ]

adding some more points. Right now server setup with

lower_case_file_system=OFF
lower_case_table_names=0

Tables are
CHARSET=utf8

Comment by Mukesh Prasad [ 2018-05-01 ]

One more findings. When we run the given query with Innodb tables with small case of table name then it runs fine other wise give error.

Is it a limitation of Columnstore engine join with innodb small case table name only or
some setting is there for columnstore table/database case sensitivity.

Please help us as we need join columnstore table with innodb table with table name case sensitive only.

Comment by Andrew Hutchings (Inactive) [ 2018-05-01 ]

That would do it (although I would expect the error to be a little different). ColumnStore does not support lower_case_table_names=0. This would be a large architecture change to support it. Having said that this will likely be possible as part of the server convergence project in a couple of major releases time.

For now I will make this a feature request to support lower_case_table_names.

Comment by Andrew Hutchings (Inactive) [ 2018-05-01 ]

Simplified test case for confirmation:

1. Edit my.cnf and set lower_case_table_names=0
2. mcsadmin restart
3.

create table table1 (a int, b int) engine=columnstore;
 
create table TaBlE2 (a int, b int);

4.

select * from table1 join TaBlE2 on table1.a = TaBlE2.a;

Comment by David Hall (Inactive) [ 2020-11-09 ]

This problem goes away since Columnstore now supports case in table names from 5.5+

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