SELECT ALL Support - SQL Standard ALL Keyword

[mathiasrw]

Priority: 1 (Critical)
Impact: SQL-99 Compliance
Test File: test/test260.js
Test Location: Line 14
Test Name: "3. SELECT ALL"

Problem Description

The test test260.js line 14 contains a skipped test for the SQL standard SELECT ALL keyword. This test verifies that AlaSQL supports the SQL-99 standard ALL keyword in SELECT statements, which should include all rows (including duplicates) in the result set.

Specific Test Case

it.skip('3. SELECT ALL', function (done) {
    done();
});

Expected Behavior

According to SQL-99 standard, SELECT ALL should:

• Return all rows from the query result
• Include duplicate rows (unlike SELECT DISTINCT)
• Be the default behavior when neither ALL nor DISTINCT is specified
• Work with complex queries including JOINs

Current Status

• Test Status: Skipped (it.skip)
• Error: Unknown (test not executed)
• Root Cause: Test is empty placeholder, SELECT ALL functionality needs to be implemented

Steps to Reproduce

1. Navigate to test/test260.js
2. Change it.skip('3. SELECT ALL' to it('3. SELECT ALL'
3. Run the test with: yarn test-only -- test/test260.js
4. Test will fail because it's currently empty

Evidence from Related Tests

From test259.js, there's a working test that shows the expected syntax:

it('3. SELECT ALL', function (done) {
    alasql('CREATE TABLE tab1;CREATE TABLE tab2');
    var res = alasql('SELECT ALL * FROM tab1 cor0 CROSS JOIN tab1, tab2 AS cor1');
    assert(Array.isArray(res));
    alasql('DROP TABLE tab1;DROP TABLE tab2');
    done();
});

From comments in test260.js, the expected query format:

SELECT ALL * FROM tab1 cor0 CROSS JOIN tab1, tab2 AS cor1

Implementation Requirements

1. Parser Support: Ensure ALL keyword is properly recognized in SELECT statements
2. Query Execution: Verify that SELECT ALL includes all rows (including duplicates)
3. Test Data: Create test tables with duplicate data to verify ALL vs DISTINCT behavior
4. Complex Queries: Test with JOINs and subqueries

SQL-99 Features Involved

• SELECT ALL keyword
• Default vs explicit ALL behavior
• Duplicate row handling
• Complex query support with ALL

Dependencies

• AlaSQL parser (src/alasqlparser.jison)
• Query execution engine
• DISTINCT/ALL logic in result processing
• Test database setup

Acceptance Criteria

• Test is enabled (remove it.skip)
• Test contains actual SELECT ALL test cases
• Parser recognizes ALL keyword correctly
• SELECT ALL includes duplicate rows
• Test with complex JOINs works
• All assertions pass
• No regression in existing SELECT functionality

Test Implementation Suggestions

it('3. SELECT ALL', function (done) {
    alasql('CREATE DATABASE test260; USE test260');
    alasql('CREATE TABLE tab1 (a INT, b INT)');
    alasql('CREATE TABLE tab2 (c INT, d INT)');
    
    // Insert duplicate data
    alasql('INSERT INTO tab1 VALUES (1,1), (1,1), (2,2)');
    alasql('INSERT INTO tab2 VALUES (3,3), (4,4)');
    
    // Test SELECT ALL with duplicates
    var res = alasql('SELECT ALL * FROM tab1');
    assert.equal(res.length, 3); // Should include duplicates
    
    // Test SELECT ALL with JOIN
    var res2 = alasql('SELECT ALL * FROM tab1 cor0 CROSS JOIN tab1, tab2 AS cor1');
    assert(Array.isArray(res2));
    assert(res2.length > 0); // Should include all combinations
    
    alasql('DROP DATABASE test260');
    done();
});

Notes

This is a critical SQL-99 compliance test. The SELECT ALL keyword should be the default behavior, but the explicit keyword needs to be properly parsed and handled. The test should verify that duplicates are preserved when ALL is specified.