Problem is in WE_DMLCommandProc::processSingleInsert:

origVals = columnPtr->get_DataVector();

...

tmpStr = origVals[i];

if ( tmpStr.length() == 0 )

        isNULL = true;

else

        isNULL = false;

So an empty string would be set to NULL.

Written a 16KB patch so far on this basically implementing NULL support at every stage throughout a DML query. Still some work to do in the WriteEngine area and possible data reading to differentiate between empty strings and NULL data.

I believe cpimport also has code that treats empty string as NULL, and perhaps the bridge between cpimport and the bulk loading interface too.

Progress so far in this patch: https://github.com/LinuxJedi/mariadb-columnstore-engine/commit/0434612514f851e459595924c428af30f7914118

MCOL-171 is fixed

MCOL-271 is mostly fixed. IS NULL/NOT NULL comparisons aren't yet working due to some issue in PrimProc that I haven't managed to fully diagnose yet.

On hold for now. I have to make heavy changes to the write engine to make this work.

I now think this should be fixed as part of MCOL-92. If we have a separate container for NULL entries in columns (and maybe other potential bitmasks?) the problem with int ranges goes away and it can be used with character type columns too.

I think that, not having special NULL bitmap as a part of column, it is possible to use something akin to ```boost::option<string>``` (not actual boost type, but something alike) where empty value represents NULL and one has to obtain a reference to string contained by checking the non-emptiness.

Type checking can be leveraged to find out all places where this replacement has to be done.

I'll discuss that with Roman and, probably, Gagan further. Most probably, Gagan will help with advices on how he needs it to be done in his work.

Relevant branch: https://github.com/mariadb-SergeyZefirov/mariadb-columnstore-engine/tree/MCOL-271-empty-strings-should-not-be-nulls

Here are some more NULL values:

        if (((colType.colDataType == execplan::CalpontSystemCatalog::DATE) && (inData == "0000-00-00")) ||

            ((colType.colDataType == execplan::CalpontSystemCatalog::DATETIME) &&

             (inData == "0000-00-00 00:00:00")) ||

            ((colType.colDataType == execplan::CalpontSystemCatalog::TIMESTAMP) &&

             (inData == "0000-00-00 00:00:00")))

        {

          isNull = true;

        }

(writeengine/server/we_dmlcommandproc.cpp)

I haven't found anything else that is also implicitly converted to NULL.

sergey.zefirov drrtuy I want to share some related experience we had in IBI in the area.
We also started with having '.' string treated as NULL.

It was major project to support the correct approach . We broke it into 2 major stages:

1. Correct the "object orientation" by removing direct access to control blocks and encapsulating access into functions
2. change the actual function from using '.' to using a special bit

Of course stage 1 was 99% of the work and we kept merging into main branch without changing overall desired behavior. This allowed immediate daily testing by QA and the rest of team.

once stage 1 was done we did an additional step of having a system-wide setting that was used in each of the encapsulated functions, that allowed to switch back/forth on the NULL implementation.

we tested with old approach being default for some time before changing the default to new.

This may or may not be relevant to us here .

av

Tests that fail to load data:

1. mcs103_ldi_fields_enclosed_by
2. mcs102_lds_transform_csv
3. mcs28_load_data_local_infile
4. mcs29_load_data_local_infile_negative
5. unsigned_joins

There are 23 tests that call test01_distinct procedure from ctype_cmp_create.inc. Some of them pass, but majority fail. Judging from the output, the problem is in use of GROUP_CONCAT in the test01_distinct procedure. There are 6 other tests that use GROUP_CONCAT and also fail.

I see no other "hot spots" here.

sergey.zefirov
You say "only new are used" . Should we remove unused code ?

No new problems observed.

The list to fix:

Working Folder Test scripts that failed:

Compare failed - working_ssb_compareLogOnly/ssb_dim_mixed_outer_2colgrp/ssb_dim_mixed_outer_2colgrp3.sql

Compare failed - working_tpch1/aggregation/bitop.sql

Build verified:

engine: 1e56a0b557efb677d07533d05eb02ad723955317
server: 11c83d9ae9eb249d00589cc6ab71e7f4e67ffa27
buildNo: 7534

Testing the following areas:

INSERT
UPDATE
LDI, not using cpimport
LDI, using cpimport
cpimport, with and without -n
queries, with IS NULL and IS NOT NULL
subquery, with IS NULL and IS NOT NULL
sql_mode, with and without EMPTY_STRING_IS_NULL;
NULL values and '' (empty) strings
ETC

There is a MTR performance issue for the develop branch as of 04/24 night. According to the developer, MTR tests passed on the MCOL-271 commit.

Two tickets have been opened.
MCOL-5480 LDI loads values incorrectly for MEDIUMINT, TIME and TIMESTAMP when cpimport is used for batch insert
The issue is not caused by this fix. It also exists in release 23.02.2. For completeness, we should fix this issue, as part of MCOL-271.

MCOL-5483 For UPDATE operation, row count for 'Changed: ' returned is incorrect
Not release to this fix.