SA server version 9.0.2.3456 32-bit, client has 9.0.2.3886 32-bit on
win7 x64. I'm using the 32-bit jvm and 32-bit Eclipse for development,
so the entire query chain is 32-bit.

When I execute the following query in my application, I get the error
listed at the bottom. when executed throught isql or a 3rd-party odbc
database management tool, it works fine, so the problem appears to be
something with the jdbc-odbc bridge. Any suggestions? I have made sure
that the app is using the version of jodbc.jar from my ASA installation,
and not the one that came with the jre.

Any suggestions? I'm setting up to try this on xp 32-bit, but I'm not
there yet.


SELECT s.GrossSales, s.NetSales, s.SalesTemperature, s.DateGenerated,
s.SalesTypeKey, s.SalesKey, s.ObservationTime, s.FuelSysKey,
s.DataSummKey, s.Shift, getAllBlendedSales( s.datasummkey,
d.dategenerated, s.dategenerated, s.SalesTypeKey, 0 ) AS BlendSales,
getAllBlendedSales( s.datasummkey, d.dategenerated, s.dategenerated,
s.SalesTypeKey, 1 ) AS NetBlendSales
FROM Sales s JOIN DataSumm d ON d.DataSummKey = s.DataSummKey
WHERE s.DataSummKey in ( 622829, 623047, 623319, 623590, 623764, 624062,
624285, 624588, 624813 )
ORDER BY s.ObservationTime, s.Shift, s.DataSummKey, SalesTypeKey,
s.DateGenerated


java.sql.SQLException: [Sybase][ODBC Driver][Adaptive Server Anywhere]
Function or column reference to 'GROSSSALES' must also appear in a GROUP
BY

D

If you are using the Sun JDBC-ODBC bridge it is possible it may
be adding a DISTINCT or something else to the query. If not
there may be other components doing something like that (in, say,
a framework you may be using).

Running the database server with the '-z' and '-zr sql ' may tell
you more about the SQL hitting the server and the connection
involved.






"David Kerber" <ns_dkerber (AT) ns_warrenrogersassociates (DOT) com> wrote

In article <4b1995ef$1@forums-1-dub>, "Nick Elson [Sybase iAnywhere]"
<@nick@dot@elson@at@sybase@dot@com@> says...
I'm using the Sybase bridge: ianywhere.ml.jdbcodbc.IDriver, and more
testing has found that it doesn't always throw this error; sometimes it
works ok. So it looks like the optimizer is doing something funky to my
request.


ODBC tracing doesn't show anything useful: just the query and sometimes
the error response right after it, along with the miscellaneous data
being logged.

I'll see what the server logging turns up.

In article <MPG.25870feb5ea8bb1c989687 (AT) forums (DOT) sybase.com>,
ns_dkerber (AT) ns_warrenrogersassociates (DOT) com says...
Here's an excerpt from the "-zr sql entries in the server window; it
looks to me like the statement is getting a "COUNT(*)" added to the
front of the query field list, which is blowing it up. Why is it doing
that?


** DONE conn: 2 COMMIT

** REQUEST conn: 2 STMT_PREPARE [large string
unavailable]

** EXTRA conn: 2 SELECT s.GrossSales, s.NetSales,
s.SalesTemperature, s.DateGenerated, s.SalesTypeKey, s.SalesKey,
s.ObservationTime, s.FuelSysKey, s.DataSummKey, s.Shift,
getAllBlendedSales( s.datasummkey, d.dategenerated, s.dategenerated,
s.SalesTypeKey, 0 ) AS BlendSales, getAllBlendedSales( s.datasummkey,
d.dategenerated, s.dategenerated, s.SalesTypeKey, 1 ) AS NetBlendSales
FROM Sales s JOIN DataSumm d ON d.DataSummKey = s.DataSummKey WHERE
s.DataSummKey in ( 623624, 623866, 624084, 624414, 624665, 624912,
625124, 625216 ) ORDER BY s.ObservationTime, s.Shift, s.DataSummKey,
SalesTypeKey, s.DateGenerated

** DONE conn: 2 STMT_PREPARE Stmt=2426340

** REQUEST conn: 2 STMT_EXECUTE Stmt=-1

** WARNING conn: 2 code: 111 "Statement cannot be executed"

** DONE conn: 2 STMT_EXECUTE

** REQUEST conn: 2 CURSOR_OPEN Stmt=2426340

** DONE conn: 2 CURSOR_OPEN Crsr=2426341

** WARNING conn: 2 code: 100 "Row not found"

** REQUEST conn: 2 COMMIT

** DONE conn: 2 COMMIT

** REQUEST conn: 2 STMT_PREPARE [large string
unavailable]

** EXTRA conn: 2 SELECT COUNT(*), S.GROSSSALES, S.NETSALES,
S.SALESTEMPERATURE, S.DATEGENERATED, S.SALESTYPEKEY, S.SALESKEY,
S.OBSERVATIONTIME, S.FUELSYSKEY, S.DATASUMMKEY, S.SHIFT,
GETALLBLENDEDSALES( S.DATASUMMKEY, D.DATEGENERATED, S.DATEGENERATED,
S.SALESTYPEKEY, 0 ) AS BLENDSALES, GETALLBLENDEDSALES( S.DATASUMMKEY,
D.DATEGENERATED, S.DATEGENERATED, S.SALESTYPEKEY, 1 ) AS NETBLENDSALES
FROM SALES S JOIN DATASUMM D ON D.DATASUMMKEY = S.DATASUMMKEY WHERE
S.DATASUMMKEY IN ( 623624, 623866, 624084, 624414, 624665, 624912,
625124, 625216 )

** DONE conn: 2 STMT_PREPARE Stmt=2491878

** ERROR conn: 2 code: -149 "Function or column reference to
'GROSSSALES' must also appear in a GROUP BY"

** REQUEST conn: 2 STMT_DROP Stmt=2491878

** DONE conn: 2 STMT_DROP

** REQUEST conn: 2 COMMIT


D

Clearly a correct error due to thr aggregate function (count(*))
being added to the start of that query [see 2nd prepare in the
trace shown].

Not certain which piece of software is doing this, but pretty
certain it is not the optimizer of server (in general) since that
is a SQL request from the client application.



"David Kerber" <ns_dkerber (AT) ns_warrenrogersassociates (DOT) com> wrote

In article <4b1d6408$1@forums-1-dub>, "Nick Elson [Sybase iAnywhere]"
<@nick@dot@elson@at@sybase@dot@com@> says...
Yes, I realized that when I saw it.


That's what I figured after I saw the logs; the optimizer guess was from
a post before I got the request logs. I think it pretty much has to be
something in the jdbc-odbc bridge, or in odbc itself because that's
about all that is left. I'm using the ianywhere components for both
those pieces. I'm in the process of setting up another machine to test
with the latest ebf (3886) to see if that helps.

D

In article <MPG.258730ff4ce1d719989689 (AT) forums (DOT) sybase.com>,
ns_dkerber (AT) ns_warrenrogersassociates (DOT) com says...
Installing EBF 3886 on the client made no difference. And I did make
sure that I moved the jodbc.jar to where it needed to be for my app to
use it and not the old one, and that the new dbjodbc9.dll was also in
the correct place.

.....

D

I don't think this is coming from our client libraries or jars either.

I'd look to the Eclipse environment and (alternatively) possibly
another branch in your code.


"David Kerber" <ns_dkerber (AT) ns_warrenrogersassociates (DOT) com> wrote

In article <4b1d96ea$1@forums-1-dub>, "Nick Elson [Sybase iAnywhere]"
<@nick@dot@elson@at@sybase@dot@com@> says...
I know it's not coming from my code, because I've checked the sql being
submitted to the query execute method, and it doesn't have the count()
in it. In addition, a full text search of my code turns up no instances
of COUNT in any of my queries at all.

Just for grins, I added a DISTINCT to the beginning of my sql, and the -
z log on the server then kicked it out with:

** EXTRA conn: 1 SELECT COUNT(*), DISTINCT S.GROSSSALES,
S.NETSALES, S.SALESTEMPERATURE, S.DATEGENERATED, S.SALESTYPEKEY,
S.SALESKEY, S.OBSERVATIONTIME, S.FUELSYSKEY, S.DATASUMMKEY, S.SHIFT,
GETALLBLENDEDSALES( S.DATASUMMKEY, D.DATEGENERATED, S.DATEGENERATED,
S.SALESTYPEKEY, 0 ) AS BLENDSALES, GETALLBLENDEDSALES( S.DATASUMMKEY,
D.DATEGENERATED, S.DATEGENERATED, S.SALESTYPEKEY, 1 ) AS NETBLENDSALES
FROM SALES S JOIN DATASUMM D ON D.DATASUMMKEY = S.DATASUMMKEY WHERE
S.DATASUMMKEY IN ( 623921, 624123, 624337, 624559, 624855, 625026,
625228 )


Which is obviously bogus. So something between my code and the server
is adding a COUNT(*) to the beginning of my sql, even when it obviously
should not.

More digging is obviously indicated...

D

Neither the iAnywhere bridge nor the server itself uses this kind of SQL
injection because (obviously) the result is typically a
syntactically-invalid statement. Moreover, since 9.x is mature software
we would know about this type of problem if it was an issue with the
JDBC bridge. My guess is that something in a layer above the bridge (but
not in your own code) is injecting the COUNT() aggregate function.

A hypothesis is that there is a Java method that you are calling
inadvertently that is attempting to determine the size of the result set
(in rows) before actually executing the statement. Replacing the
original SELECT list with *only* COUNT(*) will accomplish this for query
specifications, even those containing GROUP BY (but not DISTINCT, nor
will it work for query expressions - UNION, EXCEPT, INTERSECT). A bug in
this code that failed to eliminate the SELECT list in all cases would
match your symptoms. In my experience, SQL parsing by Java toolkits is
done only via rudimentary string-matching, and not actual parsing, and
hence is prone to these types of bugs.

What I have seen in Java toolkits (with Eclipse) is the overloading of
JDBC API calls by utility JARs, including basic calls such as
ExecuteQuery() or CreateQuery(). I wonder if you have JARs in your
Eclipse environment that do this kind of thing.

Glenn

David Kerber wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport