package net.sourceforge.mayfly.acceptance.expression; import net.sourceforge.mayfly.acceptance.SqlTestCase; import java.sql.ResultSet; public class WhereTest extends SqlTestCase { public void testWhere() throws Exception { execute("create table foo (a integer, b integer)"); execute("insert into foo (a, b) values (4, 16)"); execute("insert into foo (a, b) values (5, 25)"); ResultSet results = query("select a, b from foo where b = 25"); assertTrue(results.next()); assertEquals(5, results.getInt("a")); assertEquals(25, results.getInt("b")); assertFalse(results.next()); } public void testWhereIsCaseSensitive() throws Exception { execute("create table foo (a varchar(80))"); execute("insert into foo (a) values ('Foo')"); ResultSet wrongCase = query("select a from foo where a = 'FOO'"); if (dialect.stringComparisonsAreCaseInsensitive()) { assertTrue(wrongCase.next()); assertEquals("Foo", wrongCase.getString("a")); } assertFalse(wrongCase.next()); ResultSet correctCase = query("select a from foo where a = 'Foo'"); assertTrue(correctCase.next()); assertEquals("Foo", correctCase.getString("a")); assertFalse(correctCase.next()); } public void testWhereAnd() throws Exception { execute("create table foo (a integer, b integer, c integer)"); execute("insert into foo (a, b, c) values (1, 1, 1)"); execute("insert into foo (a, b, c) values (1, 1, 2)"); execute("insert into foo (a, b, c) values (1, 2, 1)"); execute("insert into foo (a, b, c) values (1, 2, 2)"); execute("insert into foo (a, b, c) values (2, 2, 2)"); execute("insert into foo (a, b, c) values (1, 2, null)"); execute("insert into foo (a, b, c) values (null, 1, 1)"); assertResultSet( new String[] { " 1, 1, 1 ", " 1, 2, 1 " }, query("select a, b, c from foo where a=1 and c=1") ); } public void testWhatOr() throws Exception { execute("create table foo (a integer, b integer, c integer)"); execute("insert into foo (a, b, c) values (1, 1, 1)"); execute("insert into foo (a, b, c) values (1, 1, 2)"); execute("insert into foo (a, b, c) values (1, 2, 1)"); execute("insert into foo (a, b, c) values (1, 2, 2)"); execute("insert into foo (a, b, c) values (2, 2, 2)"); execute("insert into foo (a, b, c) values (2, null, 1)"); execute("insert into foo (a, b, c) values (null, 1, 1)"); execute("insert into foo (a, b, c) values (null, null, 1)"); assertResultSet( new String[] { " 1, 1, 1 ", " 1, 1, 2 ", " 2, 2, 2 ", " 2, null, 1 ", " null, 1, 1 ", }, query("select a, b, c from foo where a=2 or b=1") ); } public void testNotEqual() throws Exception { execute("create table foo (a integer)"); execute("insert into foo (a) values (4)"); execute("insert into foo (a) values (5)"); execute("insert into foo (a) values (6)"); assertResultSet( new String[] { " 4 ", " 6 ", }, query("select a from foo where a != 5") ); assertResultSet( new String[] { " 4 ", " 6 ", }, query("select a from foo where 5 != a") ); assertResultSet( new String[] { " 4 ", " 6 ", }, query("select a from foo where a <> 5") ); assertResultSet( new String[] { " 4 ", " 6 ", }, query("select a from foo where 5 <> a") ); } public void testGreaterThan() throws Exception { execute("create table foo (a integer)"); execute("insert into foo (a) values (4)"); execute("insert into foo (a) values (5)"); execute("insert into foo (a) values (6)"); execute("insert into foo (a) values (null)"); assertResultSet( new String[] { " 5 ", " 6 ", }, query("select a from foo where a > 4") ); assertResultSet( new String[] { " 4 ", " 5 ", }, query("select a from foo where 6 > a ") ); assertResultSet( new String[] { " 4 ", " 5 ", }, query("select a from foo where a < 6 ") ); } public void testLessThanOrEqual() throws Exception { execute("create table foo (a integer)"); execute("insert into foo (a) values (4)"); execute("insert into foo (a) values (5)"); execute("insert into foo (a) values (6)"); execute("insert into foo (a) values (null)"); assertResultSet( new String[] { " 4 ", " 5 ", }, query("select a from foo where a <= 5") ); } public void testGreaterThanOrEqual() throws Exception { execute("create table foo (a integer)"); execute("insert into foo (a) values (4)"); execute("insert into foo (a) values (5)"); execute("insert into foo (a) values (6)"); execute("insert into foo (a) values (null)"); assertResultSet( new String[] { " 5 ", " 6 ", }, query("select a from foo where a >= 5") ); } public void testSimpleIn() throws Exception { execute("create table foo (a integer, b integer)"); execute("insert into foo (a, b) values (1, 1)"); execute("insert into foo (a, b) values (2, 4)"); execute("insert into foo (a, b) values (3, 9)"); // OK, this one is where an SQL boolean needs to be true,false,null. // I think. // execute("insert into foo (a, b) values (null, -1)"); assertResultSet( new String[] { " 1 ", " 9 ", }, query("select b from foo where foo.a in (1, 3)") ); assertResultSet( new String[] { " 4 ", }, query("select b from foo where not (foo.a in (1, 3))") ); assertResultSet( new String[] { " 4 ", }, query("select b from foo where foo.a not in (1, 3)") ); } public void testExpressions() throws Exception { execute("create table foo (a integer, b integer, c integer, description varchar(255))"); execute("insert into foo(a, b, c, description) values (1, 1, 3, 'equals b')"); execute("insert into foo(a, b, c, description) values (null, null, 3, 'is null')"); execute("insert into foo(a, b, c, description) values (1, 2, 3, 'equals nothing')"); assertResultSet( new String[] { " 'equals b' " }, query("select description from foo where a in (b, c)") ); assertResultSet( new String[] { " 'equals nothing' " , " 'equals b' " }, query("select description from foo where a in (b, b - 1)") ); } public void testNullLiteralOnRightHandSideOfIn() throws Exception { execute("create table foo (a integer, b integer)"); execute("insert into foo(a, b) values (null, 17)"); String sql = "select b from foo where a in (null, 5)"; if (dialect.disallowNullOnRightHandSideOfIn()) { expectQueryFailure(sql, "To check for null, use IS NULL or IS NOT NULL, not a null literal"); } else { // Hah! Gotcha! null = null evaluates to false. assertResultSet(new String[] { }, query(sql)); } } public void testInPrecedence() throws Exception { execute("create table foo (a integer, b integer)"); execute("insert into foo (a, b) values (1, 1)"); execute("insert into foo (a, b) values (2, 4)"); execute("insert into foo (a, b) values (3, 9)"); String negateTheIn = "select b from foo where not foo.a in (1, 3)"; if (dialect.notBindsMoreTightlyThanIn()) { assertResultSet( new String[] { }, query(negateTheIn) ); } else { assertResultSet( new String[] { " 4 " }, query(negateTheIn) ); } String booleanAsLeftSideOfIn = "select b from foo where (not foo.a) in (1, 3)"; if (dialect.notRequiresBoolean()) { // Mayfly and Postgres are pickier than some databases about boolean vs non-boolean // If some writes SQL like that they are either making a mistake, or they are // being too clever for our tastes. expectQueryFailure(booleanAsLeftSideOfIn, "expected boolean expression but got non-boolean expression"); // The message should identify what part of the expression is the problem. // For example, "expected boolean expression but got foo.a" // And/or by context, for example: // expectQueryFailure(booleanAsLeftSideOfIn, "operand of NOT must be a boolean expression"); } else { assertResultSet(new String[] { }, query(booleanAsLeftSideOfIn)); } } public void testLikePrecedence() throws Exception { /* Another case involving NOT and an infix. Inspired by a blog post by David Pashley. */ execute("create table foo(a varchar(40))"); execute("insert into foo(a) values(null)"); execute("insert into foo(a) values('aa')"); execute("insert into foo(a) values('a-a')"); execute("insert into foo(a) values('0')"); execute("insert into foo(a) values('1')"); assertResultSet(new String[] { " null ", " 'aa' ", " '0' ", " '1' " }, query("select a from foo where a is null or not a like '%-%'")); assertResultSet(new String[] { " null ", " 'aa' ", " '0' ", " '1' " }, query("select a from foo where a is null or a not like '%-%'")); String notOnAString = "select a from foo where a is null or (not a) like '%-%'"; if (dialect.notRequiresBooleanForLike()) { expectQueryFailure(notOnAString, "expected boolean expression but got non-boolean expression"); } else { assertResultSet(new String[] { " null " }, query(notOnAString)); } } public void testInWithSubselect() throws Exception { execute("create table foo (a integer, b integer)"); execute("insert into foo (a, b) values (1, 1)"); execute("insert into foo (a, b) values (2, 4)"); execute("insert into foo (a, b) values (3, 9)"); execute("create table bar (c integer)"); execute("insert into bar (c) values (2)"); execute("insert into bar (c) values (3)"); assertResultSet( new String[] { " 4 ", " 9 ", }, query("select b from foo where foo.a in (select c from bar)") ); } public void testReferToColumnAlias() throws Exception { execute("create table foo(a integer, b integer)"); execute("insert into foo(a, b) values(3, 10)"); execute("insert into foo(a, b) values(7, 20)"); String sql = "select a + b as a_and_b from foo where a_and_b < 20"; if (dialect.whereCanReferToColumnAlias()) { assertResultSet( new String[] { " 13 " }, query(sql) ); } else { expectQueryFailure(sql, "no column a_and_b"); } } }