Query with parenthesis in where clause fails in Qserv :
mysql> SELECT sce.filterId, sce.filterName FROM Science_Ccd_Exposure AS sce WHERE (sce.visit = 887404831) AND (sce.raftName = '3,3') AND (sce.ccdName LIKE '%');
ERROR 4120 (Proxy): Error during execution: 'open failed for chunk(s): 7138 6800 6970read failed for chunk(s): 7310 7140 6631 7648 6630 6971 7478 7308 6968 6801'
whereas it returns a result without parentheses :
mysql> SELECT sce.filterId, sce.filterName FROM Science_Ccd_Exposure AS sce WHERE sce.visit = 887404831 AND sce.raftName = '3,3' AND sce.ccdName LIKE '%';
+----------+------------+
| filterId | filterName |
+----------+------------+
| 3 | i |
| 3 | i |
| 3 | i |
| 3 | i |
| 3 | i |
| 3 | i |
...
| 3 | i |
| 3 | i |
| 3 | i |
| 3 | i |
| 3 | i |
| 3 | i |
| 3 | i |
| 3 | i |
+----------+------------+
52 rows in set (1.49 sec)
Nevertheless, the previous query launched againt a simple mysql server returns only 4 rows :
mysql> SELECT sce.filterId, sce.filterName FROM Science_Ccd_Exposure AS sce WHERE (sce.visit = 887404831) AND (sce.raftName = '3,3') AND (sce.ccdName LIKE '%');
+----------+------------+
| filterId | filterName |
+----------+------------+
| 3 | i |
| 3 | i |
| 3 | i |
| 3 | i |
+----------+------------+
4 rows in set (0.01 sec)
Log files for the first query are available here :
Here’s an extract of xrootd.log :
TIMING,q_eb02QueryExecStart,1381412813
>>Unknown column 'OR_OP' in 'where clause' Unable to execute query: CREATE TABLE r_1709a07899f85014ec26dc6dd8a3b5560_7308_0 SELECT sce.filterId,sce.filterName FROM LSST.Science_Ccd_Exposure AS sce WHERE (OR_OP) AND(OR_OP) AND(OR_OP);
<<---Error with piece 0 complete (size=1).
TIMING,q_8b36QueryExecFinish,1381412813
Broken! ,q_8b36QueryExec---Unknown column 'OR_OP' in 'where clause' Unable to execute query: CREATE TABLE r_1709a07899f85014ec26dc6dd8a3b5560_7308_0 SELECT sce.filterId,sce.filterName FROM LSST.Science_Ccd_Exposure AS sce WHERE (OR_OP) AND(OR_OP) AND(OR_OP);
(during QueryExec)
QueryFragment: CREATE TABLE r_1709a07899f85014ec26dc6dd8a3b5560_7308_0 SELECT sce.filterId,sce.filterName FROM LSST.Science_Ccd_Exposure AS sce WHERE (OR_OP) AND(OR_OP) AND(OR_OP);
Here’s an extract of qserv-master.log :
Requesting squash 7138 because open=22 queryWrite=174 read=-38
Skipped merge (read failed for id=7138)
7138 QmFinalizeError Thu Oct 10 15:46:53 2013 5.1e-05
7138 QmFinalizeErase Thu Oct 10 15:46:53 2013 1.2e-05
7138 QmFinalizeResult Thu Oct 10 15:46:53 2013 2.2e-05
7138 QmFinalize Thu Oct 10 15:46:53 2013 0.000102
Requesting squash 7308 because open=24 queryWrite=174 read=-38
Skipped merge (read failed for id=7308)
Requesting squash 6801 because open=18 queryWrite=1747308 QmFinalizeError Thu Oct 10 15:46:53 2013 5.3e-05
7308 QmFinalizeErase Thu Oct 10 15:46:53 2013 1.5e-05
Please note that the same type of error seems to occur with query 2001_fullObjectScan.sql :
-- Full table scan for Object table with some cuts.
--
-- Similar queries:
--
-- * Find quasars
-- http://dev.lsstcorp.org/trac/wiki/dbQuery018
--
-- * Low-z QSO candidates using the color cuts
-- http://dev.lsstcorp.org/trac/wiki/dbQuery020
--
-- * Find high proper motion white dwarf candidates
-- http://dev.lsstcorp.org/trac/wiki/dbQuery026
--
-- * Find extremely red galaxies
-- http://dev.lsstcorp.org/trac/wiki/dbQuery037
SELECT objectId,
scisql_fluxToAbMag(uFlux_PS),
scisql_fluxToAbMag(gFlux_PS),
scisql_fluxToAbMag(rFlux_PS),
scisql_fluxToAbMag(iFlux_PS),
scisql_fluxToAbMag(zFlux_PS),
scisql_fluxToAbMag(yFlux_PS),
ra_PS, decl_PS
FROM Object
WHERE ( scisql_fluxToAbMag(gFlux_PS)-scisql_fluxToAbMag(rFlux_PS) > 0.7 OR
scisql_fluxToAbMag(gFlux_PS) > 22.3 )
AND scisql_fluxToAbMag(gFlux_PS)-scisql_fluxToAbMag(rFlux_PS) > 0.1
AND ( scisql_fluxToAbMag(rFlux_PS)-scisql_fluxToAbMag(iFlux_PS) <
(0.08 + 0.42 * (scisql_fluxToAbMag(gFlux_PS)-scisql_fluxToAbMag(rFlux_PS) - 0.96))
OR scisql_fluxToAbMag(gFlux_PS)-scisql_fluxToAbMag(rFlux_PS) > 1.26 )
AND scisql_fluxToAbMag(iFlux_PS)-scisql_fluxToAbMag(zFlux_PS) < 0.8
ORDER BY objectId