From f035b6e0a41238d092ea2ddd10fdd5de298ff789 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 23 Oct 2013 17:41:55 -0400 Subject: An overhaul of expression handling for special symbols particularly with conjunctions, e.g. ``None`` :func:`.expression.null` :func:`.expression.true` :func:`.expression.false`, including consistency in rendering NULL in conjunctions, "short-circuiting" of :func:`.and_` and :func:`.or_` expressions which contain boolean constants, and rendering of boolean constants and expressions as compared to "1" or "0" for backends that don't feature ``true``/``false`` constants. [ticket:2804] --- test/sql/test_compiler.py | 68 +++++++++++---- test/sql/test_operators.py | 210 ++++++++++++++++++++++++++++++++++++++++++++- test/sql/test_types.py | 9 +- 3 files changed, 265 insertions(+), 22 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index cd8ac2aef..fbb88924d 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -18,7 +18,7 @@ from sqlalchemy import Integer, String, MetaData, Table, Column, select, \ literal, and_, null, type_coerce, alias, or_, literal_column,\ Float, TIMESTAMP, Numeric, Date, Text, collate, union, except_,\ intersect, union_all, Boolean, distinct, join, outerjoin, asc, desc,\ - over, subquery, case + over, subquery, case, true import decimal from sqlalchemy.util import u from sqlalchemy import exc, sql, util, types, schema @@ -272,9 +272,10 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "SELECT foo() AS foo_1" ) + # this is native_boolean=False for default dialect self.assert_compile( select([not_(True)], use_labels=True), - "SELECT NOT :param_1" + "SELECT :param_1 = 0" ) self.assert_compile( @@ -874,6 +875,26 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "SELECT t.x FROM t WHERE t.x = :x_1 OR t.x = :x_2" ) + def test_true_short_circuit(self): + t = table('t', column('x')) + + self.assert_compile( + select([t]).where(true()), + "SELECT t.x FROM t WHERE 1 = 1", + dialect=default.DefaultDialect(supports_native_boolean=False) + ) + self.assert_compile( + select([t]).where(true()), + "SELECT t.x FROM t WHERE true", + dialect=default.DefaultDialect(supports_native_boolean=True) + ) + + self.assert_compile( + select([t]), + "SELECT t.x FROM t", + dialect=default.DefaultDialect(supports_native_boolean=True) + ) + def test_distinct(self): self.assert_compile( select([table1.c.myid.distinct()]), @@ -2921,6 +2942,7 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): "(:rem_id, :datatype_id, :value)") + class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = 'default' @@ -3250,13 +3272,34 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): ) class CoercionTest(fixtures.TestBase, AssertsCompiledSQL): - __dialect__ = 'default' + __dialect__ = default.DefaultDialect(supports_native_boolean=True) def _fixture(self): m = MetaData() return Table('foo', m, Column('id', Integer)) + bool_table = table('t', column('x', Boolean)) + + def test_coerce_bool_where(self): + self.assert_compile( + select([self.bool_table]).where(self.bool_table.c.x), + "SELECT t.x FROM t WHERE t.x" + ) + + def test_coerce_bool_where_non_native(self): + self.assert_compile( + select([self.bool_table]).where(self.bool_table.c.x), + "SELECT t.x FROM t WHERE t.x = 1", + dialect=default.DefaultDialect(supports_native_boolean=False) + ) + + self.assert_compile( + select([self.bool_table]).where(~self.bool_table.c.x), + "SELECT t.x FROM t WHERE t.x = 0", + dialect=default.DefaultDialect(supports_native_boolean=False) + ) + def test_null_constant(self): self.assert_compile(_literal_as_text(None), "NULL") @@ -3269,12 +3312,12 @@ class CoercionTest(fixtures.TestBase, AssertsCompiledSQL): def test_val_and_false(self): t = self._fixture() self.assert_compile(and_(t.c.id == 1, False), - "foo.id = :id_1 AND false") + "false") def test_val_and_true_coerced(self): t = self._fixture() self.assert_compile(and_(t.c.id == 1, True), - "foo.id = :id_1 AND true") + "foo.id = :id_1") def test_val_is_null_coerced(self): t = self._fixture() @@ -3282,26 +3325,21 @@ class CoercionTest(fixtures.TestBase, AssertsCompiledSQL): "foo.id IS NULL") def test_val_and_None(self): - # current convention is None in and_() or - # other clauselist is ignored. May want - # to revise this at some point. t = self._fixture() self.assert_compile(and_(t.c.id == 1, None), - "foo.id = :id_1") + "foo.id = :id_1 AND NULL") def test_None_and_val(self): - # current convention is None in and_() or - # other clauselist is ignored. May want - # to revise this at some point. t = self._fixture() - self.assert_compile(and_(t.c.id == 1, None), - "foo.id = :id_1") + self.assert_compile(and_(None, t.c.id == 1), + "NULL AND foo.id = :id_1") def test_None_and_nothing(self): # current convention is None in and_() # returns None May want # to revise this at some point. - assert and_(None) is None + self.assert_compile( + and_(None), "NULL") def test_val_and_null(self): t = self._fixture() diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index 97ce3d3dd..0124d85fa 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -9,14 +9,18 @@ from sqlalchemy.sql import operators, table import operator from sqlalchemy import String, Integer from sqlalchemy import exc +from sqlalchemy.engine import default +from sqlalchemy.sql.elements import _literal_as_text from sqlalchemy.schema import Column, Table, MetaData -from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType +from sqlalchemy.types import TypeEngine, TypeDecorator, UserDefinedType, Boolean from sqlalchemy.dialects import mysql, firebird, postgresql, oracle, \ sqlite, mssql from sqlalchemy import util import datetime import collections from sqlalchemy import text, literal_column +from sqlalchemy import and_, not_, between, or_ +from sqlalchemy.sql import true, false, null class LoopOperate(operators.ColumnOperators): def operate(self, op, *other, **kwargs): @@ -35,11 +39,11 @@ class DefaultColumnComparatorTest(fixtures.TestBase): left = column('left') assert left.comparator.operate(operator, right).compare( - BinaryExpression(left, right, operator) + BinaryExpression(_literal_as_text(left), _literal_as_text(right), operator) ) assert operator(left, right).compare( - BinaryExpression(left, right, operator) + BinaryExpression(_literal_as_text(left), _literal_as_text(right), operator) ) self._loop_test(operator, right) @@ -384,7 +388,205 @@ class ExtensionOperatorTest(fixtures.TestBase, testing.AssertsCompiledSQL): "x -> :x_1" ) -from sqlalchemy import and_, not_, between + +class BooleanEvalTest(fixtures.TestBase, testing.AssertsCompiledSQL): + """test standalone booleans being wrapped in an AsBoolean, as well + as true/false compilation.""" + + def _dialect(self, native_boolean): + d = default.DefaultDialect() + d.supports_native_boolean = native_boolean + return d + + def test_one(self): + c = column('x', Boolean) + self.assert_compile( + select([c]).where(c), + "SELECT x WHERE x", + dialect=self._dialect(True) + ) + + def test_two(self): + c = column('x', Boolean) + self.assert_compile( + select([c]).where(c), + "SELECT x WHERE x = 1", + dialect=self._dialect(False) + ) + + def test_three(self): + c = column('x', Boolean) + self.assert_compile( + select([c]).where(~c), + "SELECT x WHERE x = 0", + dialect=self._dialect(False) + ) + + def test_four(self): + c = column('x', Boolean) + self.assert_compile( + select([c]).where(~c), + "SELECT x WHERE NOT x", + dialect=self._dialect(True) + ) + + def test_five(self): + c = column('x', Boolean) + self.assert_compile( + select([c]).having(c), + "SELECT x HAVING x = 1", + dialect=self._dialect(False) + ) + + def test_six(self): + self.assert_compile( + or_(false(), true()), + "1 = 1", + dialect=self._dialect(False) + ) + + def test_eight(self): + self.assert_compile( + and_(false(), true()), + "false", + dialect=self._dialect(True) + ) + + def test_nine(self): + self.assert_compile( + and_(false(), true()), + "0 = 1", + dialect=self._dialect(False) + ) + + def test_ten(self): + c = column('x', Boolean) + self.assert_compile( + c == 1, + "x = :x_1", + dialect=self._dialect(False) + ) + + def test_eleven(self): + c = column('x', Boolean) + self.assert_compile( + c.is_(true()), + "x IS true", + dialect=self._dialect(True) + ) + + def test_twelve(self): + c = column('x', Boolean) + # I don't have a solution for this one yet, + # other than adding some heavy-handed conditionals + # into compiler + self.assert_compile( + c.is_(true()), + "x IS 1", + dialect=self._dialect(False) + ) + + +class ConjunctionTest(fixtures.TestBase, testing.AssertsCompiledSQL): + """test interaction of and_()/or_() with boolean , null constants + """ + __dialect__ = default.DefaultDialect(supports_native_boolean=True) + + def test_one(self): + self.assert_compile(~and_(true()), "false") + + def test_two(self): + self.assert_compile(or_(~and_(true())), "false") + + def test_three(self): + self.assert_compile(or_(and_()), "") + + def test_four(self): + x = column('x') + self.assert_compile( + and_(or_(x == 5), or_(x == 7)), + "x = :x_1 AND x = :x_2") + + + def test_five(self): + x = column("x") + self.assert_compile( + and_(true()._ifnone(None), x == 7), + "x = :x_1" + ) + + def test_six(self): + x = column("x") + self.assert_compile(or_(true(), x == 7), "true") + self.assert_compile(or_(x == 7, true()), "true") + self.assert_compile(~or_(x == 7, true()), "false") + + def test_six_pt_five(self): + x = column("x") + self.assert_compile(select([x]).where(or_(x == 7, true())), + "SELECT x WHERE true") + + self.assert_compile(select([x]).where(or_(x == 7, true())), + "SELECT x WHERE 1 = 1", + dialect=default.DefaultDialect(supports_native_boolean=False)) + + def test_seven(self): + x = column("x") + self.assert_compile( + and_(true(), x == 7, true(), x == 9), + "x = :x_1 AND x = :x_2") + + def test_eight(self): + x = column("x") + self.assert_compile( + or_(false(), x == 7, false(), x == 9), + "x = :x_1 OR x = :x_2") + + def test_nine(self): + x = column("x") + self.assert_compile( + and_(x == 7, x == 9, false(), x == 5), + "false" + ) + self.assert_compile( + ~and_(x == 7, x == 9, false(), x == 5), + "true" + ) + + def test_ten(self): + self.assert_compile( + and_(None, None), + "NULL AND NULL" + ) + + def test_eleven(self): + x = column("x") + self.assert_compile( + select([x]).where(None).where(None), + "SELECT x WHERE NULL AND NULL" + ) + + def test_twelve(self): + x = column("x") + self.assert_compile( + select([x]).where(and_(None, None)), + "SELECT x WHERE NULL AND NULL" + ) + + def test_thirteen(self): + x = column("x") + self.assert_compile( + select([x]).where(~and_(None, None)), + "SELECT x WHERE NOT (NULL AND NULL)" + ) + + def test_fourteen(self): + x = column("x") + self.assert_compile( + select([x]).where(~null()), + "SELECT x WHERE NOT NULL" + ) + class OperatorPrecedenceTest(fixtures.TestBase, testing.AssertsCompiledSQL): __dialect__ = 'default' diff --git a/test/sql/test_types.py b/test/sql/test_types.py index 13d4e378e..30a00ca56 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -1234,15 +1234,18 @@ class ExpressionTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiled ) self.assert_compile( and_(c1 == True, c2 == True, c3 == True), - "x = :x_1 AND x = true AND x = :x_2" + "x = :x_1 AND x = true AND x = :x_2", + dialect=default.DefaultDialect(supports_native_boolean=True) ) self.assert_compile( and_(c1 == 3, c2 == 3, c3 == 3), - "x = :x_1 AND x = :x_2 AND x = :x_3" + "x = :x_1 AND x = :x_2 AND x = :x_3", + dialect=default.DefaultDialect(supports_native_boolean=True) ) self.assert_compile( and_(c1.is_(True), c2.is_(True), c3.is_(True)), - "x IS :x_1 AND x IS true AND x IS :x_2" + "x IS :x_1 AND x IS true AND x IS :x_2", + dialect=default.DefaultDialect(supports_native_boolean=True) ) -- cgit v1.2.1