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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/2b6cdbd2-90ec-4446-9853-46de7cd9848bn%40googlegroups.com.
