Understood. Thanks!
--
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://gr
> Are they really peers if there's a unique primary key?
Yes, they are if this primary key isn't included into sort specification.
If rows aren't distinct with respect to the sort specification, they are
peers of each other. Columns aren't included into sort specification don't
matter here.
Da
I understand your suggested workaround. The workaround involves copying all the
records into a temp table via query with an order by and then paginating
through the temp table using the _rowid_ as the order by. Gotcha.
Evgenij also suggest something a little more simpler that works for me -
si
On Tue, 2024-04-16 at 09:54 +, 'Peter Borissow' via H2 Database
wrote:
> Hmm... I kinda have a row id already via the unique primary key. What
> advantage would a temp table give in my case?
The advantage is, that special column _rowid_ is the physical row of
the data frame and it would not ch
Hmm... I kinda have a row id already via the unique primary key. What advantage
would a temp table give in my case?
--
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
In my understanding, you could create a temporary table from your query
(without pagination) and then use the special column `_row_id_` for the
pagination.
On Tue, 2024-04-16 at 16:42 +0700, Andreas Reichel wrote:
> On Tue, 2024-04-16 at 09:14 +, 'Peter Borissow' via H2 Database
> wrote:
> > I
I see what you are saying and I can replicate what you're seeing. However the
two test cases are slightly different.
One difference between my test and yours is that I have a unique primary key.
Are they really peers if there's a unique primary key?
Try this:
create table test(a int, b int, c
On Tuesday 16 April 2024 at 17:18:41 UTC+8 Evgenij Ryazanov wrote:
These rows are called *pears* in the SQL Standard.
I meant *peers*, of course.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving
On Tue, 2024-04-16 at 09:14 +, 'Peter Borissow' via H2 Database
wrote:
> I don't care if 218 appears before 217 or if 217 appears before 218
> as long as they both appear.
Peter,
please consider that
1) the second query call knows nothing about the first query call ( and
this fact alone m
These rows are called *pears* in the SQL Standard. The SQL Standard doesn't
require any exact ordering of peers and doesn't require any stable ordering
rules for them. These things are implementation-dependent.
If some particular DBMS doesn't have own documented ordering rules for
pears (I neve
I expect to see both rows 217 and 218 at the break point like this:
I don't care if 218 appears before 217 or if 217 appears before 218 as long as
they both appear.
Unfortunately, sometimes I get "duplicate" 217 at the break point, with no 218.
Other times, I get "duplicate" 218 at the break
Peter,
from what I can see, your records are NOT DISTINCT regarding the ORDER
criteria? So what exactly do you expect to happen when there is no
formal contract on the order and sorting?
Thought experiment: insert all your records but with exactly the same
Date/Timestamp -- in this case, your quer
On 4/16/2024 10:19 AM, 'Peter Borissow' via H2 Database wrote:
(2) The query response is inconsistent. I see 4 different responses coming back from the same query on a static table. I
don't think that's right.
SQL databases implement the relational model. The relational model works with
s
I don't know guys. It looks like a bug to me.
The problem is twofold:
(1) Whenever I see a "duplicate" record, the database is actually skipping a
record. In other words, when I see 218 at the end of the first query and 218 at
the start of the second, record 217 is missing. That is bad.
(2) The q
Hi Peter,
IMHO, Evgenij is 100% correct. This is NOT a bug.
If something (like offset pagination) is not uncommon, it does not make it
the best practice.
If PostgressSQL always yields some deterministic behaviour, where
non-determinism is allowed by SQL standard (and common sence),
it does not
Thanks Evgeni, as always, for your prompt response!
So you don't think this is a bug? Pagination using offset and limit is not
uncommon.
Also, I should have mentioned this earlier but I don't see this behavior in
PostgreSQL. Not sure how other RDBMS behave but it would be interesting to
compa
Hello!
Try the following query: SELECT RANK() OVER (ORDER BY start_date DESC NULLS
FIRST) R, * FROM test ORDER BY 1; Some rows have the same rank:
149 130 2019-01-11 00:00:00-05
150 217 2019-01-02 00:00:00-05
150 218 2019-01-02 00:00:00-05
152 164 2019-01-01 00:00:00-05
152 166 2019-01-01 00:00:0
17 matches
Mail list logo
