#!/usr/bin/env bash
# Tags: no-fasttest

CUR_DIR=$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)
# shellcheck source=../shell_config.sh
. "$CUR_DIR"/../shell_config.sh

DB_PATH=${USER_FILES_PATH}/${CURR_DATABASE}_${RANDOM}_db1
CLICKHOUSE_RANDOM_DATABASE=${CLICKHOUSE_DATABASE}_${RANDOM}

sqlite3 "${DB_PATH}" 'DROP TABLE IF EXISTS t1'
sqlite3 "${DB_PATH}" 'CREATE TABLE t1(c0 INT,c1 INT);'

chmod ugo+w "${DB_PATH}"

${CLICKHOUSE_CLIENT} --query="DROP DATABASE IF EXISTS ${CLICKHOUSE_RANDOM_DATABASE};"
${CLICKHOUSE_CLIENT} --query="CREATE DATABASE IF NOT EXISTS ${CLICKHOUSE_RANDOM_DATABASE};"
${CLICKHOUSE_CLIENT} --query="CREATE TABLE ${CLICKHOUSE_RANDOM_DATABASE}.t0 (c0 Int) ENGINE = Memory();"
${CLICKHOUSE_CLIENT} --query="CREATE TABLE ${CLICKHOUSE_RANDOM_DATABASE}.t1 (c0 Int, c1 Int) ENGINE = SQLite('${DB_PATH}', 't1');";
${CLICKHOUSE_CLIENT} --query="CREATE TABLE ${CLICKHOUSE_RANDOM_DATABASE}.t2 (c0 Int, c1 Int) ENGINE = Memory();";
${CLICKHOUSE_CLIENT} --query="INSERT INTO TABLE ${CLICKHOUSE_RANDOM_DATABASE}.t0 (c0) VALUES (1);"
${CLICKHOUSE_CLIENT} --query="INSERT INTO TABLE ${CLICKHOUSE_RANDOM_DATABASE}.t1 (c0, c1) VALUES (-3, 0), (1, 0), (-2, 0);";
${CLICKHOUSE_CLIENT} --query="INSERT INTO TABLE ${CLICKHOUSE_RANDOM_DATABASE}.t2 (c0, c1) VALUES (-3, 0), (1, 0), (-2, 0);";

${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t0 tx RIGHT JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t1 ty ON ty.c1 = tx.c0 WHERE tx.c0 < 1;"
${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t0 tx RIGHT JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t2 ty ON ty.c1 = tx.c0 WHERE tx.c0 < 1;"
echo "-----"
${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t1 ty FULL JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t0 tx ON tx.c0 = ty.c0 WHERE ty.c1 = 0 OR tx.c0 IS NOT NULL;"
${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t2 ty FULL JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t0 tx ON tx.c0 = ty.c0 WHERE ty.c1 = 0 OR tx.c0 IS NOT NULL;"
echo "-----"
${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t1 a JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t1 b ON a.c0 < b.c0 WHERE a.c1 = 0 AND b.c1 = 0;"
${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t2 a JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t2 b ON a.c0 < b.c0 WHERE a.c1 = 0 AND b.c1 = 0;"
echo "-----"
${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t1 ty INNER JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t0 tx ON tx.c0 + 2 = ty.c0 WHERE ty.c1 = 0;"
${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t2 ty INNER JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t0 tx ON tx.c0 + 2 = ty.c0 WHERE ty.c1 = 0;"
echo "-----"
${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t1 ty FULL OUTER JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t0 tx ON ty.c0 = tx.c0 WHERE ty.c0 IS NOT NULL;"
${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t2 ty FULL OUTER JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t0 tx ON ty.c0 = tx.c0 WHERE ty.c0 IS NOT NULL;"
echo "-----"
${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t0 tx JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t1 ty ON tx.c0 = ty.c0 JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t1 tz ON ty.c0 < tz.c0 WHERE ty.c1 = 0 AND tz.c1 = 0;"
${CLICKHOUSE_CLIENT} --enable-analyzer 1 --query="SELECT count() FROM ${CLICKHOUSE_RANDOM_DATABASE}.t0 tx JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t2 ty ON tx.c0 = ty.c0 JOIN ${CLICKHOUSE_RANDOM_DATABASE}.t2 tz ON ty.c0 < tz.c0 WHERE ty.c1 = 0 AND tz.c1 = 0;"
