<commit_before>package org.sagebionetworks.table.cluster;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;

import org.junit.Before;
import org.junit.Test;
import org.sagebionetworks.collections.Transform;
import org.sagebionetworks.repo.model.dbo.dao.table.TableModelTestUtils;
import org.sagebionetworks.repo.model.table.ColumnModel;
import org.sagebionetworks.repo.model.table.ColumnType;
import org.sagebionetworks.table.query.ParseException;
import org.sagebionetworks.table.query.model.ColumnReference;
import org.sagebionetworks.table.query.model.Predicate;
import org.sagebionetworks.table.query.util.SqlElementUntils;

import com.google.common.base.Function;
import com.google.common.collect.Lists;

public class SQLQueryTest {
	
	private static final String DATE1 = "11-11-11";
	private static final String DATE1TIME = "1320969600000";

	private static final String DATE2TIME = "1298332800000";
	private static final String DATE2 = "11-02-22";

	Map<String, ColumnModel> columnNameToModelMap;
	List<ColumnModel> tableSchema;
	
	@Before
	public void before(){
		columnNameToModelMap = new HashMap<String, ColumnModel>();
		columnNameToModelMap.put("foo", TableModelTestUtils.createColumn(111L, "foo", ColumnType.STRING));
		columnNameToModelMap.put("has space", TableModelTestUtils.createColumn(222L, "has space", ColumnType.STRING));
		columnNameToModelMap.put("bar", TableModelTestUtils.createColumn(333L, "bar", ColumnType.STRING));
		columnNameToModelMap.put("foobar", TableModelTestUtils.createColumn(444L, "foobar", ColumnType.STRING));
		columnNameToModelMap.put("Foo", TableModelTestUtils.createColumn(555L, "Foo", ColumnType.STRING));
		columnNameToModelMap.put("datetype", TableModelTestUtils.createColumn(666L, "datetype", ColumnType.DATE));
		tableSchema = new ArrayList<ColumnModel>(columnNameToModelMap.values());
	}
	
	@Test
	public void testTranslateColumnReferenceNoRightHandSide() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("foo");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("C111", builder.toString());
	}
	
	@Test
	public void testTranslateColumnReferenceCaseSensitive() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("Foo");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("C555", builder.toString());
	}
	
	@Test
	public void testTranslateColumnReferenceTrim() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("Foo ");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("C555", builder.toString());
	}
	
	@Test 
	public void testTranslateColumnReferenceUnknownColumn() throws ParseException{
		try{
			ColumnReference columnReference = SqlElementUntils.createColumnReference("fake");
			StringBuilder builder = new StringBuilder();
			SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
			fail("this column does not exist so it should have failed.");
		}catch(IllegalArgumentException e){
			assertTrue(e.getMessage().contains("fake"));
		}
	}
	
	@Test
	public void testTranslateColumnReferenceWithRightHandSide() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("foo.bar");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("C111_bar", builder.toString());
	}
	
	@Test
	public void testTranslateColumnReferenceWithQuotes() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("\"has space\"");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("C222", builder.toString());
	}
	
	@Test
	public void testTranslateColumnReferenceRowId() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("ROW_ID");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("ROW_ID", builder.toString());
	}
	
	@Test
	public void testTranslateColumnReferenceRowVersionIgnoreCase() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("row_version");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("ROW_VERSION", builder.toString());
	}
	
	@Test
	public void testSelectStar() throws ParseException{
		SqlQuery translator = new SqlQuery("select * from syn123", tableSchema);
		assertEquals("SELECT * FROM T123", translator.getOutputSQL());
		assertFalse(translator.isAggregatedResult());
		assertNotNull(translator.getSelectColumnModels());
		assertEquals(translator.getSelectColumnModels().size(), 6);
		assertTrue(translator.getSelectColumnModels().containsAll(this.tableSchema));
	}
	@Test
	public void testSelectSingColumns() throws ParseException{
		SqlQuery translator = new SqlQuery("select foo from syn123", tableSchema);
		assertEquals("SELECT C111, ROW_ID, ROW_VERSION FROM T123", translator.getOutputSQL());
		assertFalse(translator.isAggregatedResult());
		ColumnModel cm = new ColumnModel();
		cm.setId("111");
		List<ColumnModel> expectedSelect = Arrays.asList(cm);
		assertEquals(expectedSelect, translator.getSelectColumnModels());
	}
	
	@Test
	public void testSelectMultipleColumns() throws ParseException{
		SqlQuery translator = new SqlQuery("select foo, bar from syn123", tableSchema);
		assertEquals("SELECT C111, C333, ROW_ID, ROW_VERSION FROM T123", translator.getOutputSQL());
		assertFalse(translator.isAggregatedResult());
		ColumnModel cm111 = new ColumnModel();
		cm111.setId("111");
		ColumnModel cm333 = new ColumnModel();
		cm333.setId("333");
		List<ColumnModel> expectedSelect = Arrays.asList(cm111, cm333);
		assertEquals(expectedSelect, translator.getSelectColumnModels());
	}
	
	@Test
	public void testSelectDistinct() throws ParseException{
		SqlQuery translator = new SqlQuery("select distinct foo, bar from syn123", tableSchema);
		assertEquals("SELECT DISTINCT C111, C333, ROW_ID, ROW_VERSION FROM T123", translator.getOutputSQL());
		assertFalse(translator.isAggregatedResult());
		ColumnModel cm111 = new ColumnModel();
		cm111.setId("111");
		ColumnModel cm333 = new ColumnModel();
		cm333.setId("333");
		List<ColumnModel> expectedSelect = Arrays.asList(cm111, cm333);
		assertEquals(expectedSelect, translator.getSelectColumnModels());
	}
	
	@Test
	public void testSelectCountStar() throws ParseException{
		SqlQuery translator = new SqlQuery("select count(*) from syn123", tableSchema);
		assertEquals("SELECT COUNT(*) FROM T123", translator.getOutputSQL());
		assertTrue(translator.isAggregatedResult());
		assertTrue(translator.getSelectColumnModels().isEmpty());
	}
	
	@Test
	public void testSelectAggregate() throws ParseException{
		SqlQuery translator = new SqlQuery("select avg(foo) from syn123", tableSchema);
		assertEquals("SELECT AVG(C111) FROM T123", translator.getOutputSQL());
		assertTrue(translator.isAggregatedResult());
		assertTrue(translator.getSelectColumnModels().isEmpty());
	}
	
	@Test
	public void testSelectAggregateMultiple() throws ParseException{
		SqlQuery translator = new SqlQuery("select avg(foo), max(bar) from syn123", tableSchema);
		assertEquals("SELECT AVG(C111), MAX(C333) FROM T123", translator.getOutputSQL());
		assertTrue(translator.isAggregatedResult());
		assertTrue(translator.getSelectColumnModels().isEmpty());<commit_msg>test fix<commit_after>package org.sagebionetworks.table.cluster;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.junit.Before;
import org.junit.Test;
import org.sagebionetworks.repo.model.dbo.dao.table.TableModelTestUtils;
import org.sagebionetworks.repo.model.table.ColumnModel;
import org.sagebionetworks.repo.model.table.ColumnType;
import org.sagebionetworks.table.query.ParseException;
import org.sagebionetworks.table.query.model.ColumnReference;
import org.sagebionetworks.table.query.model.Predicate;
import org.sagebionetworks.table.query.util.SqlElementUntils;

public class SQLQueryTest {
	
	private static final String DATE1 = "11-11-11";
	private static final String DATE1TIME = "1320969600000";

	private static final String DATE2TIME = "1298332800000";
	private static final String DATE2 = "11-02-22";

	Map<String, ColumnModel> columnNameToModelMap;
	List<ColumnModel> tableSchema;
	
	@Before
	public void before(){
		columnNameToModelMap = new HashMap<String, ColumnModel>();
		columnNameToModelMap.put("foo", TableModelTestUtils.createColumn(111L, "foo", ColumnType.STRING));
		columnNameToModelMap.put("has space", TableModelTestUtils.createColumn(222L, "has space", ColumnType.STRING));
		columnNameToModelMap.put("bar", TableModelTestUtils.createColumn(333L, "bar", ColumnType.STRING));
		columnNameToModelMap.put("foobar", TableModelTestUtils.createColumn(444L, "foobar", ColumnType.STRING));
		columnNameToModelMap.put("Foo", TableModelTestUtils.createColumn(555L, "Foo", ColumnType.STRING));
		columnNameToModelMap.put("datetype", TableModelTestUtils.createColumn(666L, "datetype", ColumnType.DATE));
		tableSchema = new ArrayList<ColumnModel>(columnNameToModelMap.values());
	}
	
	@Test
	public void testTranslateColumnReferenceNoRightHandSide() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("foo");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("C111", builder.toString());
	}
	
	@Test
	public void testTranslateColumnReferenceCaseSensitive() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("Foo");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("C555", builder.toString());
	}
	
	@Test
	public void testTranslateColumnReferenceTrim() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("Foo ");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("C555", builder.toString());
	}
	
	@Test 
	public void testTranslateColumnReferenceUnknownColumn() throws ParseException{
		try{
			ColumnReference columnReference = SqlElementUntils.createColumnReference("fake");
			StringBuilder builder = new StringBuilder();
			SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
			fail("this column does not exist so it should have failed.");
		}catch(IllegalArgumentException e){
			assertTrue(e.getMessage().contains("fake"));
		}
	}
	
	@Test
	public void testTranslateColumnReferenceWithRightHandSide() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("foo.bar");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("C111_bar", builder.toString());
	}
	
	@Test
	public void testTranslateColumnReferenceWithQuotes() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("\"has space\"");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("C222", builder.toString());
	}
	
	@Test
	public void testTranslateColumnReferenceRowId() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("ROW_ID");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("ROW_ID", builder.toString());
	}
	
	@Test
	public void testTranslateColumnReferenceRowVersionIgnoreCase() throws ParseException{
		ColumnReference columnReference = SqlElementUntils.createColumnReference("row_version");
		StringBuilder builder = new StringBuilder();
		SQLTranslatorUtils.translate(columnReference, builder, columnNameToModelMap);
		assertEquals("ROW_VERSION", builder.toString());
	}
	
	@Test
	public void testSelectStar() throws ParseException{
		SqlQuery translator = new SqlQuery("select * from syn123", tableSchema);
		assertEquals("SELECT * FROM T123", translator.getOutputSQL());
		assertFalse(translator.isAggregatedResult());
		assertNotNull(translator.getSelectColumnModels());
		assertEquals(translator.getSelectColumnModels().size(), 6);
		assertTrue(translator.getSelectColumnModels().containsAll(this.tableSchema));
	}
	@Test
	public void testSelectSingColumns() throws ParseException{
		SqlQuery translator = new SqlQuery("select foo from syn123", tableSchema);
		assertEquals("SELECT C111, ROW_ID, ROW_VERSION FROM T123", translator.getOutputSQL());
		assertFalse(translator.isAggregatedResult());
		List<ColumnModel> expectedSelect = Arrays.asList(columnNameToModelMap.get("foo"));
		assertEquals(expectedSelect, translator.getSelectColumnModels());
	}
	
	@Test
	public void testSelectMultipleColumns() throws ParseException{
		SqlQuery translator = new SqlQuery("select foo, bar from syn123", tableSchema);
		assertEquals("SELECT C111, C333, ROW_ID, ROW_VERSION FROM T123", translator.getOutputSQL());
		assertFalse(translator.isAggregatedResult());
		List<ColumnModel> expectedSelect = Arrays.asList(columnNameToModelMap.get("foo"), columnNameToModelMap.get("bar"));
		assertEquals(expectedSelect, translator.getSelectColumnModels());
	}
	
	@Test
	public void testSelectDistinct() throws ParseException{
		SqlQuery translator = new SqlQuery("select distinct foo, bar from syn123", tableSchema);
		assertEquals("SELECT DISTINCT C111, C333, ROW_ID, ROW_VERSION FROM T123", translator.getOutputSQL());
		assertFalse(translator.isAggregatedResult());
		List<ColumnModel> expectedSelect = Arrays.asList(columnNameToModelMap.get("foo"), columnNameToModelMap.get("bar"));
		assertEquals(expectedSelect, translator.getSelectColumnModels());
	}
	
	@Test
	public void testSelectCountStar() throws ParseException{
		SqlQuery translator = new SqlQuery("select count(*) from syn123", tableSchema);
		assertEquals("SELECT COUNT(*) FROM T123", translator.getOutputSQL());
		assertTrue(translator.isAggregatedResult());
		assertTrue(translator.getSelectColumnModels().isEmpty());
	}
	
	@Test
	public void testSelectAggregate() throws ParseException{
		SqlQuery translator = new SqlQuery("select avg(foo) from syn123", tableSchema);
		assertEquals("SELECT AVG(C111) FROM T123", translator.getOutputSQL());
		assertTrue(translator.isAggregatedResult());
		assertTrue(translator.getSelectColumnModels().isEmpty());
	}
	
	@Test
	public void testSelectAggregateMultiple() throws ParseException{
		SqlQuery translator = new SqlQuery("select avg(foo), max(bar) from syn123", tableSchema);
		assertEquals("SELECT AVG(C111), MAX(C333) FROM T123", translator.getOutputSQL());
		assertTrue(translator.isAggregatedResult());
		assertTrue(translator.getSelectColumnModels().isEmpty());
{'fraction_non_alphanumeric': 0.1076051779935275, 'fraction_numerical': 0.02084889220811551, 'mean_word_length': 7.466280295047419, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3114231', 'n_tokens_mistral': 4911, 'n_tokens_neox': 4409, 'n_words': 933}