tests_config.py 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565
  1. sql_parser_tests = [
  2. ("create table asd;", [{"type": "create_stmt", "table_name": "asd", "cols": []}]),
  3. (
  4. "create table tb (col1 INT, col2 string, col3 FLOAT);",
  5. [
  6. {
  7. "type": "create_stmt",
  8. "table_name": "tb",
  9. "cols": [
  10. {
  11. "type": "create_column",
  12. "column_name": "col1",
  13. "data_type": "INT",
  14. "primary_key": False,
  15. },
  16. {
  17. "type": "create_column",
  18. "column_name": "col2",
  19. "data_type": "string",
  20. "primary_key": False,
  21. },
  22. {
  23. "type": "create_column",
  24. "column_name": "col3",
  25. "data_type": "FLOAT",
  26. "primary_key": False,
  27. },
  28. ],
  29. }
  30. ],
  31. ),
  32. (
  33. """
  34. create table tb1 (
  35. col1 int primary key,
  36. col2 FLOAT
  37. );
  38. """,
  39. [
  40. {
  41. "type": "create_stmt",
  42. "table_name": "tb1",
  43. "cols": [
  44. {
  45. "type": "create_column",
  46. "column_name": "col1",
  47. "data_type": "int",
  48. "primary_key": True,
  49. },
  50. {
  51. "type": "create_column",
  52. "column_name": "col2",
  53. "data_type": "FLOAT",
  54. "primary_key": False,
  55. },
  56. ],
  57. }
  58. ],
  59. ),
  60. (
  61. """
  62. create table tb2 (
  63. x float,
  64. y int,
  65. z int
  66. );
  67. """,
  68. [
  69. {
  70. "type": "create_stmt",
  71. "table_name": "tb2",
  72. "cols": [
  73. {
  74. "type": "create_column",
  75. "column_name": "x",
  76. "data_type": "float",
  77. "primary_key": False,
  78. },
  79. {
  80. "type": "create_column",
  81. "column_name": "y",
  82. "data_type": "int",
  83. "primary_key": False,
  84. },
  85. {
  86. "type": "create_column",
  87. "column_name": "z",
  88. "data_type": "int",
  89. "primary_key": False,
  90. },
  91. ],
  92. }
  93. ],
  94. ),
  95. (
  96. """insert into tb1 values (1, 'foo');""",
  97. [
  98. {
  99. "type": "insert_stmt",
  100. "table_name": "tb1",
  101. "values": [
  102. {"type": "int", "value": 1},
  103. {"type": "string", "value": "'foo'"},
  104. ],
  105. }
  106. ],
  107. ),
  108. (
  109. """insert into tb1 values (2, 'foo', 'zxc', 1234.234);""",
  110. [
  111. {
  112. "type": "insert_stmt",
  113. "table_name": "tb1",
  114. "values": [
  115. {"type": "int", "value": 2},
  116. {"type": "string", "value": "'foo'"},
  117. {"type": "string", "value": "'zxc'"},
  118. {"type": "float", "value": 1234.234},
  119. ],
  120. }
  121. ],
  122. ),
  123. (
  124. "update tb1 set col1=3, col4=4 where col1=2 and col2=4;",
  125. [
  126. {
  127. "type": "update_stmt",
  128. "table_name": "tb1",
  129. "set": [
  130. {
  131. "type": "assign_const",
  132. "left": {"type": "identifier", "value": "col1"},
  133. "right": {"type": "int", "value": 3},
  134. },
  135. {
  136. "type": "assign_const",
  137. "left": {"type": "identifier", "value": "col4"},
  138. "right": {"type": "int", "value": 4},
  139. },
  140. ],
  141. "where": {
  142. "type": "且",
  143. "left": {
  144. "type": "相等",
  145. "left": {"type": "identifier", "value": "col1"},
  146. "right": {"type": "int", "value": 2},
  147. },
  148. "right": {
  149. "type": "相等",
  150. "left": {"type": "identifier", "value": "col2"},
  151. "right": {"type": "int", "value": 4},
  152. },
  153. },
  154. }
  155. ],
  156. ),
  157. (
  158. "update tb1 set col1=3, col4=4 where not not not col1=2 and col2=4 or col3=col2;",
  159. [
  160. {
  161. "type": "update_stmt",
  162. "table_name": "tb1",
  163. "set": [
  164. {
  165. "type": "assign_const",
  166. "left": {"type": "identifier", "value": "col1"},
  167. "right": {"type": "int", "value": 3},
  168. },
  169. {
  170. "type": "assign_const",
  171. "left": {"type": "identifier", "value": "col4"},
  172. "right": {"type": "int", "value": 4},
  173. },
  174. ],
  175. "where": {
  176. "type": "或",
  177. "left": {
  178. "type": "且",
  179. "left": {
  180. "type": "非",
  181. "left": {
  182. "type": "非",
  183. "left": {
  184. "type": "非",
  185. "left": {
  186. "type": "相等",
  187. "left": {"type": "identifier", "value": "col1"},
  188. "right": {"type": "int", "value": 2},
  189. },
  190. },
  191. },
  192. },
  193. "right": {
  194. "type": "相等",
  195. "left": {"type": "identifier", "value": "col2"},
  196. "right": {"type": "int", "value": 4},
  197. },
  198. },
  199. "right": {
  200. "type": "相等",
  201. "left": {"type": "identifier", "value": "col3"},
  202. "right": {"type": "identifier", "value": "col2"},
  203. },
  204. },
  205. }
  206. ],
  207. ),
  208. (
  209. "delete from tb1 where c1 = 1 and c2= 3 or c3=3;",
  210. [
  211. {
  212. "type": "delete_stmt",
  213. "table_name": "tb1",
  214. "where": {
  215. "type": "或",
  216. "left": {
  217. "left": {
  218. "type": "相等",
  219. "left": {"type": "identifier", "value": "c1"},
  220. "right": {"type": "int", "value": 1},
  221. },
  222. "type": "且",
  223. "right": {
  224. "type": "相等",
  225. "left": {"type": "identifier", "value": "c2"},
  226. "right": {"type": "int", "value": 3},
  227. },
  228. },
  229. "right": {
  230. "type": "相等",
  231. "left": {"type": "identifier", "value": "c3"},
  232. "right": {"type": "int", "value": 3},
  233. },
  234. },
  235. }
  236. ],
  237. ),
  238. (
  239. "delete from tb1 where c1 = 1 and (c2= 3 or c3=3) or (c4='asd');",
  240. [
  241. {
  242. "type": "delete_stmt",
  243. "table_name": "tb1",
  244. "where": {
  245. "type": "或",
  246. "left": {
  247. "type": "且",
  248. "left": {
  249. "type": "相等",
  250. "left": {"type": "identifier", "value": "c1"},
  251. "right": {"type": "int", "value": 1},
  252. },
  253. "right": {
  254. "type": "或",
  255. "left": {
  256. "type": "相等",
  257. "left": {"type": "identifier", "value": "c2"},
  258. "right": {"type": "int", "value": 3},
  259. },
  260. "right": {
  261. "type": "相等",
  262. "left": {"type": "identifier", "value": "c3"},
  263. "right": {"type": "int", "value": 3},
  264. },
  265. },
  266. },
  267. "right": {
  268. "type": "相等",
  269. "left": {"type": "identifier", "value": "c4"},
  270. "right": {"type": "string", "value": "'asd'"},
  271. },
  272. },
  273. }
  274. ],
  275. ),
  276. (
  277. "select * from t2;",
  278. [
  279. {
  280. "type": "select_stmt",
  281. "select_cols": [
  282. {
  283. "type": "select_all_column",
  284. "target": {
  285. "type": "select_all_column",
  286. "value": "select_all_column",
  287. },
  288. }
  289. ],
  290. "table_names": ["t2"],
  291. "where": {},
  292. }
  293. ],
  294. ),
  295. (
  296. "select c2 as t from t2 where col1>2;",
  297. [
  298. {
  299. "type": "select_stmt",
  300. "select_cols": [
  301. {
  302. "type": "select_column",
  303. "target": {"type": "identifier", "value": "c2"},
  304. "alias": "t",
  305. }
  306. ],
  307. "table_names": ["t2"],
  308. "where": {
  309. "type": "大于",
  310. "left": {"type": "identifier", "value": "col1"},
  311. "right": {"type": "int", "value": 2},
  312. },
  313. }
  314. ],
  315. ),
  316. (
  317. """SELECT Sname FROM Student WHERE Sno IN (1,2) and c in (3, 4, 5);""",
  318. [
  319. {
  320. "type": "select_stmt",
  321. "select_cols": [
  322. {
  323. "type": "select_column",
  324. "target": {"type": "identifier", "value": "Sname"},
  325. }
  326. ],
  327. "table_names": ["Student"],
  328. "where": {
  329. "type": "且",
  330. "left": {
  331. "type": "包含于",
  332. "left": {"type": "identifier", "value": "Sno"},
  333. "right": [
  334. {"type": "int", "value": 1},
  335. {"type": "int", "value": 2},
  336. ],
  337. },
  338. "right": {
  339. "type": "包含于",
  340. "left": {"type": "identifier", "value": "c"},
  341. "right": [
  342. {"type": "int", "value": 3},
  343. {"type": "int", "value": 4},
  344. {"type": "int", "value": 5},
  345. ],
  346. },
  347. },
  348. }
  349. ],
  350. ),
  351. (
  352. """SELECT Student.Sname
  353. FROM Student
  354. WHERE Sno IN (
  355. SELECT Sno
  356. FROM SC
  357. WHERE SC.Cno='81003'
  358. );
  359. """,
  360. [
  361. {
  362. "type": "select_stmt",
  363. "select_cols": [
  364. {
  365. "type": "select_column",
  366. "target": {
  367. "type": "table_field",
  368. "table": "Student",
  369. "field": "Sname",
  370. },
  371. }
  372. ],
  373. "table_names": ["Student"],
  374. "where": {
  375. "type": "包含于",
  376. "left": {"type": "identifier", "value": "Sno"},
  377. "right": {
  378. "type": "select_stmt",
  379. "select_cols": [
  380. {
  381. "type": "select_column",
  382. "target": {"type": "identifier", "value": "Sno"},
  383. }
  384. ],
  385. "table_names": ["SC"],
  386. "where": {
  387. "type": "相等",
  388. "left": {
  389. "type": "table_field",
  390. "table": "SC",
  391. "field": "Cno",
  392. },
  393. "right": {"type": "string", "value": "'81003'"},
  394. },
  395. },
  396. },
  397. }
  398. ],
  399. ),
  400. (
  401. """
  402. select Student.Sname
  403. from Student join SC
  404. on Student.Sno=SC.Sno
  405. where SC.Cno='81003';
  406. """,
  407. [
  408. {
  409. "type": "select_stmt",
  410. "select_cols": [
  411. {
  412. "type": "select_column",
  413. "target": {
  414. "type": "table_field",
  415. "table": "Student",
  416. "field": "Sname",
  417. },
  418. }
  419. ],
  420. "table_names": ["Student"],
  421. "join_options": {
  422. "type": "join_options",
  423. "join_with": {"type": "identifier", "value": "SC"},
  424. "on": {
  425. "type": "相等",
  426. "left": {
  427. "type": "table_field",
  428. "table": "Student",
  429. "field": "Sno",
  430. },
  431. "right": {"type": "table_field", "table": "SC", "field": "Sno"},
  432. },
  433. },
  434. "where": {
  435. "type": "相等",
  436. "left": {"type": "table_field", "table": "SC", "field": "Cno"},
  437. "right": {"type": "string", "value": "'81003'"},
  438. },
  439. }
  440. ],
  441. ),
  442. (
  443. """
  444. select Student.Sname
  445. from Student join SC
  446. on Student.Sno=SC.Sno
  447. and SC.Cno='81003';
  448. """,
  449. [
  450. {
  451. "type": "select_stmt",
  452. "select_cols": [
  453. {
  454. "type": "select_column",
  455. "target": {
  456. "type": "table_field",
  457. "table": "Student",
  458. "field": "Sname",
  459. },
  460. }
  461. ],
  462. "table_names": ["Student"],
  463. "join_options": {
  464. "type": "join_options",
  465. "join_with": {"type": "identifier", "value": "SC"},
  466. "on": {
  467. "type": "且",
  468. "left": {
  469. "type": "相等",
  470. "left": {
  471. "type": "table_field",
  472. "table": "Student",
  473. "field": "Sno",
  474. },
  475. "right": {
  476. "type": "table_field",
  477. "table": "SC",
  478. "field": "Sno",
  479. },
  480. },
  481. "right": {
  482. "type": "相等",
  483. "left": {
  484. "type": "table_field",
  485. "table": "SC",
  486. "field": "Cno",
  487. },
  488. "right": {"type": "string", "value": "'81003'"},
  489. },
  490. },
  491. },
  492. "where": {},
  493. }
  494. ],
  495. ),
  496. ("drop table t1;", [{"type": "drop_stmt", "table_name": "t1"}]),
  497. (
  498. "select * from tb1, tb2;",
  499. [
  500. {
  501. "type": "select_stmt",
  502. "select_cols": [
  503. {
  504. "type": "select_all_column",
  505. "target": {
  506. "type": "select_all_column",
  507. "value": "select_all_column",
  508. },
  509. }
  510. ],
  511. "table_names": ["tb1", "tb2"],
  512. "where": {},
  513. }
  514. ],
  515. ),
  516. ]
  517. sql_checker_tests = [
  518. ("drop table person;", True),
  519. ("create table person(name string, age int, classId int);", True),
  520. ("select age from person;", True),
  521. ("select * from person;", True),
  522. ("select gender from person;", "column `gender` not exists in `person`"),
  523. ("select 123 from person;", True),
  524. ("drop table class;", True),
  525. ("create table class (id int, grade int, faculty string);", True),
  526. ("select * from class where grade = 2 and faculty = 'Computer Science';", True),
  527. (
  528. "select * from class where grade = 2 and count=33;",
  529. "column `count` not exists in `class`",
  530. ),
  531. ("select age, class.grade from class, person;", True),
  532. (
  533. "select age, person.grade from class, person;",
  534. "column `person.grade` not exists in `class, person`",
  535. ),
  536. (
  537. """SELECT person.name, grade, faculty
  538. FROM person
  539. WHERE name = '张三' and classId IN (
  540. SELECT *
  541. FROM class
  542. WHERE class.id = classId
  543. );
  544. """,
  545. 'column `grade` not exists in `person`',
  546. ),
  547. (
  548. """select person.name
  549. from person join class
  550. on class.id=person.classId and person.grade=2
  551. where age=22;
  552. """,
  553. "column `person.grade` not exists in `person, class`",
  554. ),
  555. (
  556. """select person.name
  557. from person join class
  558. on class.id=person.classId and class.grade=2
  559. where age=22;
  560. """,
  561. True
  562. ),
  563. ]