table mode 0 and 2 has bad result

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' );
-------------

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

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

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

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

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
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' );
---------

---------

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

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

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

MariaDB [tpch1]>