Mycat-Server
Mycat-Server copied to clipboard
MyCATApache
mycat sql兼容问题反馈
二表关联查询发现少记录 有个goods_order表, 测试环境经常出现-1主键,使用修改后的mysql函数
发件人： 从零开始 发送时间： 2015-07-07 11:04 收件人： MyCATApache/Mycat-Server 主题： [Mycat-Server] mycat sql兼容问题反馈 (#392) 如果大家有sql 兼容问题，可以在这里发。 — Reply to this email directly or view it on GitHub.
查询视图时候and 和 or 混写出现记录重复
视图sql
CREATE ALGORITHM=UNDEFINED DEFINER=root@% SQL SECURITY DEFINER VIEW note_category_info AS select cat.category_id AS category_id,cat.parent_id AS parent_id,cat.category_type AS category_type,trans.lang_type AS lang_type,trans.content AS content,cat.note_count AS note_count,cat.create_date AS create_date,cat.update_date AS update_date,trans.create_by_user_id AS create_by_user_id,trans.is_sys_category AS is_sys_category from (note_categories cat join note_translations trans on(((cat.category_id = trans.content_id) and (trans.content_type = 1))))
视图配置 table name="note_category_info" type="global" dataNode="cdn1,cdn2,cdn3" 视图note_category_info 是两个全局表（dataNode="cdn1,cdn2,cdn3"/）的关联查询
explain select category_id,content from note_category_info where category_type = 2 and parent_id = 11 and lang_type = 1 and (is_sys_category =1 or create_by_user_id = 5200) 这个语句有时候会在两个几点取数据造成数据重复
explain select category_id,content from note_category_info where category_type = 2 and parent_id = 11 and lang_type = 1 这个语句ok
测试工具navicate，手动点了10几次测试的结果 mycat1.4rc的版本。安装在centos 6.5环境
全mysql环境，1.4RC版本。
1、mysql> select 员工表.empno,员工表.ename from emp 员工表; ERROR 1064 (HY000): com.alibaba.druid.sql.parser.ParserException: illegal identifier
不支持中文表别名。
2、mysql> select sin(51.746) from dual; ERROR 1064 (HY000): can't find table define in schema DUAL schema:scott 虚拟表，不支持。mysql oracle都支持这种写法，好像oracle是必须这样写。
3、mysql> select job as '工作职位',count(*) as '数量' from emp group by job; ERROR 1105 (HY000): java.lang.IllegalArgumentException: all columns in group by clause should be in the selected column list.!'工作职位' group by 中不支持中文别名。
4、mysql> select * from emp group by job having sal>1000 and comm is null;
ERROR 1105 (HY000): java.lang.NullPointerException
mysql>
mysql> select * from emp group by job having sal>1000 and deptno in (20,30);
ERROR 1105 (HY000): java.lang.NullPointerException
group by 中，多条件过滤，会出错。
5、now()，sysdate()，curdate()，curtime()，current_timestamp()等这类函数，因为mycat中执行SQL有先后，可能会导致数据不一致问题。
6、出错，右外连接。 mysql> select * from emp e right outer join bonus b on e.ename=b.ename; +-------+--------+----------+------+------------+------+------+--------+--------+----------+------+------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | ename | job | sal | comm | +-------+--------+----------+------+------------+------+------+--------+--------+----------+------+------+ | 7369 | smith | clerk | 7902 | 1980-12-17 | 800 | NULL | 20 | smith | clerk | 800 | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | allen | salesman | 1600 | 300 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ward | salesman | 1250 | 500 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | jones | manager | 2975 | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | martin | salesman | 1250 | 1400 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | blake | manager | 2850 | NULL | | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | allen | salesman | 1600 | 300 | | 7521 | ward | salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 | ward | salesman | 1250 | 500 | | 7566 | jones | manager | 7839 | 1981-04-02 | 2975 | NULL | 20 | jones | manager | 2975 | NULL | | 7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 | martin | salesman | 1250 | 1400 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | smith | clerk | 800 | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | blake | manager | 2850 | NULL | | 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | blake | manager | 2850 | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | smith | clerk | 800 | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | allen | salesman | 1600 | 300 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ward | salesman | 1250 | 500 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | jones | manager | 2975 | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | martin | salesman | 1250 | 1400 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | smith | clerk | 800 | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | allen | salesman | 1600 | 300 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ward | salesman | 1250 | 500 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | jones | manager | 2975 | NULL | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | martin | salesman | 1250 | 1400 | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | blake | manager | 2850 | NULL | +-------+--------+----------+------+------------+------+------+--------+--------+----------+------+------+ 24 rows in set (0.01 sec) 这里出现了问题。？？？？why？
7、自连接： mysql> select * from emp e join emp p on e.empno=p.mgr; +-------+-------+-----------+------+------------+------+------+--------+-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+-------+--------+----------+------+------------+------+------+--------+ | 7839 | king | president | NULL | 1981-11-17 | 5000 | NULL | 10 | 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7839 | king | president | NULL | 1981-11-17 | 5000 | NULL | 10 | 7782 | clark | manager | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | 7844 | turner | salesman | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7788 | scott | analyst | 7566 | 1987-07-13 | 3000 | NULL | 20 | 7876 | adams | clerk | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | 7900 | james | clerk | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7782 | clark | manager | 7839 | 1981-06-09 | 2450 | NULL | 10 | 7934 | miller | clerk | 7782 | 1982-01-23 | 1300 | NULL | 10 | +-------+-------+-----------+------+------------+------+------+--------+-------+--------+----------+------+------------+------+------+--------+ 6 rows in set (0.01 sec) 看到这个结果，就知道，又错了。使用的是与oracle的scott用户表一样，在mysql上测试的。
8、mysql> explain select * from emp empno in (select empno from emp where job='manager'); +-----------+-----------------------------------+ | DATA_NODE | SQL | +-----------+-----------------------------------+ | dn1 | SELECT * FROM emp empno LIMIT 100 | | dn2 | SELECT * FROM emp empno LIMIT 100 | 发现什么了吗？是的，SQL都有问题，mycat还能出来结果。
9、any，all的问题。 select * from emp where sal > all (select sal from emp where job='clerk'); select * from emp where sal > any (select sal from emp where job='clerk');
出来的结果，是有问题的。测试在mysql上，表是oracle自带用户scott用户的表cp到mysql上测试。
关于联合查询问题：
mysql> select * from emp where empno=7788 -> union -> select * from emp where ename='smith' -> union -> select * from emp where sal=5000; +-------+-------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+ | 7788 | scott | analyst | 7566 | 1987-07-13 | 3000 | NULL | 20 | | 7839 | king | president | NULL | 1981-11-17 | 5000 | NULL | 10 | +-------+-------+-----------+------+------------+------+------+--------+ 2 rows in set (0.01 sec)
mysql> select * from emp where ename='smith'; +-------+-------+-------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-------+------+------------+------+------+--------+ | 7369 | smith | clerk | 7902 | 1980-12-17 | 800 | NULL | 20 | +-------+-------+-------+------+------------+------+------+--------+ 1 row in set (0.01 sec) 改变个顺序看看。 mysql> select * from emp where empno=7788 -> union -> select * from emp where sal=5000 -> union -> select * from emp where ename='smith'; 效果一样。查看数据情况，7788，7839在同一分片节点3，而7369在节点2上，经过测试，如果三个请求的数据，在同一分片，能返回正常结果，通过改变顺序，结果还是不变，那么可以肯定这跟分片字段有关。这里empno就是分片字段。 mysql> explain select * from emp where empno=7369 union select* from emp where sal=5000 union select * from emp where empno=7788; +-----------+-------------------------------------------------------------------------------------------------------------------+ |DATA_NODE|SQL | +-----------+-------------------------------------------------------------------------------------------------------------------+ | dn1 | select * from emp where empno=7369 union select* from emp where sal=5000 union select * from emp where empno=7788 | | dn3 | select * from emp where empno=7369 union select* from emp where sal=5000 union select * from emp where empno=7788 | +-----------+-------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) 这样就正确了。如果没有分片字段，那么会发送给全部节点，结果也可以正确。 mysql> explain select * from emp where ename='smith' union select* from emp where sal=5000 union select * from emp where ename='allen'; +-----------+-------------------------------------------------------------------------------------------------------------------------+ |DATA_NODE|SQL | +-----------+-------------------------------------------------------------------------------------------------------------------------+ | dn1 | select * from emp where ename='smith' union select* from emp where sal=5000 union select * from emp where ename='allen' | | dn2 | select * from emp where ename='smith' union select* from emp where sal=5000 union select * from emp where ename='allen' | | dn3 | select * from emp where ename='smith' union select* from emp where sal=5000 union select * from emp where ename='allen' | | dn4 | select * from emp where ename='smith' union select* from emp where sal=5000 union select * from emp where ename='allen' | +-----------+-------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)
排序： mysql> select * from emp where ename='smith' union all select* from emp where sal=5000 union all select * from emp where ename='allen' order by empno; +-------+-------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+ | 7369 | smith | clerk | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7839 | king | president | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | +-------+-------+-----------+------+------------+------+------+--------+ 3 rows in set (0.01 sec) 使用empno排序，明显的有问题， mysql> select * from emp where ename='smith' union all select* from emp where sal=5000 union all select * from emp where ename='allen' order by deptno; +-------+-------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+ | 7369 | smith | clerk | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7839 | king | president | NULL | 1981-11-17 | 5000 | NULL | 10 | +-------+-------+-----------+------+------------+------+------+--------+ 3 rows in set (0.01 sec) 这个排序也有问题。
楼上的sql： 2. dual是oracle特有，可以通过在schemal.xml配置为全局表或普通表解决 5. 这些函数使用与否由应用自己决定，mycat不做处理 6.7涉及表连接，如果其中没有全局表或者ER，必须用sharjoin注解 8.这个是druid解析时候碰到识别不了会当做别名跳过
忘记说一点，emp表是分片，dept，salgrade，bonus均为全局表。 4.关于group by多条件，问题。 6、7表连接问题，建议多再测试测试，项目中，连接是很多的，毫无疑问，这是最根本的，mycat目前最应该考虑的问题，必须保证输出的结果是可信的，是正确的。如果不能，直接不支持也是可以的。
8.这个也必须要处理，不能SQL错的，也能执行，这就不科学了。基本的语法结构检查，关键字检查总要有的吧。
其他非主要问题，应该在手册中，详细说明。
本次仅仅简单为开发准备的基本SQL测试。望重视。mycat的根本在于，分布式，分片。什么大数据，都是浮云，那不是mycat的专项。一个中间只为完美解决一个问题。
mysql信息：mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1
mycat信息(version.txt)： BuildTime 2018-10-31 11:55:33 GitVersion null MavenVersion 1.6.6.1-release GitUrl https://github.com/MyCATApache/Mycat-Server.git MyCatSite http://www.mycat.org.cn QQGroup 106088787
不兼容问题： 1064 - unsupported mysql function expression: CURDATE()
在mycat 执行 insert into xxx values （xx,xx,xx,curdate()）出现上述问题。
在mysql正常执行。
似乎官网的权威指南没有提及这些使用的注意事项，也可能是我获取信息的能力不够强。
望指正，或者添加相关的需要注意的事项。
SELECT t.inv_detail_id, t.invoice_id, t.type, t.accounting_id, t.serv_detail, t.cust_id, t.acct_id, t.order_number, t.order_item_id, t.prod_inst_id, t.acc_nbr, t.prod_id, t.product_name, t.project_manager, t.cust_ref_no, t.crm_ref_no, t.end_user, t.begin_rent_date, t.stop_rent_date, t.rfs, t.service_destination, t.settlement_mode, t.income_type, t.bandwidth, t.agent, t.cost_type, t.cust_role, t.quote_ref_no, t.master_offer_id, t.master_offer_name, t.billing_cycle_id, t.pay_cycle_id, t.fee_cycle_id, t.org_id, t.vendor_name, t.vendor_circuit_id, t.vendor_type, t.contract_id, t.contract_order_ref_no, t.leasing_contract, t.pay_org_id, t.belong_org_id, t.handle_org_id, t.region_id, t.consignee_id, t.consignee_code, t.consignee_name, t.fee_currency_id, t.mrc_amount, t.nrc_amount, t.mrc_tax, t.nrc_tax, t.mrc_adjust_amount, t.flow_amount, t.voice_amount, t.sms_amount, t.amount, t.tax, t.hkc_amount, t.hkc_tax, t.base_currency_id, t.base_currency_amount, t.base_currency_tax, t.domestic_currency_id, t.domestic_currency_amount, t.domestic_currency_tax, t.region_currency_id, t.region_currency_amount, t.region_currency_tax, t.serv_begin_date, t.serv_end_date, t.dispute_flag, t.bad_debt_flag, t.income_flag, t.income_date, t.income_amount, t.income_staff, t.create_date, t.route_id, t.customer_manager, t.dispute_staff, t.dispute_date, t.dispute_reason, t.income_gst, t.order_source, t.create_staff, t.staff_id, t.access_no, t.sap_type, t.OPPOSITE_NBR, t.status_cd, t.re_detail_id, t.push_flag, t.push_date, t.push_log_id, t.is_mea, t.billing_id, t.base_currency_rate, t.region_currency_rate, t.domestic_currency_rate, t.hkc_rate, t.payment_date, t.overdue_flag, t.remark , '220607192553' as result_id FROM a_invoice_detail t where 1=1 order by t.inv_detail_id+0 limit 20000
Cause: java.sql.SQLException: The column 'inv_detail_id + 0' in order by clause should be in the selected column list.
; uncategorized SQLException; SQL state [HY000]; error code [1105]; The column 'inv_detail_id + 0' in order by clause should be in the selected column list.; nested exception is java.sql.SQLException: The column 'inv_detail_id + 0' in order by clause should be in the selected column list. 不懂，我本地测都没事，生产上去查这个mycat的表，报这个错，要我把inv_detail_id+0放select列表里？
