packages/sql-semantics/tests/Unit/Core/Binding/NullFactsTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Core\Binding;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\DataProvider;
9use PHPUnit\Framework\Attributes\Medium;
10use PHPUnit\Framework\Attributes\TestWith;
11use PHPUnit\Framework\TestCase;
12use SqlSemantics\Core\Binder;
13use SqlSemantics\Core\Dialect;
14use SqlSemantics\Core\SchemaBuilder;
15use SqlSemantics\Core\SemanticException;
16use SqlSemantics\Core\Type\Nullability;
17use SqlSemantics\Platform\MySql\Dialect as MySqlDialect;
18use SqlSemantics\Platform\PostgreSql\Dialect as PostgreSqlDialect;
19use SqlSemantics\Platform\Sqlite\Dialect as SqliteDialect;
20
21#[CoversClass(\SqlSemantics\Core\Binding\NullFacts::class)]
22#[CoversClass(\SqlSemantics\Core\Binding\ExpressionBinder::class)]
23#[CoversClass(\SqlSemantics\Core\Binding\ExpressionRules::class)]
24#[CoversClass(\SqlSemantics\Core\Binding\FromBinder::class)]
25#[CoversClass(\SqlSemantics\Core\Binding\LiteralBinder::class)]
26#[CoversClass(\SqlSemantics\Core\Binding\ProjectionBinder::class)]
27#[CoversClass(\SqlSemantics\Core\Binding\SelectBinder::class)]
28#[CoversClass(\SqlSemantics\Core\Binding\SyntaxGuard::class)]
29#[CoversClass(\SqlSemantics\Core\Binding\SelectModifiersBinder::class)]
30#[CoversClass(\SqlSemantics\Core\Binding\TypeResolution::class)]
31#[CoversClass(Binder::class)]
32#[CoversClass(SchemaBuilder::class)]
33#[CoversClass(\SqlSemantics\Core\Ast\DialectParser::class)]
34#[CoversClass(\SqlSemantics\Core\Ast\ColumnReader::class)]
35#[CoversClass(\SqlSemantics\Core\Ast\ConstraintReader::class)]
36#[CoversClass(\SqlSemantics\Core\Ast\Identifiers::class)]
37#[CoversClass(\SqlSemantics\Core\Ast\SchemaReader::class)]
38#[CoversClass(\SqlSemantics\Core\Ast\StatementList::class)]
39#[CoversClass(\SqlSemantics\Core\Ast\TokenGroups::class)]
40#[CoversClass(\SqlSemantics\Core\Ast\Tree::class)]
41#[CoversClass(\SqlSemantics\Core\Ast\TypeReader::class)]
42#[CoversClass(\SqlSemantics\Core\Binding\BoundRelation::class)]
43#[CoversClass(\SqlSemantics\Core\Binding\IdentitySequence::class)]
44#[CoversClass(\SqlSemantics\Core\Binding\Scope::class)]
45#[CoversClass(\SqlSemantics\Core\Binding\TableResolver::class)]
46#[CoversClass(\SqlSemantics\Core\Model\ColumnBinding::class)]
47#[CoversClass(\SqlSemantics\Core\Model\Expression::class)]
48#[CoversClass(\SqlSemantics\Core\Model\Join::class)]
49#[CoversClass(\SqlSemantics\Core\Model\Ordering::class)]
50#[CoversClass(\SqlSemantics\Core\Model\OutputColumn::class)]
51#[CoversClass(\SqlSemantics\Core\Model\BoundSelect::class)]
52#[CoversClass(\SqlSemantics\Core\Model\TableUse::class)]
53#[CoversClass(\SqlSemantics\Core\Schema::class)]
54#[CoversClass(\SqlSemantics\Core\Schema\ColumnDefinition::class)]
55#[CoversClass(\SqlSemantics\Core\Schema\TableConstraint::class)]
56#[CoversClass(\SqlSemantics\Core\Schema\TableDefinition::class)]
57#[CoversClass(SemanticException::class)]
58#[CoversClass(\SqlSemantics\Core\Type\TypeDescriptor::class)]
59#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Core\Policy\SyntaxRules::class)]
60#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\QueryRules::class)]
61#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\Platform::class)]
62#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\TypeRules::class)]
63#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\NameRules::class)]
64#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\PostgreSql\SchemaRules::class)]
65#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\QueryRules::class)]
66#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\Platform::class)]
67#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\TypeRules::class)]
68#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\NameRules::class)]
69#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\Sqlite\SchemaRules::class)]
70#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\QueryRules::class)]
71#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\Platform::class)]
72#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\TypeRules::class)]
73#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\NameRules::class)]
74#[\PHPUnit\Framework\Attributes\UsesClass(\SqlSemantics\Platform\MySql\SchemaRules::class)]
75#[Medium]
76final class NullFactsTest extends TestCase
77{
78    #[TestWith([PostgreSqlDialect::PostgreSql])]
79    #[TestWith([MySqlDialect::MySql])]
80    #[TestWith([SqliteDialect::Sqlite])]
81    public function testCoalesceRequiresANonNullOperandToGuaranteeAValue(Dialect $dialect): void
82    {
83        $schema = (new SchemaBuilder($dialect))->build('CREATE TABLE users (id INTEGER PRIMARY KEY, parent_id INTEGER, score INTEGER NOT NULL)');
84        $statement = (new Binder($schema))->bind('SELECT COALESCE(parent_id, NULL) AS a, COALESCE(NULL, NULL) AS b, COALESCE(parent_id, score) AS c FROM users');
85        self::assertSame(Nullability::MaybeNull, $statement->outputs[0]->expression->nullability);
86        self::assertSame(Nullability::AlwaysNull, $statement->outputs[1]->expression->nullability);
87        self::assertSame(Nullability::NotNull, $statement->outputs[2]->expression->nullability);
88    }
89
90    public function testStrictComparisonPropagatesNullButBooleanOrDoesNot(): void
91    {
92        $schema = (new SchemaBuilder(PostgreSqlDialect::PostgreSql))->build();
93        $statement = (new Binder($schema))->bind('SELECT NULL = 1 AS a, NULL OR TRUE AS b');
94        self::assertSame(Nullability::AlwaysNull, $statement->outputs[0]->expression->nullability);
95        self::assertSame(Nullability::MaybeNull, $statement->outputs[1]->expression->nullability);
96    }
97
98    public function testExtensionsRetainsAllNullableOperandCauses(): void
99    {
100        $schema = (new SchemaBuilder(PostgreSqlDialect::PostgreSql))->build('CREATE TABLE users (id INTEGER PRIMARY KEY, parent_id INTEGER, score INTEGER NOT NULL)');
101        $statement = (new Binder($schema))->bind('SELECT a.id+b.id FROM users a FULL JOIN users b ON a.id=b.id');
102        self::assertSame(['j0'], $statement->outputs[0]->expression->nullExtendedBy);
103        self::assertSame(Nullability::MaybeNull, $statement->outputs[0]->expression->nullability);
104    }
105
106    #[DataProvider('providerFacts')]
107    public function testStrictAndCoalesceFacts(Nullability $left, Nullability $right, Nullability $strict, Nullability $coalesce): void
108    {
109        $type = new \SqlSemantics\Core\Type\TypeDescriptor(PostgreSqlDialect::PostgreSql, 'integer');
110        $source = new \SqlParser\Parser\Node('expr', 0, []);
111        $operands = [
112            new \SqlSemantics\Core\Model\Expression(\SqlSemantics\Core\Model\ExpressionKind::Literal, $type, $left, $source),
113            new \SqlSemantics\Core\Model\Expression(\SqlSemantics\Core\Model\ExpressionKind::Literal, $type, $right, $source),
114        ];
115        self::assertSame($strict, \SqlSemantics\Core\Binding\NullFacts::strict($operands));
116        self::assertSame($coalesce, \SqlSemantics\Core\Binding\NullFacts::coalesce($operands));
117    }
118
119    /**
120     * @return iterable<string, array{Nullability, Nullability, Nullability, Nullability}>
121     */
122    public static function providerFacts(): iterable
123    {
124        yield 'NotNull-NotNull' => [Nullability::NotNull, Nullability::NotNull, Nullability::NotNull, Nullability::NotNull];
125        yield 'NotNull-MaybeNull' => [Nullability::NotNull, Nullability::MaybeNull, Nullability::MaybeNull, Nullability::NotNull];
126        yield 'NotNull-AlwaysNull' => [Nullability::NotNull, Nullability::AlwaysNull, Nullability::AlwaysNull, Nullability::NotNull];
127        yield 'NotNull-Unknown' => [Nullability::NotNull, Nullability::Unknown, Nullability::Unknown, Nullability::NotNull];
128        yield 'MaybeNull-NotNull' => [Nullability::MaybeNull, Nullability::NotNull, Nullability::MaybeNull, Nullability::NotNull];
129        yield 'MaybeNull-MaybeNull' => [Nullability::MaybeNull, Nullability::MaybeNull, Nullability::MaybeNull, Nullability::MaybeNull];
130        yield 'MaybeNull-AlwaysNull' => [Nullability::MaybeNull, Nullability::AlwaysNull, Nullability::AlwaysNull, Nullability::MaybeNull];
131        yield 'MaybeNull-Unknown' => [Nullability::MaybeNull, Nullability::Unknown, Nullability::Unknown, Nullability::Unknown];
132        yield 'AlwaysNull-NotNull' => [Nullability::AlwaysNull, Nullability::NotNull, Nullability::AlwaysNull, Nullability::NotNull];
133        yield 'AlwaysNull-MaybeNull' => [Nullability::AlwaysNull, Nullability::MaybeNull, Nullability::AlwaysNull, Nullability::MaybeNull];
134        yield 'AlwaysNull-AlwaysNull' => [Nullability::AlwaysNull, Nullability::AlwaysNull, Nullability::AlwaysNull, Nullability::AlwaysNull];
135        yield 'AlwaysNull-Unknown' => [Nullability::AlwaysNull, Nullability::Unknown, Nullability::AlwaysNull, Nullability::Unknown];
136        yield 'Unknown-NotNull' => [Nullability::Unknown, Nullability::NotNull, Nullability::Unknown, Nullability::NotNull];
137        yield 'Unknown-MaybeNull' => [Nullability::Unknown, Nullability::MaybeNull, Nullability::Unknown, Nullability::Unknown];
138        yield 'Unknown-AlwaysNull' => [Nullability::Unknown, Nullability::AlwaysNull, Nullability::AlwaysNull, Nullability::Unknown];
139        yield 'Unknown-Unknown' => [Nullability::Unknown, Nullability::Unknown, Nullability::Unknown, Nullability::Unknown];
140    }
141}
142