parSelectTest.cpp 10.9 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
/*
 * Copyright (c) 2019 TAOS Data, Inc. <jhtao@taosdata.com>
 *
 * This program is free software: you can use, redistribute, and/or modify
 * it under the terms of the GNU Affero General Public License, version 3
 * or later ("AGPL"), as published by the Free Software Foundation.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 * FITNESS FOR A PARTICULAR PURPOSE.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */

#include "parTestUtil.h"

using namespace std;

namespace ParserTest {

class ParserSelectTest : public ParserTestBase {};

TEST_F(ParserSelectTest, basic) {
  useDb("root", "test");

X
Xiaoyu Wang 已提交
27
  run("SELECT * FROM t1");
28

X
Xiaoyu Wang 已提交
29
  run("SELECT * FROM test.t1");
30

X
Xiaoyu Wang 已提交
31
  run("SELECT ts, c1 FROM t1");
32

X
Xiaoyu Wang 已提交
33
  run("SELECT ts, t.c1 FROM (SELECT * FROM t1) t");
34

X
Xiaoyu Wang 已提交
35
  run("SELECT * FROM t1 tt1, t1 tt2 WHERE tt1.c1 = tt2.c1");
X
Xiaoyu Wang 已提交
36 37

  run("SELECT * FROM st1");
38 39 40 41 42
}

TEST_F(ParserSelectTest, constant) {
  useDb("root", "test");

X
Xiaoyu Wang 已提交
43
  run("SELECT 123, 20.4, 'abc', \"wxy\", timestamp '2022-02-09 17:30:20', true, false, 10s FROM t1");
44

X
Xiaoyu Wang 已提交
45 46
  run("SELECT 1234567890123456789012345678901234567890, 20.1234567890123456789012345678901234567890, 'abc', \"wxy\", "
      "timestamp '2022-02-09 17:30:20', true, false, 15s FROM t1");
47

X
Xiaoyu Wang 已提交
48
  run("SELECT 123 + 45 FROM t1 WHERE 2 - 1");
49 50

  run("SELECT * FROM t1 WHERE -2");
51 52 53 54 55
}

TEST_F(ParserSelectTest, expression) {
  useDb("root", "test");

X
Xiaoyu Wang 已提交
56
  run("SELECT ts + 10s, c1 + 10, concat(c2, 'abc') FROM t1");
57

X
Xiaoyu Wang 已提交
58
  run("SELECT ts > 0, c1 < 20 and c2 = 'qaz' FROM t1");
59

X
Xiaoyu Wang 已提交
60
  run("SELECT ts > 0, c1 between 10 and 20 and c2 = 'qaz' FROM t1");
61 62 63 64 65
}

TEST_F(ParserSelectTest, condition) {
  useDb("root", "test");

X
Xiaoyu Wang 已提交
66
  run("SELECT c1 FROM t1 WHERE ts in (true, false)");
67

X
Xiaoyu Wang 已提交
68
  run("SELECT * FROM t1 WHERE c1 > 10 and c1 is not null");
69 70 71 72 73
}

TEST_F(ParserSelectTest, pseudoColumn) {
  useDb("root", "test");

74
  run("SELECT _WSTARTTS, _WENDTS, COUNT(*) FROM t1 INTERVAL(10s)");
75 76
}

77 78 79
TEST_F(ParserSelectTest, pseudoColumnSemanticCheck) {
  useDb("root", "test");

80
  run("SELECT TBNAME FROM (SELECT * FROM st1s1)", TSDB_CODE_PAR_INVALID_TBNAME, PARSER_STAGE_TRANSLATE);
81 82
}

83 84 85 86 87 88
TEST_F(ParserSelectTest, aggFunc) {
  useDb("root", "test");

  run("SELECT LEASTSQUARES(c1, -1, 1) FROM t1");
}

89 90 91
TEST_F(ParserSelectTest, multiResFunc) {
  useDb("root", "test");

92
  run("SELECT LAST(*), FIRST(*), LAST_ROW(*) FROM t1");
93

94
  run("SELECT LAST(c1, c2), FIRST(t1.*), LAST_ROW(c3) FROM t1");
95

96
  run("SELECT LAST(t2.*), FIRST(t1.c1, t2.*), LAST_ROW(t1.*, t2.*) FROM st1s1 t1, st1s2 t2 WHERE t1.ts = t2.ts");
97 98
}

X
Xiaoyu Wang 已提交
99 100 101
TEST_F(ParserSelectTest, timelineFunc) {
  useDb("root", "test");

102
  run("SELECT LAST(*), FIRST(*) FROM t1");
X
Xiaoyu Wang 已提交
103

104
  run("SELECT FIRST(ts), FIRST(c1), FIRST(c2), FIRST(c3) FROM t1");
X
Xiaoyu Wang 已提交
105

106 107 108
  run("SELECT LAST(*), FIRST(*) FROM t1 GROUP BY c1");

  run("SELECT LAST(*), FIRST(*) FROM t1 INTERVAL(10s)");
X
Xiaoyu Wang 已提交
109

X
Xiaoyu Wang 已提交
110
  run("SELECT diff(c1) FROM t1");
X
Xiaoyu Wang 已提交
111 112
}

113 114
TEST_F(ParserSelectTest, selectFunc) {
  useDb("root", "test");
115

116 117 118 119 120 121 122 123 124
  // select function
  run("SELECT MAX(c1), MIN(c1) FROM t1");
  // select function for GROUP BY clause
  run("SELECT MAX(c1), MIN(c1) FROM t1 GROUP BY c1");
  // select function for INTERVAL clause
  run("SELECT MAX(c1), MIN(c1) FROM t1 INTERVAL(10s)");
  // select function along with the columns of select row
  run("SELECT MAX(c1), c2 FROM t1");
  run("SELECT MAX(c1), t1.* FROM t1");
125 126 127 128 129 130 131
  // select function along with the columns of select row, and with GROUP BY clause
  run("SELECT MAX(c1), c2 FROM t1 GROUP BY c3");
  run("SELECT MAX(c1), t1.* FROM t1 GROUP BY c3");
  // select function along with the columns of select row, and with window clause
  run("SELECT MAX(c1), c2 FROM t1 INTERVAL(10s)");
  run("SELECT MAX(c1), c2 FROM t1 SESSION(ts, 10s)");
  run("SELECT MAX(c1), c2 FROM t1 STATE_WINDOW(c3)");
132 133
}

X
Xiaoyu Wang 已提交
134
TEST_F(ParserSelectTest, IndefiniteRowsFunc) {
135 136 137 138 139
  useDb("root", "test");

  run("SELECT DIFF(c1) FROM t1");
}

X
Xiaoyu Wang 已提交
140
TEST_F(ParserSelectTest, IndefiniteRowsFuncSemanticCheck) {
141 142 143 144 145 146 147 148 149
  useDb("root", "test");

  run("SELECT DIFF(c1), c2 FROM t1", TSDB_CODE_PAR_NOT_ALLOWED_FUNC, PARSER_STAGE_TRANSLATE);

  run("SELECT DIFF(c1), tbname FROM t1", TSDB_CODE_PAR_NOT_ALLOWED_FUNC, PARSER_STAGE_TRANSLATE);

  run("SELECT DIFF(c1), count(*) FROM t1", TSDB_CODE_PAR_NOT_ALLOWED_FUNC, PARSER_STAGE_TRANSLATE);

  run("SELECT DIFF(c1), CSUM(c1) FROM t1", TSDB_CODE_PAR_NOT_ALLOWED_FUNC, PARSER_STAGE_TRANSLATE);
150 151

  // run("SELECT DIFF(c1) FROM t1 INTERVAL(10s)");
152 153
}

154 155 156 157 158 159 160 161
TEST_F(ParserSelectTest, useDefinedFunc) {
  useDb("root", "test");

  run("SELECT udf1(c1) FROM t1");

  run("SELECT udf2(c1) FROM t1 GROUP BY c2");
}

162
TEST_F(ParserSelectTest, groupBy) {
163 164
  useDb("root", "test");

X
Xiaoyu Wang 已提交
165
  run("SELECT COUNT(*) cnt FROM t1 WHERE c1 > 0");
166

167
  run("SELECT COUNT(*), c2 cnt FROM t1 WHERE c1 > 0 GROUP BY c2");
168

169
  run("SELECT COUNT(*) cnt FROM t1 WHERE c1 > 0 GROUP BY c2 having COUNT(c1) > 10");
170

171
  run("SELECT COUNT(*), c1, c2 + 10, c1 + c2 cnt FROM t1 WHERE c1 > 0 GROUP BY c2, c1");
172

173
  run("SELECT COUNT(*), c1 + 10, c2 cnt FROM t1 WHERE c1 > 0 GROUP BY c1 + 10, c2");
174 175 176 177
}

TEST_F(ParserSelectTest, orderBy) {
  useDb("root", "test");
178

179
  run("SELECT COUNT(*) cnt FROM t1 WHERE c1 > 0 GROUP BY c2 order by cnt");
180

181
  run("SELECT COUNT(*) cnt FROM t1 WHERE c1 > 0 GROUP BY c2 order by 1");
182 183 184 185
}

TEST_F(ParserSelectTest, distinct) {
  useDb("root", "test");
186

X
Xiaoyu Wang 已提交
187
  // run("SELECT distinct c1, c2 FROM t1 WHERE c1 > 0 order by c1");
188

X
Xiaoyu Wang 已提交
189
  // run("SELECT distinct c1 + 10, c2 FROM t1 WHERE c1 > 0 order by c1 + 10, c2");
190

X
Xiaoyu Wang 已提交
191
  // run("SELECT distinct c1 + 10 cc1, c2 cc2 FROM t1 WHERE c1 > 0 order by cc1, c2");
192

193
  // run("SELECT distinct COUNT(c2) FROM t1 WHERE c1 > 0 GROUP BY c1 order by COUNT(c2)");
194 195
}

X
Xiaoyu Wang 已提交
196 197 198 199
// INTERVAL(interval_val [, interval_offset]) [SLIDING (sliding_val)] [FILL(fill_mod_and_val)]
// fill_mod_and_val = { NONE | PREV | NULL | LINEAR | NEXT | value_mod }
// value_mod = VALUE , val ...
TEST_F(ParserSelectTest, interval) {
200
  useDb("root", "test");
X
Xiaoyu Wang 已提交
201 202 203 204 205 206 207
  // INTERVAL(interval_val)
  run("SELECT COUNT(*) FROM t1 INTERVAL(10s)");
  // INTERVAL(interval_val, interval_offset)
  run("SELECT COUNT(*) FROM t1 INTERVAL(10s, 5s)");
  // INTERVAL(interval_val, interval_offset) SLIDING (sliding_val)
  run("SELECT COUNT(*) FROM t1 INTERVAL(10s, 5s) SLIDING(7s)");
  // INTERVAL(interval_val) FILL(NONE)
X
Xiaoyu Wang 已提交
208 209
  run("SELECT COUNT(*) FROM t1 WHERE ts > TIMESTAMP '2022-04-01 00:00:00' and ts < TIMESTAMP '2022-04-30 23:59:59' "
      "INTERVAL(10s) FILL(NONE)");
210 211
}

212 213 214
TEST_F(ParserSelectTest, intervalSemanticCheck) {
  useDb("root", "test");

215
  run("SELECT c1 FROM t1 INTERVAL(10s)", TSDB_CODE_PAR_NOT_SINGLE_GROUP, PARSER_STAGE_TRANSLATE);
216 217
  run("SELECT DISTINCT c1, c2 FROM t1 WHERE c1 > 3 INTERVAL(1d) FILL(NEXT)", TSDB_CODE_PAR_INVALID_FILL_TIME_RANGE,
      PARSER_STAGE_TRANSLATE);
218 219
}

220 221 222 223 224 225 226 227 228 229 230 231
TEST_F(ParserSelectTest, subquery) {
  useDb("root", "test");

  run("SELECT SUM(a) FROM (SELECT MAX(c1) a, ts FROM st1s1 INTERVAL(1m)) INTERVAL(1n)");

  run("SELECT SUM(a) FROM (SELECT MAX(c1) a, _wstartts FROM st1s1 INTERVAL(1m)) INTERVAL(1n)");

  run("SELECT SUM(a) FROM (SELECT MAX(c1) a, ts FROM st1s1 PARTITION BY TBNAME INTERVAL(1m)) INTERVAL(1n)");

  run("SELECT SUM(a) FROM (SELECT MAX(c1) a, _wstartts FROM st1s1 PARTITION BY TBNAME INTERVAL(1m)) INTERVAL(1n)");
}

X
Xiaoyu Wang 已提交
232
TEST_F(ParserSelectTest, subquerySemanticCheck) {
233 234 235 236 237 238
  useDb("root", "test");

  run("SELECT SUM(a) FROM (SELECT MAX(c1) a FROM st1s1 INTERVAL(1m)) INTERVAL(1n)", TSDB_CODE_PAR_NOT_ALLOWED_WIN_QUERY,
      PARSER_STAGE_TRANSLATE);
}

X
Xiaoyu Wang 已提交
239
TEST_F(ParserSelectTest, semanticCheck) {
240 241 242
  useDb("root", "test");

  // TSDB_CODE_PAR_INVALID_COLUMN
X
Xiaoyu Wang 已提交
243
  run("SELECT c1, cc1 FROM t1", TSDB_CODE_PAR_INVALID_COLUMN, PARSER_STAGE_TRANSLATE);
244

X
Xiaoyu Wang 已提交
245
  run("SELECT t1.c1, t1.cc1 FROM t1", TSDB_CODE_PAR_INVALID_COLUMN, PARSER_STAGE_TRANSLATE);
246 247

  // TSDB_CODE_PAR_TABLE_NOT_EXIST
X
Xiaoyu Wang 已提交
248
  run("SELECT * FROM t10", TSDB_CODE_PAR_TABLE_NOT_EXIST, PARSER_STAGE_TRANSLATE);
249

X
Xiaoyu Wang 已提交
250
  run("SELECT * FROM test.t10", TSDB_CODE_PAR_TABLE_NOT_EXIST, PARSER_STAGE_TRANSLATE);
251

X
Xiaoyu Wang 已提交
252
  run("SELECT t2.c1 FROM t1", TSDB_CODE_PAR_TABLE_NOT_EXIST, PARSER_STAGE_TRANSLATE);
253 254

  // TSDB_CODE_PAR_AMBIGUOUS_COLUMN
X
Xiaoyu Wang 已提交
255
  run("SELECT c2 FROM t1 tt1, t1 tt2 WHERE tt1.c1 = tt2.c1", TSDB_CODE_PAR_AMBIGUOUS_COLUMN, PARSER_STAGE_TRANSLATE);
256

257 258
  run("SELECT c2 FROM (SELECT c1 c2, c2 FROM t1)", TSDB_CODE_PAR_AMBIGUOUS_COLUMN, PARSER_STAGE_TRANSLATE);

259
  // TSDB_CODE_PAR_WRONG_VALUE_TYPE
260
  run("SELECT timestamp '2010a' FROM t1", TSDB_CODE_PAR_WRONG_VALUE_TYPE, PARSER_STAGE_TRANSLATE);
261

262 263 264 265
  run("SELECT LAST(*) + SUM(c1) FROM t1", TSDB_CODE_PAR_WRONG_VALUE_TYPE, PARSER_STAGE_TRANSLATE);

  run("SELECT CEIL(LAST(ts, c1)) FROM t1", TSDB_CODE_PAR_WRONG_VALUE_TYPE, PARSER_STAGE_TRANSLATE);

266
  // TSDB_CODE_PAR_ILLEGAL_USE_AGG_FUNCTION
X
Xiaoyu Wang 已提交
267
  run("SELECT c2 FROM t1 tt1 join t1 tt2 on COUNT(*) > 0", TSDB_CODE_PAR_ILLEGAL_USE_AGG_FUNCTION,
268 269
      PARSER_STAGE_TRANSLATE);

X
Xiaoyu Wang 已提交
270
  run("SELECT c2 FROM t1 WHERE COUNT(*) > 0", TSDB_CODE_PAR_ILLEGAL_USE_AGG_FUNCTION, PARSER_STAGE_TRANSLATE);
271

272
  run("SELECT c2 FROM t1 GROUP BY COUNT(*)", TSDB_CODE_PAR_ILLEGAL_USE_AGG_FUNCTION, PARSER_STAGE_TRANSLATE);
273 274

  // TSDB_CODE_PAR_WRONG_NUMBER_OF_SELECT
X
Xiaoyu Wang 已提交
275
  run("SELECT c2 FROM t1 order by 0", TSDB_CODE_PAR_WRONG_NUMBER_OF_SELECT, PARSER_STAGE_TRANSLATE);
276

X
Xiaoyu Wang 已提交
277
  run("SELECT c2 FROM t1 order by 2", TSDB_CODE_PAR_WRONG_NUMBER_OF_SELECT, PARSER_STAGE_TRANSLATE);
278 279

  // TSDB_CODE_PAR_GROUPBY_LACK_EXPRESSION
X
Xiaoyu Wang 已提交
280
  run("SELECT COUNT(*) cnt FROM t1 having c1 > 0", TSDB_CODE_PAR_GROUPBY_LACK_EXPRESSION, PARSER_STAGE_TRANSLATE);
281

282
  run("SELECT COUNT(*) cnt FROM t1 GROUP BY c2 having c1 > 0", TSDB_CODE_PAR_GROUPBY_LACK_EXPRESSION,
283 284
      PARSER_STAGE_TRANSLATE);

285
  run("SELECT COUNT(*), c1 cnt FROM t1 GROUP BY c2 having c2 > 0", TSDB_CODE_PAR_GROUPBY_LACK_EXPRESSION,
286 287
      PARSER_STAGE_TRANSLATE);

288
  run("SELECT COUNT(*) cnt FROM t1 GROUP BY c2 having c2 > 0 order by c1", TSDB_CODE_PAR_GROUPBY_LACK_EXPRESSION,
289 290 291
      PARSER_STAGE_TRANSLATE);

  // TSDB_CODE_PAR_NOT_SINGLE_GROUP
X
Xiaoyu Wang 已提交
292
  run("SELECT COUNT(*), c1 FROM t1", TSDB_CODE_PAR_NOT_SINGLE_GROUP, PARSER_STAGE_TRANSLATE);
293

X
Xiaoyu Wang 已提交
294
  run("SELECT COUNT(*) FROM t1 order by c1", TSDB_CODE_PAR_NOT_SINGLE_GROUP, PARSER_STAGE_TRANSLATE);
295

X
Xiaoyu Wang 已提交
296
  run("SELECT c1 FROM t1 order by COUNT(*)", TSDB_CODE_PAR_NOT_SINGLE_GROUP, PARSER_STAGE_TRANSLATE);
297 298

  // TSDB_CODE_PAR_NOT_SELECTED_EXPRESSION
X
Xiaoyu Wang 已提交
299
  run("SELECT distinct c1, c2 FROM t1 WHERE c1 > 0 order by ts", TSDB_CODE_PAR_NOT_SELECTED_EXPRESSION,
300 301
      PARSER_STAGE_TRANSLATE);

X
Xiaoyu Wang 已提交
302
  run("SELECT distinct c1 FROM t1 WHERE c1 > 0 order by COUNT(c2)", TSDB_CODE_PAR_NOT_SELECTED_EXPRESSION,
303 304
      PARSER_STAGE_TRANSLATE);

X
Xiaoyu Wang 已提交
305
  run("SELECT distinct c2 FROM t1 WHERE c1 > 0 order by COUNT(c2)", TSDB_CODE_PAR_NOT_SELECTED_EXPRESSION,
306 307 308
      PARSER_STAGE_TRANSLATE);
}

X
Xiaoyu Wang 已提交
309 310 311
TEST_F(ParserSelectTest, setOperator) {
  useDb("root", "test");

X
Xiaoyu Wang 已提交
312
  run("SELECT * FROM t1 UNION ALL SELECT * FROM t1");
X
Xiaoyu Wang 已提交
313

X
Xiaoyu Wang 已提交
314
  run("(SELECT * FROM t1) UNION ALL (SELECT * FROM t1)");
315 316

  run("SELECT c1 FROM (SELECT c1 FROM t1 UNION ALL SELECT c1 FROM t1)");
X
Xiaoyu Wang 已提交
317 318
}

X
Xiaoyu Wang 已提交
319 320 321 322 323 324
TEST_F(ParserSelectTest, informationSchema) {
  useDb("root", "test");

  run("SELECT * FROM information_schema.user_databases WHERE name = 'information_schema'");
}

325
}  // namespace ParserTest