Query Operator Tests

Relevant source files

• test/evidence/in1.test
• test/evidence/in2.test
• test/evidence/slt_lang_aggfunc.test

Purpose and Scope

This document describes the query operator tests in the evidence test suite, focusing on SQL operators used in SELECT queries. These tests validate the behavior of IN/NOT IN operators and aggregate functions across different database platforms.

For detailed coverage of specific operators:

• IN/NOT IN operator behavior with NULL values and empty sets: see IN and NOT IN Operator Tests
• Aggregate function behavior with DISTINCT and NULL handling: see Aggregate Function Tests
• Data manipulation operations (UPDATE, REPLACE): see Data Manipulation Language (DML) Tests-tests)

Test File Organization

Query operator tests are located in the test/evidence/ directory and organized by operator type:

Sources: test/evidence/in1.test:1-1156 test/evidence/in2.test:1-314 test/evidence/slt_lang_aggfunc.test:1-500

Test File to Operator Mapping

Sources: test/evidence/in1.test:11-20 test/evidence/in1.test:329-336 test/evidence/in2.test:2-21 test/evidence/in2.test:292-295 test/evidence/slt_lang_aggfunc.test:24-27

IN and NOT IN Operator Tests

Test Coverage

The IN/NOT IN operator tests validate compliance with SQL specification evidence tags, particularly focusing on edge cases not commonly tested:

Empty Right-Hand Side (RHS)

• Tests validate SQLite's non-standard support for empty lists: SELECT 1 IN ()
• Most databases (MySQL, MSSQL, Oracle) skip these tests via conditional directives
• Evidence tag: R-52275-55503 and R-64309-54027

Table-as-Operand Syntax

• SQLite allows direct table references: SELECT 1 IN t1
• Other databases require subquery form: SELECT 1 IN (SELECT * FROM t1)
• Tests use onlyif sqlite directives for direct table syntax

NULL Value Handling

• Comprehensive matrix testing of NULL in left operand, right operand, and both
• Tests validate three-valued logic (true, false, NULL) for all combinations

Sources: test/evidence/in1.test:11-20 test/evidence/in1.test:22-26 test/evidence/in1.test:69-73 test/evidence/in2.test:2-21

Behavior Matrix Implementation

Sources: test/evidence/in1.test:329-336 test/evidence/in2.test:14-21 test/evidence/in1.test:795-902

Test Execution Pattern

The tests systematically cover each row of the behavior matrix documented in evidence tag R-50221-42915:

Sources: test/evidence/in1.test:329-357 test/evidence/in1.test:555-791 test/evidence/in1.test:795-1155

Aggregate Function Tests

graph LR
    subgraph "Aggregate Functions Tested"
        COUNT["count(x)\ncount(DISTINCT x)\ncount(*)"]
AVG["avg(x)\navg(DISTINCT x)"]
SUM["sum(x)\nsum(DISTINCT x)"]
TOTAL["total(x)\ntotal(DISTINCT x)"]
MIN["min(x)\nmin(DISTINCT x)"]
MAX["max(x)\nmax(DISTINCT x)"]
CONCAT["group_concat(x)\ngroup_concat(x,sep)"]
end
    
    subgraph "Test Aspects"
        DIST["DISTINCT filtering\nR-00466-56349"]
NULL_HAND["NULL handling\nPer-function rules"]
TYPE_CONV["Type conversion\nString to number"]
RETURN_TYPE["Return type\nInteger vs Float"]
end
    
 
   COUNT --> DIST
 
   AVG --> DIST
 
   SUM --> DIST
 
   TOTAL --> DIST
 
   MIN --> DIST
 
   MAX --> DIST
 
   CONCAT --> DIST
    
 
   COUNT --> NULL_HAND
 
   AVG --> NULL_HAND
 
   SUM --> NULL_HAND
    
 
   AVG --> RETURN_TYPE
 
   SUM --> RETURN_TYPE
 
   TOTAL --> RETURN_TYPE

Supported Functions

The slt_lang_aggfunc.test file validates all standard SQL aggregate functions:

Sources: test/evidence/slt_lang_aggfunc.test:21-22 test/evidence/slt_lang_aggfunc.test:24-62

DISTINCT Keyword Validation

All aggregate functions support the DISTINCT keyword, which filters duplicate values before aggregation:

Evidence Tag:R-00466-56349

• Tests verify: count(DISTINCT x), avg(DISTINCT x), sum(DISTINCT x), etc.
• Validation approach: Insert duplicate values, compare results with and without DISTINCT

Example Test Pattern:

INSERT INTO t1 VALUES(2,'true')
INSERT INTO t1 VALUES(2,'true')  -- Duplicate value
SELECT count(DISTINCT x) FROM t1  -- Should count 2 once

Sources: test/evidence/slt_lang_aggfunc.test:24-62 test/evidence/slt_lang_aggfunc.test:71-81

NULL Handling Per Function

Each aggregate function has distinct NULL handling behavior validated by the tests:

Sources: test/evidence/slt_lang_aggfunc.test:198-234 test/evidence/slt_lang_aggfunc.test:311-370 test/evidence/slt_lang_aggfunc.test:396-418

Type Conversion and Return Types

The tests validate type coercion and return type behavior:

String to Number Conversion (Evidence:R-29052-00975):

• Non-numeric strings are interpreted as 0
• Tests use VARCHAR column y with values like 'true', 'false', 'NULL'
• Aggregate functions treat these as 0

Return Type Rules:

• avg(x): Always returns float if any non-NULL input (Evidence: R-17177-10067)
• total(x): Always returns float (Evidence: R-07734-01023)
• sum(x): Returns integer if all inputs are integers, else float (Evidence: R-19660-56479, R-33611-59266)

Test Implementation:

-- avg always returns float
SELECT avg(x) FROM t1  -- Returns 1.250 (float) not 1

-- sum returns integer with integer inputs
SELECT sum(x) FROM t1  -- Returns 5 (integer)

-- sum returns float with any float input
INSERT INTO t1 VALUES(4.0,'true')
SELECT sum(x) FROM t1  -- Returns 9.000 (float)

Sources: test/evidence/slt_lang_aggfunc.test:92-124 test/evidence/slt_lang_aggfunc.test:168-182 test/evidence/slt_lang_aggfunc.test:420-464

flowchart TD
 
   START["Test Execution Begins"] --> CHECK_PLATFORM{"Platform\nDetection"}
CHECK_PLATFORM -->|onlyif mssql halt| SKIP_MSSQL["Skip entire file\n(MSSQL)"]
CHECK_PLATFORM -->|onlyif oracle halt| SKIP_ORACLE["Skip entire file\n(Oracle)"]
CHECK_PLATFORM -->|SQLite or MySQL| CONTINUE["Continue execution"]
CONTINUE --> CHECK_FEATURE{"Feature\nCheck"}
CHECK_FEATURE -->|onlyif sqlite| SQLITE_ONLY["Execute SQLite-only tests\n- Empty RHS\n- Table-as-operand"]
CHECK_FEATURE -->|skipif mysql| SKIP_MYSQL["Skip MySQL-incompatible\n- Empty RHS\n- Some NULL tests"]
CHECK_FEATURE -->|Common SQL| COMMON["Execute cross-platform\ncompatible tests"]
SQLITE_ONLY --> VALIDATE["Validate Results"]
SKIP_MYSQL --> VALIDATE
 
   COMMON --> VALIDATE
    
 
   SKIP_MSSQL --> END["Test Complete"]
SKIP_ORACLE --> END
 
   VALIDATE --> END

Cross-Database Compatibility

Platform-Specific Test Execution

The query operator tests use conditional directives to handle database differences:

Sources: test/evidence/in1.test:3-9 test/evidence/in1.test:22-26 test/evidence/in1.test:69-73

Platform Compatibility Matrix

Sources: test/evidence/in1.test:22-64 test/evidence/in1.test:398-427 test/evidence/slt_lang_aggfunc.test:18-19

MySQL-Specific Workarounds

MySQL requires special handling in several test scenarios:

UNIQUE Index on TEXT Columns:

• SQLite: CREATE TABLE t7(a TEXT UNIQUE)
• MySQL: CREATE TABLE t7(a TEXT, UNIQUE (a(1))) -- Requires length prefix

NULL in Subqueries:

• Some NULL IN (SELECT ...) tests are skipped on MySQL due to known failures
• Tests marked with skipif mysql comments

Sources: test/evidence/in1.test:398-404 test/evidence/in1.test:421-427 test/evidence/in1.test:951-975

Test Result Validation

Label-Based Test Groups

Tests use label markers to group related assertions that should produce identical results:

query I nosort label-1
SELECT 1 IN t1                    -- SQLite-only direct table syntax
----
0

query I nosort label-1
SELECT 1 IN (SELECT * FROM t1)    -- Standard subquery syntax
----
0

Both queries share label-1 and must return the same result (0), validating that SQLite's table-as-operand syntax is equivalent to standard subquery form.

Sources: test/evidence/in1.test:69-78 test/evidence/in1.test:441-461

Test Data Setup Pattern

Query operator tests follow a consistent setup pattern:

1. Create tables with different index configurations (UNIQUE, PRIMARY KEY, no index)
2. Populate with base data and NULL-containing variants
3. Execute operator tests against each table variant
4. Validate results across table types are consistent

Table Types Used:

• t1, t2, t3: Base tables (no index, PRIMARY KEY, UNIQUE)
• t4, t5, t6: Integer tables with different indexes
• t4n, t6n: Same as above but with NULL values added
• t7, t8: Text tables with different indexes
• t7n, t8n: Text tables with NULL values

Sources: test/evidence/in1.test:66-161 test/evidence/in1.test:359-439