<single-table> Incorrect "Duplicate column name" with prepared statements in maria-5.3-mwl106

[Philip Stoev]

Not reproducible in maria-5.3. When executing a prepared statement twice, the second execution results in a 1060: 'Duplicate column name ''' error.

Test case:

CREATE TABLE t1 (
  f1 integer,
  f2 integer,
  f3 integer
);
INSERT INTO t1 VALUES (2, 0, 0),(2,0,0);

CREATE VIEW v1 AS SELECT * FROM t1;

PREPARE st1 FROM "
        SELECT
        alias1.f1 AS field1,
        alias1.f2 AS field2,
        alias1.f3 AS field3
        FROM ( v1 AS alias1 JOIN ( t1 AS alias2 JOIN t1 AS alias3 ON 1 = 1 ) ON 1 = 1 )
";

EXECUTE st1;
--error 0
EXECUTE st1;

bzr version-info:

revision-id: <email address hidden>
date: 2011-06-05 21:54:25 -0700
build-date: 2011-06-06 09:58:05 +0300
revno: 3027
branch-nick: maria-5.3-mwl106

[Igor Babaev (igorb-seattle)]

I failed to reproduce this bug on the maria-5.3-mwl106 tree before the fix for bug #793386 (as well as after that fix).

I had:

MariaDB [test]> CREATE TABLE t1 (
    -> f1 integer,
    -> f2 integer,
    -> f3 integer
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO t1 VALUES (2, 0, 0),(2,0,0);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]>
MariaDB [test]> CREATE VIEW v1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]>
MariaDB [test]> PREPARE st1 FROM "
    "> SELECT
    "> alias1.f1 AS field1,
    "> alias1.f2 AS field2,
    "> alias1.f3 AS field3
    "> FROM ( v1 AS alias1 JOIN ( t1 AS alias2 JOIN t1 AS alias3 ON 1 = 1 ) ON 1 = 1 )
    "> ";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [test]>
MariaDB [test]> EXECUTE st1;
+--------+--------+--------+
| field1 | field2 | field3 |
+--------+--------+--------+
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
+--------+--------+--------+
8 rows in set (0.00 sec)

MariaDB [test]> EXECUTE st1;
+--------+--------+--------+
| field1 | field2 | field3 |
+--------+--------+--------+
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
+--------+--------+--------+
8 rows in set (0.01 sec)

Philip,
If the bug is still reproducible I need more details: I need revision # and the specification of the platform where we have the failure.

[Philip Stoev (pstoev-askmonty)]

Still reproducible on fedora 13
Linux eve 2.6.33.3-85.fc13.i686.PAE #1 SMP Thu May 6 18:27:11 UTC 2010 i686 i686 i386 GNU/Linux

server compiled with ./BUILD/compile-pentium-debug-max-no-ndb

server started with

MTR_VERSION=1 perl mysql-test-run.pl --start-and-exit 1st

bzr version-info

revision-id: <email address hidden>
date: 2011-06-06 12:19:35 -0700
build-date: 2011-06-07 07:58:42 +0300
revno: 3028
branch-nick: maria-5.3-mwl106

[Philip Stoev (pstoev-askmonty)]

Also reproducible on 64-bit fedora core 13 with a freshly-compiled binary. Access to this machine is available if needed.

[Philip Stoev (pstoev-askmonty)]

Valgrind does not print any warnings. Not reproducible with a release binary.

[Philip Stoev (pstoev-askmonty)]

The problem is still repeatable with

bzr version-info
revision-id: <email address hidden>
date: 2011-07-01 15:35:34 +0300
build-date: 2011-07-02 11:11:26 +0300
revno: 3074
branch-nick: maria-5.3

new test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  f1 int(11) ,
  f2 int(11) ,
  f3 int(11) ,
  f4 int(11)
);

DEALLOCATE PREPARE st1;
DROP VIEW IF EXISTS v1;
CREATE ALGORITHM=MERGE VIEW v1 AS select a1.f1, a1.f2, a2.f3, a2.f4 from (t1 AS a1 join t1 AS a2) where ((a1.f4 >= a2.f2) and (a2.f3 < 0) ) ;
PREPARE st1 FROM "SELECT a2 . f1 AS f1 , a2 . f2 AS f2 , a2 . f3 AS f3 , a1 . f4 AS f4 FROM v1 AS a1 LEFT JOIN v1 AS a2 USING ( f4 ) WHERE a1 . f2 <> 1225 ";
EXECUTE st1;
EXECUTE st1;

backtrace of error:

#0 my_error (nr=1060, MyFlags=0) at my_error.c:81
#1 0x00000000008a52db in check_duplicate_names (item_list=..., gen_unique_view_name=false) at sql_view.cc:154
#2 0x000000000089f493 in mysql_derived_prepare (thd=0x8cb6b48, lex=0x8d24bd8, derived=0x8d26708) at sql_derived.cc:641
#3 0x00000000008a0078 in mysql_handle_single_derived (lex=0x8d24bd8, derived=0x8d26708, phases=2) at sql_derived.cc:171
#4 0x000000000070d23d in TABLE_LIST::handle_derived (this=0x8d26708, lex=0x8d24bd8, phases=2) at table.cc:5766
#5 0x000000000057a42f in st_select_lex::handle_derived (this=0x8d25160, lex=0x8d24bd8, phases=2) at sql_lex.cc:3209
#6 0x000000000074d75d in JOIN::prepare (this=0x8d3f200, rref_pointer_array=0x8d25380, tables_init=0x8d262d0, wild_num=0, conds_init=0x8d12d28, og_num=0,
    order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x8d25160, unit_arg=0x8d24c78) at sql_select.cc:510
#7 0x000000000074eb9e in mysql_select (thd=0x8cb6b48, rref_pointer_array=0x8d25380, tables=0x8d262d0, wild_num=0, fields=..., conds=0x8d12d28, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2416200192, result=0x8d26b90, unit=0x8d24c78, select_lex=0x8d25160)
    at sql_select.cc:2867
#8 0x0000000000755002 in handle_select (thd=0x8cb6b48, lex=0x8d24bd8, result=0x8d26b90, setup_tables_done_option=0) at sql_select.cc:283
#9 0x00000000006a1fdc in execute_sqlcom_select (thd=0x8cb6b48, all_tables=0x8d262d0) at sql_parse.cc:5087
#10 0x00000000006a3cba in mysql_execute_command (thd=0x8cb6b48) at sql_parse.cc:2231
#11 0x000000000076dcdf in Prepared_statement::execute (this=0x8d1e178, expanded_query=0x44e59c60, open_cursor=false) at sql_prepare.cc:3733
#12 0x000000000076dfea in Prepared_statement::execute_loop (this=0x8d1e178, expanded_query=0x44e59c60, open_cursor=false, packet=0x0, packet_end=0x0)
    at sql_prepare.cc:3414
#13 0x000000000076e2a3 in mysql_sql_stmt_execute (thd=0x8cb6b48) at sql_prepare.cc:2639
#14 0x00000000006a3ce5 in mysql_execute_command (thd=0x8cb6b48) at sql_parse.cc:2240
#15 0x00000000006aca53 in mysql_parse (thd=0x8cb6b48, rawbuf=0x8d12bb0 "EXECUTE st1", length=11, found_semicolon=0x44e5af08) at sql_parse.cc:6088
#16 0x00000000006ad8eb in dispatch_command (command=COM_QUERY, thd=0x8cb6b48, packet=0x8cf44b9 "EXECUTE st1", packet_length=11) at sql_parse.cc:1208
#17 0x00000000006aeef9 in do_command (thd=0x8cb6b48) at sql_parse.cc:906
#18 0x000000000069996f ...

[Philip Stoev (pstoev-askmonty)]

Not reproducible with maria-5.3 before WL#106

[Timour Katchaounov (timour)]

I reproduced this bug both on the machine with CentOS that Philip mentions above,
and on my laptop with the latest 64-bit Ubuntu 11.04. I used the latest 5.3 as of
July 6.

Igor, if this bug is not in your area, please assign it to whoever you think is most
suitable.

[Igor Babaev (igorb-seattle)]

This is a legacy bug.
Maybe it makes sense to port to apply the fix to 5.1 and 5.2 as well