title: How to join data.tables when one is a lookup table?
body: <p>I'm having trouble applying a simple data.table join example to a larger (10GB) data set.  merge() works just fine on data.frames with the larger dataset, although I'd love to take advantage of the speed in data.table.  Could anyone point out what I'm misunderstanding about data.table (and the error message in particular)?</p>
  
  <p>Here is the simple example (derived from this thread: <a href="https://stackoverflow.com/questions/22699596/join-of-two-data-tables-fails">Join of two data.tables fails</a>).  </p>
  
  <pre><code># The data of interest.
  (DT &lt;- data.table(id    = c(rep(1154:1155, 2), 1160),
                    price = c(1.99, 2.50, 15.63, 15.00, 0.75), 
                    key   = "id"))
  
       id price
  1: 1154  1.99
  2: 1154 15.63
  3: 1155  2.50
  4: 1155 15.00
  5: 1160  0.75
  
  # Lookup table.
  (lookup &lt;- data.table(id      = 1153:1160, 
                        version = c(1,1,3,4,2,1,1,2), 
                        yr      = rep(2006, 4), 
                        key     = "id"))
  
       id version   yr
  1: 1153       1 2006
  2: 1154       1 2006
  3: 1155       3 2006
  4: 1156       4 2006
  5: 1157       2 2006
  6: 1158       1 2006
  7: 1159       1 2006
  8: 1160       2 2006
  
  # The desired table.  Note: lookup[DT] works as well.
  DT[lookup, allow.cartesian = T, nomatch=0]
  
       id price version   yr
  1: 1154  1.99       1 2006
  2: 1154 15.63       1 2006
  3: 1155  2.50       3 2006
  4: 1155 15.00       3 2006
  5: 1160  0.75       2 2006
  </code></pre>
  
  <p>The larger data set consists of two data.frames: temp.3561 (the dataset of interest) and temp.versions (the lookup dataset).  They have the same structure as DT and lookup (above), respectively.  Using merge() works well, however my application of data.table is clearly flawed:</p>
  
  <pre><code># Merge data.frames: works just fine
  long.merged         &lt;- merge(temp.versions, temp.3561, by = "id")
  
  # Convert the data.frames to data.tables
  DTtemp.3561         &lt;- as.data.table(temp.3561)
  DTtemp.versions     &lt;- as.data.table(temp.versions)
  
  # Merge the data.tables: doesn't work
  setkey(DTtemp.3561, id)
  setkey(DTtemp.versions, id)
  DTlong.merged       &lt;- merge(DTtemp.versions, DTtemp.3561, by = "id")
  
  Error in vecseq(f__, len__, if (allow.cartesian) NULL else as.integer(max(nrow(x),  : 
    Join results in 11277332 rows; more than 7946667 = max(nrow(x),nrow(i)). Check for duplicate 
  key values in i, each of which join to the same group in x over and over again. If that's ok, 
  try including `j` and dropping `by` (by-without-by) so that j runs for each group to avoid the 
  large allocation. If you are sure you wish to proceed, rerun with allow.cartesian=TRUE. 
  Otherwise, please search for this error message in the FAQ, Wiki, Stack Overflow and datatable-
  help for advice.
  </code></pre>
  
  <p>DTtemp.versions has the same structure as lookup (in the simple example), and the key "id" consists of 779,473 unique values (no duplicates).</p>
  
  <p>DTtemp3561 has the same structure as DT (in the simple example) plus a few other variables, but its key "id" only has 829 unique values despite the 7,946,667 observations (lots of duplicates).</p>
  
  <p>Since I'm just trying to add version numbers and years from DTtemp.versions to each observation in DTtemp.3561, the merged data.table should have the same number of observations as DTtemp.3561 (7,946,667).  Specifically, I don't understand why merge() generates "excess" observations when using data.table but not when using data.frame.</p>
  
  <p>Likewise</p>
  
  <pre><code># Same error message, but with 12,055,777 observations
  altDTlong.merged   &lt;- DTtemp.3561[DTtemp.versions]
  
  # Same error message, but with 11,277,332 observations
  alt2DTlong.merged  &lt;- DTtemp.versions[DTtemp.3561]
  </code></pre>
  
  <p>Including allow.cartesian=T and nomatch=0 doesn't drop the "excess" observations.</p>
  
  <p>Oddly, if I truncate the dataset of interest to have 10 observatons, merge() works fine on both data.frames and data.tables.</p>
  
  <pre><code># Merge short DF: works just fine
  short.3561         &lt;- temp.3561[-(11:7946667),]
  short.merged       &lt;- merge(temp.versions, short.3561, by = "id")
  
  # Merge short DT
  DTshort.3561       &lt;- data.table(short.3561, key = "id")
  DTshort.merged     &lt;- merge(DTtemp.versions, DTshort.3561, by = "id")
  </code></pre>
  
  <p>I've been through the FAQ (<a href="http://datatable.r-forge.r-project.org/datatable-faq.pdf" rel="nofollow noreferrer">http://datatable.r-forge.r-project.org/datatable-faq.pdf</a>, and 1.12 in particular).  How would you suggest thinking about this?</p>