https://www.timestored.com/data/h2-database-online/?qcode=DROP%20TABLE%20IF%20EXISTS%20weather%3B%0ADROP%20TABLE%20IF%20EXISTS%20cities%3B%0ADROP%20TABLE%20IF%20EXISTS%20sales%3B%0A%0A%0Acreate%20table%20sales(item%20text%2C%20sale_time%20timestamp%2C%20quantity%20int)%3B%0Ainsert%20into%20sales%20values('a'%2C%20'2001-01-01%2000%3A18%3A00'%2C%2010)%3B%0Ainsert%20into%20sales%20values('b'%2C%20'2001-01-01%2000%3A18%3A30'%2C%2020)%3B%0Ainsert%20into%20sales%20values('c'%2C%20'2001-01-01%2000%3A19%3A00'%2C%2030)%3B%0A%0ACREATE%20TABLE%20weather%20(%0Acity%20%20%20%20%20%20%20%20%20%20%20VARCHAR%2C%0Atemp_lo%20%20%20%20%20%20%20%20INTEGER%2C%20--%20minimum%20temperature%20on%20a%20day%0Atemp_hi%20%20%20%20%20%20%20%20INTEGER%2C%20--%20maximum%20temperature%20on%20a%20day%0Aprcp%20%20%20%20%20%20%20%20%20%20%20REAL%2C%0Adate%20%20%20%20%20%20%20%20%20%20%20DATE%0A)%3B%0A%0AINSERT%20INTO%20weather%20VALUES%20('San%20Francisco'%2C%2046%2C%2050%2C%200.25%2C%20'1994-11-27')%3B%0AINSERT%20INTO%20weather%20VALUES%20('San%20Francisco'%2C%2060%2C%2070%2C%200.22%2C%20'2022-06-10')%3B%0AINSERT%20INTO%20weather%20VALUES%20('San%20Francisco'%2C%2044%2C%2055%2C%200.33%2C%20'2023-01-01')%3B%0AINSERT%20INTO%20weather%20(city%2C%20temp_lo%2C%20temp_hi%2C%20prcp%2C%20date)%20%20%20%20%20VALUES%20('San%20Francisco'%2C%2043%2C%2057%2C%200.0%2C%20'1994-11-29')%3B%0A%0A%0ACREATE%20TABLE%20cities(Country%20VARCHAR%2C%20Name%20VARCHAR%2C%20%60Year%60%20INTEGER%2C%20Population%20INTEGER)%3B%0AINSERT%20INTO%20cities%20VALUES%20('NL'%2C%20'Amsterdam'%2C%202000%2C%201005)%3B%0AINSERT%20INTO%20cities%20VALUES%20('NL'%2C%20'Amsterdam'%2C%202010%2C%201065)%3B%0AINSERT%20INTO%20cities%20VALUES%20('NL'%2C%20'Amsterdam'%2C%202020%2C%201158)%3B%0AINSERT%20INTO%20cities%20VALUES%20('US'%2C%20'Seattle'%2C%202000%2C%20564)%3B%0AINSERT%20INTO%20cities%20VALUES%20('US'%2C%20'Seattle'%2C%202010%2C%20608)%3B%0AINSERT%20INTO%20cities%20VALUES%20('US'%2C%20'Seattle'%2C%202020%2C%20738)%3B%0AINSERT%20INTO%20cities%20VALUES%20('US'%2C%20'New%20York%20City'%2C%202000%2C%208015)%3B%0AINSERT%20INTO%20cities%20VALUES%20('US'%2C%20'New%20York%20City'%2C%202010%2C%208175)%3B%0AINSERT%20INTO%20cities%20VALUES%20('US'%2C%20'New%20York%20City'%2C%202020%2C%208772)%3B%0A%0A%0A%2F**%20SELECT%20*%20FROm%20information_schema.tables%3B%20**%2F%0ASELECT%20*%20FROM%20sales%3B%0ASELECT%20*%20FROM%20weather%3B%0ASELECT%20*%20FROM%20cities%3B%0Aip%20adress%3A10.108.103.208 Pada Minggu, 17 September 2023 pukul 16.08.44 UTC+8 Abeleshev Artem menulis:
> > Thank you for the answer. It works as expected. > But I realized that it will not solve my original problem I have in mind. > Actually, I'm tryting to come up with some more or less ANSI compliant > solution for the following issue: > > I have two (or more) columns in database that represents the state of some > single complex object. Taking an example from above, let's imagine that > "bar" and "baz" are parts of one object. In case of "foo" contains scalar > value (not collection) of the object, both columns "bar" and "baz" will > have scalar types (not array): > > > > > > > *create table foo ( id varchar(256) primary key, bar integer, baz > varchar(256));* > and I can select using following query: > > *select * from foo where bar = ... and baz = ...* > > But in case of "foo" contains collection of the this objects, both columns > "bar" and "baz" become array types: > > > > > > > *create table foo ( id varchar(256) primary key, bar integer array, baz > varchar(256) array);* > and if I select using following query: > > *select * from foo where ... = any(bar) and ... = any(baz)* > > it will give me false matches, as I need to compare only values from both > arrays of the same ordinality. > > While unnest in H2 supports unnesting of multiple arrays, it is impossible > to use arrays taken from the currently selected row (it seems it is unable > to reference columns of outside query from unnest finction). like this: > > *select * from foo where exists (select 1 from unnest ((select bar from > foo where id = foo.id <http://foo.id>), (select baz from foo where id = > foo.id <http://foo.id>)) as e(e1, e2) where e1 = '...' and e2 = '...')* > > In PostgreSQL referencing columns of curretly selected row from unnest is > possible though. As well as it supports more clean way, allowing to use > column references directly instead of array expressions within the unnest > function: > > *select * from foo where exists (select 1 from unnest (bar, baz) as e(e1, > e2) where e1 = '...' and e2 = '...')* > > BTW, Is there are any plans to support latest? > > Thanks! > > With respect, > Artem > On Sunday, September 17, 2023 at 12:10:08 PM UTC+9 Evgenij Ryazanov wrote: > >> Hello! >> >> Yes, it is possible, but with valid SQL only. >> >> Subqueries must be enclosed in parentheses, all your queries are >> incorrect. Valid queries are >> >> select * from unnest((select bar from foo)); >> select * from unnest((select baz from foo)); >> select * from unnest((select bar from foo), (select baz from foo)); >> >> In some cases H2 allows subqueries without parentheses due to historic >> reasons, but this undocumented syntax was implemented very inconsistently >> and actually it isn't possible to implement it in reliable way due to >> syntax conflicts. Don't use it, it is not supported and it may not work in >> future versions of H2 in places where it works in 2.2.222. >> > -- 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/2b6cdbd2-90ec-4446-9853-46de7cd9848bn%40googlegroups.com.
