[MDEV-32283] CTE renaming columns too early in union subquery elements - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.4.31
Fix Version/s: 10.4(EOL)
Component/s: Optimizer - CTE
Labels:
- cte
- name_resolution

Description

with cte (c1,c2) as (select a, b from t1 union select a, b from t1 order by a ) select * from cte;

provokes an error

ERROR 1054 (42S22): Unknown column 'a' in 'order clause'

Where both of these do not

select a, b from t1 union select a, b from t1 order by a;

with cte (c1,c2) as (select * from (select a, b from t1 union select a, b from t1 order by b) tt ) select * from cte;

Attachments

Issue Links

is blocked by

MDEV-26831 New name resolution

Stalled

Activity

Rex Johnston added a comment - 2023-09-28 19:17 - edited

stack trace captured when raising error

#0  my_error (nr=1054, MyFlags=0) at /home/rex/src/mariadb/server.10.4-MDEV-31995/mysys/my_error.c:109

#1  0x000056200257766c in find_field_in_tables (thd=0x562007558080, item=0x56200756ddc8, first_table=0x56200756c068, last_table=0x0, ref=0x56200756df00, report_error=REPORT_ALL_ERRORS,

    check_privileges=true, register_tree_change=true) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_base.cc:6606

#2  0x00005620029a64b1 in Item_field::fix_outer_field (this=0x56200756ddc8, thd=0x562007558080, from_field=0x7fb2f0135a40, reference=0x56200756df00)

    at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/item.cc:5739

#3  0x00005620029a72a1 in Item_field::fix_fields (this=0x56200756ddc8, thd=0x562007558080, reference=0x56200756df00) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/item.cc:5994

#4  0x00005620024f3f0f in Item::fix_fields_if_needed (this=0x56200756ddc8, thd=0x562007558080, ref=0x56200756df00) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/item.h:966

#5  0x00005620024f3f45 in Item::fix_fields_if_needed_for_scalar (this=0x56200756ddc8, thd=0x562007558080, ref=0x56200756df00) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/item.h:970

#6  0x00005620026b91c5 in Item::fix_fields_if_needed_for_order_by (this=0x56200756ddc8, thd=0x562007558080, ref=0x56200756df00) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/item.h:978

#7  0x00005620026a80d6 in find_order_in_list (thd=0x562007558080, ref_pointer_array=..., tables=0x56200756c068, order=0x56200756def0, fields=..., all_fields=..., is_group_field=false,

    add_to_all_fields=true, from_window_spec=false) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_select.cc:24916

#8  0x00005620026a82ac in setup_order (thd=0x562007558080, ref_pointer_array=..., tables=0x56200756c068, fields=..., all_fields=..., order=0x56200756def0, from_window_spec=false)

    at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_select.cc:24963

#9  0x00005620026605b8 in setup_without_group (thd=0x562007558080, ref_pointer_array=..., tables=0x56200756c068, leaves=..., fields=..., all_fields=..., conds=0x5620075e92b0,

    order=0x56200756def0, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x5620075e918f) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_select.cc:753

#10 0x00005620026638f4 in JOIN::prepare (this=0x5620075e8ea8, tables_init=0x56200756c068, wild_num=0, conds_init=0x0, og_num=1, order_init=0x56200756def0, skip_order_by=false,

    group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x56200756d8f8, unit_arg=0x56200756c020) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_select.cc:1354

#11 0x00005620026709e1 in mysql_select (thd=0x562007558080, tables=0x56200756c068, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x56200756def0, group=0x0, having=0x0, proc_param=0x0,

    select_options=268435456, result=0x56200756fe58, unit=0x56200756c020, select_lex=0x56200756d8f8) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_select.cc:4781

#12 0x0000562002735329 in st_select_lex_unit::exec (this=0x56200756c020) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_union.cc:1729

#13 0x00005620025be0d1 in mysql_derived_fill (thd=0x562007558080, lex=0x56200755bf08, derived=0x56200756e6a0) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_derived.cc:1255

#14 0x00005620025bb18d in mysql_handle_single_derived (lex=0x56200755bf08, derived=0x56200756e6a0, phases=96) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_derived.cc:200

columns renamed here in mysql_derived_prepare()

  // st_select_lex_unit::prepare correctly work for single select

  if ((res= unit->prepare(derived, derived->derived_result, 0)))

    goto exit;

  if (derived->with &&

      (res= derived->with->rename_columns_of_derived_unit(thd, unit)))

    goto exit;

  if ((res= check_duplicate_names(thd, unit->types, 0)))

    goto exit;

Rex Johnston added a comment - 2023-09-28 19:17 - edited stack trace captured when raising error #0 my_error (nr=1054, MyFlags=0) at /home/rex/src/mariadb/server.10.4-MDEV-31995/mysys/my_error.c:109 #1 0x000056200257766c in find_field_in_tables (thd=0x562007558080, item=0x56200756ddc8, first_table=0x56200756c068, last_table=0x0, ref=0x56200756df00, report_error=REPORT_ALL_ERRORS, check_privileges=true, register_tree_change=true) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_base.cc:6606 #2 0x00005620029a64b1 in Item_field::fix_outer_field (this=0x56200756ddc8, thd=0x562007558080, from_field=0x7fb2f0135a40, reference=0x56200756df00) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/item.cc:5739 #3 0x00005620029a72a1 in Item_field::fix_fields (this=0x56200756ddc8, thd=0x562007558080, reference=0x56200756df00) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/item.cc:5994 #4 0x00005620024f3f0f in Item::fix_fields_if_needed (this=0x56200756ddc8, thd=0x562007558080, ref=0x56200756df00) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/item.h:966 #5 0x00005620024f3f45 in Item::fix_fields_if_needed_for_scalar (this=0x56200756ddc8, thd=0x562007558080, ref=0x56200756df00) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/item.h:970 #6 0x00005620026b91c5 in Item::fix_fields_if_needed_for_order_by (this=0x56200756ddc8, thd=0x562007558080, ref=0x56200756df00) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/item.h:978 #7 0x00005620026a80d6 in find_order_in_list (thd=0x562007558080, ref_pointer_array=..., tables=0x56200756c068, order=0x56200756def0, fields=..., all_fields=..., is_group_field=false, add_to_all_fields=true, from_window_spec=false) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_select.cc:24916 #8 0x00005620026a82ac in setup_order (thd=0x562007558080, ref_pointer_array=..., tables=0x56200756c068, fields=..., all_fields=..., order=0x56200756def0, from_window_spec=false) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_select.cc:24963 #9 0x00005620026605b8 in setup_without_group (thd=0x562007558080, ref_pointer_array=..., tables=0x56200756c068, leaves=..., fields=..., all_fields=..., conds=0x5620075e92b0, order=0x56200756def0, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x5620075e918f) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_select.cc:753 #10 0x00005620026638f4 in JOIN::prepare (this=0x5620075e8ea8, tables_init=0x56200756c068, wild_num=0, conds_init=0x0, og_num=1, order_init=0x56200756def0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x56200756d8f8, unit_arg=0x56200756c020) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_select.cc:1354 #11 0x00005620026709e1 in mysql_select (thd=0x562007558080, tables=0x56200756c068, wild_num=0, fields=..., conds=0x0, og_num=1, order=0x56200756def0, group=0x0, having=0x0, proc_param=0x0, select_options=268435456, result=0x56200756fe58, unit=0x56200756c020, select_lex=0x56200756d8f8) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_select.cc:4781 #12 0x0000562002735329 in st_select_lex_unit::exec (this=0x56200756c020) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_union.cc:1729 #13 0x00005620025be0d1 in mysql_derived_fill (thd=0x562007558080, lex=0x56200755bf08, derived=0x56200756e6a0) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_derived.cc:1255 #14 0x00005620025bb18d in mysql_handle_single_derived (lex=0x56200755bf08, derived=0x56200756e6a0, phases=96) at /home/rex/src/mariadb/server.10.4-MDEV-31995/sql/sql_derived.cc:200 columns renamed here in mysql_derived_prepare() // st_select_lex_unit::prepare correctly work for single select if ((res= unit->prepare(derived, derived->derived_result, 0))) goto exit ; if (derived->with && (res= derived->with->rename_columns_of_derived_unit(thd, unit))) goto exit ; if ((res= check_duplicate_names(thd, unit->types, 0))) goto exit ;

MariaDB Server

CTE renaming columns too early in union subquery elements

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration