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
-
MDEV-31275 OFFSET is ignored when it is combined with the DISTINCT, IN() and JOIN operation
-
- Closed
-
Activity
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;
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
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.