TestSqlStatementParser.cpp 44 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785
  1. /*
  2. * Copyright (c) 2021, Tim Flynn <trflynn89@serenityos.org>
  3. * Copyright (c) 2021, Jan de Visser <jan@de-visser.net>
  4. * Copyright (c) 2021, Mahmoud Mandour <ma.mandourr@gmail.com>
  5. *
  6. * SPDX-License-Identifier: BSD-2-Clause
  7. */
  8. #include <LibTest/TestCase.h>
  9. #include <AK/DeprecatedString.h>
  10. #include <AK/Optional.h>
  11. #include <AK/Result.h>
  12. #include <AK/StringView.h>
  13. #include <AK/TypeCasts.h>
  14. #include <AK/Vector.h>
  15. #include <LibSQL/AST/Lexer.h>
  16. #include <LibSQL/AST/Parser.h>
  17. namespace {
  18. using ParseResult = AK::Result<NonnullRefPtr<SQL::AST::Statement>, DeprecatedString>;
  19. ParseResult parse(StringView sql)
  20. {
  21. auto parser = SQL::AST::Parser(SQL::AST::Lexer(sql));
  22. auto statement = parser.next_statement();
  23. if (parser.has_errors()) {
  24. return parser.errors()[0].to_deprecated_string();
  25. }
  26. return statement;
  27. }
  28. }
  29. TEST_CASE(create_table)
  30. {
  31. EXPECT(parse("CREATE TABLE"sv).is_error());
  32. EXPECT(parse("CREATE TABLE test"sv).is_error());
  33. EXPECT(parse("CREATE TABLE test ()"sv).is_error());
  34. EXPECT(parse("CREATE TABLE test ();"sv).is_error());
  35. EXPECT(parse("CREATE TABLE test ( column1 "sv).is_error());
  36. EXPECT(parse("CREATE TABLE test ( column1 )"sv).is_error());
  37. EXPECT(parse("CREATE TABLE IF test ( column1 );"sv).is_error());
  38. EXPECT(parse("CREATE TABLE IF NOT test ( column1 );"sv).is_error());
  39. EXPECT(parse("CREATE TABLE AS;"sv).is_error());
  40. EXPECT(parse("CREATE TABLE AS SELECT;"sv).is_error());
  41. EXPECT(parse("CREATE TABLE test ( column1 varchar()"sv).is_error());
  42. EXPECT(parse("CREATE TABLE test ( column1 varchar(abc)"sv).is_error());
  43. EXPECT(parse("CREATE TABLE test ( column1 varchar(123 )"sv).is_error());
  44. EXPECT(parse("CREATE TABLE test ( column1 varchar(123, )"sv).is_error());
  45. EXPECT(parse("CREATE TABLE test ( column1 varchar(123, ) )"sv).is_error());
  46. EXPECT(parse("CREATE TABLE test ( column1 varchar(.) )"sv).is_error());
  47. EXPECT(parse("CREATE TABLE test ( column1 varchar(.abc) )"sv).is_error());
  48. EXPECT(parse("CREATE TABLE test ( column1 varchar(0x) )"sv).is_error());
  49. EXPECT(parse("CREATE TABLE test ( column1 varchar(0xzzz) )"sv).is_error());
  50. EXPECT(parse("CREATE TABLE test ( column1 int ) AS SELECT * FROM table_name;"sv).is_error());
  51. EXPECT(parse("CREATE TABLE test AS SELECT * FROM table_name ( column1 int ) ;"sv).is_error());
  52. struct Column {
  53. StringView name;
  54. StringView type;
  55. Vector<double> signed_numbers {};
  56. };
  57. auto validate = [](StringView sql, StringView expected_schema, StringView expected_table, Vector<Column> expected_columns, bool expected_is_temporary = false, bool expected_is_error_if_table_exists = true) {
  58. auto result = parse(sql);
  59. if (result.is_error())
  60. outln("{}: {}", sql, result.error());
  61. EXPECT(!result.is_error());
  62. auto statement = result.release_value();
  63. EXPECT(is<SQL::AST::CreateTable>(*statement));
  64. const auto& table = static_cast<const SQL::AST::CreateTable&>(*statement);
  65. EXPECT_EQ(table.schema_name(), expected_schema);
  66. EXPECT_EQ(table.table_name(), expected_table);
  67. EXPECT_EQ(table.is_temporary(), expected_is_temporary);
  68. EXPECT_EQ(table.is_error_if_table_exists(), expected_is_error_if_table_exists);
  69. bool expect_select_statement = expected_columns.is_empty();
  70. EXPECT_EQ(table.has_selection(), expect_select_statement);
  71. EXPECT_EQ(table.has_columns(), !expect_select_statement);
  72. const auto& select_statement = table.select_statement();
  73. EXPECT_EQ(select_statement.is_null(), !expect_select_statement);
  74. const auto& columns = table.columns();
  75. EXPECT_EQ(columns.size(), expected_columns.size());
  76. for (size_t i = 0; i < columns.size(); ++i) {
  77. const auto& column = columns[i];
  78. const auto& expected_column = expected_columns[i];
  79. EXPECT_EQ(column.name(), expected_column.name);
  80. const auto& type_name = column.type_name();
  81. EXPECT_EQ(type_name->name(), expected_column.type);
  82. const auto& signed_numbers = type_name->signed_numbers();
  83. EXPECT_EQ(signed_numbers.size(), expected_column.signed_numbers.size());
  84. for (size_t j = 0; j < signed_numbers.size(); ++j) {
  85. double signed_number = signed_numbers[j].value();
  86. double expected_signed_number = expected_column.signed_numbers[j];
  87. EXPECT_EQ(signed_number, expected_signed_number);
  88. }
  89. }
  90. };
  91. validate("CREATE TABLE test ( column1 );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "BLOB"sv } });
  92. validate("Create Table test ( column1 );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "BLOB"sv } });
  93. validate(R"(CREATE TABLE "test" ( "column1" );)"sv, {}, "test"sv, { { "column1"sv, "BLOB"sv } });
  94. validate(R"(CREATE TABLE "te""st" ( "co""lumn1" );)"sv, {}, "te\"st"sv, { { "co\"lumn1"sv, "BLOB"sv } });
  95. validate("CREATE TABLE schema_name.test ( column1 );"sv, "SCHEMA_NAME"sv, "TEST"sv, { { "COLUMN1"sv, "BLOB"sv } });
  96. validate("CREATE TABLE \"schema\".test ( column1 );"sv, "schema"sv, "TEST"sv, { { "COLUMN1"sv, "BLOB"sv } });
  97. validate("CREATE TEMP TABLE test ( column1 );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "BLOB"sv } }, true, true);
  98. validate("CREATE TEMPORARY TABLE test ( column1 );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "BLOB"sv } }, true, true);
  99. validate("CREATE TABLE IF NOT EXISTS test ( column1 );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "BLOB"sv } }, false, false);
  100. validate("CREATE TABLE test AS SELECT * FROM table_name;"sv, {}, "TEST"sv, {});
  101. validate("CREATE TABLE test ( column1 int );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "INT"sv } });
  102. validate("CREATE TABLE test ( column1 varchar );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "VARCHAR"sv } });
  103. validate("CREATE TABLE test ( column1 varchar(255) );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "VARCHAR"sv, { 255 } } });
  104. validate("CREATE TABLE test ( column1 varchar(255, 123) );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "VARCHAR"sv, { 255, 123 } } });
  105. validate("CREATE TABLE test ( column1 varchar(255, -123) );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "VARCHAR"sv, { 255, -123 } } });
  106. validate("CREATE TABLE test ( column1 varchar(0xff) );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "VARCHAR"sv, { 255 } } });
  107. validate("CREATE TABLE test ( column1 varchar(3.14) );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "VARCHAR"sv, { 3.14 } } });
  108. validate("CREATE TABLE test ( column1 varchar(1e3) );"sv, {}, "TEST"sv, { { "COLUMN1"sv, "VARCHAR"sv, { 1000 } } });
  109. }
  110. TEST_CASE(alter_table)
  111. {
  112. // This test case only contains common error cases of the AlterTable subclasses.
  113. EXPECT(parse("ALTER"sv).is_error());
  114. EXPECT(parse("ALTER TABLE"sv).is_error());
  115. EXPECT(parse("ALTER TABLE table_name"sv).is_error());
  116. EXPECT(parse("ALTER TABLE table_name;"sv).is_error());
  117. }
  118. TEST_CASE(alter_table_rename_table)
  119. {
  120. EXPECT(parse("ALTER TABLE table_name RENAME"sv).is_error());
  121. EXPECT(parse("ALTER TABLE table_name RENAME TO"sv).is_error());
  122. EXPECT(parse("ALTER TABLE table_name RENAME TO new_table"sv).is_error());
  123. auto validate = [](StringView sql, StringView expected_schema, StringView expected_table, StringView expected_new_table) {
  124. auto result = parse(sql);
  125. EXPECT(!result.is_error());
  126. auto statement = result.release_value();
  127. EXPECT(is<SQL::AST::RenameTable>(*statement));
  128. const auto& alter = static_cast<const SQL::AST::RenameTable&>(*statement);
  129. EXPECT_EQ(alter.schema_name(), expected_schema);
  130. EXPECT_EQ(alter.table_name(), expected_table);
  131. EXPECT_EQ(alter.new_table_name(), expected_new_table);
  132. };
  133. validate("ALTER TABLE table_name RENAME TO new_table;"sv, {}, "TABLE_NAME"sv, "NEW_TABLE"sv);
  134. validate("ALTER TABLE schema_name.table_name RENAME TO new_table;"sv, "SCHEMA_NAME"sv, "TABLE_NAME"sv, "NEW_TABLE"sv);
  135. }
  136. TEST_CASE(alter_table_rename_column)
  137. {
  138. EXPECT(parse("ALTER TABLE table_name RENAME"sv).is_error());
  139. EXPECT(parse("ALTER TABLE table_name RENAME COLUMN"sv).is_error());
  140. EXPECT(parse("ALTER TABLE table_name RENAME COLUMN column_name"sv).is_error());
  141. EXPECT(parse("ALTER TABLE table_name RENAME COLUMN column_name TO"sv).is_error());
  142. EXPECT(parse("ALTER TABLE table_name RENAME COLUMN column_name TO new_column"sv).is_error());
  143. EXPECT(parse("ALTER TABLE table_name RENAME column_name"sv).is_error());
  144. EXPECT(parse("ALTER TABLE table_name RENAME column_name TO"sv).is_error());
  145. EXPECT(parse("ALTER TABLE table_name RENAME column_name TO new_column"sv).is_error());
  146. auto validate = [](StringView sql, StringView expected_schema, StringView expected_table, StringView expected_column, StringView expected_new_column) {
  147. auto result = parse(sql);
  148. EXPECT(!result.is_error());
  149. auto statement = result.release_value();
  150. EXPECT(is<SQL::AST::RenameColumn>(*statement));
  151. const auto& alter = static_cast<const SQL::AST::RenameColumn&>(*statement);
  152. EXPECT_EQ(alter.schema_name(), expected_schema);
  153. EXPECT_EQ(alter.table_name(), expected_table);
  154. EXPECT_EQ(alter.column_name(), expected_column);
  155. EXPECT_EQ(alter.new_column_name(), expected_new_column);
  156. };
  157. validate("ALTER TABLE table_name RENAME column_name TO new_column;"sv, {}, "TABLE_NAME"sv, "COLUMN_NAME"sv, "NEW_COLUMN"sv);
  158. validate("ALTER TABLE table_name RENAME COLUMN column_name TO new_column;"sv, {}, "TABLE_NAME"sv, "COLUMN_NAME"sv, "NEW_COLUMN"sv);
  159. validate("ALTER TABLE schema_name.table_name RENAME column_name TO new_column;"sv, "SCHEMA_NAME"sv, "TABLE_NAME"sv, "COLUMN_NAME"sv, "NEW_COLUMN"sv);
  160. validate("ALTER TABLE schema_name.table_name RENAME COLUMN column_name TO new_column;"sv, "SCHEMA_NAME"sv, "TABLE_NAME"sv, "COLUMN_NAME"sv, "NEW_COLUMN"sv);
  161. }
  162. TEST_CASE(alter_table_add_column)
  163. {
  164. EXPECT(parse("ALTER TABLE table_name ADD"sv).is_error());
  165. EXPECT(parse("ALTER TABLE table_name ADD COLUMN"sv).is_error());
  166. EXPECT(parse("ALTER TABLE table_name ADD COLUMN column_name"sv).is_error());
  167. struct Column {
  168. StringView name;
  169. StringView type;
  170. Vector<double> signed_numbers {};
  171. };
  172. auto validate = [](StringView sql, StringView expected_schema, StringView expected_table, Column expected_column) {
  173. auto result = parse(sql);
  174. EXPECT(!result.is_error());
  175. auto statement = result.release_value();
  176. EXPECT(is<SQL::AST::AddColumn>(*statement));
  177. const auto& alter = static_cast<const SQL::AST::AddColumn&>(*statement);
  178. EXPECT_EQ(alter.schema_name(), expected_schema);
  179. EXPECT_EQ(alter.table_name(), expected_table);
  180. const auto& column = alter.column();
  181. EXPECT_EQ(column->name(), expected_column.name);
  182. const auto& type_name = column->type_name();
  183. EXPECT_EQ(type_name->name(), expected_column.type);
  184. const auto& signed_numbers = type_name->signed_numbers();
  185. EXPECT_EQ(signed_numbers.size(), expected_column.signed_numbers.size());
  186. for (size_t j = 0; j < signed_numbers.size(); ++j) {
  187. double signed_number = signed_numbers[j].value();
  188. double expected_signed_number = expected_column.signed_numbers[j];
  189. EXPECT_EQ(signed_number, expected_signed_number);
  190. }
  191. };
  192. validate("ALTER TABLE test ADD column1;"sv, {}, "TEST"sv, { "COLUMN1"sv, "BLOB"sv });
  193. validate("ALTER TABLE test ADD column1 int;"sv, {}, "TEST"sv, { "COLUMN1"sv, "INT"sv });
  194. validate("ALTER TABLE test ADD column1 varchar;"sv, {}, "TEST"sv, { "COLUMN1"sv, "VARCHAR"sv });
  195. validate("ALTER TABLE test ADD column1 varchar(255);"sv, {}, "TEST"sv, { "COLUMN1"sv, "VARCHAR"sv, { 255 } });
  196. validate("ALTER TABLE test ADD column1 varchar(255, 123);"sv, {}, "TEST"sv, { "COLUMN1"sv, "VARCHAR"sv, { 255, 123 } });
  197. validate("ALTER TABLE schema_name.test ADD COLUMN column1;"sv, "SCHEMA_NAME"sv, "TEST"sv, { "COLUMN1"sv, "BLOB"sv });
  198. validate("ALTER TABLE schema_name.test ADD COLUMN column1 int;"sv, "SCHEMA_NAME"sv, "TEST"sv, { "COLUMN1"sv, "INT"sv });
  199. validate("ALTER TABLE schema_name.test ADD COLUMN column1 varchar;"sv, "SCHEMA_NAME"sv, "TEST"sv, { "COLUMN1"sv, "VARCHAR"sv });
  200. validate("ALTER TABLE schema_name.test ADD COLUMN column1 varchar(255);"sv, "SCHEMA_NAME"sv, "TEST"sv, { "COLUMN1"sv, "VARCHAR"sv, { 255 } });
  201. validate("ALTER TABLE schema_name.test ADD COLUMN column1 varchar(255, 123);"sv, "SCHEMA_NAME"sv, "TEST"sv, { "COLUMN1"sv, "VARCHAR"sv, { 255, 123 } });
  202. }
  203. TEST_CASE(alter_table_drop_column)
  204. {
  205. EXPECT(parse("ALTER TABLE table_name DROP"sv).is_error());
  206. EXPECT(parse("ALTER TABLE table_name DROP COLUMN"sv).is_error());
  207. EXPECT(parse("ALTER TABLE table_name DROP column_name"sv).is_error());
  208. EXPECT(parse("ALTER TABLE table_name DROP COLUMN column_name"sv).is_error());
  209. auto validate = [](StringView sql, StringView expected_schema, StringView expected_table, StringView expected_column) {
  210. auto result = parse(sql);
  211. EXPECT(!result.is_error());
  212. auto statement = result.release_value();
  213. EXPECT(is<SQL::AST::DropColumn>(*statement));
  214. const auto& alter = static_cast<const SQL::AST::DropColumn&>(*statement);
  215. EXPECT_EQ(alter.schema_name(), expected_schema);
  216. EXPECT_EQ(alter.table_name(), expected_table);
  217. EXPECT_EQ(alter.column_name(), expected_column);
  218. };
  219. validate("ALTER TABLE table_name DROP column_name;"sv, {}, "TABLE_NAME"sv, "COLUMN_NAME"sv);
  220. validate("ALTER TABLE table_name DROP COLUMN column_name;"sv, {}, "TABLE_NAME"sv, "COLUMN_NAME"sv);
  221. validate("ALTER TABLE schema_name.table_name DROP column_name;"sv, "SCHEMA_NAME"sv, "TABLE_NAME"sv, "COLUMN_NAME"sv);
  222. validate("ALTER TABLE schema_name.table_name DROP COLUMN column_name;"sv, "SCHEMA_NAME"sv, "TABLE_NAME"sv, "COLUMN_NAME"sv);
  223. }
  224. TEST_CASE(drop_table)
  225. {
  226. EXPECT(parse("DROP"sv).is_error());
  227. EXPECT(parse("DROP TABLE"sv).is_error());
  228. EXPECT(parse("DROP TABLE test"sv).is_error());
  229. EXPECT(parse("DROP TABLE IF test;"sv).is_error());
  230. auto validate = [](StringView sql, StringView expected_schema, StringView expected_table, bool expected_is_error_if_table_does_not_exist = true) {
  231. auto result = parse(sql);
  232. EXPECT(!result.is_error());
  233. auto statement = result.release_value();
  234. EXPECT(is<SQL::AST::DropTable>(*statement));
  235. const auto& table = static_cast<const SQL::AST::DropTable&>(*statement);
  236. EXPECT_EQ(table.schema_name(), expected_schema);
  237. EXPECT_EQ(table.table_name(), expected_table);
  238. EXPECT_EQ(table.is_error_if_table_does_not_exist(), expected_is_error_if_table_does_not_exist);
  239. };
  240. validate("DROP TABLE test;"sv, {}, "TEST"sv);
  241. validate("DROP TABLE schema_name.test;"sv, "SCHEMA_NAME"sv, "TEST"sv);
  242. validate("DROP TABLE IF EXISTS test;"sv, {}, "TEST"sv, false);
  243. }
  244. TEST_CASE(insert)
  245. {
  246. EXPECT(parse("INSERT"sv).is_error());
  247. EXPECT(parse("INSERT INTO"sv).is_error());
  248. EXPECT(parse("INSERT INTO table_name"sv).is_error());
  249. EXPECT(parse("INSERT INTO table_name (column_name)"sv).is_error());
  250. EXPECT(parse("INSERT INTO table_name (column_name, ) DEFAULT VALUES;"sv).is_error());
  251. EXPECT(parse("INSERT INTO table_name VALUES"sv).is_error());
  252. EXPECT(parse("INSERT INTO table_name VALUES ();"sv).is_error());
  253. EXPECT(parse("INSERT INTO table_name VALUES (1)"sv).is_error());
  254. EXPECT(parse("INSERT INTO table_name SELECT"sv).is_error());
  255. EXPECT(parse("INSERT INTO table_name SELECT * from table_name"sv).is_error());
  256. EXPECT(parse("INSERT OR INTO table_name DEFAULT VALUES;"sv).is_error());
  257. EXPECT(parse("INSERT OR foo INTO table_name DEFAULT VALUES;"sv).is_error());
  258. auto validate = [](StringView sql, SQL::AST::ConflictResolution expected_conflict_resolution, StringView expected_schema, StringView expected_table, StringView expected_alias, Vector<StringView> expected_column_names, Vector<size_t> expected_chain_sizes, bool expect_select_statement) {
  259. auto result = parse(sql);
  260. EXPECT(!result.is_error());
  261. auto statement = result.release_value();
  262. EXPECT(is<SQL::AST::Insert>(*statement));
  263. const auto& insert = static_cast<const SQL::AST::Insert&>(*statement);
  264. EXPECT_EQ(insert.conflict_resolution(), expected_conflict_resolution);
  265. EXPECT_EQ(insert.schema_name(), expected_schema);
  266. EXPECT_EQ(insert.table_name(), expected_table);
  267. EXPECT_EQ(insert.alias(), expected_alias);
  268. const auto& column_names = insert.column_names();
  269. EXPECT_EQ(column_names.size(), expected_column_names.size());
  270. for (size_t i = 0; i < column_names.size(); ++i)
  271. EXPECT_EQ(column_names[i], expected_column_names[i]);
  272. EXPECT_EQ(insert.has_expressions(), !expected_chain_sizes.is_empty());
  273. if (insert.has_expressions()) {
  274. const auto& chained_expressions = insert.chained_expressions();
  275. EXPECT_EQ(chained_expressions.size(), expected_chain_sizes.size());
  276. for (size_t i = 0; i < chained_expressions.size(); ++i) {
  277. const auto& chained_expression = chained_expressions[i];
  278. const auto& expressions = chained_expression.expressions();
  279. EXPECT_EQ(expressions.size(), expected_chain_sizes[i]);
  280. for (const auto& expression : expressions)
  281. EXPECT(!is<SQL::AST::ErrorExpression>(expression));
  282. }
  283. }
  284. EXPECT_EQ(insert.has_selection(), expect_select_statement);
  285. EXPECT_EQ(insert.default_values(), expected_chain_sizes.is_empty() && !expect_select_statement);
  286. };
  287. validate("INSERT OR ABORT INTO table_name DEFAULT VALUES;"sv, SQL::AST::ConflictResolution::Abort, {}, "TABLE_NAME"sv, {}, {}, {}, false);
  288. validate("INSERT OR FAIL INTO table_name DEFAULT VALUES;"sv, SQL::AST::ConflictResolution::Fail, {}, "TABLE_NAME"sv, {}, {}, {}, false);
  289. validate("INSERT OR IGNORE INTO table_name DEFAULT VALUES;"sv, SQL::AST::ConflictResolution::Ignore, {}, "TABLE_NAME"sv, {}, {}, {}, false);
  290. validate("INSERT OR REPLACE INTO table_name DEFAULT VALUES;"sv, SQL::AST::ConflictResolution::Replace, {}, "TABLE_NAME"sv, {}, {}, {}, false);
  291. validate("INSERT OR ROLLBACK INTO table_name DEFAULT VALUES;"sv, SQL::AST::ConflictResolution::Rollback, {}, "TABLE_NAME"sv, {}, {}, {}, false);
  292. auto resolution = SQL::AST::ConflictResolution::Abort;
  293. validate("INSERT INTO table_name DEFAULT VALUES;"sv, resolution, {}, "TABLE_NAME"sv, {}, {}, {}, false);
  294. validate("INSERT INTO schema_name.table_name DEFAULT VALUES;"sv, resolution, "SCHEMA_NAME"sv, "TABLE_NAME"sv, {}, {}, {}, false);
  295. validate("INSERT INTO table_name AS foo DEFAULT VALUES;"sv, resolution, {}, "TABLE_NAME"sv, "FOO"sv, {}, {}, false);
  296. validate("INSERT INTO table_name (column_name) DEFAULT VALUES;"sv, resolution, {}, "TABLE_NAME"sv, {}, { "COLUMN_NAME"sv }, {}, false);
  297. validate("INSERT INTO table_name (column1, column2) DEFAULT VALUES;"sv, resolution, {}, "TABLE_NAME"sv, {}, { "COLUMN1"sv, "COLUMN2"sv }, {}, false);
  298. validate("INSERT INTO table_name VALUES (1);"sv, resolution, {}, "TABLE_NAME"sv, {}, {}, { 1 }, false);
  299. validate("INSERT INTO table_name VALUES (1, 2);"sv, resolution, {}, "TABLE_NAME"sv, {}, {}, { 2 }, false);
  300. validate("INSERT INTO table_name VALUES (1, 2), (3, 4, 5);"sv, resolution, {}, "TABLE_NAME"sv, {}, {}, { 2, 3 }, false);
  301. validate("INSERT INTO table_name SELECT * FROM table_name;"sv, resolution, {}, "TABLE_NAME"sv, {}, {}, {}, true);
  302. }
  303. TEST_CASE(update)
  304. {
  305. EXPECT(parse("UPDATE"sv).is_error());
  306. EXPECT(parse("UPDATE table_name"sv).is_error());
  307. EXPECT(parse("UPDATE table_name SET"sv).is_error());
  308. EXPECT(parse("UPDATE table_name SET column_name"sv).is_error());
  309. EXPECT(parse("UPDATE table_name SET column_name=4"sv).is_error());
  310. EXPECT(parse("UPDATE table_name SET column_name=4, ;"sv).is_error());
  311. EXPECT(parse("UPDATE table_name SET (column_name)=4"sv).is_error());
  312. EXPECT(parse("UPDATE table_name SET (column_name)=4, ;"sv).is_error());
  313. EXPECT(parse("UPDATE table_name SET (column_name, )=4;"sv).is_error());
  314. EXPECT(parse("UPDATE table_name SET column_name=4 FROM"sv).is_error());
  315. EXPECT(parse("UPDATE table_name SET column_name=4 FROM table_name"sv).is_error());
  316. EXPECT(parse("UPDATE table_name SET column_name=4 WHERE"sv).is_error());
  317. EXPECT(parse("UPDATE table_name SET column_name=4 WHERE 1==1"sv).is_error());
  318. EXPECT(parse("UPDATE table_name SET column_name=4 RETURNING"sv).is_error());
  319. EXPECT(parse("UPDATE table_name SET column_name=4 RETURNING *"sv).is_error());
  320. EXPECT(parse("UPDATE table_name SET column_name=4 RETURNING column_name"sv).is_error());
  321. EXPECT(parse("UPDATE table_name SET column_name=4 RETURNING column_name AS"sv).is_error());
  322. EXPECT(parse("UPDATE OR table_name SET column_name=4;"sv).is_error());
  323. EXPECT(parse("UPDATE OR foo table_name SET column_name=4;"sv).is_error());
  324. auto validate = [](StringView sql, SQL::AST::ConflictResolution expected_conflict_resolution, StringView expected_schema, StringView expected_table, StringView expected_alias, Vector<Vector<DeprecatedString>> expected_update_columns, bool expect_where_clause, bool expect_returning_clause, Vector<StringView> expected_returned_column_aliases) {
  325. auto result = parse(sql);
  326. EXPECT(!result.is_error());
  327. auto statement = result.release_value();
  328. EXPECT(is<SQL::AST::Update>(*statement));
  329. const auto& update = static_cast<const SQL::AST::Update&>(*statement);
  330. EXPECT_EQ(update.conflict_resolution(), expected_conflict_resolution);
  331. const auto& qualified_table_name = update.qualified_table_name();
  332. EXPECT_EQ(qualified_table_name->schema_name(), expected_schema);
  333. EXPECT_EQ(qualified_table_name->table_name(), expected_table);
  334. EXPECT_EQ(qualified_table_name->alias(), expected_alias);
  335. const auto& update_columns = update.update_columns();
  336. EXPECT_EQ(update_columns.size(), expected_update_columns.size());
  337. for (size_t i = 0; i < update_columns.size(); ++i) {
  338. const auto& update_column = update_columns[i];
  339. const auto& expected_update_column = expected_update_columns[i];
  340. EXPECT_EQ(update_column.column_names.size(), expected_update_column.size());
  341. EXPECT(!is<SQL::AST::ErrorExpression>(*update_column.expression));
  342. for (size_t j = 0; j < update_column.column_names.size(); ++j)
  343. EXPECT_EQ(update_column.column_names[j], expected_update_column[j]);
  344. }
  345. const auto& where_clause = update.where_clause();
  346. EXPECT_EQ(where_clause.is_null(), !expect_where_clause);
  347. if (where_clause)
  348. EXPECT(!is<SQL::AST::ErrorExpression>(*where_clause));
  349. const auto& returning_clause = update.returning_clause();
  350. EXPECT_EQ(returning_clause.is_null(), !expect_returning_clause);
  351. if (returning_clause) {
  352. EXPECT_EQ(returning_clause->columns().size(), expected_returned_column_aliases.size());
  353. for (size_t i = 0; i < returning_clause->columns().size(); ++i) {
  354. const auto& column = returning_clause->columns()[i];
  355. const auto& expected_column_alias = expected_returned_column_aliases[i];
  356. EXPECT(!is<SQL::AST::ErrorExpression>(*column.expression));
  357. EXPECT_EQ(column.column_alias, expected_column_alias);
  358. }
  359. }
  360. };
  361. Vector<Vector<DeprecatedString>> update_columns { { "COLUMN_NAME" } };
  362. validate("UPDATE OR ABORT table_name SET column_name=1;"sv, SQL::AST::ConflictResolution::Abort, {}, "TABLE_NAME"sv, {}, update_columns, false, false, {});
  363. validate("UPDATE OR FAIL table_name SET column_name=1;"sv, SQL::AST::ConflictResolution::Fail, {}, "TABLE_NAME"sv, {}, update_columns, false, false, {});
  364. validate("UPDATE OR IGNORE table_name SET column_name=1;"sv, SQL::AST::ConflictResolution::Ignore, {}, "TABLE_NAME"sv, {}, update_columns, false, false, {});
  365. validate("UPDATE OR REPLACE table_name SET column_name=1;"sv, SQL::AST::ConflictResolution::Replace, {}, "TABLE_NAME"sv, {}, update_columns, false, false, {});
  366. validate("UPDATE OR ROLLBACK table_name SET column_name=1;"sv, SQL::AST::ConflictResolution::Rollback, {}, "TABLE_NAME"sv, {}, update_columns, false, false, {});
  367. auto resolution = SQL::AST::ConflictResolution::Abort;
  368. validate("UPDATE table_name SET column_name=1;"sv, resolution, {}, "TABLE_NAME"sv, {}, update_columns, false, false, {});
  369. validate("UPDATE schema_name.table_name SET column_name=1;"sv, resolution, "SCHEMA_NAME"sv, "TABLE_NAME"sv, {}, update_columns, false, false, {});
  370. validate("UPDATE table_name AS foo SET column_name=1;"sv, resolution, {}, "TABLE_NAME"sv, "FOO"sv, update_columns, false, false, {});
  371. validate("UPDATE table_name SET column_name=1;"sv, resolution, {}, "TABLE_NAME"sv, {}, { { "COLUMN_NAME"sv } }, false, false, {});
  372. validate("UPDATE table_name SET column1=1, column2=2;"sv, resolution, {}, "TABLE_NAME"sv, {}, { { "COLUMN1"sv }, { "COLUMN2"sv } }, false, false, {});
  373. validate("UPDATE table_name SET (column1, column2)=1, column3=2;"sv, resolution, {}, "TABLE_NAME"sv, {}, { { "COLUMN1"sv, "COLUMN2"sv }, { "COLUMN3"sv } }, false, false, {});
  374. validate("UPDATE table_name SET column_name=1 WHERE 1==1;"sv, resolution, {}, "TABLE_NAME"sv, {}, update_columns, true, false, {});
  375. validate("UPDATE table_name SET column_name=1 RETURNING *;"sv, resolution, {}, "TABLE_NAME"sv, {}, update_columns, false, true, {});
  376. validate("UPDATE table_name SET column_name=1 RETURNING column_name;"sv, resolution, {}, "TABLE_NAME"sv, {}, update_columns, false, true, { {} });
  377. validate("UPDATE table_name SET column_name=1 RETURNING column_name AS alias;"sv, resolution, {}, "TABLE_NAME"sv, {}, update_columns, false, true, { "ALIAS"sv });
  378. validate("UPDATE table_name SET column_name=1 RETURNING column1 AS alias1, column2 AS alias2;"sv, resolution, {}, "TABLE_NAME"sv, {}, update_columns, false, true, { "ALIAS1"sv, "ALIAS2"sv });
  379. }
  380. TEST_CASE(delete_)
  381. {
  382. EXPECT(parse("DELETE"sv).is_error());
  383. EXPECT(parse("DELETE FROM"sv).is_error());
  384. EXPECT(parse("DELETE FROM table_name"sv).is_error());
  385. EXPECT(parse("DELETE FROM table_name WHERE"sv).is_error());
  386. EXPECT(parse("DELETE FROM table_name WHERE 15"sv).is_error());
  387. EXPECT(parse("DELETE FROM table_name WHERE 15 RETURNING"sv).is_error());
  388. EXPECT(parse("DELETE FROM table_name WHERE 15 RETURNING *"sv).is_error());
  389. EXPECT(parse("DELETE FROM table_name WHERE 15 RETURNING column_name"sv).is_error());
  390. EXPECT(parse("DELETE FROM table_name WHERE 15 RETURNING column_name AS;"sv).is_error());
  391. EXPECT(parse("DELETE FROM table_name WHERE (');"sv).is_error());
  392. auto validate = [](StringView sql, StringView expected_schema, StringView expected_table, StringView expected_alias, bool expect_where_clause, bool expect_returning_clause, Vector<StringView> expected_returned_column_aliases) {
  393. auto result = parse(sql);
  394. EXPECT(!result.is_error());
  395. auto statement = result.release_value();
  396. EXPECT(is<SQL::AST::Delete>(*statement));
  397. const auto& delete_ = static_cast<const SQL::AST::Delete&>(*statement);
  398. const auto& qualified_table_name = delete_.qualified_table_name();
  399. EXPECT_EQ(qualified_table_name->schema_name(), expected_schema);
  400. EXPECT_EQ(qualified_table_name->table_name(), expected_table);
  401. EXPECT_EQ(qualified_table_name->alias(), expected_alias);
  402. const auto& where_clause = delete_.where_clause();
  403. EXPECT_EQ(where_clause.is_null(), !expect_where_clause);
  404. if (where_clause)
  405. EXPECT(!is<SQL::AST::ErrorExpression>(*where_clause));
  406. const auto& returning_clause = delete_.returning_clause();
  407. EXPECT_EQ(returning_clause.is_null(), !expect_returning_clause);
  408. if (returning_clause) {
  409. EXPECT_EQ(returning_clause->columns().size(), expected_returned_column_aliases.size());
  410. for (size_t i = 0; i < returning_clause->columns().size(); ++i) {
  411. const auto& column = returning_clause->columns()[i];
  412. const auto& expected_column_alias = expected_returned_column_aliases[i];
  413. EXPECT(!is<SQL::AST::ErrorExpression>(*column.expression));
  414. EXPECT_EQ(column.column_alias, expected_column_alias);
  415. }
  416. }
  417. };
  418. validate("DELETE FROM table_name;"sv, {}, "TABLE_NAME"sv, {}, false, false, {});
  419. validate("DELETE FROM schema_name.table_name;"sv, "SCHEMA_NAME"sv, "TABLE_NAME"sv, {}, false, false, {});
  420. validate("DELETE FROM schema_name.table_name AS alias;"sv, "SCHEMA_NAME"sv, "TABLE_NAME"sv, "ALIAS"sv, false, false, {});
  421. validate("DELETE FROM table_name WHERE (1 == 1);"sv, {}, "TABLE_NAME"sv, {}, true, false, {});
  422. validate("DELETE FROM table_name RETURNING *;"sv, {}, "TABLE_NAME"sv, {}, false, true, {});
  423. validate("DELETE FROM table_name RETURNING column_name;"sv, {}, "TABLE_NAME"sv, {}, false, true, { {} });
  424. validate("DELETE FROM table_name RETURNING column_name AS alias;"sv, {}, "TABLE_NAME"sv, {}, false, true, { "ALIAS"sv });
  425. validate("DELETE FROM table_name RETURNING column1 AS alias1, column2 AS alias2;"sv, {}, "TABLE_NAME"sv, {}, false, true, { "ALIAS1"sv, "ALIAS2"sv });
  426. }
  427. TEST_CASE(select)
  428. {
  429. EXPECT(parse("SELECT"sv).is_error());
  430. EXPECT(parse("SELECT;"sv).is_error());
  431. EXPECT(parse("SELECT DISTINCT;"sv).is_error());
  432. EXPECT(parse("SELECT ALL;"sv).is_error());
  433. EXPECT(parse("SELECT *"sv).is_error());
  434. EXPECT(parse("SELECT * FROM;"sv).is_error());
  435. EXPECT(parse("SELECT table_name. FROM table_name;"sv).is_error());
  436. EXPECT(parse("SELECT column_name AS FROM table_name;"sv).is_error());
  437. EXPECT(parse("SELECT * FROM ("sv).is_error());
  438. EXPECT(parse("SELECT * FROM ()"sv).is_error());
  439. EXPECT(parse("SELECT * FROM ();"sv).is_error());
  440. EXPECT(parse("SELECT * FROM (table_name1)"sv).is_error());
  441. EXPECT(parse("SELECT * FROM (table_name1, )"sv).is_error());
  442. EXPECT(parse("SELECT * FROM (table_name1, table_name2)"sv).is_error());
  443. EXPECT(parse("SELECT * FROM table_name"sv).is_error());
  444. EXPECT(parse("SELECT * FROM table_name AS;"sv).is_error());
  445. EXPECT(parse("SELECT * FROM table_name WHERE;"sv).is_error());
  446. EXPECT(parse("SELECT * FROM table_name WHERE 1 ==1"sv).is_error());
  447. EXPECT(parse("SELECT * FROM table_name GROUP;"sv).is_error());
  448. EXPECT(parse("SELECT * FROM table_name GROUP BY;"sv).is_error());
  449. EXPECT(parse("SELECT * FROM table_name GROUP BY column_name"sv).is_error());
  450. EXPECT(parse("SELECT * FROM table_name ORDER:"sv).is_error());
  451. EXPECT(parse("SELECT * FROM table_name ORDER BY column_name"sv).is_error());
  452. EXPECT(parse("SELECT * FROM table_name ORDER BY column_name COLLATE:"sv).is_error());
  453. EXPECT(parse("SELECT * FROM table_name ORDER BY column_name COLLATE collation"sv).is_error());
  454. EXPECT(parse("SELECT * FROM table_name ORDER BY column_name NULLS;"sv).is_error());
  455. EXPECT(parse("SELECT * FROM table_name ORDER BY column_name NULLS SECOND;"sv).is_error());
  456. EXPECT(parse("SELECT * FROM table_name LIMIT;"sv).is_error());
  457. EXPECT(parse("SELECT * FROM table_name LIMIT 12"sv).is_error());
  458. EXPECT(parse("SELECT * FROM table_name LIMIT 12 OFFSET;"sv).is_error());
  459. EXPECT(parse("SELECT * FROM table_name LIMIT 12 OFFSET 15"sv).is_error());
  460. EXPECT(parse("SELECT * FROM table_name LIMIT 15, 16;"sv).is_error());
  461. struct Type {
  462. SQL::AST::ResultType type;
  463. StringView table_name_or_column_alias {};
  464. };
  465. struct From {
  466. StringView schema_name;
  467. StringView table_name;
  468. StringView table_alias;
  469. };
  470. struct Ordering {
  471. DeprecatedString collation_name;
  472. SQL::Order order;
  473. SQL::Nulls nulls;
  474. };
  475. auto validate = [](StringView sql, Vector<Type> expected_columns, Vector<From> expected_from_list, bool expect_where_clause, size_t expected_group_by_size, bool expect_having_clause, Vector<Ordering> expected_ordering, bool expect_limit_clause, bool expect_offset_clause) {
  476. auto result = parse(sql);
  477. EXPECT(!result.is_error());
  478. auto statement = result.release_value();
  479. EXPECT(is<SQL::AST::Select>(*statement));
  480. const auto& select = static_cast<const SQL::AST::Select&>(*statement);
  481. const auto& result_column_list = select.result_column_list();
  482. EXPECT_EQ(result_column_list.size(), expected_columns.size());
  483. for (size_t i = 0; i < result_column_list.size(); ++i) {
  484. const auto& result_column = result_column_list[i];
  485. const auto& expected_column = expected_columns[i];
  486. EXPECT_EQ(result_column.type(), expected_column.type);
  487. switch (result_column.type()) {
  488. case SQL::AST::ResultType::All:
  489. EXPECT(expected_column.table_name_or_column_alias.is_null());
  490. break;
  491. case SQL::AST::ResultType::Table:
  492. EXPECT_EQ(result_column.table_name(), expected_column.table_name_or_column_alias);
  493. break;
  494. case SQL::AST::ResultType::Expression:
  495. EXPECT_EQ(result_column.column_alias(), expected_column.table_name_or_column_alias);
  496. break;
  497. }
  498. }
  499. const auto& table_or_subquery_list = select.table_or_subquery_list();
  500. EXPECT_EQ(table_or_subquery_list.size(), expected_from_list.size());
  501. for (size_t i = 0; i < table_or_subquery_list.size(); ++i) {
  502. const auto& result_from = table_or_subquery_list[i];
  503. const auto& expected_from = expected_from_list[i];
  504. EXPECT_EQ(result_from.schema_name(), expected_from.schema_name);
  505. EXPECT_EQ(result_from.table_name(), expected_from.table_name);
  506. EXPECT_EQ(result_from.table_alias(), expected_from.table_alias);
  507. }
  508. const auto& where_clause = select.where_clause();
  509. EXPECT_EQ(where_clause.is_null(), !expect_where_clause);
  510. if (where_clause)
  511. EXPECT(!is<SQL::AST::ErrorExpression>(*where_clause));
  512. const auto& group_by_clause = select.group_by_clause();
  513. EXPECT_EQ(group_by_clause.is_null(), (expected_group_by_size == 0));
  514. if (group_by_clause) {
  515. const auto& group_by_list = group_by_clause->group_by_list();
  516. EXPECT_EQ(group_by_list.size(), expected_group_by_size);
  517. for (size_t i = 0; i < group_by_list.size(); ++i)
  518. EXPECT(!is<SQL::AST::ErrorExpression>(group_by_list[i]));
  519. const auto& having_clause = group_by_clause->having_clause();
  520. EXPECT_EQ(having_clause.is_null(), !expect_having_clause);
  521. if (having_clause)
  522. EXPECT(!is<SQL::AST::ErrorExpression>(*having_clause));
  523. }
  524. const auto& ordering_term_list = select.ordering_term_list();
  525. EXPECT_EQ(ordering_term_list.size(), expected_ordering.size());
  526. for (size_t i = 0; i < ordering_term_list.size(); ++i) {
  527. const auto& result_order = ordering_term_list[i];
  528. const auto& expected_order = expected_ordering[i];
  529. EXPECT(!is<SQL::AST::ErrorExpression>(*result_order.expression()));
  530. EXPECT_EQ(result_order.collation_name(), expected_order.collation_name);
  531. EXPECT_EQ(result_order.order(), expected_order.order);
  532. EXPECT_EQ(result_order.nulls(), expected_order.nulls);
  533. }
  534. const auto& limit_clause = select.limit_clause();
  535. EXPECT_EQ(limit_clause.is_null(), !expect_limit_clause);
  536. if (limit_clause) {
  537. const auto& limit_expression = limit_clause->limit_expression();
  538. EXPECT(!is<SQL::AST::ErrorExpression>(*limit_expression));
  539. const auto& offset_expression = limit_clause->offset_expression();
  540. EXPECT_EQ(offset_expression.is_null(), !expect_offset_clause);
  541. if (offset_expression)
  542. EXPECT(!is<SQL::AST::ErrorExpression>(*offset_expression));
  543. }
  544. };
  545. Vector<Type> all { { SQL::AST::ResultType::All } };
  546. Vector<From> from { { {}, "TABLE_NAME"sv, {} } };
  547. validate("SELECT * FROM table_name;"sv, { { SQL::AST::ResultType::All } }, from, false, 0, false, {}, false, false);
  548. validate("SELECT table_name.* FROM table_name;"sv, { { SQL::AST::ResultType::Table, "TABLE_NAME"sv } }, from, false, 0, false, {}, false, false);
  549. validate("SELECT column_name AS alias FROM table_name;"sv, { { SQL::AST::ResultType::Expression, "ALIAS"sv } }, from, false, 0, false, {}, false, false);
  550. validate("SELECT table_name.column_name AS alias FROM table_name;"sv, { { SQL::AST::ResultType::Expression, "ALIAS"sv } }, from, false, 0, false, {}, false, false);
  551. validate("SELECT schema_name.table_name.column_name AS alias FROM table_name;"sv, { { SQL::AST::ResultType::Expression, "ALIAS"sv } }, from, false, 0, false, {}, false, false);
  552. validate("SELECT column_name AS alias, *, table_name.* FROM table_name;"sv, { { SQL::AST::ResultType::Expression, "ALIAS"sv }, { SQL::AST::ResultType::All }, { SQL::AST::ResultType::Table, "TABLE_NAME"sv } }, from, false, 0, false, {}, false, false);
  553. validate("SELECT * FROM table_name;"sv, all, { { {}, "TABLE_NAME"sv, {} } }, false, 0, false, {}, false, false);
  554. validate("SELECT * FROM schema_name.table_name;"sv, all, { { "SCHEMA_NAME"sv, "TABLE_NAME"sv, {} } }, false, 0, false, {}, false, false);
  555. validate("SELECT * FROM schema_name.table_name AS alias;"sv, all, { { "SCHEMA_NAME"sv, "TABLE_NAME"sv, "ALIAS"sv } }, false, 0, false, {}, false, false);
  556. validate("SELECT * FROM schema_name.table_name AS alias, table_name2, table_name3 AS table_name4;"sv, all, { { "SCHEMA_NAME"sv, "TABLE_NAME"sv, "ALIAS"sv }, { {}, "TABLE_NAME2"sv, {} }, { {}, "TABLE_NAME3"sv, "TABLE_NAME4"sv } }, false, 0, false, {}, false, false);
  557. validate("SELECT * FROM table_name WHERE column_name IS NOT NULL;"sv, all, from, true, 0, false, {}, false, false);
  558. validate("SELECT * FROM table_name GROUP BY column_name;"sv, all, from, false, 1, false, {}, false, false);
  559. validate("SELECT * FROM table_name GROUP BY column1, column2, column3;"sv, all, from, false, 3, false, {}, false, false);
  560. validate("SELECT * FROM table_name GROUP BY column_name HAVING 'abc';"sv, all, from, false, 1, true, {}, false, false);
  561. validate("SELECT * FROM table_name ORDER BY column_name;"sv, all, from, false, 0, false, { { {}, SQL::Order::Ascending, SQL::Nulls::First } }, false, false);
  562. validate("SELECT * FROM table_name ORDER BY column_name COLLATE collation;"sv, all, from, false, 0, false, { { "COLLATION"sv, SQL::Order::Ascending, SQL::Nulls::First } }, false, false);
  563. validate("SELECT * FROM table_name ORDER BY column_name ASC;"sv, all, from, false, 0, false, { { {}, SQL::Order::Ascending, SQL::Nulls::First } }, false, false);
  564. validate("SELECT * FROM table_name ORDER BY column_name DESC;"sv, all, from, false, 0, false, { { {}, SQL::Order::Descending, SQL::Nulls::Last } }, false, false);
  565. validate("SELECT * FROM table_name ORDER BY column_name ASC NULLS LAST;"sv, all, from, false, 0, false, { { {}, SQL::Order::Ascending, SQL::Nulls::Last } }, false, false);
  566. validate("SELECT * FROM table_name ORDER BY column_name DESC NULLS FIRST;"sv, all, from, false, 0, false, { { {}, SQL::Order::Descending, SQL::Nulls::First } }, false, false);
  567. validate("SELECT * FROM table_name ORDER BY column1, column2 DESC, column3 NULLS LAST;"sv, all, from, false, 0, false, { { {}, SQL::Order::Ascending, SQL::Nulls::First }, { {}, SQL::Order::Descending, SQL::Nulls::Last }, { {}, SQL::Order::Ascending, SQL::Nulls::Last } }, false, false);
  568. validate("SELECT * FROM table_name LIMIT 15;"sv, all, from, false, 0, false, {}, true, false);
  569. validate("SELECT * FROM table_name LIMIT 15 OFFSET 16;"sv, all, from, false, 0, false, {}, true, true);
  570. }
  571. TEST_CASE(common_table_expression)
  572. {
  573. EXPECT(parse("WITH"sv).is_error());
  574. EXPECT(parse("WITH;"sv).is_error());
  575. EXPECT(parse("WITH DELETE FROM table_name;"sv).is_error());
  576. EXPECT(parse("WITH table_name DELETE FROM table_name;"sv).is_error());
  577. EXPECT(parse("WITH table_name AS DELETE FROM table_name;"sv).is_error());
  578. EXPECT(parse("WITH RECURSIVE table_name DELETE FROM table_name;"sv).is_error());
  579. EXPECT(parse("WITH RECURSIVE table_name AS DELETE FROM table_name;"sv).is_error());
  580. // Below are otherwise valid common-table-expressions, but attached to statements which do not allow them.
  581. EXPECT(parse("WITH table_name AS (SELECT * AS TABLE) CREATE TABLE test ( column1 );"sv).is_error());
  582. EXPECT(parse("WITH table_name AS (SELECT * FROM table_name) DROP TABLE test;"sv).is_error());
  583. struct SelectedTableList {
  584. struct SelectedTable {
  585. StringView table_name {};
  586. Vector<StringView> column_names {};
  587. };
  588. bool recursive { false };
  589. Vector<SelectedTable> selected_tables {};
  590. };
  591. auto validate = [](StringView sql, SelectedTableList expected_selected_tables) {
  592. auto result = parse(sql);
  593. EXPECT(!result.is_error());
  594. auto statement = result.release_value();
  595. EXPECT(is<SQL::AST::Delete>(*statement));
  596. const auto& delete_ = static_cast<const SQL::AST::Delete&>(*statement);
  597. const auto& common_table_expression_list = delete_.common_table_expression_list();
  598. EXPECT(!common_table_expression_list.is_null());
  599. EXPECT_EQ(common_table_expression_list->recursive(), expected_selected_tables.recursive);
  600. const auto& common_table_expressions = common_table_expression_list->common_table_expressions();
  601. EXPECT_EQ(common_table_expressions.size(), expected_selected_tables.selected_tables.size());
  602. for (size_t i = 0; i < common_table_expressions.size(); ++i) {
  603. const auto& common_table_expression = common_table_expressions[i];
  604. const auto& expected_common_table_expression = expected_selected_tables.selected_tables[i];
  605. EXPECT_EQ(common_table_expression.table_name(), expected_common_table_expression.table_name);
  606. EXPECT_EQ(common_table_expression.column_names().size(), expected_common_table_expression.column_names.size());
  607. for (size_t j = 0; j < common_table_expression.column_names().size(); ++j)
  608. EXPECT_EQ(common_table_expression.column_names()[j], expected_common_table_expression.column_names[j]);
  609. }
  610. };
  611. validate("WITH table_name AS (SELECT * FROM table_name) DELETE FROM table_name;"sv, { false, { { "TABLE_NAME"sv } } });
  612. validate("WITH table_name (column_name) AS (SELECT * FROM table_name) DELETE FROM table_name;"sv, { false, { { "TABLE_NAME"sv, { "COLUMN_NAME"sv } } } });
  613. validate("WITH table_name (column1, column2) AS (SELECT * FROM table_name) DELETE FROM table_name;"sv, { false, { { "TABLE_NAME"sv, { "COLUMN1"sv, "COLUMN2"sv } } } });
  614. validate("WITH RECURSIVE table_name AS (SELECT * FROM table_name) DELETE FROM table_name;"sv, { true, { { "TABLE_NAME"sv, {} } } });
  615. }
  616. TEST_CASE(nested_subquery_limit)
  617. {
  618. auto subquery = DeprecatedString::formatted("{:(^{}}table_name{:)^{}}", "", SQL::AST::Limits::maximum_subquery_depth - 1, "", SQL::AST::Limits::maximum_subquery_depth - 1);
  619. EXPECT(!parse(DeprecatedString::formatted("SELECT * FROM {};"sv, subquery)).is_error());
  620. EXPECT(parse(DeprecatedString::formatted("SELECT * FROM ({});"sv, subquery)).is_error());
  621. }
  622. TEST_CASE(bound_parameter_limit)
  623. {
  624. auto subquery = DeprecatedString::repeated("?, "sv, SQL::AST::Limits::maximum_bound_parameters);
  625. EXPECT(!parse(DeprecatedString::formatted("INSERT INTO table_name VALUES ({}42);"sv, subquery)).is_error());
  626. EXPECT(parse(DeprecatedString::formatted("INSERT INTO table_name VALUES ({}?);"sv, subquery)).is_error());
  627. }
  628. TEST_CASE(describe_table)
  629. {
  630. EXPECT(parse("DESCRIBE"sv).is_error());
  631. EXPECT(parse("DESCRIBE;"sv).is_error());
  632. EXPECT(parse("DESCRIBE TABLE;"sv).is_error());
  633. EXPECT(parse("DESCRIBE table_name;"sv).is_error());
  634. auto validate = [](StringView sql, StringView expected_schema, StringView expected_table) {
  635. auto result = parse(sql);
  636. if (result.is_error())
  637. outln("{}: {}", sql, result.error());
  638. EXPECT(!result.is_error());
  639. auto statement = result.release_value();
  640. EXPECT(is<SQL::AST::DescribeTable>(*statement));
  641. const auto& describe_table_statement = static_cast<const SQL::AST::DescribeTable&>(*statement);
  642. EXPECT_EQ(describe_table_statement.qualified_table_name()->schema_name(), expected_schema);
  643. EXPECT_EQ(describe_table_statement.qualified_table_name()->table_name(), expected_table);
  644. };
  645. validate("DESCRIBE TABLE TableName;"sv, {}, "TABLENAME"sv);
  646. validate("DESCRIBE TABLE SchemaName.TableName;"sv, "SCHEMANAME"sv, "TABLENAME"sv);
  647. }