{ $conn = static::$conn as nonnull; $results = await $conn->query('SELECT * FROM (SELECT id FROM table4 WHERE id = 1001 OR id = 1004) as sub WHERE id = 1001'); expect($results->rows())->toBeSame(vec[ dict['id' => 1001], ]); } public async function testSubqueryInSelectAndWhere(): Awaitable { $conn = static::$conn as nonnull; $results = await $conn->query( 'SELECT id, (SELECT description from table4 where id=1004) as foo FROM table3 WHERE id IN (SELECT table_3_id FROM association_table WHERE table_4_id=1003)', ); expect($results->rows())->toBeSame(vec[ dict['id' => 3, 'foo' => 'desc2'], ]); } public async function testUnion(): Awaitable { $conn = static::$conn as nonnull; $results = await $conn->query( 'SELECT * from table3 where id = 1 UNION SELECT * from table3 WHERE id = 2 UNION SELECT * from table3 where id = 3 UNION SELECT * from table3 where id = 3', ); expect($results->rows())->toBeSame(vec[ dict['id' => 1, 'group_id' => 12345, 'name' => 'name1'], dict['id' => 2, 'group_id' => 12345, 'name' => 'name2'], // because UNION dedupes, this row is not duplicated even though it's selected twice dict['id' => 3, 'group_id' => 12345, 'name' => 'name3'], ]); } public async function testUnionAll(): Awaitable { $conn = static::$conn as nonnull; $results = await $conn->query( 'SELECT * from table3 where id = 1 UNION ALL SELECT * from table3 WHERE id = 2 UNION ALL SELECT * from table3 where id = 3 UNION ALL SELECT * from table3 where id = 3', ); expect($results->rows())->toBeSame(vec[ dict['id' => 1, 'group_id' => 12345, 'name' => 'name1'], dict['id' => 2, 'group_id' => 12345, 'name' => 'name2'], // no dedupe with union all dict['id' => 3, 'group_id' => 12345, 'name' => 'name3'], dict['id' => 3, 'group_id' => 12345, 'name' => 'name3'], ]); } public async function testIntersect(): Awaitable { $conn = static::$conn as nonnull; $results = await $conn->query( 'SELECT * from table3 INTERSECT SELECT * from table3 WHERE id = 2', ); expect($results->rows())->toBeSame(vec[ dict['id' => 2, 'group_id' => 12345, 'name' => 'name2'], ]); } public async function testExcept(): Awaitable { $conn = static::$conn as nonnull; $results = await $conn->query( 'SELECT * from table3 EXCEPT SELECT * from table3 WHERE id = 2', ); expect($results->rows())->toBeSame(vec[ dict['id' => 1, 'group_id' => 12345, 'name' => 'name1'], // no dedupe with union all dict['id' => 3, 'group_id' => 12345, 'name' => 'name3'], dict['id' => 4, 'group_id' => 6, 'name' => 'name4'], dict['id' => 6, 'group_id' => 6, 'name' => 'name5'], ]); } <<__Override>> public static async function beforeFirstTestAsync(): Awaitable { static::$conn = await SharedSetup::initAsync(); // block hole logging Logger::setHandle(new \HH\Lib\IO\MemoryHandle()); } <<__Override>> public async function beforeEachTestAsync(): Awaitable { restore('setup'); QueryContext::$strictSchemaMode = false; QueryContext::$strictSQLMode = false; } }