[MDEV-16917] Index affects query results - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.8, 10.3.9, 10.3(EOL)
Fix Version/s: 10.3.10
Component/s: Optimizer
Labels:
None
Environment:
CentOS 7 3.10.0-862.9.1.el7.x86_64

Description

CREATE TABLE IF NOT EXISTS `test` (

  `n1` int(10) NOT NULL,

  `n2` int(10) NOT NULL,

  `c1` char(1) NOT NULL,

  KEY `c1` (`c1`) USING BTREE,

  KEY `n1_c1_n2` (`n1`,`c1`,`n2`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`n1`, `n2`, `c1`) VALUES

(0, 2, 'a'),

(1, 3, 'a');

SELECT test.n1

FROM test

INNER JOIN (

	SELECT n1, n2

	FROM test

	WHERE

		c1 = 'a'

	GROUP BY n1

	) t2

	ON (t2.n1 = test.n1 AND t2.n2 = test.n2)

WHERE c1 = 'a'

GROUP BY n1

Running the above query, as setup above, gives the incorrect result of:
1

Removing n2 from the multi-column index (`n1`,`c1`,`n2`), or rearranging that index to be (`n2`,`c1`,`n1`), or removing that index altogether, gives the correct query result of:
0
1

The combination of that specific multi-column index with the single-column index of c1 seems to trigger the issue.

Attachments

Activity

Ascending order - Click to sort in descending order

Frank E created issue - 2018-08-08 01:36

Sergei Golubchik made changes - 2018-08-08 12:09

Field	Original Value	New Value
Description	CREATE TABLE IF NOT EXISTS `test` ( `n1` int(10) NOT NULL, `n2` int(10) NOT NULL, `c1` char(1) NOT NULL, KEY `c1` (`c1`) USING BTREE, KEY `n1_c1_n2` (`n1`,`c1`,`n2`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test` (`n1`, `n2`, `c1`) VALUES (926, 1413870671, '3'), (27343, 1531920142, '3'); SELECT test.n1 FROM test INNER JOIN ( SELECT n1, n2 FROM test WHERE c1 = '3' GROUP BY n1 ) t2 ON (t2.n1 = test.n1 AND t2.n2 = test.n2) WHERE c1 = '3' GROUP BY n1 Running the above query, as setup above, gives the incorrect result of: 27343 Removing the `n2` from the multi-column index n1_c1_n2, or rearranging that index to be (`n2`,`c1`,`n1`), or removing that index altogether, gives the correct query result of: 926 27343	{code:sql} CREATE TABLE IF NOT EXISTS `test` ( `n1` int(10) NOT NULL, `n2` int(10) NOT NULL, `c1` char(1) NOT NULL, KEY `c1` (`c1`) USING BTREE, KEY `n1_c1_n2` (`n1`,`c1`,`n2`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test` (`n1`, `n2`, `c1`) VALUES (926, 1413870671, '3'), (27343, 1531920142, '3'); SELECT test.n1 FROM test INNER JOIN ( SELECT n1, n2 FROM test WHERE c1 = '3' GROUP BY n1 ) t2 ON (t2.n1 = test.n1 AND t2.n2 = test.n2) WHERE c1 = '3' GROUP BY n1 {code} Running the above query, as setup above, gives the incorrect result of: 27343 Removing the `n2` from the multi-column index n1_c1_n2, or rearranging that index to be (`n2`,`c1`,`n1`), or removing that index altogether, gives the correct query result of: 926 27343

Frank E made changes - 2018-08-08 21:29

Description

{code:sql}
CREATE TABLE IF NOT EXISTS `test` (
  `n1` int(10) NOT NULL,
  `n2` int(10) NOT NULL,
  `c1` char(1) NOT NULL,
  KEY `c1` (`c1`) USING BTREE,
  KEY `n1_c1_n2` (`n1`,`c1`,`n2`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`n1`, `n2`, `c1`) VALUES
(926, 1413870671, '3'),
(27343, 1531920142, '3');

SELECT test.n1
FROM test
INNER JOIN (
SELECT n1, n2
FROM test
WHERE
c1 = '3'
GROUP BY n1
) t2
ON (t2.n1 = test.n1 AND t2.n2 = test.n2)
WHERE c1 = '3'
GROUP BY n1
{code}
Running the above query, as setup above, gives the incorrect result of:
27343

Removing the `n2` from the multi-column index n1_c1_n2, or rearranging that index to be (`n2`,`c1`,`n1`), or removing that index altogether, gives the correct query result of:
926
27343

{code:sql}
CREATE TABLE IF NOT EXISTS `test` (
  `n1` int(10) NOT NULL,
  `n2` int(10) NOT NULL,
  `c1` char(1) NOT NULL,
  KEY `c1` (`c1`) USING BTREE,
  KEY `n1_c1_n2` (`n1`,`c1`,`n2`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`n1`, `n2`, `c1`) VALUES
(0, 2, 'a'),
(1, 3, 'a');

SELECT test.n1
FROM test
INNER JOIN (
SELECT n1, n2
FROM test
WHERE
c1 = 'a'
GROUP BY n1
) t2
ON (t2.n1 = test.n1 AND t2.n2 = test.n2)
WHERE c1 = 'a'
GROUP BY n1
{code}
Running the above query, as setup above, gives the incorrect result of:
1

Removing n2 from the multi-column index (`n1`,`c1`,`n2`), or rearranging that index to be (`n2`,`c1`,`n1`), or removing that index altogether, gives the correct query result of:
0
1

The combination of that specific multi-column index with the single-column index of c1 seem to cause the issue.

Frank E made changes - 2018-08-08 21:45

Description

{code:sql}
CREATE TABLE IF NOT EXISTS `test` (
  `n1` int(10) NOT NULL,
  `n2` int(10) NOT NULL,
  `c1` char(1) NOT NULL,
  KEY `c1` (`c1`) USING BTREE,
  KEY `n1_c1_n2` (`n1`,`c1`,`n2`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`n1`, `n2`, `c1`) VALUES
(0, 2, 'a'),
(1, 3, 'a');

SELECT test.n1
FROM test
INNER JOIN (
SELECT n1, n2
FROM test
WHERE
c1 = 'a'
GROUP BY n1
) t2
ON (t2.n1 = test.n1 AND t2.n2 = test.n2)
WHERE c1 = 'a'
GROUP BY n1
{code}
Running the above query, as setup above, gives the incorrect result of:
1

Removing n2 from the multi-column index (`n1`,`c1`,`n2`), or rearranging that index to be (`n2`,`c1`,`n1`), or removing that index altogether, gives the correct query result of:
0
1

The combination of that specific multi-column index with the single-column index of c1 seem to cause the issue.

{code:sql}
CREATE TABLE IF NOT EXISTS `test` (
  `n1` int(10) NOT NULL,
  `n2` int(10) NOT NULL,
  `c1` char(1) NOT NULL,
  KEY `c1` (`c1`) USING BTREE,
  KEY `n1_c1_n2` (`n1`,`c1`,`n2`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`n1`, `n2`, `c1`) VALUES
(0, 2, 'a'),
(1, 3, 'a');

SELECT test.n1
FROM test
INNER JOIN (
SELECT n1, n2
FROM test
WHERE
c1 = 'a'
GROUP BY n1
) t2
ON (t2.n1 = test.n1 AND t2.n2 = test.n2)
WHERE c1 = 'a'
GROUP BY n1
{code}
Running the above query, as setup above, gives the incorrect result of:
1

Removing n2 from the multi-column index (`n1`,`c1`,`n2`), or rearranging that index to be (`n2`,`c1`,`n1`), or removing that index altogether, gives the correct query result of:
0
1

The combination of that specific multi-column index with the single-column index of c1 seems to trigger the issue.

Frank E made changes - 2018-08-15 18:52

Affects Version/s

10.3.9 [ 23114 ]

Elena Stepanova made changes - 2018-09-02 23:06

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2018-09-02 23:08

Component/s		Optimizer [ 10200 ]
Fix Version/s		10.3 [ 22126 ]
Affects Version/s		10.3 [ 22126 ]
Assignee		Igor Babaev [ igor ]

Igor Babaev (Inactive) made changes - 2018-09-14 06:33

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Igor Babaev (Inactive) made changes - 2018-09-15 23:49

Fix Version/s		10.3.10 [ 23140 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:47

Workflow

MariaDB v3 [ 88787 ]

MariaDB v4 [ 154762 ]

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Frank E

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2018-08-08 01:36

Updated:: 2018-10-06 19:32

Resolved:: 2018-09-15 23:49

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration