Re: [h2] Re: Same query, sometimes I get JdbcSQLSyntaxErrorException, sometimes not :o
P.S. Yay, it works now - thank you everyone, I'm really grateful for your advise. :) ciphe...@gmail.com kirjutas pühapäev, 23. mai 2021 kl 11:15:47 UTC+3: > Wow, thanks for educating me about new SQL features. :) > > I suspected this was data-dependent, but couldn't figure out how this was > possible, or what exactly was data-dependent. > > P.S. > > Also, many thanks for the new query you proposed. :) It didn't run on > first try, but I'll adjust it and get things working soon. :) > and...@manticore-projects.com kirjutas pühapäev, 23. mai 2021 kl 07:59:26 > UTC+3: > >> 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/d0b88c9b-6481-4c0e-b1a6-084b2ff680c4n%40googlegroups.com.
Re: [h2] Re: Same query, sometimes I get JdbcSQLSyntaxErrorException, sometimes not :o
Wow, thanks for educating me about new SQL features. :) I suspected this was data-dependent, but couldn't figure out how this was possible, or what exactly was data-dependent. P.S. Also, many thanks for the new query you proposed. :) It didn't run on first try, but I'll adjust it and get things working soon. :) and...@manticore-projects.com kirjutas pühapäev, 23. mai 2021 kl 07:59:26 UTC+3: > 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/1a0cbffb-d1ac-4ea5-b158-4ed9dd45881fn%40googlegroups.com.
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.
[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.
