[MCOL-361] table mode 0 and 2 has bad result Created: 2016-10-14  Updated: 2016-11-22  Resolved: 2016-11-22

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

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

CentOS

Sprint: 2016-21, 2016-22

 Description   

Re working_tpch1_compareLogOnly/tableMode/autoswitch.sql

When a query fails in vtable mode table mode 2 tries again using table mode 0 in which Columnstore sends the query using the standard engine mechanism, which is much slower. But it does allow syntax that vtable mode rejects.

The purpose of this test is to test the auto-switch capabilities of vtable mode 2. That part is working. However, the test has uncovered a bug in vtable mode 0.

In this case, the following query works in InfiniDB, but not Columnstore:

<InfiniDB>
mysql> SELECT SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE FROM LINEITEM, PART WHERE ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#41' AND P_CONTAINER IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND L_QUANTITY >= 3 AND L_QUANTITY <= 3 + 1 AND P_SIZE BETWEEN 1 AND 5 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' ) OR ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#33' AND P_CONTAINER IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND L_QUANTITY >= 14 AND L_QUANTITY <= 14 + 1 AND P_SIZE BETWEEN 1 AND 10 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' ) OR ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#45' AND P_CONTAINER IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND L_QUANTITY >= 25 AND L_QUANTITY <= 25 + 1 AND P_SIZE BETWEEN 1 AND 15 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' );
-------------

REVENUE

-------------

607989.6988

-------------
1 row in set, 1 warning (13.89 sec)

mysql> show warnings; -------------------------------------------------------------------------------------------------------------------------------------------------------------------

Level Code Message

-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note 1051 Unknown table '$vtable_1'
Note 1051 Unknown table '$vtable_1'
Warning 9999 The query includes syntax that is not supported by InfiniDB distributed mode. The execution was switched to standard mode with downgraded performance.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)

mysql>

<Columnstore>
MariaDB [tpch1]> SELECT SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE FROM LINEITEM, PART WHERE ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#41' AND P_CONTAINER IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND L_QUANTITY >= 3 AND L_QUANTITY <= 3 + 1 AND P_SIZE BETWEEN 1 AND 5 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' ) OR ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#33' AND P_CONTAINER IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND L_QUANTITY >= 14 AND L_QUANTITY <= 14 + 1 AND P_SIZE BETWEEN 1 AND 10 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' ) OR ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'Brand#45' AND P_CONTAINER IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND L_QUANTITY >= 25 AND L_QUANTITY <= 25 + 1 AND P_SIZE BETWEEN 1 AND 15 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' );
---------

REVENUE

---------

NULL // Should not be NULL
---------
1 row in set, 1 warning (3.69 sec)

MariaDB [tpch1]> show warnings;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Level Code Message

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note 1051 Unknown table 'infinidb_vtable.$vtable_684'
Error 1815 Internal error: IDB-1000: 'lineitem' and 'part' are not joined.
Note 1051 Unknown table 'infinidb_vtable.$vtable_684'
Warning 9999 The query includes syntax that is not supported by MariaDB Columnstore distributed mode. The execution was switched to standard mode with downgraded performance.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 rows in set (0.00 sec)

MariaDB [tpch1]>



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

This test is repeated in tablemode.sql which uses table mode 0 and may be easier to debug in table mode 0

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

All queries that work in table mode 1 (vtable mode) should work in table mode 0 (no vtable). In tablemode.sql, we find the following query that returns the proper answer in table mode 1, yet returns an empty set in table mode 0:

SELECT L_SHIPMODE, SUM( CASE WHEN O_ORDERPRIORITY = '1-URGENT' OR O_ORDERPRIORITY = '2-HIGH' THEN 1 ELSE 0 END) AS HIGH_LINE_COUNT,SUM( CASE WHEN O_ORDERPRIORITY <> '1- URGENT' AND O_ORDERPRIORITY <> '2-HIGH'THEN 1 ELSE 0 END) AS LOW_LINE_COUNT FROM ORDERS,LINEITEM WHERE o_custkey < 100 and O_ORDERKEY = L_ORDERKEY AND L_SHIPMODE IN ('FOB','SHIP') AND L_COMMITDATE < L_RECEIPTDATE AND L_SHIPDATE < L_COMMITDATE AND L_RECEIPTDATE >= date '1994-01-01' AND L_RECEIPTDATE < date '1994-01-01' + interval '1' year GROUP BY L_SHIPMODE ORDER BY L_SHIPMODE;

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

These tests are now commented out in working_tpch1_compareLogOnly/tableMode/autoswitch.sql and tablemode.sql

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

Duplicate of MCOL-91?

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

No. MCOL-361 says that table mode 0 is broken. MCOL-91 says table mode 1 is broken.

Comment by David Hall (Inactive) [ 2016-11-01 ]

When MariaDB server is setting up the IN array, it doesn't copy the strings, but rather keeps pointers to the Strings in the items. Later, in engine processing, we use the c_ptr() function to access the data because Strings are not zero terminated. c_ptr() forces a zero termination. Unfortunately, it also may cause a realloc, which invalidates the pointers in the IN array.

Comment by David Hall (Inactive) [ 2016-11-01 ]

Replaced all uses of c_ptr() in Columnstore of String references from the server. In all cases, we were trying to instantiate or assign a std::string. Use instead std::string::assign(str->ptr(), str->length()). ptr() and length() do not modify the String's data.

Andrew: Please review. For some damn reason I can't put this into review state.

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

Build verified: 1.0.5-1

mcsadmin> getsoft
getsoftwareinfo Tue Nov 22 10:13:49 2016

Name : mariadb-columnstore-platform
Version : 1.0.5
Release : 1
Architecture: x86_64
Install Date: Tue 22 Nov 2016 10:10:12 AM CST
Group : Applications/Databases
Size : 9990112
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 : Mon 21 Nov 2016 07:29:37 PM CST

Verified both vtable modes 0 and 1 returned the results now.

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