public void testSqlFunctionAsAlias() throws Exception {
    String functionName = locateAppropriateDialectFunctionNameForAliasTest();
    if (functionName == null) {
      log.info("Dialect does not list any no-arg functions");
      return;
    }

    log.info("Using function named [" + functionName + "] for 'function as alias' test");
    String query =
        "select "
            + functionName
            + " from Simple as "
            + functionName
            + " where "
            + functionName
            + ".id = 10";

    Session s = openSession();
    Transaction t = s.beginTransaction();
    Simple simple = new Simple();
    simple.setName("Simple 1");
    s.save(simple, new Long(10));
    t.commit();
    s.close();

    s = openSession();
    t = s.beginTransaction();
    List result = s.find(query);
    assertTrue(result.size() == 1);
    assertTrue(result.get(0) instanceof Simple);
    s.delete(result.get(0));
    t.commit();
    s.close();
  }
  public void testDialectSQLFunctions() throws Exception {

    Session s = openSession();
    Transaction t = s.beginTransaction();

    Iterator iter = s.iterate("select max(s.count) from Simple s");

    if (getDialect() instanceof MySQLDialect) assertTrue(iter.hasNext() && iter.next() == null);

    Simple simple = new Simple();
    simple.setName("Simple Dialect Function Test");
    simple.setAddress("Simple Address");
    simple.setPay(new Float(45.8));
    simple.setCount(2);
    s.save(simple, new Long(10));

    // Test to make sure allocating an specified object operates correctly.
    assertTrue(
        s.find("select new org.hibernate.test.legacy.S(s.count, s.address) from Simple s").size()
            == 1);

    // Quick check the base dialect functions operate correctly
    assertTrue(s.find("select max(s.count) from Simple s").size() == 1);
    assertTrue(s.find("select count(*) from Simple s").size() == 1);

    if (getDialect() instanceof OracleDialect) {
      // Check Oracle Dialect mix of dialect functions - no args (no parenthesis and single arg
      // functions
      java.util.List rset =
          s.find("select s.name, sysdate(), trunc(s.pay), round(s.pay) from Simple s");
      assertNotNull("Name string should have been returned", (((Object[]) rset.get(0))[0]));
      assertNotNull("Todays Date should have been returned", (((Object[]) rset.get(0))[1]));
      assertEquals("trunc(45.8) result was incorrect ", new Float(45), ((Object[]) rset.get(0))[2]);
      assertEquals("round(45.8) result was incorrect ", new Float(46), ((Object[]) rset.get(0))[3]);

      simple.setPay(new Float(-45.8));
      s.update(simple);

      // Test type conversions while using nested functions (Float to Int).
      rset = s.find("select abs(round(s.pay)) from Simple s");
      assertEquals("abs(round(-45.8)) result was incorrect ", new Float(46), rset.get(0));

      // Test a larger depth 3 function example - Not a useful combo other than for testing
      assertTrue(s.find("select trunc(round(sysdate())) from Simple s").size() == 1);

      // Test the oracle standard NVL funtion as a test of multi-param functions...
      simple.setPay(null);
      s.update(simple);
      Integer value =
          (Integer)
              s.find("select MOD( NVL(s.pay, 5000), 2 ) from Simple as s where s.id = 10").get(0);
      assertTrue(0 == value.intValue());
    }

    if ((getDialect() instanceof HSQLDialect)) {
      // Test the hsql standard MOD funtion as a test of multi-param functions...
      Integer value =
          (Integer) s.find("select MOD(s.count, 2) from Simple as s where s.id = 10").get(0);
      assertTrue(0 == value.intValue());
    }

    s.delete(simple);
    t.commit();
    s.close();
  }
  public void testSQLFunctions() throws Exception {
    Session s = openSession();
    Transaction t = s.beginTransaction();
    Simple simple = new Simple();
    simple.setName("Simple 1");
    s.save(simple, new Long(10));

    if (getDialect() instanceof DB2Dialect) {
      s.find("from Simple s where repeat('foo', 3) = 'foofoofoo'");
      s.find("from Simple s where repeat(s.name, 3) = 'foofoofoo'");
      s.find("from Simple s where repeat( lower(s.name), 3 + (1-1) / 2) = 'foofoofoo'");
    }

    assertTrue(s.find("from Simple s where upper( s.name ) ='SIMPLE 1'").size() == 1);
    if (!(getDialect() instanceof HSQLDialect)) {
      assertTrue(
          s.find(
                      "from Simple s where not( upper( s.name ) ='yada' or 1=2 or 'foo'='bar' or not('foo'='foo') or 'foo' like 'bar' )")
                  .size()
              == 1);
    }
    if (!(getDialect() instanceof MySQLDialect)
        && !(getDialect() instanceof SybaseDialect)
        && !(getDialect() instanceof MckoiDialect)
        && !(getDialect() instanceof InterbaseDialect)
        && !(getDialect()
            instanceof TimesTenDialect)) { // My SQL has a funny concatenation operator
      assertTrue(
          s.find("from Simple s where lower( s.name || ' foo' ) ='simple 1 foo'").size() == 1);
    }
    if ((getDialect() instanceof SybaseDialect)) {
      assertTrue(
          s.find("from Simple s where lower( s.name + ' foo' ) ='simple 1 foo'").size() == 1);
    }
    if ((getDialect() instanceof MckoiDialect) || (getDialect() instanceof TimesTenDialect)) {
      assertTrue(
          s.find("from Simple s where lower( concat(s.name, ' foo') ) ='simple 1 foo'").size()
              == 1);
    }

    Simple other = new Simple();
    other.setName("Simple 2");
    other.setCount(12);
    simple.setOther(other);
    s.save(other, new Long(20));
    // s.find("from Simple s where s.name ## 'cat|rat|bag'");
    assertTrue(s.find("from Simple s where upper( s.other.name ) ='SIMPLE 2'").size() == 1);
    assertTrue(s.find("from Simple s where not ( upper( s.other.name ) ='SIMPLE 2' )").size() == 0);
    assertTrue(
        s.find(
                    "select distinct s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2")
                .size()
            == 1);
    assertTrue(
        s.find(
                    "select s from Simple s where ( ( s.other.count + 3 ) = (15*2)/2 and s.count = 69) or ( ( s.other.count + 2 ) / 7 ) = 2 order by s.other.count")
                .size()
            == 1);
    Simple min = new Simple();
    min.setCount(-1);
    s.save(min, new Long(30));
    if (!(getDialect() instanceof MySQLDialect)
        && !(getDialect() instanceof HSQLDialect)) { // My SQL has no subqueries
      assertTrue(
          s.find("from Simple s where s.count > ( select min(sim.count) from Simple sim )").size()
              == 2);
      t.commit();
      t = s.beginTransaction();
      assertTrue(
          s.find(
                      "from Simple s where s = some( select sim from Simple sim where sim.count>=0 ) and s.count >= 0")
                  .size()
              == 2);
      assertTrue(
          s.find(
                      "from Simple s where s = some( select sim from Simple sim where sim.other.count=s.other.count ) and s.other.count > 0")
                  .size()
              == 1);
    }

    Iterator iter =
        s.iterate("select sum(s.count) from Simple s group by s.count having sum(s.count) > 10");
    assertTrue(iter.hasNext());
    assertTrue(new Integer(12).equals(iter.next()));
    assertTrue(!iter.hasNext());
    if (!(getDialect() instanceof MySQLDialect)) {
      iter = s.iterate("select s.count from Simple s group by s.count having s.count = 12");
      assertTrue(iter.hasNext());
    }

    s.iterate(
        "select s.id, s.count, count(t), max(t.date) from Simple s, Simple t where s.count = t.count group by s.id, s.count order by s.count");

    Query q = s.createQuery("from Simple s");
    q.setMaxResults(10);
    assertTrue(q.list().size() == 3);
    q = s.createQuery("from Simple s");
    q.setMaxResults(1);
    assertTrue(q.list().size() == 1);
    q = s.createQuery("from Simple s");
    assertTrue(q.list().size() == 3);
    q = s.createQuery("from Simple s where s.name = ?");
    q.setString(0, "Simple 1");
    assertTrue(q.list().size() == 1);
    q = s.createQuery("from Simple s where s.name = ? and upper(s.name) = ?");
    q.setString(1, "SIMPLE 1");
    q.setString(0, "Simple 1");
    q.setFirstResult(0);
    assertTrue(q.iterate().hasNext());
    q =
        s.createQuery(
            "from Simple s where s.name = :foo and upper(s.name) = :bar or s.count=:count or s.count=:count + 1");
    q.setParameter("bar", "SIMPLE 1");
    q.setString("foo", "Simple 1");
    q.setInteger("count", 69);
    q.setFirstResult(0);
    assertTrue(q.iterate().hasNext());
    q = s.createQuery("select s.id from Simple s");
    q.setFirstResult(1);
    q.setMaxResults(2);
    iter = q.iterate();
    int i = 0;
    while (iter.hasNext()) {
      assertTrue(iter.next() instanceof Long);
      i++;
    }
    assertTrue(i == 2);
    q = s.createQuery("select all s, s.other from Simple s where s = :s");
    q.setParameter("s", simple);
    assertTrue(q.list().size() == 1);

    q = s.createQuery("from Simple s where s.name in (:name_list) and s.count > :count");
    HashSet set = new HashSet();
    set.add("Simple 1");
    set.add("foo");
    q.setParameterList("name_list", set);
    q.setParameter("count", new Integer(-1));
    assertTrue(q.list().size() == 1);

    ScrollableResults sr = s.createQuery("from Simple s").scroll();
    sr.next();
    sr.get(0);
    sr.close();

    s.delete(other);
    s.delete(simple);
    s.delete(min);
    t.commit();
    s.close();
  }