-- source ../include/have_columnstore.inc

-- disable_warnings
DROP DATABASE IF EXISTS mcol_4505;
-- enable_warnings

CREATE DATABASE mcol_4505;
USE mcol_4505;

CREATE TABLE t1 (lid INT, name CHAR(10)) ENGINE=ColumnStore;
INSERT INTO t1 (lid, name) VALUES (1, 'YES'), (2, 'NO'), (3, 'MAYBE');


CREATE TABLE t2 (id INT, gid INT, lid INT, dt DATE) ENGINE=ColumnStore;
INSERT INTO t2 (id, gid, lid, dt) VALUES
 (1, 1, 1, '2007-01-01'),
 (2, 1, 2, '2007-01-02'),
 (3, 2, 2, '2007-02-01'),
 (4, 2, 1, '2007-02-02'),
 (5, 1, 3, '2007-01-03'),
 (6, 3, 1, '2007-03-01');


-- sorted_result
SELECT DISTINCT t2.gid,
       (SELECT t1.name FROM t1, t2
        WHERE t1.lid = t2.lid AND t2.gid = t1.lid AND t1.lid = 1) AS clid
FROM t2;

# cleanup
DROP DATABASE mcol_4505;