This jira will cover a complete fix for this and should also work for the additional queries in MCOL-963

There's a problem with math in the order by clause. removing
round(ss_qty/(coalesce(ws_qty+cs_qty,1)),2)
stops the problem.

Tests will be done to see if math on aggregates is the problem, or if any math causes issues.

The query in question now results in the following error:
ERROR 1815 (HY000): Internal error: Query cannot be processed using operational mode 1 (vtable mode)
MariaDB [tpcds_1]>

Running in mode 0 results in the correct result, but execution time is unacceptable (hours).
MCOL-964 is left open to fix the vtable mode 1 problem, but is not needed for any specific release.

A simpler query that demonstrates the problem:

with ss as
(select ss_item_sk, ss_customer_sk,
sum(ss_quantity) ss_qty
from store_sales
group by ss_item_sk, ss_customer_sk
)

select
ss_item_sk, ss_customer_sk
from ss
order by
ss_item_sk, ss_customer_sk,
round(ss_qty,2)
limit 10;

This even simpler query asserts mysqld in debug (see MDEV-14981)

with ss as
(select ss_item_sk,
sum(ss_quantity) ss_qty
from store_sales
group by ss_item_sk
)

select
ss_item_sk
from ss
order by
ss_item_sk,
round(ss_qty,2)
limit 10;

The logic say that if an order by column isn't already in the select – round(ss_qty,2) is not already selected – then add it to the select as a hidden column and re-run the query. Something breaks when it's a cte.

The bug is caused by a field in the order by that isn't in the select list. Columnstore attempts to re-write and re-run the query. During the rewrite, the CTE is converted to a derived table, which works fine. However, it attempts to prepend the names of the select fields with the database name. This is invalid for derived tables which have no database. the fields couldn't be found by the fully qualified name and it errored.

By changing the place in server (Columnstore only code) where the name is written to not prepend with the database name, it works like a normal derived table.

I have a small amount of trepidation that there is some use case for the database prepend other than for views, but I can't think of one. It passes the standard regression test.

Reproduced the issue in 1.1.2-1
Build verified: Github source 1.1.3-1

root@stretch:~# cat mariadb-columnstore-1.1.3-1-stretch.x86_64.bin.tar.txt
/root/columnstore/mariadb-columnstore-server
commit e5499e513d88a3dfefbe9a356e20a1bceb1bde38
Merge: 99cdb0a4b5 4840a435aa
Author: david hill <david.hill@mariadb.com>
Date: Wed Jan 31 16:53:52 2018 -0600

Merge pull request #92 from mariadb-corporation/MCOL-1152

MCOL-1152: Change the debian package names.

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 446cf74a68eaaa8697a448d8de0327e0459d4341
Merge: c792188a 6f4cfcdc
Author: David.Hall <david.hall@mariadb.com>
Date: Fri Feb 2 13:09:51 2018 -0600

Merge pull request #392 from mariadb-corporation/MCOL-1070

MCOL-1070 Fix exists in view subquery

Both the original query 78, and Mr. Hall's simpler query returned the following error.

ERROR 1815 (HY000): Internal error: Query cannot be processed using operational mode 1 (vtable mode)

The original error was reported in 1.1.0 and the above was put in in 1.1.2-1

Both queries returned result in 1.1.3-1