Oracle sucks — различия между версиями

Материал из YourcmcWiki
Перейти к: навигация, поиск
м
м
 
(не показана одна промежуточная версия этого же участника)
Строка 1: Строка 1:
 +
Oracle, бывает, очень прикольно исполняет некоторые запросы.
 +
 +
Например, здесь, если раскомментировать /* ORDER BY "alias1".id_employee */, результат запроса меняется!
 +
 
<source lang="sql">
 
<source lang="sql">
 
SELECT
 
SELECT
"alias6".birth_date AS "col4",
+
"alias6".birth_date AS "col4",
"alias6".e_category AS "col5",
+
"alias6".e_category AS "col5",
"alias6".id_chief AS "col6",
+
"alias6".id_chief AS "col6",
"alias6".id_department AS "col7",
+
"alias6".id_department AS "col7",
"alias6".fire_date AS "col8",
+
"alias6".fire_date AS "col8",
"alias6".first_name AS "col9",
+
"alias6".first_name AS "col9",
"alias6".hire_date AS "col10",
+
"alias6".hire_date AS "col10",
"alias6".id_employee AS "col11",
+
"alias6".id_employee AS "col11",
"alias6".last_name AS "col12",
+
"alias6".last_name AS "col12",
"alias6".dtm_modified AS "col13",
+
"alias6".dtm_modified AS "col13",
"alias6".salary AS "col14",
+
"alias6".salary AS "col14",
"alias6".e_sex AS "col15",
+
"alias6".e_sex AS "col15",
"alias6".id_product AS "col16"
+
"alias6".id_product AS "col16"
FROM
+
FROM (
(
+
SELECT "alias1".id_employee as "col123",
SELECT
+
  CASE WHEN (EXISTS (
"alias1".id_employee as "col123",
+
    SELECT '1' AS "col1"
CASE WHEN (EXISTS
+
    FROM t_employee "alias2"
(
+
    WHERE "alias2".id_chief = "alias1".id_employee
SELECT
+
  ))
'1' AS "col1"
+
  THEN ("alias1".id_employee) ELSE ("alias1".id_chief) END AS "col2"
FROM
+
FROM t_employee "alias1"
t_employee "alias2"
+
/* ORDER BY "alias1".id_employee - если раскомментировать, результат запроса меняется! */
WHERE
+
"alias2".id_chief = "alias1".id_employee
+
)
+
) THEN ("alias1".id_employee) ELSE ("alias1".id_chief) END AS "col2"
+
FROM
+
t_employee "alias1"
+
/* ORDER BY
+
"alias1".id_employee */
+
 
) "alias4"
 
) "alias4"
LEFT JOIN
+
LEFT JOIN (t_employee "alias3"
(
+
LEFT JOIN (t_employee "alias5"
t_employee "alias3"
+
  LEFT JOIN t_employee "alias6" ON "alias5".id_chief = "alias6".id_employee
LEFT JOIN
+
) ON "alias3".id_chief = "alias5".id_employee
(
+
) ON "alias4"."col2" = "alias3".id_employee
t_employee "alias5"
+
ORDER BY "alias4"."col123"
LEFT JOIN
+
t_employee "alias6"
+
ON "alias5".id_chief = "alias6".id_employee
+
)
+
ON "alias3".id_chief = "alias5".id_employee
+
)
+
ON "alias4"."col2" = "alias3".id_employee
+
order by "alias4"."col123"
+
 
</source>
 
</source>

Текущая версия на 14:11, 23 марта 2011

Oracle, бывает, очень прикольно исполняет некоторые запросы.

Например, здесь, если раскомментировать /* ORDER BY "alias1".id_employee */, результат запроса меняется!

SELECT
 "alias6".birth_date AS "col4",
 "alias6".e_category AS "col5",
 "alias6".id_chief AS "col6",
 "alias6".id_department AS "col7",
 "alias6".fire_date AS "col8",
 "alias6".first_name AS "col9",
 "alias6".hire_date AS "col10",
 "alias6".id_employee AS "col11",
 "alias6".last_name AS "col12",
 "alias6".dtm_modified AS "col13",
 "alias6".salary AS "col14",
 "alias6".e_sex AS "col15",
 "alias6".id_product AS "col16"
FROM (
 SELECT "alias1".id_employee AS "col123",
  CASE WHEN (EXISTS (
    SELECT '1' AS "col1"
    FROM t_employee "alias2"
    WHERE "alias2".id_chief = "alias1".id_employee
   ))
  THEN ("alias1".id_employee) ELSE ("alias1".id_chief) END AS "col2"
 FROM t_employee "alias1"
 /* ORDER BY "alias1".id_employee - если раскомментировать, результат запроса меняется! */
) "alias4"
LEFT JOIN (t_employee "alias3"
 LEFT JOIN (t_employee "alias5"
  LEFT JOIN t_employee "alias6" ON "alias5".id_chief = "alias6".id_employee
 ) ON "alias3".id_chief = "alias5".id_employee
) ON "alias4"."col2" = "alias3".id_employee
ORDER BY "alias4"."col123"