Posted to tcl by sebres at Thu Jan 17 16:07:34 GMT 2019view raw

  1. ## Sqlite3 test case btree02-110 wrapped to illustrates check-in
  2. ## https://www2.sqlite.org/src/fdiff?v1=a0f33669ba766322&v2=7555a5440453d900
  3. ##
  4. ## Variant "new, amend +160" is the current state of trunk (sqlite3-repo).
  5.  
  6. ## ================= 8.6.8 ===================
  7. ## old, amend
  8. ## ==> 20 ==? 27 (expected)
  9. ## new, amend
  10. ## ==> 10 ==? 10 (expected)
  11. ## old, amend +160
  12. ## ==> 20 ==? 27 (expected)
  13. ## new, amend +160
  14. ## ==> 10 ==? 10 (expected)
  15. ##
  16. ## ================= 8.6.9.1 ===================
  17. ## old, amend
  18. ## ==> 27 ==? 27 (expected)
  19. ## new, amend
  20. ## ==> 24 ==? 10 (expected)
  21. ## old, amend +160
  22. ## ==> 20 ==? 27 (expected)
  23. ## new, amend +160
  24. ## ==> 10 ==? 10 (expected)
  25. ##
  26. ## ================= 8.5 (latest) ===================
  27. ## old, amend
  28. ## ==> 27 ==? 27 (expected)
  29. ## new, amend
  30. ## ==> 24 ==? 10 (expected)
  31. ## old, amend +160
  32. ## ==> 20 ==? 27 (expected)
  33. ## new, amend +160
  34. ## ==> 10 ==? 10 (expected)
  35.  
  36. ## =============================================================================
  37.  
  38. proc _init {{amend {}}} {
  39. catch {db close}
  40. sqlite3 db :memory:
  41. db eval "
  42. CREATE TABLE t1(a TEXT, ax INTEGER, b INT, PRIMARY KEY(a,ax)) WITHOUT ROWID;
  43. WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<10)
  44. INSERT INTO t1(a,ax,b) SELECT printf('%02x',i$amend), random(), i FROM c;
  45. CREATE INDEX t1a ON t1(a);
  46. CREATE TABLE t2(x,y);
  47. CREATE TABLE t3(cnt);
  48. WITH RECURSIVE c(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM c WHERE i<4)
  49. INSERT INTO t3(cnt) SELECT i FROM c;
  50. SELECT count(*) FROM t1;
  51. "
  52. }
  53.  
  54. foreach amend {{} +160} {
  55. ## old (3.26.0) test-case [3f5f60cd75]:
  56. puts "old, amend $amend"
  57. _init $amend
  58.  
  59. puts " ==> [eval {
  60. db eval BEGIN
  61. set i 0
  62. db eval {SELECT a, ax, b, cnt FROM t1 CROSS JOIN t3 WHERE b IS NOT NULL} {
  63. if {$a==""} {set a 0}
  64. if {$b==""} {set b 0}
  65. db eval {INSERT INTO t2(x,y) VALUES($b,$cnt)}
  66. # puts "a,b,cnt = ($a,$b,$cnt)"
  67. incr i
  68. if {$i%2==1} {
  69. set bx [expr {$b+1000}]
  70. # puts "INSERT ($a),$bx"
  71. db eval {INSERT INTO t1(a,ax,b) VALUES(printf('(%s)',$a),random(),$bx)}
  72.  
  73. } else {
  74. # puts "DELETE a=$a"
  75. db eval {DELETE FROM t1 WHERE a=$a}
  76.  
  77. }
  78. db eval {COMMIT; BEGIN}
  79. }
  80. db one {COMMIT; SELECT count(*) FROM t1;}
  81. }] ==? 27 (expected)"
  82. db close
  83.  
  84. ## new fixed test-case [d9e4f95746] at 2018-12-27 16:55:01 on branch trunk:
  85. puts "new, amend $amend"
  86. _init $amend
  87.  
  88. puts " ==> [eval {
  89. db eval BEGIN
  90. set i 0
  91. # showt1
  92. db eval {SELECT a, ax, b, cnt FROM t1 CROSS JOIN t3 WHERE b IS NOT NULL} {
  93. if {$a==""} continue
  94. db eval {INSERT INTO t2(x,y) VALUES($b,$cnt)}
  95. # puts "a,b,cnt = ($a,$b,$cnt)"
  96. incr i
  97. if {$i%2==1} {
  98. set bx [expr {$b+1000}]
  99. # puts "INSERT ($a),$bx"
  100. db eval {INSERT INTO t1(a,ax,b) VALUES(printf('(%s)',$a),random(),$bx)}
  101. # showt1
  102. } else {
  103. # puts "DELETE a=$a"
  104. db eval {DELETE FROM t1 WHERE a=$a}
  105. # showt1
  106. }
  107. db eval {COMMIT; BEGIN}
  108. }
  109. db one {COMMIT; SELECT count(*) FROM t1;}
  110. }] ==? 10 (expected)"
  111. db close
  112. }
  113.