java.sql.SQLException: Incorrect syntax near 'nulls'.

Spring JIRA | nuytsm | 3 years ago
  1. 0

    In spring-data-jpa 1.5.1 (in combination with querydsl 3.3.0) the sort on a property fails on "asc nulls last". For example; my query results in: {quote} /* select activityInfo from ActivityInfo activityInfo where activityInfo.inUse = ?1 and ( activityInfo.toDate < ?2 or activityInfo.toDate = ?2 or activityInfo.fromDate < ?2 and activityInfo.toDate > ?2 or activityInfo.toDate = ?2 or activityInfo.fromDate = ?2 or activityInfo.fromDate > ?2 or activityInfo.fromDate = ?2 or activityInfo.fromDate is null and activityInfo.toDate is null ) order by activityInfo.code asc nulls last */ select TOP(?) activityin0_.OPERATION_SID as OPERATIO1_4_, activityin0_.PERSON_SID_UPDATED_BY as PERSON_S2_4_, activityin0_.LASTUPDATETIME as LASTUPDA3_4_, activityin0_.CODE as CODE4_4_, activityin0_.DESCRIPTION as DESCRIPT5_4_, activityin0_.IN_USE as IN_USE6_4_, activityin0_.IS_COPS as IS_COPS7_4_, activityin0_.CABINET_DATE as CABINET_8_4_, activityin0_.DETCO_PERSONPROXY_SID as DETCO_P19_4_, activityin0_.FROM_DATE as FROM_DAT9_4_, activityin0_.AGPM_NEEDED as AGPM_NE10_4_, activityin0_.J8_REF_NUMBER as J11_4_, activityin0_.LAST_STATUS_UPDATE_DATE as LAST_ST12_4_, activityin0_.LEADUNIT_SID as LEADUNI20_4_, activityin0_.UNCLASS_INFO as UNCLASS13_4_, activityin0_.OPERATION_MODULE_SID as OPERATI21_4_, activityin0_.ORGANISATION_SID as ORGANIS22_4_, activityin0_.REGION_SID as REGION_23_4_, activityin0_.SCENARIO_INFO as SCENARI14_4_, activityin0_.status as status15_4_, activityin0_.STATUS_INFO as STATUS_16_4_, activityin0_.OPERATION_SUBMODULE_SID as OPERATI24_4_, activityin0_.TO_DATE as TO_DATE17_4_, activityin0_.VISA_NEEDED as VISA_NE18_4_, (select count(C.CE_ENTRY_SID) from T_CE_ENTRY c where c.OPERATION_SID = activityin0_.OPERATION_SID) as formula1_ from dbo.TC_OPERATION activityin0_ where activityin0_.IN_USE=? and ( activityin0_.TO_DATE<? or activityin0_.TO_DATE=? or activityin0_.FROM_DATE<? and activityin0_.TO_DATE>? or activityin0_.TO_DATE=? or activityin0_.FROM_DATE=? or activityin0_.FROM_DATE>? or activityin0_.FROM_DATE=? or ( activityin0_.FROM_DATE is null ) and ( activityin0_.TO_DATE is null ) ) order by *activityin0_.CODE asc nulls last* {quote} This results in an exception: {quote} DEBUG o.h.e.jdbc.spi.SqlExceptionHelper - could not extract ResultSet [n/a] java.sql.SQLException: Incorrect syntax near 'nulls'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) ~[jtds-1.2.6.jar:1.2.6] at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820) ~[jtds-1.2.6.jar:1.2.6] at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258) ~[jtds-1.2.6.jar:1.2.6] at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632) ~[jtds-1.2.6.jar:1.2.6] at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477) ~[jtds-1.2.6.jar:1.2.6] at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776) ~[jtds-1.2.6.jar:1.2.6] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_29] {quote} With spring data jpa 1.5.0, the query results in the following (works correctly): {quote} /* select activityInfo from ActivityInfo activityInfo where activityInfo.inUse = ?1 and ( activityInfo.toDate < ?2 or activityInfo.toDate = ?2 or activityInfo.fromDate < ?2 and activityInfo.toDate > ?2 or activityInfo.toDate = ?2 or activityInfo.fromDate = ?2 or activityInfo.fromDate > ?2 or activityInfo.fromDate = ?2 or activityInfo.fromDate is null and activityInfo.toDate is null ) order by activityInfo.code asc */ select TOP(?) activityin0_.OPERATION_SID as OPERATIO1_4_, activityin0_.PERSON_SID_UPDATED_BY as PERSON_S2_4_, activityin0_.LASTUPDATETIME as LASTUPDA3_4_, activityin0_.CODE as CODE4_4_, activityin0_.DESCRIPTION as DESCRIPT5_4_, activityin0_.IN_USE as IN_USE6_4_, activityin0_.IS_COPS as IS_COPS7_4_, activityin0_.CABINET_DATE as CABINET_8_4_, activityin0_.DETCO_PERSONPROXY_SID as DETCO_P19_4_, activityin0_.FROM_DATE as FROM_DAT9_4_, activityin0_.AGPM_NEEDED as AGPM_NE10_4_, activityin0_.J8_REF_NUMBER as J11_4_, activityin0_.LAST_STATUS_UPDATE_DATE as LAST_ST12_4_, activityin0_.LEADUNIT_SID as LEADUNI20_4_, activityin0_.UNCLASS_INFO as UNCLASS13_4_, activityin0_.OPERATION_MODULE_SID as OPERATI21_4_, activityin0_.ORGANISATION_SID as ORGANIS22_4_, activityin0_.REGION_SID as REGION_23_4_, activityin0_.SCENARIO_INFO as SCENARI14_4_, activityin0_.status as status15_4_, activityin0_.STATUS_INFO as STATUS_16_4_, activityin0_.OPERATION_SUBMODULE_SID as OPERATI24_4_, activityin0_.TO_DATE as TO_DATE17_4_, activityin0_.VISA_NEEDED as VISA_NE18_4_, (select count(C.CE_ENTRY_SID) from T_CE_ENTRY c where c.OPERATION_SID = activityin0_.OPERATION_SID) as formula0_ from dbo.TC_OPERATION activityin0_ where activityin0_.IN_USE=? and ( activityin0_.TO_DATE<? or activityin0_.TO_DATE=? or activityin0_.FROM_DATE<? and activityin0_.TO_DATE>? or activityin0_.TO_DATE=? or activityin0_.FROM_DATE=? or activityin0_.FROM_DATE>? or activityin0_.FROM_DATE=? or ( activityin0_.FROM_DATE is null ) and ( activityin0_.TO_DATE is null ) ) order by *activityin0_.CODE asc* {quote}

    Spring JIRA | 3 years ago | nuytsm
    java.sql.SQLException: Incorrect syntax near 'nulls'.
  2. 0

    In spring-data-jpa 1.5.1 (in combination with querydsl 3.3.0) the sort on a property fails on "asc nulls last". For example; my query results in: {quote} /* select activityInfo from ActivityInfo activityInfo where activityInfo.inUse = ?1 and ( activityInfo.toDate < ?2 or activityInfo.toDate = ?2 or activityInfo.fromDate < ?2 and activityInfo.toDate > ?2 or activityInfo.toDate = ?2 or activityInfo.fromDate = ?2 or activityInfo.fromDate > ?2 or activityInfo.fromDate = ?2 or activityInfo.fromDate is null and activityInfo.toDate is null ) order by activityInfo.code asc nulls last */ select TOP(?) activityin0_.OPERATION_SID as OPERATIO1_4_, activityin0_.PERSON_SID_UPDATED_BY as PERSON_S2_4_, activityin0_.LASTUPDATETIME as LASTUPDA3_4_, activityin0_.CODE as CODE4_4_, activityin0_.DESCRIPTION as DESCRIPT5_4_, activityin0_.IN_USE as IN_USE6_4_, activityin0_.IS_COPS as IS_COPS7_4_, activityin0_.CABINET_DATE as CABINET_8_4_, activityin0_.DETCO_PERSONPROXY_SID as DETCO_P19_4_, activityin0_.FROM_DATE as FROM_DAT9_4_, activityin0_.AGPM_NEEDED as AGPM_NE10_4_, activityin0_.J8_REF_NUMBER as J11_4_, activityin0_.LAST_STATUS_UPDATE_DATE as LAST_ST12_4_, activityin0_.LEADUNIT_SID as LEADUNI20_4_, activityin0_.UNCLASS_INFO as UNCLASS13_4_, activityin0_.OPERATION_MODULE_SID as OPERATI21_4_, activityin0_.ORGANISATION_SID as ORGANIS22_4_, activityin0_.REGION_SID as REGION_23_4_, activityin0_.SCENARIO_INFO as SCENARI14_4_, activityin0_.status as status15_4_, activityin0_.STATUS_INFO as STATUS_16_4_, activityin0_.OPERATION_SUBMODULE_SID as OPERATI24_4_, activityin0_.TO_DATE as TO_DATE17_4_, activityin0_.VISA_NEEDED as VISA_NE18_4_, (select count(C.CE_ENTRY_SID) from T_CE_ENTRY c where c.OPERATION_SID = activityin0_.OPERATION_SID) as formula1_ from dbo.TC_OPERATION activityin0_ where activityin0_.IN_USE=? and ( activityin0_.TO_DATE<? or activityin0_.TO_DATE=? or activityin0_.FROM_DATE<? and activityin0_.TO_DATE>? or activityin0_.TO_DATE=? or activityin0_.FROM_DATE=? or activityin0_.FROM_DATE>? or activityin0_.FROM_DATE=? or ( activityin0_.FROM_DATE is null ) and ( activityin0_.TO_DATE is null ) ) order by *activityin0_.CODE asc nulls last* {quote} This results in an exception: {quote} DEBUG o.h.e.jdbc.spi.SqlExceptionHelper - could not extract ResultSet [n/a] java.sql.SQLException: Incorrect syntax near 'nulls'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) ~[jtds-1.2.6.jar:1.2.6] at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820) ~[jtds-1.2.6.jar:1.2.6] at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258) ~[jtds-1.2.6.jar:1.2.6] at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632) ~[jtds-1.2.6.jar:1.2.6] at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477) ~[jtds-1.2.6.jar:1.2.6] at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776) ~[jtds-1.2.6.jar:1.2.6] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_29] {quote} With spring data jpa 1.5.0, the query results in the following (works correctly): {quote} /* select activityInfo from ActivityInfo activityInfo where activityInfo.inUse = ?1 and ( activityInfo.toDate < ?2 or activityInfo.toDate = ?2 or activityInfo.fromDate < ?2 and activityInfo.toDate > ?2 or activityInfo.toDate = ?2 or activityInfo.fromDate = ?2 or activityInfo.fromDate > ?2 or activityInfo.fromDate = ?2 or activityInfo.fromDate is null and activityInfo.toDate is null ) order by activityInfo.code asc */ select TOP(?) activityin0_.OPERATION_SID as OPERATIO1_4_, activityin0_.PERSON_SID_UPDATED_BY as PERSON_S2_4_, activityin0_.LASTUPDATETIME as LASTUPDA3_4_, activityin0_.CODE as CODE4_4_, activityin0_.DESCRIPTION as DESCRIPT5_4_, activityin0_.IN_USE as IN_USE6_4_, activityin0_.IS_COPS as IS_COPS7_4_, activityin0_.CABINET_DATE as CABINET_8_4_, activityin0_.DETCO_PERSONPROXY_SID as DETCO_P19_4_, activityin0_.FROM_DATE as FROM_DAT9_4_, activityin0_.AGPM_NEEDED as AGPM_NE10_4_, activityin0_.J8_REF_NUMBER as J11_4_, activityin0_.LAST_STATUS_UPDATE_DATE as LAST_ST12_4_, activityin0_.LEADUNIT_SID as LEADUNI20_4_, activityin0_.UNCLASS_INFO as UNCLASS13_4_, activityin0_.OPERATION_MODULE_SID as OPERATI21_4_, activityin0_.ORGANISATION_SID as ORGANIS22_4_, activityin0_.REGION_SID as REGION_23_4_, activityin0_.SCENARIO_INFO as SCENARI14_4_, activityin0_.status as status15_4_, activityin0_.STATUS_INFO as STATUS_16_4_, activityin0_.OPERATION_SUBMODULE_SID as OPERATI24_4_, activityin0_.TO_DATE as TO_DATE17_4_, activityin0_.VISA_NEEDED as VISA_NE18_4_, (select count(C.CE_ENTRY_SID) from T_CE_ENTRY c where c.OPERATION_SID = activityin0_.OPERATION_SID) as formula0_ from dbo.TC_OPERATION activityin0_ where activityin0_.IN_USE=? and ( activityin0_.TO_DATE<? or activityin0_.TO_DATE=? or activityin0_.FROM_DATE<? and activityin0_.TO_DATE>? or activityin0_.TO_DATE=? or activityin0_.FROM_DATE=? or activityin0_.FROM_DATE>? or activityin0_.FROM_DATE=? or ( activityin0_.FROM_DATE is null ) and ( activityin0_.TO_DATE is null ) ) order by *activityin0_.CODE asc* {quote}

    Spring JIRA | 3 years ago | nuytsm
    java.sql.SQLException: Incorrect syntax near 'nulls'.
  3. 0

    Error dbconnect Invalid column name - Question | Splunk Answers

    splunk.com | 1 year ago
    java.sql.SQLException: Invalid column name 'ModifieddateA'.
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Error dbconnect Invalid column name - Question | Splunk Answers

    splunk.com | 6 months ago
    java.sql.SQLException: Invalid column name 'ModifieddateA'.
  6. 0

    Database connection unexpectedly closed with Glassfish, jTDS and SQL Server 2008

    Stack Overflow | 5 years ago | fernacolo
    java.sql.SQLException: I/O Error: Connection reset by peer: socket write error

  1. ruifung 1 times, last 3 months ago
12 unregistered visitors
Not finding the right solution?
Take a tour to get the most out of Samebug.

Tired of useless tips?

Automated exception search integrated into your IDE

Root Cause Analysis

  1. java.sql.SQLException

    Incorrect syntax near 'nulls'.

    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic()
  2. jTDS
    JtdsPreparedStatement.executeQuery
    1. net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)[jtds-1.2.6.jar:1.2.6]
    2. net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)[jtds-1.2.6.jar:1.2.6]
    3. net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)[jtds-1.2.6.jar:1.2.6]
    4. net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)[jtds-1.2.6.jar:1.2.6]
    5. net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)[jtds-1.2.6.jar:1.2.6]
    6. net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776)[jtds-1.2.6.jar:1.2.6]
    6 frames
  3. Java RT
    NativeMethodAccessorImpl.invoke0
    1. sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)[na:1.6.0_29]
    1 frame