[MCOL-352] substr() on view returns NULL sometimes Created: 2016-10-10  Updated: 2016-11-11  Resolved: 2016-11-11

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 1.0.3
Fix Version/s: 1.0.5

Type: Bug Priority: Minor
Reporter: David Hall (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS

Sprint: 2016-21, 2016-22

 Description   

This issue exists in InfiniDB.

This issue arose because of an anomoly seen in working_tcph_comparelogonly/partitionOptimization/vTpch22.sql

When substr is called on a view, sometimes it returns NULL

Here we select substr and acct_bal from the table. We get the expected result.
MariaDB [tpch1]> select substr(c_phone, 1, 2) as cntrycode, c_acctbal from customer where substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17') limit 10;
--------------------+

cntrycode c_acctbal

--------------------+

23 121.65
13 794.47
30 7638.57
18 8324.07
23 3396.49
13 3857.34
18 1428.25
13 591.98
13 3332.02
23 9255.67

--------------------+
10 rows in set, 1 warning (0.30 sec)

Here we select substr and acct_bal from the view. We get unexpected NULLs on some of the rows.
MariaDB [tpch1]> select substr(c_phone, 1, 2) as cntrycode, c_acctbal from v_customer where substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17') limit 10;
--------------------+

cntrycode c_acctbal

--------------------+

NULL 121.65
13 794.47
NULL 7638.57
NULL 8324.07
NULL 3396.49
NULL 3857.34
NULL 1428.25
NULL 591.98
NULL 3332.02
23 9255.67

--------------------+
10 rows in set, 1 warning (0.34 sec)

Here we select only substr from the view. We get the expected result.
MariaDB [tpch1]> select substr(c_phone, 1, 2) as cntrycode from v_customer where substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17') limit 10;
-----------

cntrycode

-----------

23
13
30
18
23
13
18
13
13
23

-----------
10 rows in set, 1 warning (0.46 sec)

MariaDB [tpch1]>



 Comments   
Comment by David Hall (Inactive) [ 2016-10-17 ]

May be related to MCOL-82 and MCOL-334

Comment by David Hall (Inactive) [ 2016-10-17 ]

Note that this bug is intermittent. Sometimes it manifests, sometimes it doesn't. It seems to only happen in vtable mode.

The relevant test is working_tpch1_compareLogOnly/partitionOptimization/vTpch22.sql

Comment by David Hall (Inactive) [ 2016-10-17 ]

I renamed vTpch22.sql to vTpch22.sql.fixme. Be sure to name it back when fixed.

Comment by Andrew Hutchings (Inactive) [ 2016-10-25 ]

I'm pretty sure it is the same problem. Scenario appears to be: View is evaluated as a subquery. Derived processing doesn't include any LHS of query so the function applied on the view result is not pushed to PrimProc. The unprocessed result is passed straight back to mysqld which can produce unexpected results.

We appear to need a way (probably in ha_from_sub) to process a function for a column from a subquery. Or push the function down into the subquery.

Comment by David Thompson (Inactive) [ 2016-10-25 ]

Possibly same root cause as MCOL-334

Comment by Andrew Hutchings (Inactive) [ 2016-10-26 ]

Fix ready when we have the branches unlocked

Comment by Andrew Hutchings (Inactive) [ 2016-10-27 ]

Problem was actually due to the union of the CHAR columns becoming a VARCHAR so ExeMgr was expecting the VARCHAR to be NUL terminated (which it wasn't) before executing the function.

Comment by David Hall (Inactive) [ 2016-10-27 ]

Renamed vTpch22.sql.fixme to vTpch22.sql.

Comment by Daniel Lee (Inactive) [ 2016-11-11 ]

Build verified:

mcsadmin> getsoft
getsoftwareinfo Fri Nov 11 16:39:51 2016

Name : mariadb-columnstore-platform
Version : 1.0.5
Release : 1
Architecture: x86_64
Install Date: Fri 11 Nov 2016 03:09:12 PM UTC
Group : Applications/Databases
Size : 9987818
License : Copyright (c) 2016 MariaDB Corporation Ab., all rights reserved; redistributable under the terms of the GPL, see the file COPYING for details.
Signature : (none)
Source RPM : mariadb-columnstore-platform-1.0.5-1.src.rpm
Build Date : Thu 10 Nov 2016 04:31:29 PM UTC
Build Host : centos7

The two queries in the bug description returned different rows, most likely due to the ordering of the rows returned and the limit 10 clause. Without using the limit 10 clause, lots of rows returned and none of them had NULL in the first column. Just to make sure the return results match are matching, I executed the following queries, one on actual table and the other on the view.

MariaDB [tpch10c]> select distinct cntrycode, sum(c_acctbal) from (select substr(c_phone, 1, 2) as cntrycode, c_acctbal from customer where substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17') order by cntrycode, c_acctbal) a order by cntrycode;
-------------------------+

cntrycode sum(c_acctbal)

-------------------------+

13 269347303.31
17 269991779.89
18 271302483.04
23 269863231.63
29 269191526.59
30 269676520.58
31 269774378.34

-------------------------+
7 rows in set (0.74 sec)

MariaDB [tpch10c]> select distinct cntrycode, sum(c_acctbal) from (select substr(c_phone, 1, 2) as cntrycode, c_acctbal from v_customer where substr(c_phone, 1, 2) in ('13', '31', '23', '29', '30', '18', '17') order by cntrycode, c_acctbal) a order by cntrycode;
-------------------------+

cntrycode sum(c_acctbal)

-------------------------+

13 269347303.31
17 269991779.89
18 271302483.04
23 269863231.63
29 269191526.59
30 269676520.58
31 269774378.34

-------------------------+
7 rows in set (0.75 sec)

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