Skip to content

armgit / txtai

Public Imported from GitHub
0

💡 All-in-one AI framework for semantic search, LLM orchestration and language model workflows

24.2 KB
Raw Blame
"""
SQL module tests
"""

import unittest

from txtai.database import DatabaseFactory, SQL, SQLError
from txtai.database.sql import Aggregate


class TestSQL(unittest.TestCase):
    """
    Test SQL parsing and generation.
    """

    @classmethod
    def setUpClass(cls):
        """
        Initialize test data.
        """

        # Create SQL parser for SQLite
        cls.db = DatabaseFactory.create({"content": True})
        cls.db.initialize()

        cls.sql = SQL(cls.db)

    def testAggregateEmptyResults(self):
        """
        Test Aggregate returns empty results for a SQL query with no results
        """

        aggregate = Aggregate()

        # SQL query, no results across shards - must not raise IndexError
        self.assertEqual(aggregate("select id, text, score from txtai where similar('x')", []), [])

        # Non-SQL query with no results stays consistent
        self.assertEqual(aggregate("plain text query", []), [])

        # SQL query with results still aggregates as before
        self.assertEqual(aggregate("select count(*) from txtai", [{"count(*)": 1}, {"count(*)": 2}]), [{"count(*)": 3}])

    def testAggregateAvg(self):
        """
        Test Aggregate combines avg() results as a count()-weighted mean, not an unweighted
        average of averages
        """

        aggregate = Aggregate()

        # 2 rows averaging 100 and 8 rows averaging 10 must combine to the true weighted mean, 28
        query = "select count(price), avg(price) from txtai"
        results = [{"count(price)": 2, "avg(price)": 100.0}, {"count(price)": 8, "avg(price)": 10.0}]
        self.assertEqual(aggregate(query, results), [{"count(price)": 10, "avg(price)": 28.0}])

        # A single shard's average passes through unchanged
        self.assertEqual(aggregate("select avg(price) from txtai", [{"avg(price)": 42.0}]), [{"avg(price)": 42.0}])

        # Combining averages from multiple shards with no count() to weight by cannot be done
        # correctly, so it must raise rather than return an unweighted, silently wrong result
        with self.assertRaises(SQLError):
            aggregate("select avg(price) from txtai", [{"avg(price)": 100.0}, {"avg(price)": 10.0}])

    def testAggregateAvgNull(self):
        """
        Test Aggregate weights avg() by the non-NULL count of the averaged column
        """

        aggregate = Aggregate()

        # Shard A has prices [100, NULL], shard B has eight prices of 10, the true average is 180 / 9 = 20
        query = "select count(*), count(price), avg(price) from txtai"
        results = [{"count(*)": 2, "count(price)": 1, "avg(price)": 100.0}, {"count(*)": 8, "count(price)": 8, "avg(price)": 10.0}]
        self.assertEqual(aggregate(query, results), [{"count(*)": 10, "count(price)": 9, "avg(price)": 20.0}])

        # Shard A has x priced [100, NULL, NULL], shard B has x priced [10, 10, 10], the true average is 130 / 4 = 32.5
        query = "select text, count(*), count(price), avg(price) from txtai group by text"
        results = [
            {"text": "x", "count(*)": 3, "count(price)": 1, "avg(price)": 100.0},
            {"text": "x", "count(*)": 3, "count(price)": 3, "avg(price)": 10.0},
        ]
        self.assertEqual(aggregate(query, results), [{"text": "x", "count(*)": 6, "count(price)": 4, "avg(price)": 32.5}])

        # count(*) alone cannot weight avg() correctly when the averaged column has NULLs
        with self.assertRaises(SQLError):
            aggregate("select count(*), avg(price) from txtai", [{"count(*)": 2, "avg(price)": 100.0}, {"count(*)": 8, "avg(price)": 10.0}])

    def testAggregateCase(self):
        """
        Test Aggregate combines aggregate columns written in any case
        """

        aggregate = Aggregate()

        query = "select COUNT(*), Sum(price), MAX(price) from txtai"
        results = [{"COUNT(*)": 1, "Sum(price)": 1.0, "MAX(price)": 1.0}, {"COUNT(*)": 2, "Sum(price)": 5.0, "MAX(price)": 4.0}]
        self.assertEqual(aggregate(query, results), [{"COUNT(*)": 3, "Sum(price)": 6.0, "MAX(price)": 4.0}])

    def testAggregateGroupby(self):
        """
        Test Aggregate groups results using mixed case and NULL group by columns
        """

        aggregate = Aggregate()

        query = "select Category, count(*) from txtai group by Category"
        results = [{"Category": "A", "count(*)": 2}, {"Category": "A", "count(*)": 1}, {"Category": "B", "count(*)": 4}]
        self.assertEqual(aggregate(query, results), [{"Category": "A", "count(*)": 3}, {"Category": "B", "count(*)": 4}])

        query = "select category, count(*) from txtai group by category"
        results = [{"category": None, "count(*)": 2}, {"category": "A", "count(*)": 1}, {"category": None, "count(*)": 3}]
        self.assertEqual(aggregate(query, results), [{"category": None, "count(*)": 5}, {"category": "A", "count(*)": 1}])

        results = [
            {"category": "B", "count(*)": 1},
            {"category": None, "count(*)": 2},
            {"category": "A", "count(*)": 3},
            {"category": "B", "count(*)": 4},
        ]
        self.assertEqual(
            aggregate(query, results), [{"category": None, "count(*)": 2}, {"category": "A", "count(*)": 3}, {"category": "B", "count(*)": 5}]
        )

        query = "select a, B, count(*) from txtai group by A, b"
        results = [{"a": 1, "B": None, "count(*)": 1}, {"a": 1, "B": "x", "count(*)": 2}, {"a": 1, "B": None, "count(*)": 3}]
        self.assertEqual(aggregate(query, results), [{"a": 1, "B": None, "count(*)": 4}, {"a": 1, "B": "x", "count(*)": 2}])

    def testAggregateNull(self):
        """
        Test Aggregate skips NULL values returned by shards with no matching rows
        """

        aggregate = Aggregate()

        query = "select count(*), max(price), avg(price) from txtai where price > 50"
        empty, result = {"count(*)": 0, "max(price)": None, "avg(price)": None}, {"count(*)": 2, "max(price)": 100.0, "avg(price)": 80.0}

        self.assertEqual(aggregate(query, [empty, result]), [result])
        self.assertEqual(aggregate(query, [empty, empty]), [empty])

    def testAggregateOrderByNull(self):
        """
        Test Aggregate orders NULL values the same way as SQLite
        """

        aggregate = Aggregate()

        results = [{"id": "a", "price": 3.0}, {"id": "b", "price": None}, {"id": "c", "price": 1.0}]
        self.assertEqual([x["id"] for x in aggregate("select id, price from txtai order by price", results)], ["b", "c", "a"])
        self.assertEqual([x["id"] for x in aggregate("select id, price from txtai order by price desc", results)], ["a", "c", "b"])

    def testAlias(self):
        """
        Test alias clauses
        """

        self.assertSql("select", "select a as a1 from txtai", "json_extract(data, '$.a') as a1")
        self.assertSql("select", "select a 'a1' from txtai", "json_extract(data, '$.a') 'a1'")
        self.assertSql("select", 'select a "a1" from txtai', "json_extract(data, '$.a') \"a1\"")
        self.assertSql("select", "select a a1 from txtai", "json_extract(data, '$.a') a1")
        self.assertSql(
            "select",
            "select a, b as b1, c, d + 1 as 'd1' from txtai",
            "json_extract(data, '$.a') as \"a\", json_extract(data, '$.b') as b1, "
            + "json_extract(data, '$.c') as \"c\", json_extract(data, '$.d') + 1 as 'd1'",
        )
        self.assertSql("select", "select id as myid from txtai", "s.id as myid")
        self.assertSql("select", "select length(a) t from txtai", "length(json_extract(data, '$.a')) t")

        self.assertSql("where", "select id as myid from txtai where myid != 3 and a != 1", "myid != 3 and json_extract(data, '$.a') != 1")
        self.assertSql("where", "select txt T from txtai where t LIKE '%abc%'", "t LIKE '%abc%'")
        self.assertSql("where", "select txt 'T' from txtai where t LIKE '%abc%'", "t LIKE '%abc%'")
        self.assertSql("where", "select txt \"T\" from txtai where t LIKE '%abc%'", "t LIKE '%abc%'")
        self.assertSql("where", "select txt as T from txtai where t LIKE '%abc%'", "t LIKE '%abc%'")
        self.assertSql("where", "select txt as 'T' from txtai where t LIKE '%abc%'", "t LIKE '%abc%'")
        self.assertSql("where", "select txt as \"T\" from txtai where t LIKE '%abc%'", "t LIKE '%abc%'")

        self.assertSql("groupby", "select id as myid, count(*) from txtai group by myid, a", "myid, json_extract(data, '$.a')")
        self.assertSql("orderby", "select id as myid from txtai order by myid, a", "myid, json_extract(data, '$.a')")

    def testBadSQL(self):
        """
        Test invalid SQL
        """

        with self.assertRaises(SQLError):
            self.db.search("select * from txtai where order by")

        with self.assertRaises(SQLError):
            self.db.search("select * from txtai where groupby order by")

        with self.assertRaises(SQLError):
            self.db.search("select * from txtai where a(1)")

        with self.assertRaises(SQLError):
            self.db.search("select a b c from txtai where id match id")

    def testMultipleStatements(self):
        """
        Test only the first statement is run
        """

        with self.assertRaises(SQLError):
            self.db.search("select * from txtai ; delete from sections")

        self.assertSql("select", ";select id from txtai", "s.id")
        self.assertSql("select", "select id from txtai;", "s.id")

    def testStatementWhitespace(self):
        """
        Test insignificant whitespace around SQL tokens and a trailing semicolon is accepted.
        """

        self.assertSql("select", "select id from  txtai", "s.id")
        self.assertSql("select", "select id from txtai; ", "s.id")
        self.assertSql("select", "select id from txtai;\n", "s.id")
        self.assertSql("select", " ; select id from txtai", "s.id")

    def testBracket(self):
        """
        Test bracket expressions
        """

        self.assertSql("select", "select [a] from txtai", "json_extract(data, '$.a') as \"a\"")
        self.assertSql("select", "select [a] ab from txtai", "json_extract(data, '$.a') ab")
        self.assertSql("select", "select [abc] from txtai", "json_extract(data, '$.abc') as \"abc\"")
        self.assertSql("select", "select [id], text, score from txtai", "s.id, text, score")
        self.assertSql("select", "select [ab cd], text, score from txtai", "json_extract(data, '$.ab cd') as \"ab cd\", text, score")
        self.assertSql("select", "select [a[0]] from txtai", "json_extract(data, '$.a[0]') as \"a[0]\"")
        self.assertSql("select", "select [a[0].ab] from txtai", "json_extract(data, '$.a[0].ab') as \"a[0].ab\"")
        self.assertSql("select", "select [a[0].c[0]] from txtai", "json_extract(data, '$.a[0].c[0]') as \"a[0].c[0]\"")
        self.assertSql("select", "select avg([a]) from txtai", "avg(json_extract(data, '$.a')) as \"avg([a])\"")

        # Test single quote escaping in bracket expressions
        self.assertSql("select", "select [field'] from txtai", "json_extract(data, '$.field''') as \"field'\"")

        self.assertSql("where", "select * from txtai where [a b] < 1 or a > 1", "json_extract(data, '$.a b') < 1 or json_extract(data, '$.a') > 1")
        self.assertSql("where", "select [a[0].c[0]] a from txtai where a < 1", "a < 1")
        self.assertSql("where", "select * from txtai where [true] = 1", "json_extract(data, '$.true') = 1")
        self.assertSql("groupby", "select * from txtai group by [a]", "json_extract(data, '$.a')")
        self.assertSql("orderby", "select * from txtai where order by [a]", "json_extract(data, '$.a')")

    def testCast(self):
        """
        Test cast expressions don't resolve the type name as a column
        """

        self.assertSql("select", "select cast(a as integer) from txtai", "cast(json_extract(data, '$.a') as integer) as \"cast(a as integer)\"")
        self.assertSql("select", "select CAST(id AS TEXT) a from txtai", "CAST(s.id AS TEXT) a")
        self.assertSql("select", "select try_cast(a as integer) b from txtai", "try_cast(json_extract(data, '$.a') as integer) b")
        self.assertSql("where", "select * from txtai where cast(a as decimal(10, 2)) > 5", "cast(json_extract(data, '$.a') as decimal(10, 2)) > 5")
        self.assertSql(
            "where",
            "select * from txtai where cast(a as double precision) > b",
            "cast(json_extract(data, '$.a') as double precision) > json_extract(data, '$.b')",
        )
        self.assertSql(
            "orderby",
            "select * from txtai order by func(cast(a as integer), b)",
            "func(cast(json_extract(data, '$.a') as integer), json_extract(data, '$.b'))",
        )

    def testDistinct(self):
        """
        Test distinct expressions
        """

        # Attributes
        self.assertSql("select", "select distinct id from txtai", "distinct s.id")
        self.assertSql("select", "select distinct id as myid from txtai", "distinct s.id as myid")
        self.assertSql("select", "select distinct a from txtai", "distinct json_extract(data, '$.a') as \"a\"")
        self.assertSql("select", "select distinct a.b from txtai", "distinct json_extract(data, '$.a.b') as \"a.b\"")

        # Bracket expression
        self.assertSql("select", "select distinct [ab cd] from txtai", "distinct json_extract(data, '$.ab cd') as \"distinct[ab cd]\"")

        # Function expression
        self.assertSql("select", "select distinct(id) from txtai", 'distinct(s.id) as "distinct(id)"')
        self.assertSql("select", "select count(distinct id) from txtai", 'count(distinct s.id) as "count(distinct id)"')
        self.assertSql("select", "select count(distinct a) from txtai", "count(distinct json_extract(data, '$.a')) as \"count(distinct a)\"")
        self.assertSql("select", "select count(distinct avg(id)) from txtai", 'count(distinct avg(s.id)) as "count(distinct avg(id))"')
        self.assertSql(
            "select", "select count(distinct avg(a)) from txtai", "count(distinct avg(json_extract(data, '$.a'))) as \"count(distinct avg(a))\""
        )

        # Compound expression
        self.assertSql("select", "select distinct a/1 from txtai", "distinct json_extract(data, '$.a') / 1 as \"a / 1\"")
        self.assertSql("select", "select distinct(a/1) from txtai", "distinct(json_extract(data, '$.a') / 1) as \"distinct(a / 1)\"")

    def testGroupby(self):
        """
        Test group by clauses
        """

        prefix = "select count(*), flag from txtai "

        self.assertSql("groupby", prefix + "group by text", "text")
        self.assertSql("groupby", prefix + "group by distinct(a)", "distinct(json_extract(data, '$.a'))")
        self.assertSql("groupby", prefix + "where a > 1 group by text", "text")

    def testHaving(self):
        """
        Test having clauses
        """

        prefix = "select count(*), flag from txtai "

        self.assertSql("having", prefix + "group by text having count(*) > 1", "count(*) > 1")
        self.assertSql("having", prefix + "where flag = 1 group by text having count(*) > 1", "count(*) > 1")

    def testIsSQL(self):
        """
        Test SQL detection method.
        """

        self.assertTrue(self.sql.issql("select text from txtai where id = 1"))
        self.assertFalse(self.sql.issql(1234))

    def testLimit(self):
        """
        Test limit clauses
        """

        prefix = "select count(*) from txtai "

        self.assertSql("limit", prefix + "limit 100", "100")

    def testOffset(self):
        """
        Test offset clauses
        """

        prefix = "select count(*) from txtai "

        self.assertSql("offset", prefix + "limit 100 offset 50", "50")
        self.assertSql("offset", prefix + "offset 50", "50")

    def testOrderby(self):
        """
        Test order by clauses
        """

        prefix = "select * from txtai "

        self.assertSql("orderby", prefix + "order by id", "s.id")
        self.assertSql("orderby", prefix + "order by id, text", "s.id, text")
        self.assertSql("orderby", prefix + "order by id asc", "s.id asc")
        self.assertSql("orderby", prefix + "order by id desc", "s.id desc")
        self.assertSql("orderby", prefix + "order by id asc, text desc", "s.id asc, text desc")

    def testSelectBasic(self):
        """
        Test basic select clauses
        """

        self.assertSql("select", "select id, indexid, tags from txtai", "s.id, s.indexid, s.tags")
        self.assertSql("select", "select id, indexid, flag from txtai", "s.id, s.indexid, json_extract(data, '$.flag') as \"flag\"")
        self.assertSql("select", "select id, indexid, a.b.c from txtai", "s.id, s.indexid, json_extract(data, '$.a.b.c') as \"a.b.c\"")
        self.assertSql("select", "select 'id', [id], (id) from txtai", "'id', s.id, (s.id)")
        self.assertSql("select", "select * from txtai", "*")

    def testSelectCompound(self):
        """
        Test compound select clauses
        """

        self.assertSql("select", "select a + 1 from txtai", "json_extract(data, '$.a') + 1 as \"a + 1\"")
        self.assertSql("select", "select 1 * a from txtai", "1 * json_extract(data, '$.a') as \"1 * a\"")
        self.assertSql("select", "select a/1 from txtai", "json_extract(data, '$.a') / 1 as \"a / 1\"")
        self.assertSql("select", "select avg(a-b) from txtai", "avg(json_extract(data, '$.a') - json_extract(data, '$.b')) as \"avg(a - b)\"")
        self.assertSql("select", "select distinct(text) from txtai", "distinct(text)")
        self.assertSql("select", "select id, score, (a/2)*3 from txtai", "s.id, score, (json_extract(data, '$.a') / 2) * 3 as \"(a / 2) * 3\"")
        self.assertSql("select", "select id, score, (a/2*3) from txtai", "s.id, score, (json_extract(data, '$.a') / 2 * 3) as \"(a / 2 * 3)\"")
        self.assertSql(
            "select",
            "select func(func2(indexid + 1), a) from txtai",
            "func(func2(s.indexid + 1), json_extract(data, '$.a')) as \"func(func2(indexid + 1), a)\"",
        )
        self.assertSql("select", "select func(func2(indexid + 1), a) a from txtai", "func(func2(s.indexid + 1), json_extract(data, '$.a')) a")
        self.assertSql("select", "select 'prefix' || id from txtai", "'prefix' || s.id as \"'prefix' || id\"")
        self.assertSql("select", "select 'prefix' || id id from txtai", "'prefix' || s.id id")
        self.assertSql("select", "select 'prefix' || a a from txtai", "'prefix' || json_extract(data, '$.a') a")

    def testSimilar(self):
        """
        Test similar functions
        """

        prefix = "select * from txtai "

        self.assertSql("where", prefix + "where similar('abc')", "__SIMILAR__0")
        self.assertSql("similar", prefix + "where similar('abc')", [["abc"]])

        self.assertSql("where", prefix + "where similar('abc') AND id = 1", "__SIMILAR__0 AND s.id = 1")
        self.assertSql("similar", prefix + "where similar('abc')", [["abc"]])

        self.assertSql("where", prefix + "where similar('abc') and similar('def')", "__SIMILAR__0 and __SIMILAR__1")
        self.assertSql("similar", prefix + "where similar('abc') and similar('def')", [["abc"], ["def"]])

        self.assertSql("where", prefix + "where similar('abc', 1000)", "__SIMILAR__0")
        self.assertSql("similar", prefix + "where similar('abc', 1000)", [["abc", "1000"]])

        self.assertSql("where", prefix + "where similar('abc', 1000) and similar('def', 10)", "__SIMILAR__0 and __SIMILAR__1")
        self.assertSql("similar", prefix + "where similar('abc', 1000) and similar('def', 10)", [["abc", "1000"], ["def", "10"]])

        self.assertSql("where", prefix + "where coalesce(similar('abc'), similar('abc'))", "coalesce(__SIMILAR__0, __SIMILAR__1)")
        self.assertSql("similar", prefix + "where coalesce(similar('abc'), similar('abc'))", [["abc"], ["abc"]])

    def testUnterminated(self):
        """
        Test unterminated clauses
        """

        # Unterminated bracket expressions
        with self.assertRaises(SQLError):
            self.db.search("select [a from txtai")

        with self.assertRaises(SQLError):
            self.db.search("select avg([a) from txtai")

        with self.assertRaises(SQLError):
            self.db.search("select [a[0] from txtai")

        # Unterminated function expressions
        with self.assertRaises(SQLError):
            self.db.search("select func(a from txtai")

        with self.assertRaises(SQLError):
            self.db.search("select * from txtai where coalesce(a")

        # Unterminated similar clause
        with self.assertRaises(SQLError):
            self.db.search("select * from txtai where similar('abc'")

        # Empty select components (stray commas) raised IndexError
        with self.assertRaises(SQLError):
            self.db.search("select a,,b from txtai")

        with self.assertRaises(SQLError):
            self.db.search("select ,a from txtai")

        # A trailing AS with no alias name raised TypeError
        with self.assertRaises(SQLError):
            self.db.search("select a as from txtai")

    def testUpper(self):
        """
        Test SQL statements are case insensitive.
        """

        self.assertSql("groupby", "SELECT * FROM TXTAI WHERE a = 1 GROUP BY id", "s.id")
        self.assertSql("orderby", "SELECT * FROM TXTAI WHERE a = 1 ORDER BY id", "s.id")

    def testWhereBasic(self):
        """
        Test basic where clauses
        """

        prefix = "select * from txtai "

        self.assertSql("where", prefix + "where a = b", "json_extract(data, '$.a') = json_extract(data, '$.b')")
        self.assertSql("where", prefix + "where abc = def", "json_extract(data, '$.abc') = json_extract(data, '$.def')")
        self.assertSql("where", prefix + "where a = b.value", "json_extract(data, '$.a') = json_extract(data, '$.b.value')")
        self.assertSql("where", prefix + "where a = 1", "json_extract(data, '$.a') = 1")
        self.assertSql("where", prefix + "WHERE 1 = a", "1 = json_extract(data, '$.a')")
        self.assertSql("where", prefix + "WHERE a LIKE 'abc'", "json_extract(data, '$.a') LIKE 'abc'")
        self.assertSql("where", prefix + "WHERE a NOT LIKE 'abc'", "json_extract(data, '$.a') NOT LIKE 'abc'")
        self.assertSql("where", prefix + "WHERE a IN (1, 2, 3, b)", "json_extract(data, '$.a') IN (1, 2, 3, json_extract(data, '$.b'))")
        self.assertSql("where", prefix + "WHERE a is not null", "json_extract(data, '$.a') is not null")
        self.assertSql("where", prefix + "where a = true", "json_extract(data, '$.a') = true")
        self.assertSql("where", prefix + "WHERE a is not FALSE", "json_extract(data, '$.a') is not FALSE")
        self.assertSql("where", prefix + "WHERE score >= 0.15", "score >= 0.15")
        self.assertSql("where", prefix + "where a > 1e3", "json_extract(data, '$.a') > 1e3")
        self.assertSql("where", prefix + "where a > 1.5E+3 and b < 2e-3", "json_extract(data, '$.a') > 1.5E+3 and json_extract(data, '$.b') < 2e-3")
        self.assertSql("where", prefix + "where a > .5e-3 and x1e-3 > 1", "json_extract(data, '$.a') > .5e-3 and json_extract(data, '$.x1e') - 3 > 1")
        self.assertSql("select", "select 1e-3 as x from txtai", "1e-3 as x")

    def testWhereCompound(self):
        """
        Test compound where clauses
        """

        prefix = "select * from txtai "

        self.assertSql("where", prefix + "where a > (b + 1)", "json_extract(data, '$.a') > (json_extract(data, '$.b') + 1)")
        self.assertSql("where", prefix + "where a > func('abc')", "json_extract(data, '$.a') > func('abc')")
        self.assertSql(
            "where", prefix + "where (id = 1 or id = 2) and a like 'abc'", "(s.id = 1 or s.id = 2) and json_extract(data, '$.a') like 'abc'"
        )
        self.assertSql(
            "where",
            prefix + "where a > f(d(b, c, 1),1)",
            "json_extract(data, '$.a') > f(d(json_extract(data, '$.b'), json_extract(data, '$.c'), 1), 1)",
        )
        self.assertSql("where", prefix + "where (id = 1 AND id = 2) OR indexid = 3", "(s.id = 1 AND s.id = 2) OR s.indexid = 3")
        self.assertSql("where", prefix + "where f(id) = b(id)", "f(s.id) = b(s.id)")
        self.assertSql("where", prefix + "WHERE f(id)", "f(s.id)")

    def assertSql(self, clause, query, expected):
        """
        Helper method to assert a query clause is as expected.

        Args:
            clause: clause to select
            query: input query
            expected: expected transformed query value
        """

        self.assertEqual(self.sql(query)[clause], expected)