🚀 go-pugleaf

W dniu 2015-02-06 o 11:32, Marek pisze:
> Witam,
>
> W poniższym zapytaniu nie działa JOIN:
>
> WITH RECURSIVE is_child(group_id) AS (
> SELECT group_id FROM user_control WHERE user_control_id=21
> UNION ALL
> SELECT ic.group_id FROM is_child ic
> JOIN user_control uc ON (uc.user_control_id=ic.group.id)
> )
> SELECT group_id FROM is_child;
>
> Ponoć nie istnieje schemat ic. Gdy usunę linię z JOINem, zapytanie
> wykonuje się, czyli ic istnieje. O co może chodzić?

Tą część pytania anuluję. Dopiero teraz dostrzegłem, że mam kropkę w 
group.id zamiast group_id :-D

Jednakże nadal nie rozumiem czemu w UNION ALL nie można stosować w 
składowych zapytaniach sekcji ORDER BY.

-- 
Pozdrawiam,
Marek

W dniu piątek, 6 lutego 2015 14:40:00 UTC+1 użytkownik Marek napisał:
> 
> Jednakże nadal nie rozumiem czemu w UNION ALL nie można stosować w 
> składowych zapytaniach sekcji ORDER BY.
> 
> -- 
> Pozdrawiam,
> Marek

... bo UNION nie gwarantuje zachowania kolejności wierszy poszczególnych składników, tak jak, co do zasady, relacyjna baza danych jest zwolniona z dbania o jakikolwiek porządek elementów przechowywanych obiektów.

ORDER BY służy jedynie to finalnego porządkowania wyników całego zapytania. Ma to odzwierciedlenie w syntaksie: http://www.postgresql.org/docs/9.4/static/sql-select.html, gdzie [ORDER BY ... ] występuje po [ ... UNION ... ].

Tak więc jeżeli nawet opakujesz składnik unii w podzapytanie i wymusisz jego posortowanie, tak jak pokazałeś jako przypadek "działający", to i tak efekt tego sortowania zostanie zniszczony przez UNION (a przynajmniej nie ma gwarancji jego niezniszczenia).

Z powyższego wynika również, że i ORDER BY zastosowany poprawnie za całą unią, ale nadal w obrębie CTE, nadal nie na wiele się przyda, bo analogicznie, nie ma gwarancji co do tego w jakim porządku zostanie zwrócony wynik tego CTE przez zewnętrzny SELECT group_id FROM is_child. 

Dlatego, jeżeli już musisz tu cokolwiek posortować i nie ma w tym dodatkowego ukrytego zamiaru, potrzebujesz napisać:
WITH ... is_child ... AS (...) SELECT group_id FROM is_child ORDER BY 1.

W dniu 2015-02-06 o 16:28, irq pisze:

> ... bo UNION nie gwarantuje zachowania kolejności wierszy
> poszczególnych składników,

Ahhhh... faktycznie! Masz rację.

Ale... coś mi tu nie pasuje gdyż to implikuje bezcelowość istnienia 
zapytania rekurencyjnego WITH RECURSIVE...

Np mamy drzewo kategorii:

CREATE TABLE category (
   category_id SERIAL PRIMARY KEY,
   parent_id int4 REFERENCES category (category_id)
   order_idx int4
   ...

parent_id to ID rodzica lub null
order_idx oznacza kolejność danej kategorii w gałązce

Chcemy je przerzucić do XMLa więc musimy je przetrawersować 
uwzględniając hierarchię. Zapytanie rekurencyjne, które (chyba) do tego 
służy, powinno przejść przez tą strukturę rekurencyjnie i zachować 
kolejność. a jeśli UNION ALL, które musi być składnikiem tej rekurencji 
(bo taka jest składnia) nie gwarantuje kolejności, to cała zabawa traci 
sens. Czy też dostrzegasz sprzeczność?

Konkretnie w tym przypadku zrobiłbym tak (co hipotetycznie nie może 
zadziałać poprawnie choć działa):

WITH RECURSIVE hierarchy(category_id) AS (

	SELECT * FROM --obejście dla niemożności stosowania order by
	(SELECT category_id FROM category WHERE parent_idx ISNULL ORDER BY 
order_idx) AS table1 -- wybiera w/g kolejności kategorie nadrzędne

    UNION ALL

	SELECT * FROM --kolejne obejście dla braku sortowania
	(SELECT * FROM hierarchy h
	JOIN category c ON (c.parent_id=h.category_id)
	ORDER BY c.order_idx) AS table2

	)
SELECT * FROM hierarchy;

No i zapytanie zwraca mi pięknie posortowane drzewko. Przykładowo gdy 
mam rekordy:

category_id | parent_id | nazwa
--------------------------------
     1           null      k1
     2           null      k2
     3             1       k1.1
     4             1       k1.2
     5             2       k2.1
     6             4       k1.2.1

Daje kolejność:
k1
k1.1
k1.2
k1.2.1
k2
k2.1

Jeśli struktura RECURSIVE WITH nie umiałaby sortować w specyficzny 
sposób, to dawałaby taki sam rezultat jak SELECT * FROM category. To jak 
to jest z tym UNION ALL, że mimo to nie powoduje iż RECURSIVE WITH 
funkcjonuje?

-- 
Pozdrawiam,
Marek

  To jak
> to jest z tym UNION ALL, że mimo to nie powoduje iż RECURSIVE WITH
> funkcjonuje?

Miało być:

To jak  to jest z tym UNION ALL, że mimo to powoduje iż RECURSIVE WITH 
funkcjonuje?





-- 
Pozdrawiam,
Marek

W dniu piątek, 6 lutego 2015 19:00:40 UTC+1 użytkownik Marek napisał:

> Konkretnie w tym przypadku zrobiłbym tak (co hipotetycznie nie może 
> zadziałać poprawnie choć działa):
> 
> WITH RECURSIVE hierarchy(category_id) AS (
> 
> 	SELECT * FROM --obejście dla niemożności stosowania order by
> 	(SELECT category_id FROM category WHERE parent_idx ISNULL ORDER BY 
> order_idx) AS table1 -- wybiera w/g kolejności kategorie nadrzędne
> 
>     UNION ALL
> 
> 	SELECT * FROM --kolejne obejście dla braku sortowania
> 	(SELECT * FROM hierarchy h
> 	JOIN category c ON (c.parent_id=h.category_id)
> 	ORDER BY c.order_idx) AS table2
> 
> 	)
> SELECT * FROM hierarchy;
> 
> No i zapytanie zwraca mi pięknie posortowane drzewko. 

podobnie jak pisałem wcześniej, to posortowanie może być zbieżne z oczekiwanym tylko przez przypadek, w związku z konkretnym planem wykonania i jego implementacją. Ta może się zmieniać z wersji na wersję, z silnika na silnik - jeżeli rozważać nie tylko PostgreSQL. Plan wykonania może być zależny od złożoności danych, objętości danych, dostępnych indeksów, itd.

Wymagane i oczekiwane przez Ciebie sortowanie, możesz zagwarantować jedynie poprzez dodanie odpowiedniego ORDER BY po zewnętrznym SELECT * FROM hierarchy.

Przeczytaj pierwszy akapit tutaj: http://www.postgresql.org/docs/9.4/static/queries-order.html. Zwróć uwagę na drugie zdanie. Mówi ono, że swoimi ORDER BY wewnątrz WITH RECURSIVE może Ci się udać wymusić jakiś efekt, ale tylko doraźnie, bez gwarantowanej powtarzalności. Ostatnie zdanie mówi co trzeba zrobić aby rzeczywiście posortować ostateczny resultset w sposób stały i przewidywalny.

Szkoda że nie zamieściłeś kompletnego spójnego przykładu. Fragmenty, które wkleiłeś nie trzymają się kupy, a szkoda, bo można by potestować. Co będzie gdy odwrócisz kierunek sortowania w podzapytaniach?

W dniu 2015-02-09 o 16:32, irq pisze:

> Szkoda że nie zamieściłeś kompletnego spójnego przykładu. Fragmenty,
> które wkleiłeś nie trzymają się kupy, a szkoda, bo można by
> potestować.

Ok, zamieszczam, choć uproszę go. Wywalę zbyteczne pola.

> Co będzie gdy odwrócisz kierunek sortowania w
> podzapytaniach?

Katastrofa.


WITH RECURSIVE get_tree(user_control_id, group_id, weight) AS
		(
		SELECT * FROM
			(SELECT uc.user_control_id, uc.group_id, weight
			FROM user_control uc WHERE group_id ISNULL
			ORDER BY weight) AS table1
			
			UNION ALL
		
		SELECT * FROM
			(SELECT uc.user_control_id, uc.group_id, weight
			FROM get_tree gt
			JOIN user_control uc ON (uc.group_id=gt.user_control_id)
			ORDER BY weight) AS table2
		)
		SELECT * FROM get_tree

Wyjaśniam o co chodzi. Tabela user_control to drzewo hierarchiczne 
obiektów. Pole group_id wskazuje na rodzica lub jest null'em gdy obiekt 
jest nadrzędny. Tak więc w pierwszym podzapytaniu zwracam wszystkie 
obiekty nadrzędne posortowane wagą a potem zbieram podobiekty przypisane 
do nich a potem przypisane do przypisanych itd. Kolumna weight określa 
ich porządek w gałęzi. Zawsze weight zaczyna się od 1, potem 2 itd. Gdy 
w drugim podzapytaniu usuwam ORDER BY - wynik jest żaden. Otrzymuję NULL 
- nie wiem czemu. Chcę otrzymać wynik w postaci posortowanych wagą 
obiektów nadrzędnych, potem posortowanych wagą przypisanych obiektów 
przypisanych itd.

Nie wyobrażam sobie sortowania w SELECT * FROM get_tree, które 
uporządkuje rekordy jak oczekuję.

-- 
Pozdrawiam,
Marek

W dniu poniedziałek, 9 lutego 2015 23:56:49 UTC+1 użytkownik Marek napisał:
> W dniu 2015-02-09 o 16:32, irq pisze:
> 
> > Szkoda że nie zamieściłeś kompletnego spójnego przykładu. Fragmenty,
> > które wkleiłeś nie trzymają się kupy, a szkoda, bo można by
> > potestować.
> 
> Ok, zamieszczam

Ok, ale jak mam uruchomić tę kwerendę? Czy mógłbyś dodać też CREATE TABLE i INSERT przykładowych danych?