Skip to content

complex query got error "field not found:autoalias_null.autoalias_scalar" #593

New issue
New issue
Closed
Closed
complex query got error "field not found:autoalias_null.autoalias_scalar"#593
Assignees
irene-coming
Labels
complex-queryresolveproblem has been fixed by developerverifiedissue's resolve was verified by tester
Milestone
2.18.09.0
@irene-coming

Description

@irene-coming
irene-coming
opened on Jul 16, 2018
  • dble version：
    5.6.29-dble-2.18.06.0-20180620065910
  • configs：

schema.xml

<?xml version="1.0"?>
<!DOCTYPE dble:schema SYSTEM "schema.dtd">
<dble:schema xmlns:dble="http://dble.cloud/">
	<!--benchmarksql-->
    	<schema name="benchmarksql" dataNode="dn1">
	    	<table name="bmsql_warehouse"  dataNode="dn1,dn2,dn3" rule="rule-warehouse" />
		<table name="bmsql_stock"  dataNode="dn1,dn2,dn3" rule="rule-stock" />
		<table name="bmsql_history" dataNode="dn1,dn2,dn3" rule="rule-history" primaryKey="hist_id" autoIncrement="true"/>
		<table name="bmsql_new_order"  dataNode="dn1,dn2,dn3" rule="rule-new_orders" />
	    	<table name="bmsql_order_line"  dataNode="dn1,dn2,dn3" rule="rule-order_line" />
		<table name="bmsql_district"  dataNode="dn1,dn2,dn3" rule="rule-district" />
	    	<table name="bmsql_customer"  dataNode="dn1,dn2,dn3" rule="rule-customer" />
       		<table name="bmsql_oorder"  dataNode="dn1,dn2,dn3" rule="rule-orders" />
		<table name="bmsql_item"  dataNode="dn1,dn2,dn3" type="global" />
	</schema>
	<dataNode name="dn1" dataHost="host_1" database="zhj" /> <!--this database "test1" is real name in mysql -->
	<dataNode name="dn2" dataHost="host_2" database="zhj" />
	<dataNode name="dn3" dataHost="host_3" database="zhj" />

	<dataHost name="host_1" maxCon="1000" minCon="1000" balance="0" writeType="0" switchType="1"  slaveThreshold="100">
	<heartbeat>select user()</heartbeat>
	<writeHost host="hostM1" url="10.186.17.101:3306" user="action" password="action"></writeHost>
	</dataHost>

	<dataHost name="host_2" maxCon="1000" minCon="1000" balance="0" writeType="0" switchType="1"  slaveThreshold="100">
	<heartbeat>select user()</heartbeat>
	<writeHost host="hostM2" url="10.186.17.102:3306" user="action" password="action"></writeHost>
	</dataHost>

	<dataHost name="host_3" maxCon="1000" minCon="1000" balance="0" writeType="0" switchType="1" slaveThreshold="100">
	<heartbeat>select user()</heartbeat>
	<writeHost host="hostM3" url="10.186.17.105:3306" user="action" password="action"></writeHost>
	</dataHost>
</dble:schema>

rule.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE dble:rule SYSTEM "rule.dtd">
<dble:rule xmlns:dble="http://dble.cloud/">
    <tableRule name="rule-warehouse">
        <rule>
            <columns>w_id</columns>
                <algorithm>mod</algorithm>
        </rule>
    </tableRule>
    <tableRule name="rule-district">
        <rule>
            <columns>d_w_id</columns>
                <algorithm>mod</algorithm>
        </rule>
    </tableRule>
    <tableRule name="rule-stock">
        <rule>
            <columns>s_w_id</columns>
                <algorithm>mod</algorithm>
        </rule>
    </tableRule>
    <tableRule name="rule-history">
        <rule>
            <columns>h_w_id</columns>
            <algorithm>mod</algorithm>
        </rule>
    </tableRule>
    <tableRule name="rule-new_orders">
        <rule>
            <columns>no_w_id</columns>
            <algorithm>mod</algorithm>
        </rule>
    </tableRule>
    <tableRule name="rule-order_line">
        <rule>
            <columns>ol_w_id</columns>
            <algorithm>mod</algorithm>
        </rule>
    </tableRule>
    <tableRule name="rule-customer">
        <rule>
            <columns>c_w_id</columns>
            <algorithm>mod</algorithm>
        </rule>
    </tableRule>
    <tableRule name="rule-orders">
        <rule>
            <columns>o_w_id</columns>
            <algorithm>mod</algorithm>
        </rule>
    </tableRule>

    <function name="mod" class="Hash">
       <property name="partitionCount">3</property>
       <property name="partitionLength">1</property>
    </function>
</dble:rule>

server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE dble:server SYSTEM "server.dtd">
<dble:server xmlns:dble="http://dble.cloud/">

        <system>
        <property name="useSqlStat">0</property>
        <property name="useGlobleTableCheck">0</property>
        <property name="dataNodeHeartbeatPeriod">600000</property>
        <property name="dataNodeIdleCheckPeriod">600000</property>
        <property name="checkTableConsistency">0</property>
        <property name="sequnceHandlerType">2</property>
        <property name="processorBufferPoolType">0</property>
        <property name="useOffHeapForMerge">1</property>
        <property name="memoryPageSize">1m</property>
        <property name="spillsFileBufferSize">8k</property>
        <property name="useStreamOutput">0</property>
        <property name="systemReserveMemorySize">384m</property>
        <property name="useZKSwitch">true</property>
        <property name="lowerCaseTableNames">true</property>
        <property name="processors">8</property>
        <property name="backendProcessors">8</property>
        <property name="processorExecutor">16</property>
        <property name="backendProcessorExecutor">4</property>
        <property name="writeToBackendExecutor">8</property>
        <property name="sqlExecuteTimeout">3000000</property>
        <property name="useJoinStrategy">false</property>
        <property name="complexExecutor">1</property>
        <property name="costTimeStat">1</property>
        <property name="useCostTimeStat">1</property>
        <property name="costSamplePercent">1</property>
        <property name="maxCostStatSize">100</property>
        <property name="idleCount">10000000</property>
        <property name="maxHandleCount">1</property>
        <property name="bufferPoolChunkSize">4096</property>
        <property name="bufferPoolPageNumber">1280</property>
        <property name="bufferPoolPageSize">2097152</property>
        <property name="frontSocketNoDelay">1</property>
        <property name="backSocketNoDelay">1</property>
        <property name="useThreadUsageStat">1</property>
        <property name="usePerformanceMode">1</property>
        <property name="txIsolation">2</property>
        <property name="ioratio">1024</property>
        <property name="queueSizePerBackendProcessor">1</property>
        <property name="backendPostSelectExecutor">16</property>
</system>
        <user name="action">
                <property name="password">action</property>
                <property name="schemas">benchmarksql</property>
                <property name="benchmark">1000000</property>
                                <!-- this schemas name is a virtual name on dble.-->
        </user>
         <user name="test">
           <property name="password">test</property>
           <property name="manager">true</property>
        </user>
</dble:server>
  • steps：
    step1. create tables with following statements：
CREATE TABLE `bmsql_district` (
  `d_w_id` int(11) NOT NULL,
  `d_id` int(11) NOT NULL,
  `d_ytd` decimal(12,2) DEFAULT NULL,
  `d_tax` decimal(4,4) DEFAULT NULL,
  `d_next_o_id` int(11) DEFAULT NULL,
  `d_name` varchar(10) DEFAULT NULL,
  `d_street_1` varchar(20) DEFAULT NULL,
  `d_street_2` varchar(20) DEFAULT NULL,
  `d_city` varchar(20) DEFAULT NULL,
  `d_state` char(2) DEFAULT NULL,
  `d_zip` char(9) DEFAULT NULL,
  PRIMARY KEY (`d_w_id`,`d_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

CREATE TABLE `bmsql_stock` (
  `s_w_id` int(11) NOT NULL,
  `s_i_id` int(11) NOT NULL,
  `s_quantity` int(11) DEFAULT NULL,
  `s_ytd` int(11) DEFAULT NULL,
  `s_order_cnt` int(11) DEFAULT NULL,
  `s_remote_cnt` int(11) DEFAULT NULL,
  `s_data` varchar(50) DEFAULT NULL,
  `s_dist_01` char(24) DEFAULT NULL,
  `s_dist_02` char(24) DEFAULT NULL,
  `s_dist_03` char(24) DEFAULT NULL,
  `s_dist_04` char(24) DEFAULT NULL,
  `s_dist_05` char(24) DEFAULT NULL,
  `s_dist_06` char(24) DEFAULT NULL,
  `s_dist_07` char(24) DEFAULT NULL,
  `s_dist_08` char(24) DEFAULT NULL,
  `s_dist_09` char(24) DEFAULT NULL,
  `s_dist_10` char(24) DEFAULT NULL,
  PRIMARY KEY (`s_w_id`,`s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8；

CREATE TABLE `bmsql_order_line` (
  `ol_w_id` int(11) NOT NULL,
  `ol_d_id` int(11) NOT NULL,
  `ol_o_id` int(11) NOT NULL,
  `ol_number` int(11) NOT NULL,
  `ol_i_id` int(11) NOT NULL,
  `ol_delivery_d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_quantity` int(11) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

step2. query with dble business port:
SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 6 AND s_quantity < 13 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 6 AND d_id = 6 );

  • expect result：
    1. query success
  • real result：
    1. query got:
      ERROR 1003 (HY000): field not found:autoalias_null.autoalias_scalar
  • supplements：
    1. the feedback be improved if possible.

/label ~BUG

Metadata

Metadata

Assignees

Labels

complex-queryresolveproblem has been fixed by developerverifiedissue's resolve was verified by tester

Type

No type

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions