Add Database::unionConditionPermutations()

Constructs a query for the union of permutations of a set of fields, for
use in situations where the database otherwise makes poor plans due to
inability to use indexes effectively (e.g. T149077 and T168010).

Change-Id: I20980dcada664486c09198b8c45896620bd83e81

1 files changed, 169 insertions, 0 deletions

diff --git a/tests/phpunit/includes/db/DatabaseSQLTest.php b/tests/phpunit/includes/db/DatabaseSQLTest.php
index 206655c05ee9..2b587db1572f 100644
--- a/tests/phpunit/includes/db/DatabaseSQLTest.php
+++ b/tests/phpunit/includes/db/DatabaseSQLTest.php
@@ -751,6 +751,175 @@ class DatabaseSQLTest extends MediaWikiTestCase {
 	}
 
 	/**
+	 * @dataProvider provideUnionConditionPermutations
+	 * @covers Database::unionConditionPermutations
+	 */
+	public function testUnionConditionPermutations( $params, $expect ) {
+		if ( isset( $params['unionSupportsOrderAndLimit'] ) ) {
+			$this->database->setUnionSupportsOrderAndLimit( $params['unionSupportsOrderAndLimit'] );
+		}
+
+		$sql = trim( $this->database->unionConditionPermutations(
+			$params['table'],
+			$params['vars'],
+			$params['permute_conds'],
+			isset( $params['extra_conds'] ) ? $params['extra_conds'] : '',
+			'FNAME',
+			isset( $params['options'] ) ? $params['options'] : [],
+			isset( $params['join_conds'] ) ? $params['join_conds'] : []
+		) );
+		$this->assertEquals( $expect, $sql );
+	}
+
+	public static function provideUnionConditionPermutations() {
+		return [
+			// @codingStandardsIgnoreStart Generic.Files.LineLength.TooLong
+			[
+				[
+					'table' => [ 'table1', 'table2' ],
+					'vars' => [ 'field1', 'alias' => 'field2' ],
+					'permute_conds' => [
+						'field3' => [ 1, 2, 3 ],
+						'duplicates' => [ 4, 5, 4 ],
+						'empty' => [],
+						'single' => [ 0 ],
+					],
+					'extra_conds' => 'table2.bar > 23',
+					'options' => [
+						'ORDER BY' => [ 'field1', 'alias' ],
+						'INNER ORDER BY' => [ 'field1', 'field2' ],
+						'LIMIT' => 100,
+					],
+					'join_conds' => [
+						'table2' => [ 'JOIN', 'table1.foo_id = table2.foo_id' ],
+					],
+				],
+				"(SELECT  field1,field2 AS alias  FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id))   WHERE field3 = '1' AND duplicates = '4' AND single = '0' AND (table2.bar > 23)  ORDER BY field1,field2 LIMIT 100  ) UNION ALL " .
+				"(SELECT  field1,field2 AS alias  FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id))   WHERE field3 = '1' AND duplicates = '5' AND single = '0' AND (table2.bar > 23)  ORDER BY field1,field2 LIMIT 100  ) UNION ALL " .
+				"(SELECT  field1,field2 AS alias  FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id))   WHERE field3 = '2' AND duplicates = '4' AND single = '0' AND (table2.bar > 23)  ORDER BY field1,field2 LIMIT 100  ) UNION ALL " .
+				"(SELECT  field1,field2 AS alias  FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id))   WHERE field3 = '2' AND duplicates = '5' AND single = '0' AND (table2.bar > 23)  ORDER BY field1,field2 LIMIT 100  ) UNION ALL " .
+				"(SELECT  field1,field2 AS alias  FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id))   WHERE field3 = '3' AND duplicates = '4' AND single = '0' AND (table2.bar > 23)  ORDER BY field1,field2 LIMIT 100  ) UNION ALL " .
+				"(SELECT  field1,field2 AS alias  FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id))   WHERE field3 = '3' AND duplicates = '5' AND single = '0' AND (table2.bar > 23)  ORDER BY field1,field2 LIMIT 100  ) " .
+				"ORDER BY field1,alias LIMIT 100"
+			],
+			[
+				[
+					'table' => 'foo',
+					'vars' => [ 'foo_id' ],
+					'permute_conds' => [
+						'bar' => [ 1, 2, 3 ],
+					],
+					'extra_conds' => [ 'baz' => null ],
+					'options' => [
+						'NOTALL',
+						'ORDER BY' => [ 'foo_id' ],
+						'LIMIT' => 25,
+					],
+				],
+				"(SELECT  foo_id  FROM foo    WHERE bar = '1' AND baz IS NULL  ORDER BY foo_id LIMIT 25  ) UNION " .
+				"(SELECT  foo_id  FROM foo    WHERE bar = '2' AND baz IS NULL  ORDER BY foo_id LIMIT 25  ) UNION " .
+				"(SELECT  foo_id  FROM foo    WHERE bar = '3' AND baz IS NULL  ORDER BY foo_id LIMIT 25  ) " .
+				"ORDER BY foo_id LIMIT 25"
+			],
+			[
+				[
+					'table' => 'foo',
+					'vars' => [ 'foo_id' ],
+					'permute_conds' => [
+						'bar' => [ 1, 2, 3 ],
+					],
+					'extra_conds' => [ 'baz' => null ],
+					'options' => [
+						'NOTALL' => true,
+						'ORDER BY' => [ 'foo_id' ],
+						'LIMIT' => 25,
+					],
+					'unionSupportsOrderAndLimit' => false,
+				],
+				"(SELECT  foo_id  FROM foo    WHERE bar = '1' AND baz IS NULL  ) UNION " .
+				"(SELECT  foo_id  FROM foo    WHERE bar = '2' AND baz IS NULL  ) UNION " .
+				"(SELECT  foo_id  FROM foo    WHERE bar = '3' AND baz IS NULL  ) " .
+				"ORDER BY foo_id LIMIT 25"
+			],
+			[
+				[
+					'table' => 'foo',
+					'vars' => [ 'foo_id' ],
+					'permute_conds' => [],
+					'extra_conds' => [ 'baz' => null ],
+					'options' => [
+						'ORDER BY' => [ 'foo_id' ],
+						'LIMIT' => 25,
+					],
+				],
+				"SELECT  foo_id  FROM foo    WHERE baz IS NULL  ORDER BY foo_id LIMIT 25"
+			],
+			[
+				[
+					'table' => 'foo',
+					'vars' => [ 'foo_id' ],
+					'permute_conds' => [
+						'bar' => [],
+					],
+					'extra_conds' => [ 'baz' => null ],
+					'options' => [
+						'ORDER BY' => [ 'foo_id' ],
+						'LIMIT' => 25,
+					],
+				],
+				"SELECT  foo_id  FROM foo    WHERE baz IS NULL  ORDER BY foo_id LIMIT 25"
+			],
+			[
+				[
+					'table' => 'foo',
+					'vars' => [ 'foo_id' ],
+					'permute_conds' => [
+						'bar' => [ 1 ],
+					],
+					'options' => [
+						'ORDER BY' => [ 'foo_id' ],
+						'LIMIT' => 25,
+						'OFFSET' => 150,
+					],
+				],
+				"SELECT  foo_id  FROM foo    WHERE bar = '1'  ORDER BY foo_id LIMIT 150,25"
+			],
+			[
+				[
+					'table' => 'foo',
+					'vars' => [ 'foo_id' ],
+					'permute_conds' => [],
+					'extra_conds' => [ 'baz' => null ],
+					'options' => [
+						'ORDER BY' => [ 'foo_id' ],
+						'LIMIT' => 25,
+						'OFFSET' => 150,
+						'INNER ORDER BY' => [ 'bar_id' ],
+					],
+				],
+				"(SELECT  foo_id  FROM foo    WHERE baz IS NULL  ORDER BY bar_id LIMIT 175  ) ORDER BY foo_id LIMIT 150,25"
+			],
+			[
+				[
+					'table' => 'foo',
+					'vars' => [ 'foo_id' ],
+					'permute_conds' => [],
+					'extra_conds' => [ 'baz' => null ],
+					'options' => [
+						'ORDER BY' => [ 'foo_id' ],
+						'LIMIT' => 25,
+						'OFFSET' => 150,
+						'INNER ORDER BY' => [ 'bar_id' ],
+					],
+					'unionSupportsOrderAndLimit' => false,
+				],
+				"SELECT  foo_id  FROM foo    WHERE baz IS NULL  ORDER BY foo_id LIMIT 150,25"
+			],
+			// @codingStandardsIgnoreEnd
+		];
+	}
+
+	/**
 	 * @covers Database::commit
 	 */
 	public function testTransactionCommit() {