Skip to content

query with table alias as upper under [lower_case_tables_name=1] dble makes err explain #1602

@irene-coming

Description

@irene-coming
  • dble version:
    5.6.29-dble-9.9.9.9-e3cf5f30028abdc697f92ca2de57efbb7f9b7a7f-20200102065411

  • preconditions :
    all backend mysql setting:
    lower_case_table_names=1

  • configs:

schema.xml

<table name="sharding_2_t1" dataNode="dn1,dn2" cacheKey="id" rule="hash-two"/>

rule.xml



server.xml



  • steps:
    step1. query in dble:
mysql> create table sharding_2_t1 (id int,busidate char(20),zoneno char(20),brno int,tellerno int) ;
Query OK, 0 rows affected (0.16 sec)
mysql> explain SELECT F.BUSIDATE, F.ZONENO, F.BRNO, F.TELLERNO FROM sharding_2_t1 F WHERE F.id = 1 UNION ALL SELECT '20180716', '00119', 1, 0 UNION ALL SELECT '20180716', '00119', 1, 2;
ERROR 1064 (HY000):  Table 'schema1.f' doesn't exist

mysql> explain SELECT f.BUSIDATE, f.ZONENO, f.BRNO, f.TELLERNO FROM sharding_2_t1 f WHERE f.id = 1 UNION ALL SELECT '20180716', '00119', 1, 0 UNION ALL SELECT '20180716', '00119', 1, 2;
+-----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | TYPE     | SQL/REF                                                                                                                                                                   |
+-----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dn2       | BASE SQL | SELECT f.BUSIDATE, f.ZONENO, f.BRNO, f.TELLERNO FROM sharding_2_t1 f WHERE f.id = 1 UNION ALL SELECT '20180716', '00119', 1, 0 UNION ALL SELECT '20180716', '00119', 1, 2 |
+-----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
  • expect result:
    1.
    mysql> explain SELECT F.BUSIDATE, F.ZONENO, F.BRNO, F.TELLERNO FROM sharding_2_t1 F WHERE F.id = 1 UNION ALL SELECT '20180716', '00119', 1, 0 UNION ALL SELECT '20180716', '00119', 1, 2;
    +-----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | DATA_NODE | TYPE | SQL/REF |
    +-----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | dn2 | BASE SQL | SELECT f.BUSIDATE, f.ZONENO, f.BRNO, f.TELLERNO FROM sharding_2_t1 f WHERE f.id = 1 UNION ALL SELECT '20180716', '00119', 1, 0 UNION ALL SELECT '20180716', '00119', 1, 2 |
    +-----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

  • real result:
    1.

  • supplements:
    1.dble expect table alias lower while user defined it upper

Metadata

Metadata

Assignees

Labels

autotest-ignorethe issue will not be autotest coveredresolveproblem has been fixed by developer

Type

No type

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions