Re: Case sensitive issue with group by

Homer Sun, 03 Jul 2016 23:35:21 -0700

Julian,

thanks

I think I tried something like that on my path to here but ran into thisissue:


   select EX1.t1, count(*) from ex1 group by eX1.T1;

   Validate failed :From line 1, column 8 to line 1, column 13:
   Expression 'EX1.t1' is not being grouped

Maybe I need to go back and revisit this behavior in standard test

regards



On 2016-07-03 05:52 PM, Julian Hyde wrote:

I wouldn’t enable case-sensitivity by overriding SqlValidatorCatalogReader (as 
you seem to have done), because there’s too great a chance that you’ll miss 
something. I would set unquotedCasing = UNCHANGED, caseSensitive = false.

I added the following test to SqlValidatorTest and it worked fine:

@Test public void testCaseInsensitive2() {
   final SqlTester tester1 = tester
       .withCaseSensitive(false)
       .withUnquotedCasing(Casing.UNCHANGED);
   tester1.checkQuery("select deptno, count(*) from EMP group by DEPTNO");
}

Julian

On Jul 2, 2016, at 1:35 PM, Homer <[email protected]> wrote:

Hi,

I am sure it is me doing something wrong here in my parser/catalog reader setup 
but I am getting a validator issue around case sensitivity in group bys.

If I create a basic table like

   create table ex1 (t1 text);


and then insert a couple of values.

if I run the following group by query

   select t1, count(*) from ex1 group by t1;


All is fine

If i run this query

   select T1 from EX1;


All is fine and the case insensitive behavior works as expected

but if I run this query

   select t1, count(*) from ex1 group by T1;


I get an error from validator

   Validate failed :From line 1, column 8 to line 1, column 9:
   Expression 't1' is not being grouped


its probably worth noting that if I reverse the casing and run this query

   select T1, count(*) from ex1 group by t1;


All is fine, and the query works.

I have set all the parameters I can find appropriate in my parser (based on the 
csv example) for case insensitive matching

   @Override
      public RelDataTypeField field(RelDataType rowType, String alias) {
        return SqlValidatorUtil.lookupField(false, elideRecord, rowType,
                alias);
      }

      @Override
      public int fieldOrdinal(RelDataType rowType, String alias) {
        final RelDataTypeField field = field(rowType, alias);
        return field != null ? field.getIndex() : -1;
      }

      @Override
      public boolean matches(String string, String name) {
        return Util.matches(false, string, name);
      }

      @Override
      public int match(List<String> strings, String name) {
        return Util.findMatch(strings, name, false);
      }

      @Override
      public RelDataType createTypeFromProjection(final RelDataType type,
              final List<String> columnNameList) {
        return SqlValidatorUtil.createTypeFromProjection(type,
   columnNameList,
                typeFactory, false, elideRecord);
      }


Does anyone have an idea of what I am doing wrong here, it would appear the 
group by identifier matching is not respecting the case insensitive matching.

Any ideas greatly appreciated

Thanks

Re: Case sensitive issue with group by

Reply via email to