Hi, Thanks for the great test case! I can now reproduce the problem and will try to fix it for the next release.
Regards, Thomas On Fri, Jul 31, 2009 at 10:43 AM, nigelm<[email protected]> wrote: > > I've adjusted it :- > > HTH, > Nigel > > -----8<------------ > > package test; > > import java.sql.Connection; > import java.sql.DriverManager; > import java.sql.ResultSet; > import java.sql.SQLException; > import java.sql.Statement; > import org.h2.tools.DeleteDbFiles; > > public class H2Test { > > static int val = 0; > > public static int getMaxRev(Connection conn) > throws SQLException { > //System.out.println("MaxRev = " + val); > return val; > } > public static void main(String[] a) throws Exception { > Class.forName("org.h2.Driver"); > DeleteDbFiles.execute("data/test", "test", true); > Connection conn = DriverManager.getConnection( > "jdbc:h2:data/test/test", "sa", "sa"); > Statement stat = conn.createStatement(); > stat.execute("create table test(id int, val int, x varchar, > primary key (id,val))"); > > > for(int i=1;i<=10;i++) > { > stat.execute("insert into test values (1," + i + ",'val" + i > + "')"); > } > > stat.execute("CREATE ALIAS GET_MAX_REV " + > "FOR \"test.H2Test.getMaxRev\""); > > ResultSet rs; > > stat.execute("CREATE VIEW THINGY AS " + > "SELECT tbl.* FROM TEST AS tbl, (SELECT ID, MAX(VAL) > innerVal FROM test WHERE val <= GET_MAX_REV() GROUP BY ID) tbi" + > " WHERE tbl.id=tbi.id AND tbl.val=tbi.innerVal" > ); > > > val = 8; > > System.out.println("SELECT * FROM THINGY - expect result with " > + val); > rs = stat.executeQuery("SELECT * FROM THINGY"); > dump(rs); > > val = 5; > > System.out.println("SELECT * FROM THINGY - expect result with " > + val); > rs = stat.executeQuery("SELECT * FROM THINGY"); > dump(rs); > > conn.close(); > } > > protected static void dump(ResultSet rs) throws SQLException > { > for(int i=0;i<rs.getMetaData().getColumnCount();i++) > { > System.out.print(rs.getMetaData().getColumnName(i+1) + "\t"); > } > System.out.println > ("\n-------------------------------------------------"); > while(rs.next()) > { > for(int i=0;i<rs.getMetaData().getColumnCount();i++) > { > System.out.print(rs.getObject(i+1) + "\t"); > } > } > System.out.println > ("\n================================================="); > > } > } > > > On Jul 31, 7:38 am, Thomas Mueller <[email protected]> > wrote: >> Hi, >> >> Sorry I can't reproduce the problem so far. My test case is: >> >> import java.sql.Connection; >> import java.sql.DriverManager; >> import java.sql.ResultSet; >> import java.sql.SQLException; >> import java.sql.Statement; >> import org.h2.tools.DeleteDbFiles; >> >> public class TestDb2 { >> public static int getMaxRev(Connection conn) >> throws SQLException { >> ResultSet rs = conn.createStatement(). >> executeQuery("select max(id) from test"); >> rs.next(); >> System.out.println(" getMaxRev: " + rs.getInt(1)); >> return rs.getInt(1); >> } >> public static void main(String[] a) throws Exception { >> Class.forName("org.h2.Driver"); >> DeleteDbFiles.execute("data/test", "test", true); >> Connection conn = DriverManager.getConnection( >> "jdbc:h2:data/test/test", "sa", "sa"); >> Statement stat = conn.createStatement(); >> stat.execute("create table test(id identity) " + >> "as select x from system_range(1, 2);"); >> stat.execute("CREATE ALIAS GET_MAX_REV " + >> "FOR \"TestDb2.getMaxRev\""); >> stat.execute("CREATE VIEW THINGY AS " + >> "SELECT * FROM TEST WHERE ID<=GET_MAX_REV()"); >> ResultSet rs; >> System.out.println("SELECT * FROM THINGY"); >> rs = stat.executeQuery("SELECT * FROM THINGY"); >> while (rs.next()) { >> System.out.println("a: " + rs.getString(1)); >> } >> stat.execute("insert into test " + >> "select x from system_range(10, 11);"); >> System.out.println("SELECT * FROM THINGY"); >> rs = stat.executeQuery("SELECT * FROM THINGY"); >> while (rs.next()) { >> System.out.println("b: " + rs.getString(1)); >> } >> conn.close(); >> } >> >> } >> >> Could you change my test case so it fails? >> >> Regards, >> Thomas >> >> On Fri, Jul 24, 2009 at 10:30 PM, nigelm<[email protected]> wrote: >> >> > Hi >> >> > I have migrated away from HSQLDB mostly with success, save for 1 >> > issue. >> >> > We create some database views that use a function in order to control >> > their results. E.g: >> >> > CREATE ALIAS GET_MAX_REV FOR "net.foo.bar.getMaxRev" >> >> > CREATE VIEW THINGY AS >> > SELECT * FROM WIDGETS >> > WHERE REV <= GET_MAX_REV() >> >> > This works, but with one shortcoming. The getMaxRev function is >> > volatile, so successive calls to >> > SELECT * FROM THINGY >> >> > Ought to potentially return dramatically different results. But I can >> > see by setting a breakpoint in getMaxRev that periodically it is not >> > being called. >> >> > I assume that either the function result, or the query result has been >> > cached somewhere. Is there a way to declare that the function is >> > volatile (perhaps by passing an unused timestamp parameter) ? > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
