Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17395

REPLACE/INSERT ODKU: support WITHOUT OVERLAPS

Details

    • New Feature
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Versioned Tables
    • None

    Description

      Replace and Insert ODKU work differently, but both need to cope with Application periods

      Let's start from use cases.

      Say, there's an web site hosting hundreds of small online games (like, flash games).

      Typical ODKU use case (that doesn't use WITHOUT OVERLAPS): count number of players per game. The table could be

      CREATE TABLE stats (game VARCHAR(100), count INT, UNIQUE (game));

      And for every player of a specific game we do

      INSERT stats VALUES ($game_name, 1) ON DUPLICATE KEY UPDATE count=count+1;

      Now having WITHOUT OVERLAPS we can count number of players at any specific time. The table could be

      CREATE TABLE stats (
        game VARCHAR(100),
        count INT,
        start_time TIMESTAMP,
        end_time TIMESTAMP,
        PERIOD FOR playtime(start_time, end_time),
        UNIQUE (game, playtime WITHOUT OVERLAPS));

      and then, for every player

      INSERT stats VALUES ($game_name, 1, $start_time, $end_time) ON DUPLICATE KEY UPDATE count=count+1;

      Another example, a hotel booking site, where one can specify a hotel and the date range and then proceed to booking. More often than not visitors don't become customers and don't complete the booking, but, say, we will want to track the demand and record these incomplete bookings. So for every abandoned booking we increment the counter, much like above

      INSERT stats VALUES ($hotel, 1, $arrival, $departure) ON DUPLICATE KEY UPDATE count=count+1;

      This defines the behavior of ODKU. It has to do the INSERT for non-overlapping part of the range and UPDATE for the overlapping part. For example (with hotels):

      INSERT stats ("Footel", 1, '2010-01-01', '2010-03-01');
      INSERT stats ("Footel", 1, '2010-04-01', '2010-06-01');
      INSERT stats ("Footel", 1, '2010-02-01', '2010-05-01') ON DUPLICATE KEY UPDATE count=count+1;

      the result should be

      Footel    1    2010-01-01    2010-02-01
      Footel    2    2010-02-01    2010-03-01
      Footel    1    2010-03-01    2010-04-01
      Footel    2    2010-04-01    2010-05-01
      Footel    1    2010-05-01    2010-06-01


      wanted: use cases for REPLACE

      Attachments

        Issue Links

          Activity

            Reproduce

            create or replace table t(id int, s date, e date,
                                      period for p(s,e),
                                      primary key(id, p without overlaps));
            insert into t values (1, '2003-03-01', '2003-05-01');
            insert into t values (1, '2003-03-01', '2003-05-01') on duplicate key update id= id + 100;
            

            Result

            ERROR 1022 (23000): Can't write; duplicate key in table 't'
            

            Expected

            No error.

            midenok Aleksey Midenkov added a comment - Reproduce create or replace table t(id int , s date , e date , period for p(s,e), primary key (id, p without overlaps)); insert into t values (1, '2003-03-01' , '2003-05-01' ); insert into t values (1, '2003-03-01' , '2003-05-01' ) on duplicate key update id= id + 100; Result ERROR 1022 (23000): Can't write; duplicate key in table 't' Expected No error.
            midenok Aleksey Midenkov added a comment - Review progress

            serg, please review following commits from bb-10.5-MDEV-17395-replace:
            5a8bc783768 write_record: move gotos to funcs
            7b03ce7af3d MDEV-17395 REPLACE/INSERT ODKU: support WITHOUT OVERLAPS
            5c94cf3bf44 sql_insert: cleanup: remove duplicated variable
            5de6a46a61b Fix real keyread count for partitions

            nikitamalyavin Nikita Malyavin added a comment - serg , please review following commits from bb-10.5-MDEV-17395-replace : 5a8bc783768 write_record: move gotos to funcs 7b03ce7af3d MDEV-17395 REPLACE/INSERT ODKU: support WITHOUT OVERLAPS 5c94cf3bf44 sql_insert: cleanup: remove duplicated variable 5de6a46a61b Fix real keyread count for partitions

            People

              nikitamalyavin Nikita Malyavin
              nikitamalyavin Nikita Malyavin
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.