Re: [h2] Re: Same query, sometimes I get JdbcSQLSyntaxErrorException, sometimes not :o
Greetings. On Sat, 2021-05-22 at 21:26 -0700, Evgenij Ryazanov wrote: > It may be surprising, but validity of some queries depends on the > data. > H2 and some other DBMS support optional feature T301, “Functional > dependencies” from the SQL Standard. Indeed! > SELECT A, B, COUNT(C) FROM TEST GROUP BY A is not valid for DBMS > without the mentioned feature @OP: You can rewrite your query like below in order to make it work in general (without depending on T301). You can also use that form to identify any duplicates easily (using a HAVING COUNT(*)>1), which would break you original SQL Statement. Good luck. WITH t AS ( SELECT DISTINCT iwbatches.id , iwbatches.name , iwbatches.company_id , iwbatches.opener_id o_id , iwbatches.opened_when , Coalesce( Concat( openers.firstname, ' ', openers.lastname ), '' ) o_by , Coalesce( To_Char( iwbatches.opened_when, '-MM-DD hh24:mi' ), '' ) o_when , companies.name company_name , companies.code company_code , companies.streetaddress company_streetaddress , companies.settlement company_settlement , companies.regcode company_regcode , companies.vatcode company_vatcode , companies.contact company_contact , companies.email company_email , companies.phone company_phone , iwbatchrows.id rows_id FROM iwbatches LEFT JOIN iwbatchrows ON iwbatches.id = iwbatchrows.iwbatch_id AND iwbatchrows.needed IS NOT NULL LEFT JOIN persons openers ON iwbatches.opener_id = openers.id LEFT JOIN companies ON iwbatches.company_id = companies.id WHERE iwbatches.finished_when IS NULL AND ( iwbatches.opened_when >= Dateadd( 'DAY', (SELECT CAST(value AS INT) FROM cfg WHERE key = 'iwbatches.oldest.days' ), CURRENT_TIMESTAMP ) ) AND ( iwbatches.opened_when <= Dateadd( 'DAY', (SELECT CAST(value AS INT) FROM cfg WHERE key = 'iwbatches.youngest.days' ), CURRENT_TIMESTAMP ) ) ) , aggregate AS ( SELECT id , Count( rows_id ) rows FROM t GROUP BY id ) SELECT aggregate.id , aggregate.rows , t.id , t.name , t.company_id , t.o_id , t.opened_when , t.o_by , t.o_when , t.company_name , t.company_code , t.company_streetaddress , t.company_settlement , t.company_regcode , t.company_vatcode , t.company_contact , t.company_email , t.company_phone FROM aggregate LEFT JOIN t ON aggregate.id = t.id ORDER BYaggregate , id ; -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e4200bf8dc55e63680b1c02f181e938dc5a8542a.camel%40manticore-projects.com.
Re: [h2] Re: Getting error after upgrading to H2 DB 1.4.200 from 1.4.199 and deploying on Tomcat 9.x
Hello. First of all you need to ensure that your tools and your web application really use the same version of H2 and a new database file is created, in some cases build systems may fetch an unexpected version. And it would be much safer not to use AUTO_SERVER anywhere. If H2 is in classpath of your web application, you need to execute the SHUTDOWN from the lifecycle listener of you application during its unload, otherwise there is a risk that database will not be properly closed. If some version of H2 doesn't work for you, try tome other. 1.4.200 is actually less reliable than 1.4.199. You can also compile H2 from the current sources if you need some new features and can't use 1.4.199 and older versions. You can also use the standalone TCP server of H2 instead of embedded databases. Remote databases aren't affected that much by various problems of their clients. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/3956d635-8dfd-4088-be60-0d2449d671acn%40googlegroups.com.
[h2] Re: Same query, sometimes I get JdbcSQLSyntaxErrorException, sometimes not :o
Hello. It may be surprising, but validity of some queries depends on the data. H2 and some other DBMS support optional feature T301, “Functional dependencies” from the SQL Standard. When this feature is supported select expressions and where clause may reference non-aggregated columns when each such column functionally depends on grouping columns (has the same value in all source rows in each particular group). Therefore when your data satisfies this condition, there is no error. When some column has different values within a group such exception is raised. In DBMS without this optional feature queries like that must be rejected unconditionally. SELECT A, COUNT(C) FROM TEST GROUP BY A is a valid query. SELECT A, B, COUNT(C) FROM TEST GROUP BY A, B is an another valid query. SELECT A, B, COUNT(C) FROM TEST GROUP BY A is not valid for DBMS without the mentioned feature, and may be either valid or invalid for DBMS with this feature; it is valid only when rows with the same value of A have the same value of B. You need to fix your query somehow. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/8bae9292-985f-40c2-85c8-d35a83565fd0n%40googlegroups.com.
