You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/11/19 06:19:27 UTC
[GitHub] [shardingsphere] warriorsliuz opened a new issue #13698: SQL statement is not work
warriorsliuz opened a new issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
SS 5.0
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
### Actual behavior
java.sql.SQLException:Error while preparing seatment
SELECT dd.FullDateAlternateKey dataTime, IFNULL(rr.innum,0) innum, IFNULL(rr.jkl,0.00) jkl
FROM dimdate dd
LEFT JOIN
(SELECT SUM(rsd.innum) innum,ROUND(SUM(rsd.innum)/SUM(rsd.area),2) jkl,rsd.datatime datatime
FROM report_site_day rsd
LEFT JOIN site_relation sr ON sr.childId=rsd.relationId
LEFT JOIN project_status ps ON rsd.relationId=ps.site_id
LEFT JOIN site_all_info sai ON rsd.relationId=sai.SiteId
LEFT JOIN privilege_userssite AS pu ON pu.sitekey = sai.siteid
LEFT JOIN userinsmallprogram AS us ON us.userid = pu.userid
WHERE
us.openid = ?
AND sr.id =?
AND ps.status=1
AND rsd.dataTime BETWEEN ? AND ?
GROUP BY rsd.datatime
)rr
ON dd.FullDateAlternateKey=rr.datatime
WHERE dd.FullDateAlternateKey BETWEEN ? AND ?
ORDER BY dd.FullDateAlternateKey ASC
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
### Example codes for reproduce this issue (such as a github link).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] warriorsliuz commented on issue #13698: SQL statement is not work
Posted by GitBox <gi...@apache.org>.
warriorsliuz commented on issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698#issuecomment-974579266
> java.sql.SQLException:Error while preparing seatment
@strongduanmu ：Intellij detailMessages “Error querying database. Cause: java.sql.SQLException: Error while preparing statement【SELECT dd.FullDateAlternateKey dataTime, IFNULL(rr.innum,0) innum, IFNULL(rr.jkl,0.00) jkl
FROM dimdate dd】“
and the SQL is not work，same problem
```
SELECT
site.sitename AS NAME,
IFNULL(rsd.innum, 0) AS VALUE
FROM
`report_site_day` rsd
RIGHT JOIN
(SELECT
sr.id,
sai.sitename
FROM
site_relation sr
INNER JOIN site_all_info sai
ON sr.ChildId = sai.siteid
WHERE sr.parent300 = 'QBGC'
AND sr.parent400 IS NULL
AND sr.parent500 IS NULL
AND sr.parent600 IS NULL
AND sai.type = 700) site
ON site.id = rsd.relationid
AND rsd.datatime = '2021-11-19'
ORDER BY rsd.innum DESC
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #13698: SQL statement is not work
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698#issuecomment-974579650
@warriorsliuz Can you add the necessary information according to the template? For example, your configuration, version, and statements that can reproduce the problem.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #13698: SQL statement is not work
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698#issuecomment-974758804
> I find this problem `org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature IFNULL(<NUMERIC>, <NUMERIC>)` ifnull is not support？
@warriorsliuz This bug is duplicated with issue #12049, and we will support it soon.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #13698: SQL statement is not work
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698#issuecomment-974994159
Duplicated with issue #12049.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #13698: SQL statement is not work
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698#issuecomment-974572880
@warriorsliuz Can you show more exception info?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] warriorsliuz commented on issue #13698: SQL statement is not work
Posted by GitBox <gi...@apache.org>.
warriorsliuz commented on issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698#issuecomment-974625987
I find this problem
`org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature IFNULL(<NUMERIC>, <NUMERIC>)`
ifnull is not support？
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #13698: SQL statement is not work
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] warriorsliuz commented on issue #13698: SQL statement is not work
Posted by GitBox <gi...@apache.org>.
warriorsliuz commented on issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698#issuecomment-974625987
I find this problem
`org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature IFNULL(<NUMERIC>, <NUMERIC>)`
ifnull is not support？
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #13698: SQL statement is not work
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698#issuecomment-974758804
> I find this problem `org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature IFNULL(<NUMERIC>, <NUMERIC>)` ifnull is not support？
@warriorsliuz This bug is duplicated with issue #12049, and we will support it soon.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] warriorsliuz edited a comment on issue #13698: SQL statement is not work
Posted by GitBox <gi...@apache.org>.
warriorsliuz edited a comment on issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698#issuecomment-974579266
> java.sql.SQLException:Error while preparing seatment
@strongduanmu ：Intellij detailMessages “Error querying database. Cause: java.sql.SQLException: Error while preparing statement【SELECT dd.FullDateAlternateKey dataTime, IFNULL(rr.innum,0) innum, IFNULL(rr.jkl,0.00) jkl
FROM dimdate dd .....】“
and the SQL is not work，same problem
```
SELECT
site.sitename AS NAME,
IFNULL(rsd.innum, 0) AS VALUE
FROM
`report_site_day` rsd
RIGHT JOIN
(SELECT
sr.id,
sai.sitename
FROM
site_relation sr
INNER JOIN site_all_info sai
ON sr.ChildId = sai.siteid
WHERE sr.parent300 = 'QBGC'
AND sr.parent400 IS NULL
AND sr.parent500 IS NULL
AND sr.parent600 IS NULL
AND sai.type = 700) site
ON site.id = rsd.relationid
AND rsd.datatime = '2021-11-19'
ORDER BY rsd.innum DESC
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu edited a comment on issue #13698: SQL statement is not work
Posted by GitBox <gi...@apache.org>.
strongduanmu edited a comment on issue #13698:
URL: https://github.com/apache/shardingsphere/issues/13698#issuecomment-974579650
@warriorsliuz Can you add the necessary information according to the template? For example, your configuration, version, and statements that can reproduce the problem. With existing information, it is difficult to reproduce the problem.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org