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

Unexpected result when combining DISTINCT, subselect and LIMIT

Details

    Description

      Description

      • Create table with a primary key
      • Create a table with a 1:n relation to that primary key
      • Run a query with DISTINCT, a filter on a single key, then you correctly get one record
      • Add a LIMIT 10, 10, then no record is returned which is expected
      • Add a sub select in the other table, then you get a result of 1 record which is not expected

      Complete reproduction see below.

      My setup

      MariaDB 10.7.3 is started in podman:

      podman run --rm --name mariadb -p 3306:3306 --env MARIADB_USER=myuser --env MARIADB_PASSWORD=secret --env MARIADB_ROOT_PASSWORD=secret mariadb:10.7.3

      SQL script to reproduce the error

      DROP DATABASE if EXISTS bugC241QEMA;
      		 
      CREATE DATABASE bugC241QEMA;
      		 
      USE bugC241QEMA;
      		 
      CREATE TABLE `todo0` (`GGUID` binary(16) NOT NULL, PRIMARY KEY (`GGUID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
      		 
      INSERT INTO `todo0` VALUES (0x00001176B3860442AC3890A2AC8A1E3D),(0x0129D2ACADA8E842ABFB6FC445E70A25),(0x0189674371038A429A34EEED58EB9FB0),(0x0233E786F7A1F04183C22122D65215C8),(0x027D56982B57CC47A99E97FD592599C0),(0x03414BA004EB3DD2BBCD3BCC2A0C68E8),(0x03F54A731D788F42BE8CB7D7620BB76B),(0x04893FCC2D1C744EBF1D60D4ED74C443),(0x04ED5CB19FE48B4DBFD3B1AE9C4AE954),(0x050B38429819FC4B9A6EFE81E1FCBECC),(0x055AB6134A6C3C229E52514D88906D1C),(0x057CD499DE72EC4B968C252705078F9F),(0x05932628DFBACC42AF68E27BFB587BC5),(0x0642A53E237F1741BC6E331E1B118FF9),(0x075E313FED3DAE45BE46B536642ACE78),(0x07734DF9DC722F489240C7DF3F81EA85),(0x07DFD603CFC712479E8C721999D98143),(0x07E61621AD80DB4FB86AAA116EBA1272),(0x0812891A933537839A3C954FC8C05438),(0x0988C74F4505664EAA432C4B88233BCF),(0x0995F982443C244D96D0CF3753C0C353),(0x09960C8DAC40BB4E97545ECB533A857A),(0x09B08CC6AA97584F805AD95095DB1950),(0x09DAE8170AE39549983E3A642EA9AB52),(0x0B1C377283F7974AA1BEB4EE3CA13A05),(0x0B2E77EA7CB1364DA312787654F75DFC),(0x0B48244F0E5831E2AB169959B5108380),(0x0B50C66C522B434CAE3AF3237C539C26),(0x0BB56C919C4403469E4D793F6F7F849E),(0x0C0D88B73D9CCA4CB1731E225094880E),(0x0D5B2E0CE7CC30A1AAD9E0E8D60AEEA9),(0x0DCBD5F4DE4A2D429801E50BAB19EBC3),(0x0EF8284FA9AEE445859C289AADA0209B),(0x0F06C852C69FC544958680A5FD1E3BD2),(0x0F1CDE46C71A9547A1C2E73BC9DDBC7C),(0x102958783E255A45950622AEFDD95628),(0x10FB5A02B5F5AE4C9D192B2C7E468C08),(0x1133CE04F92A37679E4381BA9C7151D4),(0x115C0B9CA8A9F54BA3D3FA531F578BB8),(0x12476CFE47D2BA4A888B22781AF88174),(0x12D931002C64DD44B4524DA682046C79),(0x132C5AAFF412DF45AC0A325A52EF7856),(0x134377681C3331D5A943B02E2FF24C98),(0x138D232217BDE641A5BBC2B66EB8DBD9),(0x138E99DDFB69DF48A93F8F2504B93465),(0x155420B42B32D54992DE3B8F91EAB588),(0x1647A65CA7ABB74DBEA989F33CF8C06D),(0x16BE0B07B884F245BB93937164306131),(0x16F528C966405D43946F604129DA284A),(0x171AC29C33FCD34B814D87D72EA74101),(0x176E3C78E2C63DFAB02A684E1991303D),(0x17749914EA771F4BB167E10987767123),(0x186CC3BE8A1C8C4F8F1A47B9084FF2B3),(0x18712E081C10C64DB3BBB2C035CF2023),(0x187A05A2EF33A14E8DE3E73BAA00D3F6),(0x19096FA9B0E86A49AB30F828787E08DE),(0x1912456B3068594782B8054A5A137D02),(0x1937EFDB52EB3741B6C9491430A52A32),(0x194307AD2D798A459C23A8AEB5BE29F1),(0x1A490B2077D138A48E62EF5567D96FC2),(0x1B5AF8E66F2F5D4AAB6C42A956D86A50),(0x1B64F0288B02BA468C19CD58AAA09C63),(0x1B8538895270FA4EBBB0A694BE858954),(0x1C37F369B9D2C140BF869A9BB3DDF92F),(0x1CF81C8260B88B4886EBAF298AB8483A),(0x1DF1AF37DDD53A90A455CB6134B3B7AB),(0x1F459580D48DF5448FB937A702BB9D8A),(0x1F64BA3210587040B4A72B7CE84C4405),(0x1FA7D24F59D0BD41B36756154091E2F6),(0x20077F24C1DA32B7BECE1B6AEB65466F);
      		 
       
      		 
      CREATE TABLE `todoorel` (`TableGUID` binary(16) NOT NULL, `OID` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
      		 
       
      		 
      INSERT INTO `todoorel` VALUES (0x0F1CDE46C71A9547A1C2E73BC9DDBC7C,12),(0x138E99DDFB69DF48A93F8F2504B93465,15),(0x1C37F369B9D2C140BF869A9BB3DDF92F,17),(0x12476CFE47D2BA4A888B22781AF88174,15),(0x16BE0B07B884F245BB93937164306131,15),(0x0B50C66C522B434CAE3AF3237C539C26,10),(0x12476CFE47D2BA4A888B22781AF88174,20),(0x07E61621AD80DB4FB86AAA116EBA1272,4),(0x19096FA9B0E86A49AB30F828787E08DE,20),(0x10FB5A02B5F5AE4C9D192B2C7E468C08,12),(0x03F54A731D788F42BE8CB7D7620BB76B,12),(0x0B2E77EA7CB1364DA312787654F75DFC,15),(0x05932628DFBACC42AF68E27BFB587BC5,12),(0x115C0B9CA8A9F54BA3D3FA531F578BB8,15),(0x0812891A933537839A3C954FC8C05438,0),(0x0EF8284FA9AEE445859C289AADA0209B,2),(0x194307AD2D798A459C23A8AEB5BE29F1,15),(0x1647A65CA7ABB74DBEA989F33CF8C06D,15),(0x176E3C78E2C63DFAB02A684E1991303D,0),(0x0EF8284FA9AEE445859C289AADA0209B,0),(0x18712E081C10C64DB3BBB2C035CF2023,4),(0x186CC3BE8A1C8C4F8F1A47B9084FF2B3,2),(0x057CD499DE72EC4B968C252705078F9F,12),(0x132C5AAFF412DF45AC0A325A52EF7856,15),(0x0988C74F4505664EAA432C4B88233BCF,21),(0x1B8538895270FA4EBBB0A694BE858954,7),(0x0988C74F4505664EAA432C4B88233BCF,4),(0x1B64F0288B02BA468C19CD58AAA09C63,15),(0x16F528C966405D43946F604129DA284A,12),(0x04893FCC2D1C744EBF1D60D4ED74C443,12),(0x17749914EA771F4BB167E10987767123,0),(0x05932628DFBACC42AF68E27BFB587BC5,12),(0x138D232217BDE641A5BBC2B66EB8DBD9,2),(0x19096FA9B0E86A49AB30F828787E08DE,15),(0x1C37F369B9D2C140BF869A9BB3DDF92F,15),(0x12D931002C64DD44B4524DA682046C79,12),(0x0B48244F0E5831E2AB169959B5108380,0),(0x1912456B3068594782B8054A5A137D02,7),(0x187A05A2EF33A14E8DE3E73BAA00D3F6,12),(0x075E313FED3DAE45BE46B536642ACE78,15),(0x09DAE8170AE39549983E3A642EA9AB52,15),(0x0189674371038A429A34EEED58EB9FB0,8),(0x186CC3BE8A1C8C4F8F1A47B9084FF2B3,0),(0x09B08CC6AA97584F805AD95095DB1950,15),(0x17749914EA771F4BB167E10987767123,15),(0x07734DF9DC722F489240C7DF3F81EA85,4),(0x03414BA004EB3DD2BBCD3BCC2A0C68E8,0),(0x138D232217BDE641A5BBC2B66EB8DBD9,0),(0x0DCBD5F4DE4A2D429801E50BAB19EBC3,9),(0x055AB6134A6C3C229E52514D88906D1C,0),(0x0995F982443C244D96D0CF3753C0C353,15),(0x1FA7D24F59D0BD41B36756154091E2F6,16),(0x027D56982B57CC47A99E97FD592599C0,15),(0x03F54A731D788F42BE8CB7D7620BB76B,13),(0x07DFD603CFC712479E8C721999D98143,4),(0x155420B42B32D54992DE3B8F91EAB588,9),(0x1937EFDB52EB3741B6C9491430A52A32,6),(0x0642A53E237F1741BC6E331E1B118FF9,15),(0x1FA7D24F59D0BD41B36756154091E2F6,15),(0x171AC29C33FCD34B814D87D72EA74101,15),(0x050B38429819FC4B9A6EFE81E1FCBECC,15),(0x1F64BA3210587040B4A72B7CE84C4405,12),(0x0BB56C919C4403469E4D793F6F7F849E,15),(0x0F06C852C69FC544958680A5FD1E3BD2,7),(0x0C0D88B73D9CCA4CB1731E225094880E,9),(0x03F54A731D788F42BE8CB7D7620BB76B,14),(0x1DF1AF37DDD53A90A455CB6134B3B7AB,0),(0x09960C8DAC40BB4E97545ECB533A857A,9),(0x1133CE04F92A37679E4381BA9C7151D4,0),(0x16BE0B07B884F245BB93937164306131,6),(0x134377681C3331D5A943B02E2FF24C98,0),(0x00001176B3860442AC3890A2AC8A1E3D,12),(0x0D5B2E0CE7CC30A1AAD9E0E8D60AEEA9,0),(0x1937EFDB52EB3741B6C9491430A52A32,15),(0x1B5AF8E66F2F5D4AAB6C42A956D86A50,12),(0x04ED5CB19FE48B4DBFD3B1AE9C4AE954,9),(0x1F459580D48DF5448FB937A702BB9D8A,4),(0x1B8538895270FA4EBBB0A694BE858954,21),(0x20077F24C1DA32B7BECE1B6AEB65466F,0),(0x0129D2ACADA8E842ABFB6FC445E70A25,15),(0x1A490B2077D138A48E62EF5567D96FC2,0),(0x102958783E255A45950622AEFDD95628,4),(0x1CF81C8260B88B4886EBAF298AB8483A,23),(0x1CF81C8260B88B4886EBAF298AB8483A,15),(0x0B1C377283F7974AA1BEB4EE3CA13A05,7),(0x0233E786F7A1F04183C22122D65215C8,7);
      		 
       
      		 
       
      		 
      SELECT DISTINCT `GGUID` FROM todo0 AS TODO WHERE `GGUID` = 0x20077F24C1DA32B7BECE1B6AEB65466F LIMIT 10, 10; -- Returns 0 records as expected
      		 
      SELECT DISTINCT `GGUID` FROM todo0 AS TODO WHERE `GGUID` = 0x20077F24C1DA32B7BECE1B6AEB65466F AND TODO.`GGUID` IN (SELECT `TABLEGUID` FROM todoorel WHERE `OID` IN (15, 0)) LIMIT 10, 10; -- Returns 1 record
      		 
       

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31275 OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN operation

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Descending order - Click to sort in ascending order
            monty Michael Widenius added a comment -

            Pushed

            monty Michael Widenius added a comment - Pushed
            monty Michael Widenius added a comment -

            The issue was different plans in 10.4 and 10.5

            10.4 using the plan:
            --------------------------------------------------------------------------------------------------------+

            id select_type table type possible_keys key key_len ref rows Extra

            --------------------------------------------------------------------------------------------------------+

            1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index; Using temporary
            1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func 1  
            2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10 Using where

            --------------------------------------------------------------------------------------------------------+

            While 10.5 is using a better plan, but this is not honoring limit.

            -------------------------------------------------------------------------------------------+

            id select_type table type possible_keys key key_len ref rows Extra

            -------------------------------------------------------------------------------------------+

            1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index; Using temporary
            1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; FirstMatch(t1)

            -------------------------------------------------------------------------------------------+

            Now fixing limit handling for the second plan

            monty Michael Widenius added a comment - The issue was different plans in 10.4 and 10.5 10.4 using the plan: ----- ------------ ----------- ------ ------------- ------------ ------- ----- ---- -----------------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ------------ ----------- ------ ------------- ------------ ------- ----- ---- -----------------------------+ 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index; Using temporary 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func 1   2 MATERIALIZED t2 ALL NULL NULL NULL NULL 10 Using where ----- ------------ ----------- ------ ------------- ------------ ------- ----- ---- -----------------------------+ While 10.5 is using a better plan, but this is not honoring limit. ----- ----------- ----- ----- ------------- ------- ------- ----- ---- -----------------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ----------- ----- ----- ------------- ------- ------- ----- ---- -----------------------------+ 1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using index; Using temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; FirstMatch(t1) ----- ----------- ----- ----- ------------- ------- ------- ----- ---- -----------------------------+ Now fixing limit handling for the second plan
            monty Michael Widenius added a comment -

            A slightly simpler test case:

            create table t1 (a int primary key) engine=innodb;
            create table t2 (a int primary key, b int not null) engine=innodb;

            insert into t1 select seq from seq_1_to_10;
            insert into t2 select seq,seq from seq_1_to_10;

            select distinct a from t1 where t1.a=1 and t1.a in (select a from t2 where t2.b in (1,2));
            select distinct a from t1 where t1.a=1 and t1.a in (select a+0 from t2 where t2.b in (1,2)) limit 10,10;

            monty Michael Widenius added a comment - A slightly simpler test case: create table t1 (a int primary key) engine=innodb; create table t2 (a int primary key, b int not null) engine=innodb; insert into t1 select seq from seq_1_to_10; insert into t2 select seq,seq from seq_1_to_10; select distinct a from t1 where t1.a=1 and t1.a in (select a from t2 where t2.b in (1,2)); select distinct a from t1 where t1.a=1 and t1.a in (select a+0 from t2 where t2.b in (1,2)) limit 10,10;
            alice Alice Sherepa added a comment -

            Thank you for the report! I repeated as described on 10.5-10.8 with InnoDB/MyIsam. 10.4 and earlies versions return the correct results.

            alice Alice Sherepa added a comment - Thank you for the report! I repeated as described on 10.5-10.8 with InnoDB/MyIsam. 10.4 and earlies versions return the correct results.

            People

              monty Michael Widenius
              martin.kirchner@cas.de Martin Kirchner
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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