PageRenderTime 51ms CodeModel.GetById 24ms RepoModel.GetById 1ms app.codeStats 0ms

/test/tkt2927.test

https://bitbucket.org/aware/sqlite
Unknown | 674 lines | 659 code | 15 blank | 0 comment | 0 complexity | e62eb231cd3dabd1aa1ececdda56e9cb MD5 | raw file
  1. # 2008 Feb 6
  2. #
  3. # The author disclaims copyright to this source code. In place of
  4. # a legal notice, here is a blessing:
  5. #
  6. # May you do good and not evil.
  7. # May you find forgiveness for yourself and forgive others.
  8. # May you share freely, never taking more than you give.
  9. #
  10. #***********************************************************************
  11. #
  12. # This file is to test that ticket #2927 is fixed.
  13. #
  14. # $Id:$
  15. #
  16. set testdir [file dirname $argv0]
  17. source $testdir/tester.tcl
  18. ifcapable !compound {
  19. finish_test
  20. return
  21. }
  22. # Create a database.
  23. #
  24. do_test tkt2927-1.1 {
  25. db eval {
  26. CREATE TABLE t1(a, b);
  27. INSERT INTO t1 VALUES(1,11);
  28. INSERT INTO t1 VALUES(2,22);
  29. INSERT INTO t1 VALUES(3,33);
  30. INSERT INTO t1 VALUES(4,44);
  31. INSERT INTO t1 VALUES(5,55);
  32. SELECT * FROM t1;
  33. }
  34. } {1 11 2 22 3 33 4 44 5 55}
  35. do_test tkt2927-2.1 {
  36. db eval {
  37. SELECT a, b FROM t1
  38. UNION ALL
  39. SELECT a, b FROM t1
  40. }
  41. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  42. do_test tkt2927-2.2 {
  43. #set sqlite_addop_trace 1
  44. db eval {
  45. SELECT a, b FROM t1
  46. UNION ALL
  47. SELECT a, abs(b) FROM t1
  48. }
  49. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  50. do_test tkt2927-2.3 {
  51. db eval {
  52. SELECT a, b FROM t1
  53. UNION ALL
  54. SELECT abs(a), b FROM t1
  55. }
  56. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  57. do_test tkt2927-2.4 {
  58. db eval {
  59. SELECT a, b FROM t1
  60. UNION ALL
  61. SELECT abs(a), abs(b) FROM t1
  62. }
  63. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  64. do_test tkt2927-2.5 {
  65. db eval {
  66. SELECT a, abs(b) FROM t1
  67. UNION ALL
  68. SELECT a, b FROM t1
  69. }
  70. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  71. do_test tkt2927-2.6 {
  72. db eval {
  73. SELECT a, abs(b) FROM t1
  74. UNION ALL
  75. SELECT a, abs(b) FROM t1
  76. }
  77. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  78. do_test tkt2927-2.7 {
  79. db eval {
  80. SELECT a, abs(b) FROM t1
  81. UNION ALL
  82. SELECT abs(a), b FROM t1
  83. }
  84. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  85. do_test tkt2927-2.8 {
  86. db eval {
  87. SELECT a, abs(b) FROM t1
  88. UNION ALL
  89. SELECT abs(a), abs(b) FROM t1
  90. }
  91. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  92. do_test tkt2927-2.9 {
  93. db eval {
  94. SELECT abs(a), b FROM t1
  95. UNION ALL
  96. SELECT a, b FROM t1
  97. }
  98. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  99. do_test tkt2927-2.10 {
  100. db eval {
  101. SELECT abs(a), b FROM t1
  102. UNION ALL
  103. SELECT a, abs(b) FROM t1
  104. }
  105. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  106. do_test tkt2927-2.11 {
  107. db eval {
  108. SELECT abs(a), b FROM t1
  109. UNION ALL
  110. SELECT abs(a), b FROM t1
  111. }
  112. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  113. do_test tkt2927-2.12 {
  114. db eval {
  115. SELECT abs(a), b FROM t1
  116. UNION ALL
  117. SELECT abs(a), abs(b) FROM t1
  118. }
  119. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  120. do_test tkt2927-2.13 {
  121. db eval {
  122. SELECT abs(a), abs(b) FROM t1
  123. UNION ALL
  124. SELECT a, b FROM t1
  125. }
  126. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  127. do_test tkt2927-2.14 {
  128. db eval {
  129. SELECT abs(a), abs(b) FROM t1
  130. UNION ALL
  131. SELECT a, abs(b) FROM t1
  132. }
  133. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  134. do_test tkt2927-2.15 {
  135. db eval {
  136. SELECT abs(a), abs(b) FROM t1
  137. UNION ALL
  138. SELECT abs(a), b FROM t1
  139. }
  140. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  141. do_test tkt2927-2.16 {
  142. db eval {
  143. SELECT abs(a), abs(b) FROM t1
  144. UNION ALL
  145. SELECT abs(a), abs(b) FROM t1
  146. }
  147. } {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}
  148. do_test tkt2927-3.1 {
  149. db eval {
  150. SELECT a, b FROM t1
  151. UNION
  152. SELECT a, b FROM t1
  153. ORDER BY 1
  154. }
  155. } {1 11 2 22 3 33 4 44 5 55}
  156. do_test tkt2927-3.2 {
  157. db eval {
  158. SELECT a, b FROM t1
  159. UNION
  160. SELECT a, abs(b) FROM t1
  161. ORDER BY 1
  162. }
  163. } {1 11 2 22 3 33 4 44 5 55}
  164. do_test tkt2927-3.3 {
  165. db eval {
  166. SELECT a, b FROM t1
  167. UNION
  168. SELECT abs(a), b FROM t1
  169. ORDER BY 1
  170. }
  171. } {1 11 2 22 3 33 4 44 5 55}
  172. do_test tkt2927-3.4 {
  173. db eval {
  174. SELECT a, b FROM t1
  175. UNION
  176. SELECT abs(a), abs(b) FROM t1
  177. ORDER BY 1
  178. }
  179. } {1 11 2 22 3 33 4 44 5 55}
  180. do_test tkt2927-3.5 {
  181. db eval {
  182. SELECT a, abs(b) FROM t1
  183. UNION
  184. SELECT a, b FROM t1
  185. ORDER BY 1
  186. }
  187. } {1 11 2 22 3 33 4 44 5 55}
  188. do_test tkt2927-3.6 {
  189. db eval {
  190. SELECT a, abs(b) FROM t1
  191. UNION
  192. SELECT a, abs(b) FROM t1
  193. ORDER BY 1
  194. }
  195. } {1 11 2 22 3 33 4 44 5 55}
  196. do_test tkt2927-3.7 {
  197. db eval {
  198. SELECT a, abs(b) FROM t1
  199. UNION
  200. SELECT abs(a), b FROM t1
  201. ORDER BY 1
  202. }
  203. } {1 11 2 22 3 33 4 44 5 55}
  204. do_test tkt2927-3.8 {
  205. db eval {
  206. SELECT a, abs(b) FROM t1
  207. UNION
  208. SELECT abs(a), abs(b) FROM t1
  209. ORDER BY 1
  210. }
  211. } {1 11 2 22 3 33 4 44 5 55}
  212. do_test tkt2927-3.9 {
  213. db eval {
  214. SELECT abs(a), b FROM t1
  215. UNION
  216. SELECT a, b FROM t1
  217. ORDER BY 1
  218. }
  219. } {1 11 2 22 3 33 4 44 5 55}
  220. do_test tkt2927-3.10 {
  221. db eval {
  222. SELECT abs(a), b FROM t1
  223. UNION
  224. SELECT a, abs(b) FROM t1
  225. ORDER BY 1
  226. }
  227. } {1 11 2 22 3 33 4 44 5 55}
  228. do_test tkt2927-3.11 {
  229. db eval {
  230. SELECT abs(a), b FROM t1
  231. UNION
  232. SELECT abs(a), b FROM t1
  233. ORDER BY 1
  234. }
  235. } {1 11 2 22 3 33 4 44 5 55}
  236. do_test tkt2927-3.12 {
  237. db eval {
  238. SELECT abs(a), b FROM t1
  239. UNION
  240. SELECT abs(a), abs(b) FROM t1
  241. ORDER BY 1
  242. }
  243. } {1 11 2 22 3 33 4 44 5 55}
  244. do_test tkt2927-3.13 {
  245. db eval {
  246. SELECT abs(a), abs(b) FROM t1
  247. UNION
  248. SELECT a, b FROM t1
  249. ORDER BY 1
  250. }
  251. } {1 11 2 22 3 33 4 44 5 55}
  252. do_test tkt2927-3.14 {
  253. db eval {
  254. SELECT abs(a), abs(b) FROM t1
  255. UNION
  256. SELECT a, abs(b) FROM t1
  257. ORDER BY 1
  258. }
  259. } {1 11 2 22 3 33 4 44 5 55}
  260. do_test tkt2927-3.15 {
  261. db eval {
  262. SELECT abs(a), abs(b) FROM t1
  263. UNION
  264. SELECT abs(a), b FROM t1
  265. ORDER BY 1
  266. }
  267. } {1 11 2 22 3 33 4 44 5 55}
  268. do_test tkt2927-3.16 {
  269. db eval {
  270. SELECT abs(a), abs(b) FROM t1
  271. UNION
  272. SELECT abs(a), abs(b) FROM t1
  273. ORDER BY 1
  274. }
  275. } {1 11 2 22 3 33 4 44 5 55}
  276. do_test tkt2927-4.1 {
  277. db eval {
  278. SELECT a+b, a-b, a, b FROM t1
  279. UNION ALL
  280. SELECT a+b, a-b, a, b FROM t1
  281. }
  282. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  283. do_test tkt2927-4.2 {
  284. db eval {
  285. SELECT a+b, a-b, a, b FROM t1
  286. UNION ALL
  287. SELECT a+b, a-b, a, abs(b) FROM t1
  288. }
  289. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  290. do_test tkt2927-4.3 {
  291. db eval {
  292. SELECT a+b, a-b, a, b FROM t1
  293. UNION ALL
  294. SELECT a+b, a-b, abs(a), b FROM t1
  295. }
  296. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  297. do_test tkt2927-4.4 {
  298. db eval {
  299. SELECT a+b, a-b, a, b FROM t1
  300. UNION ALL
  301. SELECT a+b, a-b, abs(a), abs(b) FROM t1
  302. }
  303. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  304. do_test tkt2927-4.5 {
  305. db eval {
  306. SELECT a+b, a-b, a, abs(b) FROM t1
  307. UNION ALL
  308. SELECT a+b, a-b, a, b FROM t1
  309. }
  310. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  311. do_test tkt2927-4.6 {
  312. db eval {
  313. SELECT a+b, a-b, a, abs(b) FROM t1
  314. UNION ALL
  315. SELECT a+b, a-b, a, abs(b) FROM t1
  316. }
  317. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  318. do_test tkt2927-4.7 {
  319. db eval {
  320. SELECT a+b, a-b, a, abs(b) FROM t1
  321. UNION ALL
  322. SELECT a+b, a-b, abs(a), b FROM t1
  323. }
  324. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  325. do_test tkt2927-4.8 {
  326. db eval {
  327. SELECT a+b, a-b, a, abs(b) FROM t1
  328. UNION ALL
  329. SELECT a+b, a-b, abs(a), abs(b) FROM t1
  330. }
  331. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  332. do_test tkt2927-4.9 {
  333. db eval {
  334. SELECT a+b, a-b, abs(a), b FROM t1
  335. UNION ALL
  336. SELECT a+b, a-b, a, b FROM t1
  337. }
  338. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  339. do_test tkt2927-4.10 {
  340. db eval {
  341. SELECT a+b, a-b, abs(a), b FROM t1
  342. UNION ALL
  343. SELECT a+b, a-b, a, abs(b) FROM t1
  344. }
  345. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  346. do_test tkt2927-4.11 {
  347. db eval {
  348. SELECT a+b, a-b, abs(a), b FROM t1
  349. UNION ALL
  350. SELECT a+b, a-b, abs(a), b FROM t1
  351. }
  352. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  353. do_test tkt2927-4.12 {
  354. db eval {
  355. SELECT a+b, a-b, abs(a), b FROM t1
  356. UNION ALL
  357. SELECT a+b, a-b, abs(a), abs(b) FROM t1
  358. }
  359. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  360. do_test tkt2927-4.13 {
  361. db eval {
  362. SELECT a+b, a-b, abs(a), abs(b) FROM t1
  363. UNION ALL
  364. SELECT a+b, a-b, a, b FROM t1
  365. }
  366. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  367. do_test tkt2927-4.14 {
  368. db eval {
  369. SELECT a+b, a-b, abs(a), abs(b) FROM t1
  370. UNION ALL
  371. SELECT a+b, a-b, a, abs(b) FROM t1
  372. }
  373. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  374. do_test tkt2927-4.15 {
  375. db eval {
  376. SELECT a+b, a-b, abs(a), abs(b) FROM t1
  377. UNION ALL
  378. SELECT a+b, a-b, abs(a), b FROM t1
  379. }
  380. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  381. do_test tkt2927-4.16 {
  382. db eval {
  383. SELECT a+b, a-b, abs(a), abs(b) FROM t1
  384. UNION ALL
  385. SELECT a+b, a-b, abs(a), abs(b) FROM t1
  386. }
  387. } {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}
  388. do_test tkt2927-5.1 {
  389. db eval {
  390. SELECT a, b FROM t1
  391. EXCEPT
  392. SELECT a, b FROM t1
  393. }
  394. } {}
  395. do_test tkt2927-5.2 {
  396. db eval {
  397. SELECT a, b FROM t1
  398. EXCEPT
  399. SELECT a, abs(b) FROM t1
  400. }
  401. } {}
  402. do_test tkt2927-5.3 {
  403. db eval {
  404. SELECT a, b FROM t1
  405. EXCEPT
  406. SELECT abs(a), b FROM t1
  407. }
  408. } {}
  409. do_test tkt2927-5.4 {
  410. db eval {
  411. SELECT a, b FROM t1
  412. EXCEPT
  413. SELECT abs(a), abs(b) FROM t1
  414. }
  415. } {}
  416. do_test tkt2927-5.5 {
  417. db eval {
  418. SELECT a, abs(b) FROM t1
  419. EXCEPT
  420. SELECT a, b FROM t1
  421. }
  422. } {}
  423. do_test tkt2927-5.6 {
  424. db eval {
  425. SELECT a, abs(b) FROM t1
  426. EXCEPT
  427. SELECT a, abs(b) FROM t1
  428. }
  429. } {}
  430. do_test tkt2927-5.7 {
  431. db eval {
  432. SELECT a, abs(b) FROM t1
  433. EXCEPT
  434. SELECT abs(a), b FROM t1
  435. }
  436. } {}
  437. do_test tkt2927-5.8 {
  438. db eval {
  439. SELECT a, abs(b) FROM t1
  440. EXCEPT
  441. SELECT abs(a), abs(b) FROM t1
  442. }
  443. } {}
  444. do_test tkt2927-5.9 {
  445. db eval {
  446. SELECT abs(a), b FROM t1
  447. EXCEPT
  448. SELECT a, b FROM t1
  449. }
  450. } {}
  451. do_test tkt2927-5.10 {
  452. db eval {
  453. SELECT abs(a), b FROM t1
  454. EXCEPT
  455. SELECT a, abs(b) FROM t1
  456. }
  457. } {}
  458. do_test tkt2927-5.11 {
  459. db eval {
  460. SELECT abs(a), b FROM t1
  461. EXCEPT
  462. SELECT abs(a), b FROM t1
  463. }
  464. } {}
  465. do_test tkt2927-5.12 {
  466. db eval {
  467. SELECT abs(a), b FROM t1
  468. EXCEPT
  469. SELECT abs(a), abs(b) FROM t1
  470. }
  471. } {}
  472. do_test tkt2927-5.13 {
  473. db eval {
  474. SELECT abs(a), abs(b) FROM t1
  475. EXCEPT
  476. SELECT a, b FROM t1
  477. }
  478. } {}
  479. do_test tkt2927-5.14 {
  480. db eval {
  481. SELECT abs(a), abs(b) FROM t1
  482. EXCEPT
  483. SELECT a, abs(b) FROM t1
  484. }
  485. } {}
  486. do_test tkt2927-5.15 {
  487. db eval {
  488. SELECT abs(a), abs(b) FROM t1
  489. EXCEPT
  490. SELECT abs(a), b FROM t1
  491. }
  492. } {}
  493. do_test tkt2927-5.16 {
  494. db eval {
  495. SELECT abs(a), abs(b) FROM t1
  496. EXCEPT
  497. SELECT abs(a), abs(b) FROM t1
  498. }
  499. } {}
  500. do_test tkt2927-6.1 {
  501. db eval {
  502. SELECT a, b FROM t1
  503. INTERSECT
  504. SELECT a, b FROM t1
  505. ORDER BY 1
  506. }
  507. } {1 11 2 22 3 33 4 44 5 55}
  508. do_test tkt2927-6.2 {
  509. db eval {
  510. SELECT a, b FROM t1
  511. INTERSECT
  512. SELECT a, abs(b) FROM t1
  513. ORDER BY 1
  514. }
  515. } {1 11 2 22 3 33 4 44 5 55}
  516. do_test tkt2927-6.3 {
  517. db eval {
  518. SELECT a, b FROM t1
  519. INTERSECT
  520. SELECT abs(a), b FROM t1
  521. ORDER BY 1
  522. }
  523. } {1 11 2 22 3 33 4 44 5 55}
  524. do_test tkt2927-6.4 {
  525. db eval {
  526. SELECT a, b FROM t1
  527. INTERSECT
  528. SELECT abs(a), abs(b) FROM t1
  529. ORDER BY 1
  530. }
  531. } {1 11 2 22 3 33 4 44 5 55}
  532. do_test tkt2927-6.5 {
  533. db eval {
  534. SELECT a, abs(b) FROM t1
  535. INTERSECT
  536. SELECT a, b FROM t1
  537. ORDER BY 1
  538. }
  539. } {1 11 2 22 3 33 4 44 5 55}
  540. do_test tkt2927-6.6 {
  541. db eval {
  542. SELECT a, abs(b) FROM t1
  543. INTERSECT
  544. SELECT a, abs(b) FROM t1
  545. ORDER BY 1
  546. }
  547. } {1 11 2 22 3 33 4 44 5 55}
  548. do_test tkt2927-6.7 {
  549. db eval {
  550. SELECT a, abs(b) FROM t1
  551. INTERSECT
  552. SELECT abs(a), b FROM t1
  553. ORDER BY 1
  554. }
  555. } {1 11 2 22 3 33 4 44 5 55}
  556. do_test tkt2927-6.8 {
  557. db eval {
  558. SELECT a, abs(b) FROM t1
  559. INTERSECT
  560. SELECT abs(a), abs(b) FROM t1
  561. ORDER BY 1
  562. }
  563. } {1 11 2 22 3 33 4 44 5 55}
  564. do_test tkt2927-6.9 {
  565. db eval {
  566. SELECT abs(a), b FROM t1
  567. INTERSECT
  568. SELECT a, b FROM t1
  569. ORDER BY 1
  570. }
  571. } {1 11 2 22 3 33 4 44 5 55}
  572. do_test tkt2927-6.10 {
  573. db eval {
  574. SELECT abs(a), b FROM t1
  575. INTERSECT
  576. SELECT a, abs(b) FROM t1
  577. ORDER BY 1
  578. }
  579. } {1 11 2 22 3 33 4 44 5 55}
  580. do_test tkt2927-6.11 {
  581. db eval {
  582. SELECT abs(a), b FROM t1
  583. INTERSECT
  584. SELECT abs(a), b FROM t1
  585. ORDER BY 1
  586. }
  587. } {1 11 2 22 3 33 4 44 5 55}
  588. do_test tkt2927-6.12 {
  589. db eval {
  590. SELECT abs(a), b FROM t1
  591. INTERSECT
  592. SELECT abs(a), abs(b) FROM t1
  593. ORDER BY 1
  594. }
  595. } {1 11 2 22 3 33 4 44 5 55}
  596. do_test tkt2927-6.13 {
  597. db eval {
  598. SELECT abs(a), abs(b) FROM t1
  599. INTERSECT
  600. SELECT a, b FROM t1
  601. ORDER BY 1
  602. }
  603. } {1 11 2 22 3 33 4 44 5 55}
  604. do_test tkt2927-6.14 {
  605. db eval {
  606. SELECT abs(a), abs(b) FROM t1
  607. INTERSECT
  608. SELECT a, abs(b) FROM t1
  609. ORDER BY 1
  610. }
  611. } {1 11 2 22 3 33 4 44 5 55}
  612. do_test tkt2927-6.15 {
  613. db eval {
  614. SELECT abs(a), abs(b) FROM t1
  615. INTERSECT
  616. SELECT abs(a), b FROM t1
  617. ORDER BY 1
  618. }
  619. } {1 11 2 22 3 33 4 44 5 55}
  620. do_test tkt2927-6.16 {
  621. db eval {
  622. SELECT abs(a), abs(b) FROM t1
  623. INTERSECT
  624. SELECT abs(a), abs(b) FROM t1
  625. ORDER BY 1
  626. }
  627. } {1 11 2 22 3 33 4 44 5 55}
  628. # Ticket #3092 is the same bug. But another test case never hurts.
  629. #
  630. do_test tkt2927-7.1 {
  631. db eval {
  632. CREATE TABLE host (
  633. hostname text not null primary key,
  634. consoleHost text,
  635. consolePort text
  636. );
  637. INSERT INTO "host" VALUES('aald04','aalp03','4');
  638. INSERT INTO "host" VALUES('aald17','aalp01','1');
  639. CREATE VIEW consolemap1a as
  640. select hostname, consolehost, '/dev/cuaD0.' || (consoleport-1) consoleport
  641. from host where consolehost='aalp01';
  642. CREATE VIEW consolemap1b as
  643. select hostname hostname, consolehost consolehost, '/dev/cuaD' ||
  644. substr('01',1+((consoleport-1)/16),1) ||
  645. substr('0123456789abcdef',1+((consoleport-1)%16),1) consoleport
  646. from host where consolehost='aalp03';
  647. CREATE VIEW consolemap1 as
  648. select * from consolemap1a
  649. union
  650. select * from consolemap1b;
  651. SELECT * from consolemap1b;
  652. }
  653. } {aald04 aalp03 /dev/cuaD03}
  654. do_test tkt2927-7.2 {
  655. db eval {
  656. SELECT * FROM consolemap1
  657. }
  658. } {aald04 aalp03 /dev/cuaD03 aald17 aalp01 /dev/cuaD0.0}
  659. finish_test