Open SSL Version Query
Hi Team, Do we use openssl version 1.0.2, 1.1.1 or 3.0 in postgre 13. If yes then these version are vulnerable CVE-2022-0778 which is now fixed in later ssl version. Regards, Sahaj
Re: Open SSL Version Query
Hi Sahaj AFAIK this is a question for you to ask your chosen OS provider. Postgres will be compiled against the system library (dynamic linking) therefore whether your version of OpenSSL has been patched against the vulnerability is a question for your OS provider, not Postgres. Unless of course you've installed your own version of OpenSSL, in which case you need to ask yourself. ;-) Laura --- Original Message --- On Wednesday, March 23rd, 2022 at 11:48, Sahaj Diwan wrote: > Hi Team, > > Do we use openssl version 1.0.2, 1.1.1 or 3.0 in postgre 13.If yes then these > version are vulnerable CVE-2022-0778 which is now fixed in later ssl version. > > Regards,Sahaj
Unexpected zero results
Hello all,
I'm experimenting with JSON-path functions, and stumbled upon this query:
SELECT jsonb_path_query('[1,2,3]', '$[*]?(@ == 4)')
It returns 0 rows. I expected it to return one row with `null` value. Isn't
it the case that `SELECT ` should always return 1 row?
Viliam
Re: Unexpected zero results
Viliam Ďurina schrieb am 23.03.2022 um 17:56:
Hello all,
I'm experimenting with JSON-path functions, and stumbled upon this query:
SELECT jsonb_path_query('[1,2,3]', '$[*]?(@ == 4)')
It returns 0 rows. I expected it to return one row with `null` value.
Isn't it the case that `SELECT ` should always
return 1 row?
jsonb_path_query is a set returning function, so it's actually more like this:
SELECT *
FROM jsonb_path_query('[1,2,3]', '$[*]?(@ == 4)')
Then it's obvious why no row is returned.
That's one of the reasons I never use set-returning functions in the SELECT
list.
Re: Unexpected zero results
I've just realized that. I used it as an equivalent of the standard
`JSON_QUERY` that returns a JSON value. If the expression matches multiple
values, it can wrap them in a JSON array.
Now I'm surprised that a set-returning function is even allowed in SELECT
clause where the values have to be scalar. I tried another query with even
weirder result:
SELECT jsonb_path_query('[1,2,2]', '$[*]?(@ > 1)') expr1,
jsonb_path_query('[1,2,3]', '$[*]?(@ > 0)') expr2
++---+
| expr1 | expr2 |
++---+
| 2 | 1 |
| 2 | 2 |
| (null) | 3 |
|| |
++---+
Is it documented somewhere how is the set-typed result supposed to work?
Also how come a set contains two elements with the same value?
Viliam
On Wed, Mar 23, 2022 at 6:00 PM Thomas Kellerer wrote:
> Viliam Ďurina schrieb am 23.03.2022 um 17:56:
> > Hello all,
> >
> > I'm experimenting with JSON-path functions, and stumbled upon this query:
> >
> >SELECT jsonb_path_query('[1,2,3]', '$[*]?(@ == 4)')
> >
> > It returns 0 rows. I expected it to return one row with `null` value.
> > Isn't it the case that `SELECT ` should always
> > return 1 row?
>
> jsonb_path_query is a set returning function, so it's actually more like
> this:
>
> SELECT *
> FROM jsonb_path_query('[1,2,3]', '$[*]?(@ == 4)')
>
> Then it's obvious why no row is returned.
>
> That's one of the reasons I never use set-returning functions in the
> SELECT list.
>
>
>
>
Re: Unexpected zero results
On Wed, Mar 23, 2022 at 10:10 AM Viliam Ďurina
wrote:
> Now I'm surprised that a set-returning function is even allowed in SELECT
> clause where the values have to be scalar.
>
AFAIK the lateral construct, which is required to avoid doing just this, is
a relatively recent invention for SQL. I infer from that fact that the
ability to execute a set-returning function in the select clause has always
been allowed. When done, it behaves in a manner similar to an inner join
against the single input evaluation rows (i.e., the one where, typically,
the argument values come from). An inner join of one row and zero rows is
zero rows which is the behavior you are observing.
A true scalar subquery does not have this limitation - even when correlated
it gets joined to the parent relation in a left join manner and so the
single row in the parent relation will always remain and a zero record
outcome will result in null for the scalar subquery output.
FWIW this is the same behavioral dynamic that happens for Regular
Expressions. Our original regexp_matches() function eventually was
supplemented with a regexp_match() function to (mainly) allow for prettier
queries. I like having the option to choose the desired function instead
of having to write the normal single-result case always using a scalar
subquery.
> I tried another query with even weirder result:
>
> SELECT jsonb_path_query('[1,2,2]', '$[*]?(@ > 1)') expr1,
> jsonb_path_query('[1,2,3]', '$[*]?(@ > 0)') expr2
>
> ++---+
> | expr1 | expr2 |
> ++---+
> | 2 | 1 |
> | 2 | 2 |
> | (null) | 3 |
> || |
> ++---+
>
> Is it documented somewhere how is the set-typed result supposed to work?
>
Yep, though probably not where you would expect to find it. We just
haven't had a patch submission as yet that improved matters.
https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET
Also how come a set contains two elements with the same value?
>
That is just how SQL works. A result set does not have all of the
characteristics of a formal mathematical set. Every produced row has a
unique identity independent of the value(s) of the fields. There are SQL
operations that can remove all but one of these identities from a result
set based upon the comparison of the field values (DISTINCT, UNION, etc...).
David J.
Re: Unexpected zero results
"David G. Johnston" writes: > On Wed, Mar 23, 2022 at 10:10 AM Viliam Ďurina > wrote: >> Now I'm surprised that a set-returning function is even allowed in SELECT >> clause where the values have to be scalar. > AFAIK the lateral construct, which is required to avoid doing just this, is > a relatively recent invention for SQL. I infer from that fact that the > ability to execute a set-returning function in the select clause has always > been allowed. I believe that Postgres' handling of that is actually a hangover from Berkeley's PostQUEL language. Dunno what the SQL standard has to say on the subject --- but it wouldn't surprise me if they don't allow it. regards, tom lane
