Re: [h2] alias in HAVING clause
Hi,
If you try with PostgreSQL, both queries fail.
You need to use:
SELECT account_id, COUNT(time) as t FROM TABLE_123
GROUP BY account_id HAVING count(time) = 2;
H2 supports using column aliases in "having", but column names have
priority.
Regards,
Thomas
On Friday, August 21, 2015, Taras Fedkiv wrote:
> Hello,
>
> H2 v1.4.188
> H2 Console (jdbc:h2:mem:test)
>
> Create table and insert few rows in it:
> *CREATE MEMORY TABLE IF NOT EXISTS TABLE_123(account_id BIGINT, time
> BIGINT) NOT PERSISTENT*
>
> *INSERT INTO TABLE_123 (account_id,time) VALUES (10,2)*
> *INSERT INTO TABLE_123 (account_id,time) VALUES (10,2)*
> *INSERT INTO TABLE_123 (account_id,time) VALUES (20,3)*
> *INSERT INTO TABLE_123 (account_id,time) VALUES (20,3)*
>
> 1) *SELECT account_id, COUNT(time) as t FROM TABLE_123 GROUP BY
> account_id HAVING t=2*
> Returns [{account_id=10, t=2}, {account_id=20, t=2}] and its correct
>
> 2)*SELECT account_id, COUNT(time) as time FROM TABLE_123 GROUP BY
> account_id HAVING time=2 *
> Bad Result - returns only [{account_id=10, time=2}],
> Seems it treats 'time' as column but not as alias to 'COUNT(time)'
> function.
> This is incorrect because HAVING filters rows after grouping and column
> 'time' doesn't exist at that stage.
>
> Any help will be highly appreciated.
>
> Thank you,
> Taras
>
> The information in this message may be confidential. It is intended
> solely for
> the addressee(s). If you are not the intended recipient, any disclosure,
> copying or distribution of the message, or any action or omission taken by
> you
> in reliance on it, is prohibited and may be unlawful. Please immediately
> contact the sender if you have received this message in error.
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to h2-database+unsubscr...@googlegroups.com
>
> .
> To post to this group, send email to h2-database@googlegroups.com
> .
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
Re: [h2] Negative zero of DOUBLE type
Hi, Marking it stable will not make it more stable. Using the term "Beta" is just for documentation, and has no effect on the software itself. What makes a big difference on stability is not using features that are documented to be not secure, and are documented to be beta. The MVStore is documented to be beta. Eventually, H2 1.4 (with the MVStore) will be marked stable. Maybe this will be the next release, but I'm not sure yet. Regards, Thomas On Friday, August 21, 2015, Christopher Deckers wrote: > Hi Steve, > > >> You can use the latest H2 1.4 release with MV_STORE=false in the JDBC URL. >> > > I wish I could use 1.4! Unfortunately, 1.4 is still marked as Beta and we > can only include an officially stable version. > > Cheers, > -Christopher > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to h2-database+unsubscr...@googlegroups.com > > . > To post to this group, send email to h2-database@googlegroups.com > . > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] H2 seems to create a huge number of orphan lobs since 1.4.183.
Thomas, I hate to keep coming back to this... But I have built the most recent version 1.4.188 and I have been doing more testing in regards to the continual growth of the database due to the temporary LOB references. In my case, my database file seems to continue to grow even with your new LOB_TIMEOUT value; I am continuing to issue the following statements every 10 minutes when the database file exceeds 50MB SET EXCLUSIVE 1 SET RETENTION_TIME 0 CHECKPOINT_SYNC SET RETENTION_TIME 45000 SET EXCLUSIVE 0 COMMIT However it rarely helps to reduce the db size. In my most recent test the database file had reached 500MB. After 30+ minutes of no db activity ( just active connections and the pseudo compact code from above / 10 mins. ) the db size did not change. After closing the database connections ( and ultimately the database itself )... it took about a minute for the database to completely close and remove the lock file and then the file size reduced 300MB. When I restarted the database and performed the pseudo compact code above it shrunk to 300K. This behavior does not seem to correlate well with your comments in this thread and what I would have expected. It still seems like a complete shutdown is required to reduce the file size. At the risk of beating this subject to death - do you have any thoughts on this? For the most part, It is the only issue and customer complaint that I get from my users concerning the H2 database implementation that I am using. -Kent On Tuesday, April 14, 2015 at 2:12:37 AM UTC-4, Thomas Mueller wrote: > > Hi, > > Yes, I added a timeout. > > First I wanted to keep the CLOB / BLOB objects only until the next > transaction. The problem is that some methods in JdbcConnection started a > new transaction (Statement.getQueryTimeout for example). It would be better > if those transactions are not required, but thats a change for another day. > > Regards, > Thomas > > > > On Sunday, April 12, 2015, Fred&Dani&Pandora&Aquiles > wrote: > >> Hi, >> >> I think he added a timeout for BLOB and CLOB objects: >> >> References to BLOB and CLOB objects now have a timeout. The configuration >> setting is LOB_TIMEOUT (default 5 minutes). This should avoid growing the >> database file if there are many queries that return BLOB or CLOB objects, >> and the database is not closed for a longer time. >> >> Regards, >> >> Fred >> >> >> >> 2015-04-11 16:07 GMT-03:00 Kenton Garner : >> >>> Thomas, what did you decide to do for this? >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "H2 Database" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to h2-database+unsubscr...@googlegroups.com. >>> To post to this group, send email to h2-database@googlegroups.com. >>> Visit this group at http://groups.google.com/group/h2-database. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to h2-database+unsubscr...@googlegroups.com. >> To post to this group, send email to h2-database@googlegroups.com. >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] alias in HAVING clause
Hello,
H2 v1.4.188
H2 Console (jdbc:h2:mem:test)
Create table and insert few rows in it:
*CREATE MEMORY TABLE IF NOT EXISTS TABLE_123(account_id BIGINT, time
BIGINT) NOT PERSISTENT*
*INSERT INTO TABLE_123 (account_id,time) VALUES (10,2)*
*INSERT INTO TABLE_123 (account_id,time) VALUES (10,2)*
*INSERT INTO TABLE_123 (account_id,time) VALUES (20,3)*
*INSERT INTO TABLE_123 (account_id,time) VALUES (20,3)*
1) *SELECT account_id, COUNT(time) as t FROM TABLE_123 GROUP BY account_id
HAVING t=2*
Returns [{account_id=10, t=2}, {account_id=20, t=2}] and its correct
2)*SELECT account_id, COUNT(time) as time FROM TABLE_123 GROUP BY
account_id HAVING time=2 *
Bad Result - returns only [{account_id=10, time=2}],
Seems it treats 'time' as column but not as alias to 'COUNT(time)'
function.
This is incorrect because HAVING filters rows after grouping and column
'time' doesn't exist at that stage.
Any help will be highly appreciated.
Thank you,
Taras
--
The information in this message may be confidential. It is intended solely
for
the addressee(s). If you are not the intended recipient, any disclosure,
copying or distribution of the message, or any action or omission taken by
you
in reliance on it, is prohibited and may be unlawful. Please immediately
contact the sender if you have received this message in error.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
Re: [h2] Negative zero of DOUBLE type
Hi Steve, > You can use the latest H2 1.4 release with MV_STORE=false in the JDBC URL. > I wish I could use 1.4! Unfortunately, 1.4 is still marked as Beta and we can only include an officially stable version. Cheers, -Christopher -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] NPE/performance degradation with version 1.4.185?
Thank you that fixed the problem! h2-1.5.188.jar: 103.34user 16.55system 1:10.39elapsed 170%CPU (with MV_STORE=FALSE) Johann On Friday, 21 August 2015 14:11:14 UTC+2, Noel Grandin wrote: > > > > On 2015-08-21 02:03 PM, Johann Petrak wrote: > > > > Any ideas? I will stay with version h2-1.3.176 for now, but is this > something you can confirm? > > Am I doing it wrong? Is there something that can be done to make the 1.4 > version work as > > fast as the 1.3 version? > > For now, turn off the new MVStore engine by appending MV_STORE=FALSE to > your DB URL. > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] NPE/performance degradation with version 1.4.185?
On 2015-08-21 02:03 PM, Johann Petrak wrote: Any ideas? I will stay with version h2-1.3.176 for now, but is this something you can confirm? Am I doing it wrong? Is there something that can be done to make the 1.4 version work as fast as the 1.3 version? For now, turn off the new MVStore engine by appending MV_STORE=FALSE to your DB URL. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] NPE/performance degradation with version 1.4.185?
Have been away from this problem for a while but now I made a new performance comparison, loading about 9 million rows into a table with just two columns. The table looks like this: CREATE TABLE t1 ( f1 VARCHAR(100) NOT NULL PRIMARY KEY, t2 VARCHAR(100) NOT NULL ); The code I use prepares the statement INSERT INTO t1 (f1,f2) VALUES(?,?) and then, for each row, executes insertSt.setObject(1,v1) insertSt.setObject(2,v2) A single commit is executed at the very end of al inserts. The connections is opened in embedded mode and before anything is inserted into the table the following statements are executed: SET AUTOCOMMIT FALSE SET UNDO_LOG 0 SET LOCK_MODE 0 SET LOG 0 SET CACHE_SIZE 1048576 Exactly the same code is run on a newly created database file for each of the following jar versions: h2-1.3.174.jar: 105.28user 18.24system 1:10.56elapsed 175%CPU h2-1.3.176.jar: 112.24user 18.32system 1:12.28elapsed 180%CPU h2-1.4.188.jar: 1242.22user 32.65system 17:29.98elapsed 121%CPU As you can see, the 1.4 jar is more than 10 times slower for this and the slowdown seems to come mainly from CPU. Any ideas? I will stay with version h2-1.3.176 for now, but is this something you can confirm? Am I doing it wrong? Is there something that can be done to make the 1.4 version work as fast as the 1.3 version? Many thanks, Johann On Wednesday, 4 March 2015 07:43:30 UTC+1, Thomas Mueller wrote: > > Hi, > > Could you please re-try with the latest version (1.4.186)? A memory leak > was fixed there, which could also cause performance problems with a large > (well, medium size) database. If it is still a problem, then could you > please post a test case (or describe what you do in more details)? > > Regards, > Thomas > > > On Monday, March 2, 2015, Johann Petrak > > wrote: > >> Has anyone else seen a degradation in performance when moving from >> version 1.3.174 to version 1.4.185? >> A simple java program which simple does a large number of inserts (in >> embedded mode) appears to be at >> least 20 times slower using the newer version with my configuration. >> >> Also, I have seen situations with the newer version where the program >> appears to hang on shutdown >> and eventually I get the following exception: >> Exception in thread "main" org.h2.jdbc.JdbcSQLException: General error: >> "java.lang.NullPointerException" [5-185] >> at >> org.h2.message.DbException.getJdbcSQLException(DbException.java:345) >> at org.h2.message.DbException.get(DbException.java:168) >> at org.h2.message.DbException.convert(DbException.java:295) >> at >> org.h2.mvstore.db.MVTableEngine$1.uncaughtException(MVTableEngine.java:93) >> at org.h2.mvstore.MVStore.writeInBackground(MVStore.java:2384) >> at >> org.h2.mvstore.MVStore$BackgroundWriterThread.run(MVStore.java:2570) >> Caused by: java.lang.NullPointerException >> at org.h2.mvstore.db.ValueDataType.compare(ValueDataType.java:102) >> at >> org.h2.mvstore.db.TransactionStore$ArrayType.compare(TransactionStore.java:1744) >> at org.h2.mvstore.MVMap.areValuesEqual(MVMap.java:601) >> at org.h2.mvstore.MVMap.replace(MVMap.java:615) >> at org.h2.mvstore.MVMap.rewrite(MVMap.java:806) >> at org.h2.mvstore.MVMap.rewrite(MVMap.java:823) >> at org.h2.mvstore.MVMap.rewrite(MVMap.java:823) >> at org.h2.mvstore.MVMap.rewrite(MVMap.java:823) >> at org.h2.mvstore.MVMap.rewrite(MVMap.java:823) >> at org.h2.mvstore.MVMap.rewrite(MVMap.java:782) >> at org.h2.mvstore.MVStore.compactRewrite(MVStore.java:1796) >> at org.h2.mvstore.MVStore.compact(MVStore.java:1688) >> at org.h2.mvstore.MVStore.writeInBackground(MVStore.java:2380) >> >> but i have never seen this with the older version. >> >> This is happening with Java 1.8.0_20-b26 on 64 bit intel server with the >> database file residing on an SSD drive. >> The program that executes the insert statements first executes the >> following >> SET AUTOCOMMIT FALSE >> SET UNDO_LOG 0 >> SET LOCK_MODE 0 >> SET LOG 0 >> SET CACHE_SIZE 1048576 >> and it never executes a commit until the very end (before shutdown). >> It uses a prepared INSERT statement and sets the value of its fields >> using statement.setObject(col,val) where value in >> that case is always a String object. >> >> Thanks, >> johann >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to h2-database+unsubscr...@googlegroups.com. >> To post to this group, send email to h2-database@googlegroups.com. >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...
Re: [h2] Negative zero of DOUBLE type
Christopher, You can use the latest H2 1.4 release with MV_STORE=false in the JDBC URL. This will, in effect, give you what could be considered an H2 1.3 database engine, but with the performance tweaks and bug fixes of the last year or so included. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
