/ Check-in [0360fec7]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Test case updates.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1:0360fec7c093870269211447e9642b5ee34ff778
User & Date: drh 2013-06-04 15:31:16
Context
2013-06-04
18:03
Get the index-only optimization working for OR queries. check-in: 774d5ff8 user: drh tags: nextgen-query-plan-exp
15:31
Test case updates. check-in: 0360fec7 user: drh tags: nextgen-query-plan-exp
13:37
Better determination of when an index is UNIQUE. check-in: 63fd025a user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/orderby1.test.

    44     44           (NULL, 2, 1, 'two-a'),
    45     45           (NULL, 3, 1, 'three-a');
    46     46       COMMIT;
    47     47     }
    48     48   } {}
    49     49   do_test 1.1a {
    50     50     db eval {
    51         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
           51  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
    52     52     }
    53     53   } {one-a one-c two-a two-b three-a three-c}
    54     54   
    55     55   # Verify that the ORDER BY clause is optimized out
    56     56   #
    57     57   do_test 1.1b {
    58     58     db eval {
................................................................................
    62     62   } {~/ORDER BY/}  ;# ORDER BY optimized out
    63     63   
    64     64   # The same query with ORDER BY clause optimization disabled via + operators
    65     65   # should give exactly the same answer.
    66     66   #
    67     67   do_test 1.2a {
    68     68     db eval {
    69         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
           69  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
    70     70     }
    71     71   } {one-a one-c two-a two-b three-a three-c}
    72     72   
    73     73   # The output is sorted manually in this case.
    74     74   #
    75     75   do_test 1.2b {
    76     76     db eval {
    77     77       EXPLAIN QUERY PLAN
    78         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
           78  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
    79     79     }
    80     80   } {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms
    81     81   
    82     82   # The same query with ORDER BY optimizations turned off via built-in test.
    83     83   #
    84     84   do_test 1.3a {
    85     85     optimization_control db order-by-idx-join 0
    86     86     db cache flush
    87     87     db eval {
    88         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
           88  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
    89     89     }
    90     90   } {one-a one-c two-a two-b three-a three-c}
    91     91   do_test 1.3b {
    92     92     db eval {
    93     93       EXPLAIN QUERY PLAN
    94         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
           94  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
    95     95     }
    96     96   } {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
    97     97   optimization_control db all 1
    98     98   db cache flush
    99     99   
   100    100   # Reverse order sorts
   101    101   #
   102    102   do_test 1.4a {
   103    103     db eval {
   104         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
          104  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
   105    105     }
   106    106   } {three-a three-c two-a two-b one-a one-c}
   107    107   do_test 1.4b {
   108    108     db eval {
   109         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn
          109  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn
   110    110     }
   111    111   } {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
   112    112   do_test 1.4c {
   113    113     db eval {
   114    114       EXPLAIN QUERY PLAN
   115         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
          115  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
   116    116     }
   117         -} {~/ORDER BY/}  ;# optimized out
          117  +} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC
   118    118   
   119    119   
   120    120   do_test 1.5a {
   121    121     db eval {
   122         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
          122  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
   123    123     }
   124    124   } {one-c one-a two-b two-a three-c three-a}
   125    125   do_test 1.5b {
   126    126     db eval {
   127         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
          127  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
   128    128     }
   129    129   } {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
   130    130   do_test 1.5c {
   131    131     db eval {
   132    132       EXPLAIN QUERY PLAN
   133         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
          133  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
   134    134     }
   135         -} {~/ORDER BY/}  ;# optimized out
          135  +} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC
   136    136   
   137    137   do_test 1.6a {
   138    138     db eval {
   139         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
          139  +    SELECT name FROM album CROSS JOIN track USING (aid)
          140  +     ORDER BY title DESC, tn DESC
   140    141     }
   141    142   } {three-c three-a two-b two-a one-c one-a}
   142    143   do_test 1.6b {
   143    144     db eval {
   144         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
          145  +    SELECT name FROM album CROSS JOIN track USING (aid)
          146  +     ORDER BY +title DESC, +tn DESC
   145    147     }
   146    148   } {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
   147    149   do_test 1.6c {
   148    150     db eval {
   149    151       EXPLAIN QUERY PLAN
   150         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
          152  +    SELECT name FROM album CROSS JOIN track USING (aid)
          153  +     ORDER BY title DESC, tn DESC
   151    154     }
   152         -} {~/ORDER BY/}  ;# ORDER BY optimized-out
          155  +} {~/ORDER BY/}  ;# ORDER BY 
   153    156   
   154    157   
   155    158   # Reconstruct the test data to use indices rather than integer primary keys.
   156    159   #
   157    160   do_test 2.0 {
   158    161     db eval {
   159    162       BEGIN;
................................................................................
   179    182           (20, 1, 'two-a'),
   180    183           (3,  1, 'three-a');
   181    184       COMMIT;
   182    185     }
   183    186   } {}
   184    187   do_test 2.1a {
   185    188     db eval {
   186         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
          189  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
   187    190     }
   188    191   } {one-a one-c two-a two-b three-a three-c}
   189    192   
   190    193   # Verify that the ORDER BY clause is optimized out
   191    194   #
   192    195   do_test 2.1b {
   193    196     db eval {
   194    197       EXPLAIN QUERY PLAN
   195         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
          198  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
   196    199     }
   197         -} {~/ORDER BY/}  ;# ORDER BY optimized out
          200  +} {/ORDER BY/}  ;# ORDER BY required because of missing aid term in ORDER BY
   198    201   
   199    202   do_test 2.1c {
   200    203     db eval {
   201         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, aid, tn
          204  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, aid, tn
   202    205     }
   203    206   } {one-a one-c two-a two-b three-a three-c}
   204    207   do_test 2.1d {
   205    208     db eval {
   206    209       EXPLAIN QUERY PLAN
   207         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, aid, tn
          210  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, aid, tn
   208    211     }
   209         -} {~/ORDER BY/}  ;# ORDER BY optimized out
          212  +} {/ORDER BY/}  ;# ORDER BY required in this case
   210    213   
   211    214   # The same query with ORDER BY clause optimization disabled via + operators
   212    215   # should give exactly the same answer.
   213    216   #
   214    217   do_test 2.2a {
   215    218     db eval {
   216         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
          219  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
   217    220     }
   218    221   } {one-a one-c two-a two-b three-a three-c}
   219    222   
   220    223   # The output is sorted manually in this case.
   221    224   #
   222    225   do_test 2.2b {
   223    226     db eval {
   224    227       EXPLAIN QUERY PLAN
   225         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
          228  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
   226    229     }
   227    230   } {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms
   228    231   
   229    232   # The same query with ORDER BY optimizations turned off via built-in test.
   230    233   #
   231    234   do_test 2.3a {
   232    235     optimization_control db order-by-idx-join 0
   233    236     db cache flush
   234    237     db eval {
   235         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
          238  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
   236    239     }
   237    240   } {one-a one-c two-a two-b three-a three-c}
   238    241   do_test 2.3b {
   239    242     db eval {
   240    243       EXPLAIN QUERY PLAN
   241         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
          244  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
   242    245     }
   243    246   } {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
   244    247   optimization_control db all 1
   245    248   db cache flush
   246    249   
   247    250   # Reverse order sorts
   248    251   #
   249    252   do_test 2.4a {
   250    253     db eval {
   251         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
          254  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
   252    255     }
   253    256   } {three-a three-c two-a two-b one-a one-c}
   254    257   do_test 2.4b {
   255    258     db eval {
   256         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn
          259  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn
   257    260     }
   258    261   } {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
   259    262   do_test 2.4c {
   260    263     db eval {
   261    264       EXPLAIN QUERY PLAN
   262         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
          265  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn
   263    266     }
   264         -} {~/ORDER BY/}  ;# optimized out
          267  +} {/ORDER BY/}  ;# separate sorting pass due to mixed DESC/ASC
   265    268   
   266    269   
   267    270   do_test 2.5a {
   268    271     db eval {
   269         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
          272  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
   270    273     }
   271    274   } {one-c one-a two-b two-a three-c three-a}
   272    275   do_test 2.5b {
   273    276     db eval {
   274         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
          277  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
   275    278     }
   276    279   } {one-c one-a two-b two-a three-c three-a}  ;# verify same order after sorting
   277    280   do_test 2.5c {
   278    281     db eval {
   279    282       EXPLAIN QUERY PLAN
   280         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
          283  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
   281    284     }
   282         -} {~/ORDER BY/}  ;# optimized out
          285  +} {/ORDER BY/}  ;# separate sorting pass due to mixed ASC/DESC
   283    286   
   284    287   do_test 2.6a {
   285    288     db eval {
   286         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
          289  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
   287    290     }
   288    291   } {three-c three-a two-b two-a one-c one-a}
   289    292   do_test 2.6b {
   290    293     db eval {
   291         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
          294  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
   292    295     }
   293    296   } {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
   294    297   do_test 2.6c {
   295    298     db eval {
   296    299       EXPLAIN QUERY PLAN
   297         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
          300  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
   298    301     }
   299         -} {~/ORDER BY/}  ;# ORDER BY optimized out
          302  +} {/ORDER BY/}  ;# ORDER BY required
   300    303   
   301    304   
   302    305   # Generate another test dataset, but this time using mixed ASC/DESC indices.
   303    306   #
   304    307   do_test 3.0 {
   305    308     db eval {
   306    309       BEGIN;
................................................................................
   344    347   } {~/ORDER BY/}  ;# ORDER BY optimized out
   345    348   
   346    349   # The same query with ORDER BY clause optimization disabled via + operators
   347    350   # should give exactly the same answer.
   348    351   #
   349    352   do_test 3.2a {
   350    353     db eval {
   351         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
          354  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
   352    355     }
   353    356   } {one-c one-a two-b two-a three-c three-a}
   354    357   
   355    358   # The output is sorted manually in this case.
   356    359   #
   357    360   do_test 3.2b {
   358    361     db eval {
   359    362       EXPLAIN QUERY PLAN
   360         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn DESC
          363  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn DESC
   361    364     }
   362    365   } {/ORDER BY/}   ;# separate sorting pass due to "+" on ORDER BY terms
   363    366   
   364    367   # The same query with ORDER BY optimizations turned off via built-in test.
   365    368   #
   366    369   do_test 3.3a {
   367    370     optimization_control db order-by-idx-join 0
   368    371     db cache flush
   369    372     db eval {
   370         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
          373  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
   371    374     }
   372    375   } {one-c one-a two-b two-a three-c three-a}
   373    376   do_test 3.3b {
   374    377     db eval {
   375    378       EXPLAIN QUERY PLAN
   376         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn DESC
          379  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn DESC
   377    380     }
   378    381   } {/ORDER BY/}   ;# separate sorting pass due to disabled optimization
   379    382   optimization_control db all 1
   380    383   db cache flush
   381    384   
   382    385   # Without the mixed ASC/DESC on ORDER BY
   383    386   #
   384    387   do_test 3.4a {
   385    388     db eval {
   386         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
          389  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
   387    390     }
   388    391   } {one-a one-c two-a two-b three-a three-c}
   389    392   do_test 3.4b {
   390    393     db eval {
   391         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title, +tn
          394  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title, +tn
   392    395     }
   393    396   } {one-a one-c two-a two-b three-a three-c}  ;# verify same order after sorting
   394    397   do_test 3.4c {
   395    398     db eval {
   396    399       EXPLAIN QUERY PLAN
   397         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title, tn
          400  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title, tn
   398    401     }
   399         -} {~/ORDER BY/}  ;# optimized out
          402  +} {/ORDER BY/}  ;# separate sorting pass due to mismatched DESC/ASC
   400    403   
   401    404   
   402    405   do_test 3.5a {
   403    406     db eval {
   404         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
          407  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
   405    408     }
   406    409   } {three-c three-a two-b two-a one-c one-a}
   407    410   do_test 3.5b {
   408    411     db eval {
   409         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
          412  +    SELECT name FROM album JOIN track USING (aid) ORDER BY +title DESC, +tn DESC
   410    413     }
   411    414   } {three-c three-a two-b two-a one-c one-a}  ;# verify same order after sorting
   412    415   do_test 3.5c {
   413    416     db eval {
   414    417       EXPLAIN QUERY PLAN
   415         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn DESC
          418  +    SELECT name FROM album JOIN track USING (aid) ORDER BY title DESC, tn DESC
   416    419     }
   417         -} {~/ORDER BY/}  ;# optimzed out
          420  +} {/ORDER BY/}  ;# separate sorting pass due to mismatched ASC/DESC
   418    421   
   419    422   
   420    423   do_test 3.6a {
   421    424     db eval {
   422    425       SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
   423    426     }
   424    427   } {three-a three-c two-a two-b one-a one-c}
   425    428   do_test 3.6b {
   426    429     db eval {
   427         -    SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY +title DESC, +tn
          430  +    SELECT name FROM album CROSS JOIN track USING (aid)
          431  +     ORDER BY +title DESC, +tn
   428    432     }
   429    433   } {three-a three-c two-a two-b one-a one-c}  ;# verify same order after sorting
   430    434   do_test 3.6c {
   431    435     db eval {
   432    436       EXPLAIN QUERY PLAN
   433    437       SELECT name FROM album CROSS JOIN track USING (aid) ORDER BY title DESC, tn
   434    438     }
   435    439   } {~/ORDER BY/}  ;# inverted ASC/DESC is optimized out
          440  +
          441  +# Ticket 5ed1772895bf3deeab78c5e3519b1da9165c541b (2013-06-04)
          442  +# Incorrect ORDER BY on an indexed JOIN
          443  +#
          444  +do_test 4.0 {
          445  +  db eval {
          446  +    CREATE TABLE t41(a INT UNIQUE NOT NULL, b INT NOT NULL);
          447  +    CREATE INDEX t41ba ON t41(b,a);
          448  +    CREATE TABLE t42(x INT NOT NULL REFERENCES t41(a), y INT NOT NULL);
          449  +    CREATE UNIQUE INDEX t42xy ON t42(x,y);
          450  +    INSERT INTO t41 VALUES(1,1),(3,1);
          451  +    INSERT INTO t42 VALUES(1,13),(1,15),(3,14),(3,16);
          452  +    
          453  +    SELECT b, y FROM t41 CROSS JOIN t42 ON x=a ORDER BY b, y;
          454  +  }
          455  +} {1 13 1 14 1 15 1 16}
          456  +
   436    457   
   437    458   
   438    459   finish_test