Performing a CTE on a table when the MariaDB client is not in a database (ex- USE test; if the table(s) being queried reside in `test`- this only occurs when using test.table syntax to refer to a table) causes the query to return ERROR 2013 (HY000): Lost connection to MySQL server during query. The same query performed when a client can refer to just the table as the client is already in the database works without issue.

Setup

CREATE DATABASE tester;

USE tester;

CREATE TABLE `foo` (

  bar int(11) NOT NULL,

  boo varchar(2) NOT NULL

) ENGINE=Columnstore DEFAULT CHARSET=latin1;

exit;

Failure Case Example

Make sure you are not in any database (make sure no USE command has been issued and the client connection did not specify a target database), run the following-

WITH Window AS (SELECT bar, boo FROM tester.foo) SELECT * FROM Window;

Note this is not exclusive to SELECT * and adding a WHERE clause inside the CTE or outside does not improve matters.

Success Case Examples

USE tester;

WITH Window AS (SELECT bar, boo FROM foo) SELECT * FROM Window;

USE mysql;

WITH Window AS (SELECT bar, boo FROM tester.foo) SELECT * FROM Window;

Note the specific database used does not matter, as long as a database is selected this seems to work fine.

Data is Irrelevant

Also note that data appears to be irrelevant. In the test cases given here, no data is present in the tables. For example-

USE tester;

INSERT INTO foo VALUES (10,'a');

INSERT INTO foo VALUES (11,'b');

SELECT * FROM foo;

WITH Window AS (SELECT bar, boo FROM foo) SELECT * FROM Window;

exit;

mysql;

WITH Window AS (SELECT bar, boo FROM tester.foo) SELECT * FROM Window;

Data is correctly returned except when issuing the query without being inside a database.