8 ms·

Hmm, CONCAT all fields in each row into a string, hash that string, use that as a derived table where you order the rows definitively, and concat all rows from

Hmm, CONCAT all fields in each row into a string, hash that string, use that as a derived table where you order the rows definitively, and concat all rows from that derived table into a string and hash that string. Repeat for second table. Compare hashes. You can do in a single query with two derived tables. You could do it without the inner hashing, but that seems more likely to exhaust memory if your rows are big enough, since you're basically hashing an entire table at that point and that's large string in memory.

dragonwriter 3y ago
Note that if you have non-fixed-length fields, this can in principal produce false positives.
- kbenson 3y ago
  Yeah, I was just thinking of that, and how you'd probably want to concat with some level of structure to eliminate this. e.g. by adding a delimiter between fields and doing a find and replace on that delimiter (carefully, e.g. CSV rules with quotes and escapes). Whatever it takes to definitively differentiate foo/barfoo from foobar/foo. Maybe just prefixing every field with its length is sufficient. I'm sure there's some relevant papers on the simplest way to achieve this I can and should look up. Hopefully they don't summarize as it being a much harder problem to do right. ;)
  - dragonwriter 3y ago
    Instead of CONCAT you could something like JSON serialization which preserves structure (which avoids reinventing the wheel on quoting, etc.) for the rows, which may not be maximally efficient but is probably the path of least resistance.
    - kbenson 3y ago
      Yeah, as long as every RDBMS has a JSON serialization method now (do they? I'm not up on SQL standards or where each RDBMS is implmenetation of them). Otherwise if we're shipping our own serialization as a procedure from core SQL functions, something simpler might be sufficient. Replace backslash with double backslash, replace pipe with backslash pipe, concatenate with pipes.
  - Dylan16807 3y ago
    > e.g. by adding a delimiter between fields and doing a find and replace on that delimiter (carefully, e.g. CSV rules with quotes and escapes) Once you set up "CSV rules" then you won't need to find and replace anything, CSV is perfectly sufficient by itself.
feoren 3y ago
Description | Notes -------------------------- Fruit | Flies Fast Fruit Flies | Fast

dragonwriter 3y ago

Note that if you have non-fixed-length fields, this can in principal produce false positives.

kbenson 3y ago
Yeah, I was just thinking of that, and how you'd probably want to concat with some level of structure to eliminate this. e.g. by adding a delimiter between fields and doing a find and replace on that delimiter (carefully, e.g. CSV rules with quotes and escapes). Whatever it takes to definitively differentiate foo/barfoo from foobar/foo. Maybe just prefixing every field with its length is sufficient. I'm sure there's some relevant papers on the simplest way to achieve this I can and should look up. Hopefully they don't summarize as it being a much harder problem to do right. ;)
- dragonwriter 3y ago
  Instead of CONCAT you could something like JSON serialization which preserves structure (which avoids reinventing the wheel on quoting, etc.) for the rows, which may not be maximally efficient but is probably the path of least resistance.
  - kbenson 3y ago
    Yeah, as long as every RDBMS has a JSON serialization method now (do they? I'm not up on SQL standards or where each RDBMS is implmenetation of them). Otherwise if we're shipping our own serialization as a procedure from core SQL functions, something simpler might be sufficient. Replace backslash with double backslash, replace pipe with backslash pipe, concatenate with pipes.
- Dylan16807 3y ago
  > e.g. by adding a delimiter between fields and doing a find and replace on that delimiter (carefully, e.g. CSV rules with quotes and escapes) Once you set up "CSV rules" then you won't need to find and replace anything, CSV is perfectly sufficient by itself.

feoren 3y ago

Description | Notes -------------------------- Fruit | Flies Fast Fruit Flies | Fast