Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0.36, 10.1.41, 10.4.11, 10.5.15, 10.6.7
-
None
-
Linux
Description
I have a software product that I use across multiple different server setups that use a variety of MariaDB and MySQL installs. I've been getting a series of error reports that some of these servers have run out of space in /tmp/ due to temporary table usage.
I went ahead and created the following case that should reproduce the scenario with a minimal number of columns and tables:
DROP TABLE IF EXISTS `a`; |
DROP TABLE IF EXISTS `b`; |
|
CREATE TABLE `a` ( |
`i` int(11) NOT NULL, |
`d` date DEFAULT NULL, |
`txt` text DEFAULT NULL, |
PRIMARY KEY (`i`), |
KEY `o` (`d`,`i`) |
) ENGINE=InnoDB;
|
|
CREATE TABLE `b` ( |
`tid` int(11) NOT NULL, |
`a` int(11) NOT NULL, |
PRIMARY KEY (`tid`,`a`) |
) ENGINE=InnoDB;
|
|
INSERT INTO `a` VALUES (1,'2014-02-12', ''), (2,'2014-02-12', REPEAT('X', 10000)); |
INSERT INTO `b` VALUES (1,73),(1,75); |
|
explain SELECT a.i FROM (a) |
WHERE EXISTS (select 1 from b WHERE tid='1' AND a.i = b.a) |
ORDER BY a.d desc; |
|
DROP TABLE IF EXISTS `a`; |
DROP TABLE IF EXISTS `b`; |
On the majority of MariaDB installs I have tested on, I see that table `b` does the following:
Using index; Using temporary; Using filesort
Something of note: If I reduce the size of the dataset that goes into the second row of table `a`, a temporary table isn't used.
e.g: REPEAT('X', 5000).
The cutoff appears to be REPEAT('X', 8097) on several of these installs. This makes me think that there's an issue if the row length is more than 8192 bytes. This may be helpful.
On MySQL installs (5.7.28), I get:
Using where; Using index
Note: There is one copy of MariaDB I have encountered running 10.1.43 that does not do this. On this build of the software, I get:
Using index
for table `b`.
The differing behavior between (most) installs of MariaDB and MySQL make me think there's a bug here.
Please let me know if I can provide any more information if needed. Thanks.